SQL*Loader之CASE3

本文通过一个具体的案例展示了如何使用SQL*Loader加载带有附加列的数据,并利用SEQUENCE生成唯一键值,同时介绍了如何指定日期格式及使用APPEND模式进行数据追加。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

CASE3

1. SQL文件

[oracle@node3 ulcase]$ cat ulcase3.sql

set termout off

rem Do not clean up table because this example shows appending to existing
rem rows in table that also has new columns.

rem host write sys$output "Adding columns to emp.  Please wait."

alter table emp add (projno number, loadseq number);

exit
                                    

2. 控制文件

[oracle@node3 ulcase]$ cat ulcase3.ctl

-- NAME
-- ulcase3.ctl - SQL*Loader Case Study 3: Loading a Delimited,
-- Free-format File
--
-- DESCRIPTION
-- This case study demonstrates the following:
-- Loading data (enclosed and terminated) in stream format.
--
-- Loading dates using the DATE datatype.
--
-- Using SEQUENCE numbers to generate unique keys for loaded data.
--
-- Using APPEND to indicate that the table need not be empty before
-- inserting new records.
--
-- Using comments in the control file set off by two hyphens.
--
-- NOTES ABOUT THIS CONTROL FILE
-- This control file loads the same table as in case 2, but it
-- loads three additional columns (hiredate, projno, and loadseq).
-- The projno and loadseq columns are added to the emp table when
-- you run the ulcase3.sql script.
--
-- INFILE * specifies that the data is found at the end of the
-- control file.
--
-- APPEND specifies that the data can be loaded even if the table
-- already contains rows. That is, the table need not be empty.
--
-- The default terminator for the data fields is a comma, and some
-- fields may be enclosed by double quotation marks (").
--
-- The data to be loaded into column hiredate appears in the format
-- DD-Month-YYYY. The length of the date field is specified to have
-- a maximum of 20. The maximum length is in bytes, with default
-- byte-length semantics. If character-length semantics were used
-- instead, the length would be in characters. If a length is not
-- specified, then the length depends on the length of the date mask.
--
-- The SEQUENCE function generates a unique value in the column loadseq.
-- This function finds the current maximum value in column loadseq and
-- adds the increment (1) to it to obtain the value for loadseq for
-- each row inserted.
--
-- BEGINDATA specifies the end of the control information and the
-- beginning of the data.
--
-- Although each physical record equals one logical record, the fields
-- vary in length, so that some records are longer than others. Note
-- also that several rows have null values for comm.

LOAD DATA
INFILE *
APPEND

INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'     
(empno, ename, job, mgr,
 hiredate DATE(20) "DD-Month-YYYY",
 sal, comm,
 deptno   CHAR TERMINATED BY ':',
 projno,
 loadseq  SEQUENCE(MAX,1) )

BEGINDATA
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981, 5500.00,, 10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
                                       

执行后结果:

[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase3.sql

[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase3.ctl

SQL> select * from emp;

EMPNO ENAME  JOB         MGR    HIREDATE       SAL     COMM   DEPTNO PROJNO    LOADSEQ
----- ------ ---------  -----   ---------      ------- -----  ------ ------ ----------
 7782 Clark  Manager     7839   09-JUN-81      2573            10    101        1

 7839 King   President          17-NOV-81      5500            10    102        2

 7934 Miller Clerk       7782   23-JAN-82       920            10    102        3

 7566 Jones  Manager     7839   02-APR-81      3124            20    101        4 

7499 Allen Salesman 7698 20-FEB-81 1600 300 30 103 5 7654 Martin Salesman 7698 28-SEP-81 1313 1400 30 103 6 7658 Chan Analyst 7566 03-MAY-82 3450 20 101 7 7 rows selected.

 总结:在本例中,

      1> APPEND指定该操作是追加,在一般情况下,要求导入数据的表是空表,如果该表已经存在数据,则需要指明下面参数中的任意一个:APPEND,REPLACE,TRUNCATE.

      2> The data to be loaded into column hiredate appears in the format DD-Month-YYYY. The length of the date field is specified to have a maximum of 20.

      3> SEQUENCE(MAX,1)产生序列,每次都在该列最大值上加1.

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值