{"id":1585,"date":"2024-05-08T14:54:32","date_gmt":"2024-05-08T06:54:32","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=1585"},"modified":"2024-05-08T14:54:32","modified_gmt":"2024-05-08T06:54:32","slug":"sqlloader-sqlldr-spool-%e6%95%b0%e6%8d%ae%e5%af%bc%e5%87%ba%e5%af%bc%e5%85%a5%e7%9a%84%e4%b8%80%e7%82%b9%e5%b0%8f%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=1585","title":{"rendered":"sqlloader \/sqlldr \/spool \u6570\u636e\u5bfc\u51fa\u5bfc\u5165\u7684\u4e00\u70b9\u5c0f\u603b\u7ed3"},"content":{"rendered":"

`SPOOL\u3001SQLLOADER\u6570\u636e\u5bfc\u51fa\u5bfc\u5165\u7684\u4e00\u70b9\u5c0f\u603b\u7ed3
\nSQLLOADER\u7684\u547d\u4ee4\uff1a
\nSQLLDR USERID=SYS\/SYS@DB_SERVICE CONTROL=XXXX.CTL LOG=XXXX.LOG BINDSIZE=1048576 ROWS=100
\nERRORS=10000 READSIZE=2097152 SILENT=(HEADER,FEEDBACK)<\/p>\n

1\u3001SQLLOADER\u7684CONTROL\u6587\u4ef6
\n\/\/****<\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>\/\/
\n\u57fa\u672c\u683c\u5f0f\uff1a
\nLOAD DATA
\nINFILE 'T.DAT' \/\/ \u8981\u5bfc\u5165\u7684\u6570\u636e\u6587\u4ef6(\u683c\u5f0f1)
\n\/\/INFILE 'TT.DAT' \/\/ \u5bfc\u5165\u591a\u4e2a\u6587\u4ef6(\u53ef\u4ee5\u548c\u683c\u5f0f1\u5e76\u5217\u4f7f\u7528)
\n\/\/INFILE * \/\/ \u8981\u5bfc\u5165\u7684\u5185\u5bb9\u5c31\u5728CONTROL\u6587\u4ef6\u91cc \u4e0b\u9762\u7684BEGINDATA\u540e\u9762\u5c31\u662f\u5bfc\u5165\u7684\u5185\u5bb9(\u548c\u683c\u5f0f1\u4e92\u65a5\u4f7f\u7528) <\/p>\n

APPEND INTO TABLE TABLE_NAME \/\/ \u6307\u5b9a\u88c5\u5165\u7684\u8868(\u8fd9\u91cc\u6709\u51e0\u79cd\u52a0\u8f7d\u65b9\u5f0f) <\/p>\n

\/\/\u4ee5\u4e0b\u662f4\u79cd\u88c5\u5165\u8868\u7684\u65b9\u5f0f
\n\/\/APPEND \/\/ \u539f\u5148\u7684\u8868\u6709\u6570\u636e\u5c31\u52a0\u5728\u540e\u9762
\n\/\/ INSERT \/\/ \u88c5\u8f7d\u7a7a\u8868,\u5982\u679c\u539f\u5148\u7684\u8868\u6709\u6570\u636eSQLLOADER\u4f1a\u505c\u6b62\u9ed8\u8ba4\u503c
\n\/\/ REPLACE \/\/ \u539f\u5148\u7684\u8868\u6709\u6570\u636e \u539f\u5148\u7684\u6570\u636e\u4f1a\u5168\u90e8\u5220\u9664
\n\/\/ TRUNCATE \/\/ \u6307\u5b9a\u7684\u5185\u5bb9\u548cREPLACE\u7684\u76f8\u540c \u4f1a\u7528TRUNCATE\u8bed\u53e5\u5220\u9664\u73b0\u5b58\u6570\u636e <\/p>\n

BADFILE 'C:BAD.TXT' \/\/ \u6307\u5b9a\u574f\u6587\u4ef6\u5730\u5740 <\/p>\n

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
\n\/\/ \u88c5\u8f7d\u8fd9\u79cd\u6570\u636e: "10","20","30","40","50"
\n\/\/ TERMINATED BY X'09' \/\/ \u4ee5\u5341\u516d\u8fdb\u5236\u683c\u5f0f'09'\u8868\u793a\u6587\u672c\u6587\u4ef6\u7528TAB\u952e\u5206\u9694
\n\/\/ \u793a\u4f8b\u6587\u672c\u6570\u636e: "10" "20" "30" "40" "50"
\n\/\/ TERMINATED BY WHITESPACE \/\/ \u88c5\u8f7d\u8fd9\u79cd\u6570\u636e: "10" "lg" "lg" <\/p>\n

TRAILING NULLCOLS *****<\/strong><\/strong> \u8868\u7684\u5b57\u6bb5\u6ca1\u6709\u5bf9\u5e94\u7684\u503c\u65f6\u5141\u8bb8\u4e3a\u7a7a<\/p>\n

*****<\/strong><\/strong> \u4e0b\u9762\u662f\u8868\u7684\u5b57\u6bb5
\n(COL_1 , COL_2 ,COL_FILLER FILLER \/\/ FILLER \u5173\u952e\u5b57 \u6b64\u5217\u7684\u6570\u503c\u4e0d\u4f1a\u88ab\u88c5\u8f7d)<\/p>\n

\/\/\u6307\u5b9a\u7684TERMINATED\u53ef\u4ee5\u5728\u8868\u7684\u5f00\u5934 \u4e5f\u53ef\u5728\u8868\u7684\u5185\u90e8\u5b57\u6bb5\u90e8\u5206
\n\/\/ \u5f53\u6ca1\u58f0\u660eFIELDS TERMINATED BY ',' \u65f6\u4e5f\u53ef\u4ee5\u9010\u4e2a\u5b57\u6bb5\u6765\u58f0\u660e
\n\/\/ (
\n\/\/ COL_1 [INTERGER EXTERNAL] TERMINATED BY ',' ,
\n\/\/ COL_2 [DATE "DD-MON-YYY"] TERMINATED BY ',' ,
\n\/\/ COL_3 [CHAR] TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
\n\/\/ )
\n\/\/ \u5f53\u6ca1\u58f0\u660eFIELDS TERMINATED BY ','\u7528\u4f4d\u7f6e\u544a\u8bc9\u5b57\u6bb5\u88c5\u8f7d\u6570\u636e
\n\/\/ (
\n\/\/ COL_1 POSITION(1:2),
\n\/\/ COL_2 POSITION(3:10),
\n\/\/ COL_3 POSITION(*:16), \/\/ \u8fd9\u4e2a\u5b57\u6bb5\u7684\u5f00\u59cb\u4f4d\u7f6e\u5728\u524d\u4e00\u5b57\u6bb5\u7684\u7ed3\u675f\u4f4d\u7f6e
\n\/\/ COL_4 POSITION(3:10) CHAR(8), \/\/ \u6307\u5b9a\u5b57\u6bb5\u7684\u7c7b\u578b
\n\/\/ COL_5 POSITION(3:10) "TRIM(:COL_5)", \/\/ \u6324\u538b\u4e24\u7aef\u7a7a\u683c
\n\/\/ COL_6 POSITION(3:10) "SEQ.NEXTVAL", \/\/ \u53d6SEQUENCE\u503c
\n\/\/ ) <\/p>\n

BEGINDATA \/\/ \u5bf9\u5e94\u5f00\u59cb\u7684 INFILE * \u8981\u5bfc\u5165\u7684\u5185\u5bb9\u5c31\u5728CONTROL\u6587\u4ef6\u91cc
\n10,20,30
\n20,30,40
\n\/\/****<\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>\/\/
\nCONTROL\u6587\u4ef6\u793a\u4f8b:
\n\/\/\u6ce8\u610fBEGINDATA\u540e\u7684\u6570\u503c\u524d\u9762\u4e0d\u80fd\u6709\u7a7a\u683c <\/p>\n

1 **** \u666e\u901a\u88c5\u8f7d
\nLOAD DATA
\nINFILE <\/em>
\nREPLACE INTO TABLE DEPT
\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
\n(DEPTNO,
\nDNAME,
\nLOC
\n)
\nBEGINDATA
\n10,SALES,"""USA"""
\n20,ACCOUNTING,"VIRGINIA,USA"
\n30,CONSULTING,VIRGINIA
\n40,FINANCE,VIRGINIA
\n50,"FINANCE","",VIRGINIA \/\/ LOC \u5217\u5c06\u4e3a\u7a7a
\n60,"FINANCE",,VIRGINIA \/\/ LOC \u5217\u5c06\u4e3a\u7a7a <\/p>\n

2 **** FIELDS TERMINATED BY WHITESPACE \u548c FIELDS TERMINATED BY X'09' \u7684\u60c5\u51b5
\nLOAD DATA
\nINFILE <\/em>
\nREPLACE INTO TABLE DEPT
\nFIELDS TERMINATED BY WHITESPACE
\n-- FIELDS TERMINATED BY X'09'
\n(DEPTNO,
\nDNAME,
\nLOC
\n)
\nBEGINDATA
\n10 Sales Virginia <\/p>\n

3 **** \u6307\u5b9a\u4e0d\u88c5\u8f7d\u90a3\u4e00\u5217
\nLOAD DATA
\nINFILE <\/em>
\nREPLACE INTO TABLE DEPT
\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
\n( DEPTNO,
\nFILLER_1 FILLER, \/\/ \u4e0b\u9762\u7684 "Something Not To Be Loaded" \u5c06\u4e0d\u4f1a\u88ab\u88c5\u8f7d
\nDNAME,
\nLOC
\n)
\nBEGINDATA
\n20,Something Not To Be Loaded,Accounting,"Virginia,USA" <\/p>\n

4 **** POSITION\u7684\u5217\u5b50
\nLOAD DATA
\nINFILE <\/em>
\nREPLACE INTO TABLE DEPT
\n( DEPTNO POSITION(1:2),
\nDNAME POSITION(:16), \/\/ \u8fd9\u4e2a\u5b57\u6bb5\u7684\u5f00\u59cb\u4f4d\u7f6e\u5728\u524d\u4e00\u5b57\u6bb5\u7684\u7ed3\u675f\u4f4d\u7f6e
\nLOC POSITION(<\/em>:29),
\nENTIRE_LINE POSITION(1:29)
\n)
\nBEGINDATA
\n10ACCOUNTING VIRGINIA,USA <\/p>\n

5 **** \u4f7f\u7528\u51fd\u6570\u65e5\u671f\u7684\u4e00\u79cd\u8868\u8fbe TRAILING NULLCOLS\u7684\u4f7f\u7528
\nLOAD DATA
\nINFILE <\/em>
\nREPLACE INTO TABLE DEPT
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS \/\/ \u5176\u5b9e\u4e0b\u9762\u7684ENTIRE_LINE\u5728BEGINDATA\u540e\u9762\u7684\u6570\u636e\u4e2d\u662f\u6ca1\u6709\u76f4\u63a5\u5bf9\u5e94
\n\/\/ \u7684\u5217\u7684\u503c \u5982\u679c\u7b2c\u4e00\u884c\u6539\u4e3a 10,Sales,Virginia,1\/5\/2000,, \u5c31\u4e0d\u7528TRAILING NULLCOLS\u4e86
\n(DEPTNO,
\nDNAME "UPPER(NAME)", \/\/ \u4f7f\u7528\u51fd\u6570
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED DATE 'DD\/MM\/YYYY', \/\/ \u65e5\u671f\u7684\u4e00\u79cd\u8868\u8fbe\u65b9\u5f0f \u8fd8\u6709'DD-MON-YYYY' \u7b49
\nENTIRE_LINE "EPTNO||NAME||:LOC||:LAST_UPDATED"
\n)
\nBEGINDATA
\n10,Sales,Virginia,1\/5\/2000
\n20,Accounting,Virginia,21\/6\/1999
\n30,Consulting,Virginia,5\/1\/2000
\n40,Finance,Virginia,15\/3\/2001 <\/p>\n

6 ***** \u4f7f\u7528\u81ea\u5b9a\u4e49\u7684\u51fd\u6570 \/\/ \u89e3\u51b3\u7684\u65f6\u95f4\u95ee\u9898
\nCREATE OR REPLACE
\nFUNCTION MY_TO_DATE( P_STRING IN VARCHAR2 ) RETURN DATE
\nAS
\nTYPE FMTARRAY IS TABLE OF VARCHAR2(25); <\/p>\n

L_FMTS FMTARRAY := FMTARRAY( 'DD-MON-YYYY', 'DD-MONTH-YYYY',
\n'DD\/MM\/YYYY',
\n'DD\/MM\/YYYY HH24:MI:SS' );
\nL_RETURN DATE;
\nBEGIN
\nFOR I IN 1 .. L_FMTS.COUNT
\nLOOP
\nBEGIN
\nL_RETURN := TO_DATE( P_STRING, L_FMTS(I) );
\nEXCEPTION
\nWHEN OTHERS THEN NULL;
\nEND;
\nEXIT WHEN L_RETURN IS NOT NULL;
\nEND LOOP; <\/p>\n

IF ( L_RETURN IS NULL )
\nTHEN
\nL_RETURN :=
\nNEW_TIME( TO_DATE('01011970','DDMMYYYY') + 1\/24\/60\/60 *
\nP_STRING, 'GMT', 'EST' );
\nEND IF; <\/p>\n

RETURN L_RETURN;
\nEND;
\n\/ <\/p>\n

LOAD DATA
\nINFILE *
\nREPLACE INTO TABLE DEPT
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )" \/\/ \u4f7f\u7528\u81ea\u5b9a\u4e49\u7684\u51fd\u6570
\n)
\nBEGINDATA
\n10,Sales,Virginia,01-april-2001
\n20,Accounting,Virginia,13\/04\/2001
\n30,Consulting,Virginia,14\/04\/2001 12:02:02
\n40,Finance,Virginia,987268297
\n50,Finance,Virginia,02-apr-2001
\n60,Finance,Virginia,Not a date <\/p>\n

7 **** \u5408\u5e76\u591a\u884c\u8bb0\u5f55\u4e3a\u4e00\u884c\u8bb0\u5f55
\nLOAD DATA
\nINFILE <\/em>
\nCONCATENATE 3 \/\/ \u901a\u8fc7\u5173\u952e\u5b57CONCATENATE \u628a\u51e0\u884c\u7684\u8bb0\u5f55\u770b\u6210\u4e00\u884c\u8bb0\u5f55
\nINTO TABLE DEPT
\nREPLACE \/\/\u6ce8\u610f\u8fd9\u4e2a\u4f8b\u5b50\u683c\u5f0f\u4e0e\u524d\u8fb9\u6709\u4e9b\u4e0d\u540c
\nFIELDS TERMINATED BY ','
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED DATE 'DD\/MM\/YYYY'
\n)
\nBEGINDATA
\n10,Sales, \/\/ \u5176\u5b9e\u8fd93\u884c\u770b\u6210\u4e00\u884c 10,Sales,Virginia,1\/5\/2000
\nVirginia,
\n1\/5\/2000
\n\/\/ \u8fd9\u5217\u5b50\u7528 CONTINUEIF LIST="," \u4e5f\u53ef\u4ee5
\n\u544a\u8bc9SQLLDR\u5728\u6bcf\u884c\u7684\u672b\u5c3e\u627e\u9017\u53f7 \u627e\u5230\u9017\u53f7\u5c31\u628a\u4e0b\u4e00\u884c\u9644\u52a0\u5230\u4e0a\u4e00\u884c <\/p>\n

LOAD DATA
\nINFILE *
\nCONTINUEIF THIS(1:1) = '-' \/\/ \u627e\u6bcf\u884c\u7684\u5f00\u59cb\u662f\u5426\u6709\u8fde\u63a5\u5b57\u7b26 - \u6709\u5c31\u628a\u4e0b\u4e00\u884c\u8fde\u63a5\u4e3a\u4e00\u884c
\n\/\/ \u5982 -10,Sales,Virginia,
\n\/\/ 1\/5\/2000 \u5c31\u662f\u4e00\u884c 10,Sales,Virginia,1\/5\/2000
\n\/\/ \u5176\u4e2d1:1 \u8868\u793a\u4ece\u7b2c\u4e00\u884c\u5f00\u59cb \u5e76\u5728\u7b2c\u4e00\u884c\u7ed3\u675f \u8fd8\u6709CONTINUEIF NEXT \u4f46CONTINUEIF LIST\u6700\u7406\u60f3
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ','
\n(DEPTNO,
\nDNAME "upper(:dname)",
\nLOC "upper(:loc)",
\nLAST_UPDATED date 'dd\/mm\/yyyy'
\n)
\nBEGINDATA \/\/ \u4f46\u662f\u597d\u8c61\u4e0d\u80fd\u8c61\u53f3\u9762\u7684\u90a3\u6837\u4f7f\u7528
\n-10,Sales,Virginia, -10,Sales,Virginia,
\n1\/5\/2000 1\/5\/2000
\n-40, 40,Finance,Virginia,13\/04\/2001
\nFinance,Virginia,13\/04\/2001<\/p>\n

8 **** \u8f7d\u5165\u6bcf\u884c\u7684\u884c\u53f7
\nLOAD DATA
\nINFILE <\/em>
\nINTO TABLE T
\nREPLACE
\n( SEQNO RECNUM \/\/\u8f7d\u5165\u6bcf\u884c\u7684\u884c\u53f7
\nTEXT POSITION(1:1024))
\nBEGINDATA
\nfsdfasj \/\/\u81ea\u52a8\u5206\u914d\u4e00\u884c\u53f7\u7ed9\u8f7d\u5165 \u8868t \u7684seqno\u5b57\u6bb5 \u6b64\u884c\u4e3a 1
\nfasdjfasdfl \/\/ \u6b64\u884c\u4e3a 2 ... <\/p>\n

9 **** \u8f7d\u5165\u6709\u6362\u884c\u7b26\u7684\u6570\u636e
\n\u6ce8\u610f: UNIX \u548c WINDOWS \u4e0d\u540c n & \/n
\n< 1 > \u4f7f\u7528\u4e00\u4e2a\u975e\u6362\u884c\u7b26\u7684\u5b57\u7b26
\nLOAD DATA
\nINFILE <\/em>
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
\nCOMMENTS "REPLACE(:COMMENTS,'N',CHR(10))" \/\/ REPLACE \u7684\u4f7f\u7528\u5e2e\u52a9\u8f6c\u6362\u6362\u884c\u7b26
\n)
\nBEGINDATA
\n10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
\n20,Accounting,Virginia,13\/04\/2001,This is the AccountingnOffice in Virginia
\n30,Consulting,Virginia,14\/04\/2001 12:02:02,This is the ConsultingnOffice in Virginia
\n40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia <\/p>\n

< 2 > \u4f7f\u7528fix\u5c5e\u6027
\nLOAD DATA
\nINFILE DEMO17.DAT "FIX 101"
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
\nCOMMENTS
\n)
\nBEGINDATA
\n10,Sales,Virginia,01-april-2001,This is the Sales
\nOffice in Virginia
\n20,Accounting,Virginia,13\/04\/2001,This is the Accounting
\nOffice in Virginia
\n30,Consulting,Virginia,14\/04\/2001 12:02:02,This is the Consulting
\nOffice in Virginia
\n40,Finance,Virginia,987268297,This is the Finance
\nOffice in Virginia <\/p>\n

\/\/ \u8fd9\u6837\u88c5\u8f7d\u4f1a\u628a\u6362\u884c\u7b26\u88c5\u5165\u6570\u636e\u5e93,\u4e0b\u9762\u7684\u65b9\u6cd5\u5c31\u4e0d\u4f1a,\u4f46\u8981\u6c42\u6570\u636e\u7684\u683c\u5f0f\u4e0d\u540c <\/p>\n

LOAD DATA
\nINFILE DEMO18.DAT "FIX 101"
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
\nCOMMENTS
\n)
\nBEGINDATA
\n10,Sales,Virginia,01-april-2001,"This is the Sales
\nOffice in Virginia"
\n20,Accounting,Virginia,13\/04\/2001,"This is the Accounting
\nOffice in Virginia"
\n30,Consulting,Virginia,14\/04\/2001 12:02:02,"This is the Consulting
\nOffice in Virginia"
\n40,Finance,Virginia,987268297,"This is the Finance
\nOffice in Virginia" <\/p>\n

< 3 > \u4f7f\u7528var\u5c5e\u6027
\nLOAD DATA
\nINFILE DEMO19.DAT "VAR 3"
\n\/\/ 3 \u544a\u8bc9\u6bcf\u4e2a\u8bb0\u5f55\u7684\u524d3\u4e2a\u5b57\u8282\u8868\u793a\u8bb0\u5f55\u7684\u957f\u5ea6 \u5982\u7b2c\u4e00\u4e2a\u8bb0\u5f55\u7684 071 \u8868\u793a\u6b64\u8bb0\u5f55\u6709 71 \u4e2a\u5b57\u8282
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
\nCOMMENTS
\n)
\nBEGINDATA
\n07110,Sales,Virginia,01-april-2001,This is the Sales
\nOffice in Virginia
\n07820,Accounting,Virginia,13\/04\/2001,This is the Accounting
\nOffice in Virginia
\n08730,Consulting,Virginia,14\/04\/2001 12:02:02,This is the Consulting
\nOffice in Virginia
\n07140,Finance,Virginia,987268297,This is the Finance
\nOffice in Virginia <\/p>\n

< 4 > \u4f7f\u7528STR\u5c5e\u6027
\n\/\/ \u6700\u7075\u6d3b\u7684\u4e00\u4e2d \u53ef\u5b9a\u4e49\u4e00\u4e2a\u65b0\u7684\u884c\u7ed3\u5c3e\u7b26 WIN \u56de\u8f66\u6362\u884c : CHR(13)||CHR(10) <\/p>\n

\u6b64\u5217\u4e2d\u8bb0\u5f55\u662f\u4ee5 A|RN \u7ed3\u675f\u7684
\nSELECT UTL_RAW.CAST_TO_RAW('|'||CHR(13)||CHR(10)) FROM DUAL;
\n\u7ed3\u679c 7C0D0A <\/p>\n

LOAD DATA
\nINFILE DEMO20.DAT "STR X'7C0D0A'"
\nINTO TABLE DEPT
\nREPLACE
\nFIELDS TERMINATED BY ','
\nTRAILING NULLCOLS
\n(DEPTNO,
\nDNAME "UPPER(NAME)",
\nLOC "UPPER(:LOC)",
\nLAST_UPDATED "MY_TO_DATE( :LAST_UPDATED )",
\nCOMMENTS
\n)
\nBEGINDATA
\n10,Sales,Virginia,01-april-2001,This is the Sales
\nOffice in Virginia|
\n20,Accounting,Virginia,13\/04\/2001,This is the Accounting
\nOffice in Virginia|
\n30,Consulting,Virginia,14\/04\/2001 12:02:02,This is the Consulting
\nOffice in Virginia|
\n40,Finance,Virginia,987268297,This is the Finance
\nOffice in Virginia| <\/p>\n

10 *****\u8c61\u8fd9\u6837\u7684\u6570\u636e \u7528 nullif \u5b50\u53e5<\/p>\n

10-jan-200002350Flipper seemed unusually hungry today.
\n10510-jan-200009945Spread over three meals. <\/p>\n

ID POSITION(1:3) NULLIF ID=BLANKS \/\/ \u8fd9\u91cc\u53ef\u4ee5\u662fBLANKS \u6216\u8005\u522b\u7684\u8868\u8fbe\u5f0f
\n\/\/ \u4e0b\u9762\u662f\u53e6\u4e00\u4e2a\u5217\u5b50 \u7b2c\u4e00\u884c\u7684 1 \u5728\u6570\u636e\u5e93\u4e2d\u5c06\u6210\u4e3a NULL
\nLOAD DATA
\nINFILE *
\nINTO TABLE T
\nREPLACE
\n(N POSITION(1:2) INTEGER EXTERNAL NULLIF N='1',
\nV POSITION(3:8)
\n)
\nBEGINDATA
\n1 10
\n20lg
\n\/\/****<\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/strong>\/\/
\nSQLLOADER\u7684\u547d\u4ee4\uff1a
\nSQLLDR USERID=SYS\/SYS@DB_SERVICE CONTROL=XXXX.CTL LOG=XXXX.LOG BINDSIZE=1048576 ROWS=100
\nERRORS=10000 READSIZE=2097152 SILENT=(HEADER,FEEDBACK)
\n\u5173\u4e8e\u8fd9\u4e9b\u53c2\u6570\u7684\u5e2e\u52a9\u5728\u547d\u4ee4\u884c\u76f4\u63a5\u6267\u884cSQLLDR\u53ef\u4ee5\u5f97\u5230\uff0c\u8fd9\u91cc\u6307\u51faBINDSIZE\u4e0d\u5e94\u8be5\u5927\u4e8eREADSIZE\u7684\u503c\u3002<\/p>\n

\u5173\u4e8eSPOOL\u5bfc\u51fa\u6587\u672c\u6570\u636e\u7684\u4e00\u4e9b\u683c\u5f0f\u5efa\u8bae\uff1a
\nSQL*PLUS\u73af\u5883\u8bbe\u7f6eSET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 TRIMOUT ON TRIMSPOOL ON LINESIZE 2500
\n\u6ce8\uff1aLINESIZE\u8981\u7a0d\u5fae\u8bbe\u7f6e\u5927\u4e9b\uff0c\u514d\u5f97\u6570\u636e\u88ab\u622a\u65ad\uff0c\u5b83\u5e94\u548c\u76f8\u5e94\u7684TRIMSPOOL\u7ed3\u5408\u4f7f\u7528\u9632\u6b62\u5bfc\u51fa\u7684\u6587\u672c\u6709\u592a\u591a\u7684\u5c3e\u90e8\u7a7a\u683c\u3002
\n\u4f46\u662f\u5982\u679cLINESIZE\u8bbe\u7f6e\u592a\u5927\uff0c\u4f1a\u5927\u5927\u964d\u4f4e\u5bfc\u51fa\u7684\u901f\u5ea6\uff0c\u53e6\u5916\u5728WINDOWS\u4e0b\u5bfc\u51fa\u6700\u597d\u4e0d\u8981\u7528PLSQL\u5bfc\u51fa\uff0c\u901f\u5ea6\u6bd4\u8f83\u6162\uff0c
\n\u76f4\u63a5\u7528COMMEND\u4e0b\u7684SQLPLUS\u547d\u4ee4\u6700\u5c0f\u5316\u7a97\u53e3\u6267\u884c\u3002
\n\u5bf9\u4e8eSPOOL\u6570\u636e\u7684SQL\uff0c\u6700\u597d\u8981\u81ea\u5df1\u5b9a\u4e49\u683c\u5f0f\uff0c\u4ee5\u65b9\u4fbf\u6211\u4eec\u7684\u5bfc\u5165\uff0c\u5982\u4f8b\u5b50\u5982\u4e0b\uff1a
\nSELECT
\nJBSJ.JSJDM||CHR(9)||
\nJBSJ.ZZJGDM||CHR(9)||
\nJBSJ.YYZZH||CHR(9)||
\nREPLACE(REPLACE(JBSJ.NSRMC,CHR(10)),CHR(13))||CHR(9)||
\nQYRY.ZJLXDM||CHR(9)||
\nREPLACE(REPLACE(QYRY.ZJHM,CHR(10)),CHR(13))||CHR(9)||
\nREPLACE(REPLACE(QYRY.XM,CHR(10)),CHR(13))||CHR(9)||
\nREPLACE(REPLACE(JBSJ.ZCDZ,CHR(10)),CHR(13))||CHR(9)||
\nREPLACE(REPLACE(JBSJ.JYDZ,CHR(10)),CHR(13))||CHR(9)||
\nREPLACE(REPLACE(JBSJ.JYFW,CHR(10)),CHR(13))||CHR(9)||
\nREPLACE(REPLACE(JBSJ.JYDZYB,CHR(10)),CHR(13))||CHR(9)||
\nZCLX.DJZCLXDM||CHR(9)||
\nTO_CHAR(JBSJ.KYDJRQ,'YYYY-MM-DD')||CHR(9)||
\nJBSJ.SWJGZZJGDM||CHR(9)||
\nREPLACE(REPLACE(SWJGZZJG.SWJGZZJGMC,CHR(10)),CHR(13))||CHR(9)||
\nJBSJ.ZCZBJE||CHR(9)||
\nJBSJ.NSRZT||CHR(9)||
\nNSRZT.NSRZTMC
\nFROM DJDB.DJ_JL_JBSJ JBSJ,
\nDJDB.DJ_JL_QYRY QYRY,
\nDMDB.GY_DM_SWJGZZJG SWJGZZJG,
\nDMDB.DJ_DM_DJZCLX ZCLX,
\nDMDB.DJ_DM_NSRZT NSRZT
\nWHERE JBSJ.DJZCLXDM=ZCLX.DJZCLXDM
\nAND JBSJ.JSJDM=QYRY.JSJDM
\nAND QYRY.ZWDM='01'
\nAND JBSJ.SWJGZZJGDM=SWJGZZJG.SWJGZZJGDM
\nAND JBSJ.NSRZT=NSRZT.NSRZTDM
\nAND JBSJ.NSRZT!=90
\nAND JBSJ.KYDJRQ < TO_DATE('20040701','YYYYMMDD')
\n\u5bf9\u4e8e\u5b57\u6bb5\u5185\u5305\u542b\u5f88\u591a\u56de\u8f66\u6362\u884c\u7b26\u7684\u5e94\u8be5\u7ed9\u4e0e\u8fc7\u6ee4\uff0c\u5f62\u6210\u6bd4\u8f83\u89c4\u77e9\u7684\u6587\u672c\u6587\u4ef6\u3002 <\/p>\n

\u901a\u5e38\u60c5\u51b5\u4e0b\uff0c\u6211\u4eec\u4f7f\u7528SPOOL\u65b9\u6cd5\uff0c\u5c06\u6570\u636e\u5e93\u4e2d\u7684\u8868\u5bfc\u51fa\u4e3a\u6587\u672c\u6587\u4ef6\u7684\u65f6\u5019\u4f1a\u91c7\u7528\u4e24\u79cd\u65b9\u6cd5\uff0c\u5982\u4e0b\u8ff0\uff1a
\n\u65b9\u6cd5\u4e00\uff1a\u91c7\u7528\u4ee5\u4e0b\u683c\u5f0f\u811a\u672c\u3000
\nset colsep '' ------\u8bbe\u7f6e\u5217\u5206\u9694\u7b26
\n\u3000\u3000set trimspool on
\n\u3000\u3000set linesize 120
\n\u3000\u3000set pagesize 2000
\n\u3000\u3000set newpage 1
\n\u3000\u3000set heading off
\n\u3000\u3000set term off
\n\u3000\u3000spool \u8def\u5f84+\u6587\u4ef6\u540d
\n\u3000\u3000select * from tablename;
\n\u3000\u3000spool off
\n\u65b9\u6cd5\u4e8c\uff1a\u91c7\u7528\u4ee5\u4e0b\u811a\u672c
\nset trimspool on
\n\u3000\u3000set linesize 120
\n\u3000\u3000set pagesize 2000
\n\u3000\u3000set newpage 1
\n\u3000\u3000set heading off
\n\u3000\u3000set term off
\n\u3000\u3000spool \u8def\u5f84+\u6587\u4ef6\u540d
\n\u3000\u3000select col1||','||col2||','||col3||','||col4||'..' from tablename;
\n\u3000\u3000spool off
\n\u6bd4\u8f83\u4ee5\u4e0a\u65b9\u6cd5\uff0c\u5373\u65b9\u6cd5\u4e00\u91c7\u7528\u8bbe\u5b9a\u5206\u9694\u7b26\u7136\u540e\u7531sqlplus\u81ea\u5df1\u4f7f\u7528\u8bbe\u5b9a\u7684\u5206\u9694\u7b26\u5bf9\u5b57\u6bb5\u8fdb\u884c\u5206\u5272\uff0c\u65b9\u6cd5\u4e8c\u5c06\u5206\u9694\u7b26\u62fc\u63a5\u5728SELECT\u8bed\u53e5\u4e2d\uff0c\u5373\u624b\u5de5\u63a7\u5236\u8f93\u51fa\u683c\u5f0f\u3002
\n\u5728\u5b9e\u8df5\u4e2d\uff0c\u6211\u53d1\u73b0\u901a\u8fc7\u65b9\u6cd5\u4e00\u5bfc\u51fa\u6765\u7684\u6570\u636e\u5177\u6709\u5f88\u5927\u7684\u4e0d\u786e\u5b9a\u6027\uff0c\u8fd9\u79cd\u65b9\u6cd5\u5bfc\u51fa\u6765\u7684\u6570\u636e\u518d\u7531sql ldr\u5bfc\u5165\u7684\u65f6\u5019\u51fa\u9519\u7684\u53ef\u80fd\u6027\u572895%\u4ee5\u4e0a\uff0c\u5c24\u5176\u5bf9\u5927\u6279\u91cf\u7684\u6570\u636e\u8868\uff0c\u5982100\u4e07\u6761\u8bb0\u5f55\u7684\u8868\u66f4\u662f\u5982\u6b64\uff0c\u800c\u4e14\u5bfc\u51fa\u7684\u6570\u636e\u6587\u4ef6\u72c2\u5927\u3002
\n\u800c\u65b9\u6cd5\u4e8c\u5bfc\u51fa\u7684\u6570\u636e\u6587\u4ef6\u683c\u5f0f\u5f88\u89c4\u6574\uff0c\u6570\u636e\u6587\u4ef6\u7684\u5927\u5c0f\u53ef\u80fd\u662f\u65b9\u6cd5\u4e00\u76841\/4\u5de6\u53f3\u3002\u7ecf\u8fd9\u79cd\u65b9\u6cd5\u5bfc\u51fa\u6765\u7684\u6570\u636e\u6587\u4ef6\u518d\u7531sqlldr\u5bfc\u5165\u65f6\uff0c\u51fa\u9519\u7684\u53ef\u80fd\u6027\u5f88\u5c0f\uff0c\u57fa\u672c\u90fd\u53ef\u4ee5\u5bfc\u5165\u6210\u529f\u3002
\n\u56e0\u6b64\uff0c\u5b9e\u8df5\u4e2d\u6211\u5efa\u8bae\u5927\u5bb6\u4f7f\u7528\u65b9\u6cd5\u4e8c\u624b\u5de5\u53bb\u63a7\u5236spool\u6587\u4ef6\u7684\u683c\u5f0f\uff0c\u8fd9\u6837\u53ef\u4ee5\u51cf\u5c0f\u51fa\u9519\u7684\u53ef\u80fd\u6027\uff0c\u907f\u514d\u8d70\u5f88\u591a\u5f2f\u8def\u3002
\n---------------\u6570\u636e\u5bfc\u51fa
\n\u5728oracle\u4e2d\u6279\u91cf\u6570\u636e\u7684\u5bfc\u51fa\u662f\u501f\u52a9sqlplus\u7684spool\u6765\u5b9e\u73b0\u7684\u3002\u6279\u91cf\u6570\u636e\u7684\u5bfc\u5165\u662f\u901a\u8fc7sqlload\u6765\u5b9e\u73b0\u7684\u3002
\n\u5927\u91cf\u6570\u636e\u7684\u5bfc\u51fa\u90e8\u5206\u5982\u4e0b\uff1a
\n\/***<\/strong><\/strong><\/strong><\/strong><\/strong><\/strong><\/p>\n