Sample Control File

1    -- This is a sample control file
2    LOAD DATA
3    INFILE 'sample.csv'
4    BADFILE 'sample.bad'
5    DISCARDFILE 'sample.dsc'
6    APPEND
7    INTO TABLE emp
8    WHEN (57) = '.'
9    TRAILING NULLCOLS
10   ( 
10.1    hiredate SYSDATE,
10.2    deptno   POSITION(1:2)  INTEGER EXTERNAL(2)
                 NULLIF deptno=BLANKS,
10.3    job      POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
                 NULLIF job=BLANKS  "UPPER(:job)",
        mgr      POSITION(28:31) INTEGER EXTERNAL
                 TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
        ename    POSITION(34:41) CHAR
                 TERMINATED BY WHITESPACE  "UPPER(:ename)",
        empno    POSITION(45) INTEGER EXTERNAL
                 TERMINATED BY WHITESPACE,
        sal      POSITION(51) CHAR  TERMINATED BY WHITESPACE
                 "TO_NUMBER(:sal,'$99,999.99')",
10.4    comm     INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
                 ":comm * 100"
 )
1 注释用--
2 此处开始load数据
3 要load的数据文件的地址
4 为不合格数据指定文件名
5 为放弃的数据指定文件名
6 指定load数据的方式，append表示附加，如果表中存在数据，新的行附加在后面，如果表中无数据，       新的行简单的load进表，要有select权限；replace表示替换，先删除表中数据，在load进新数据，要有delete权限；truncate同replace，效率高，必须取消参照完整性约束；默认Insert，表要为空表；
7 要导入数据的表
8 指明一些load的条件
9 相对位置在记录中没有默认为null，例如：
INTO TABLE dept
    TRAILING NULLCOLS
( deptno CHAR TERMINATED BY " ",
  dname  CHAR TERMINATED BY WHITESPACE,
  loc    CHAR TERMINATED BY WHITESPACE
)
BEGINDATA
10 Accounting
Loc为null，如果没有TRAILING NULLCOLS，会产生确实数据的错误
10 包含field list，提供一些表中列的信息
10.1 设置当前字段的值为系统时间
10.2 position指明数据域的绝对位置；INTEGER EXTERNAL是sql loader的数据类型；WHEN, NULLIF, DEFAULTIF判断的是字段还是位置position，不一样
10.3 TERMINATED BY指明分隔符，表明一个字段的结束
10.4 ENCLOSED BY 指明另一种分隔符，此分隔符内的整体为一个字段

常用到的SQL function：
ABS(n) 取绝对值
SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
        15

TRUNC(n,m) trunc（nnn.nn）取整数部分
SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

  Truncate
----------
      15.7
SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

  Truncate
----------
        10

TRUNC(date,fmt) trunc(date)取年月日部分
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92

FLOOR(n) 取不大于n的最大整数
SELECT FLOOR(15.7) "Floor" FROM DUAL;

     Floor
----------
        15

CONCAT(char1,char2)连接两个字符串
select concat('nihao','hello') from dual;
 
CONCAT('NIHAO','HELLO')
------------------------------------------------------
nihaohello

LOWER(char)整体变小写

UPPER(char)整体变大写

REPLACE(str,search_str,replace_str) 替换
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
     FROM DUAL;

Changes
--------------
BLACK and BLUE

TRIM() trim（char）去除两端空格
SELECT TRIM (0 FROM 0009872348900) "TRIM Example"
   FROM DUAL;

TRIM Example
------------
    98723489

RTRIM trim（char）去除右端空格

LTRIM  trim（char）去除左端空格

TO_TIMESTAMP ( char [ , fmt ['nlsparam']] )
SELECT TO_TIMESTAMP ('1999-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS')
   FROM DUAL;

TO_TIMESTAMP('1999-12-0111:00:00','YYYY-MM-DDHH:MI:SS')
-----------------------------------------------------------
01- DEC-99 11.00.00.000000000 AM

TO_CHAR()
SELECT TO_CHAR('01110' + 1) FROM dual;

TO_C
----
1111

 

NVL(expr1,expr2) expr1如果为null，则取expr2的值

TO_DATE ( char [, fmt [, 'nlsparam']] )
SELECT TO_DATE(
    'January 15, 1989, 11:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
     FROM DUAL;

TO_DATE('
---------
15-JAN-89

DECODE(expr,search,result[,search,result]...[,default])类似switch..case语句
DECODE(dept_id,"MFG",manufacturing,"FIN",financial)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26555596/viewspace-717877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26555596/viewspace-717877/