应用场景:当一个列中的格式有多种,如时间,有些值是一种格式,有些值是另一种格式,如何分别对它们处理,就可以用CASE.
测试
会话1:修改控制文件
[oracle@oraclelinux ~]$ cat dept_load13.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_LOAD
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED
"case
when length(:last_updated) > 9
then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')
when instr(:last_updated,':') > 0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'dd/mm/yyyy')
end"
)---分情况对时间列不同格式进行转换,这里CASE前后的是双引号
BEGINDATA
10,Sales,Virginia,12:03:03 17/10/2005--时间列格式1
20,Accounting,Virginia,02:23:54--时间列格式2
30,Consulting,Virginia,01:24:00 21/10/2005
40,Finance,Virginia,17/8/2005--时间列格式3
[oracle@oraclelinux ~]$
会话1:加载数据
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load13.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 14:10:36 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 4
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select deptno,dname,loc,last_updated from dept_load;
DEPTNO DNAME LOC LAST_UPDATED
---------- -------------- ------------- --------------------
10 SALES VIRGINIA 17-oct-2005 12:03:03
20 ACCOUNTING VIRGINIA 01-may-2012 02:23:54
30 CONSULTING VIRGINIA 21-oct-2005 01:24:00
40 FINANCE VIRGINIA 17-aug-2005 00:00:00
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-723507/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-723507/