ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Sample Control File

Sample Control File

原创 Linux操作系统 作者:papaya14 时间:2012-03-06 19:23:52 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-23

  • 博文量
    19
  • 访问量
    29018