巧用sqlldr的WHEN判断加载数据到不同的表_Oracle_站长学院

巧用sqlldr的WHEN判断加载数据到不同的表

[2008-01-03 20:28:14] 作者：Northz.com 来源：北方站长网

→Tags:←

实例参考：sqlldr的WHEN判断加载数据到不同的表。

首先，我们需要创建测试表：

D:/Orion>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.6.0 - Production on 星期三 11月 12 11:36:21 2007Copyright (c) 1981, 2003, Oracle Corporation.  All rights reserved.连接到:Oracle9i EntERPrise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production19:53:59 SQL> create table test1 (19:54:14   2  a1 varchar2(10),19:54:21   3  a2 varchar2(10),19:54:26   4  a3 varchar2(10));表已创建。已用时间:  00: 00: 00.0319:54:32 SQL> create table test2 (19:54:35   2  a1 varchar2(10),19:54:39   3  a2 varchar2(10),19:54:40   4  a3 varchar2(10));表已创建。已用时间:  00: 00: 00.0419:54:42 SQL> exit从Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production中断开

◆接下来测试数据:

D:/Orion>cat data.txt01,KunMing,YunNan02,BeiJing,BeiJing02,ShenZhe,ShenZhe02,TianJin,TianJinD:/Orion>

◆下一步控制文件:

D:/Orion>cat data.ctlLOAD DATAINFILE 'data.txt'APPEND INTO TABLE test1WHEN (2) = '1'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )INTO TABLE test2WHEN (2) = '2'FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )

◆然后加载数据:

D:/Orion>sqlldr eygle/eygle errors=20000 log=data.log control=data.ctlSQL*Loader: Release 9.2.0.6.0 - Production on 星期三 11月 12 11:40:34 2007Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.达到提交点，逻辑记录计数3达到提交点，逻辑记录计数4

◆最后检查结果:（注释：假如你不选择分区表，即可以使用这个方式来直接加载数据到不同的数据表中。）

D:/Orion>sqlplus eygle/eygleSQL*Plus: Release 9.2.0.6.0 - Production on 星期三 11月 12 11:40:36 2005Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.连接到:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production19:57:38 SQL> select * from test1;A1         A2         A3---------- ---------- ----------01         KunMing    YunNan已用时间:  00: 00: 00.0019:57:42 SQL> select * from test2;A1         A2         A3---------- ---------- ----------02         BeiJing    BeiJing02         ShenZhe    ShenZhe02         TianJin    TianJin已用时间:  00: 00: 00.0019:57:45 SQL> exit从Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production中断开

3pR北方站长站