linux5 sample文件,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)