CASE7
1. SQL脚本
case7包含两个SQL脚本，一个是删除脚本ulcase7e.sql，一个是创建脚本ulcase7s.sql
[oracle@node3 ulcase]$ cat ulcase7e.sql
set termout off
rem host write sys$output "Cleaning up Case 7 Trigger and Package." DROP PACKAGE uldemo7;
DROP TRIGGER uldemo7_emp_insert; EXIT
[oracle@node3 ulcase]$ cat ulcase7s.sql
set termout off
rem host write sys$output "Building Package and Trigger for Case 7.Please wait" CREATE OR REPLACE PACKAGE uldemo7 AS
last_deptno NUMBER;
last_job CHAR(9);
last_mgr NUMBER;
END uldemo7;
/ CREATE OR REPLACE TRIGGER uldemo7_emp_insert
BEFORE INSERT ON emp
FOR EACH ROW BEGIN
IF :new.deptno IS NOT NULL THEN
uldemo7.last_deptno := :new.deptno; -- save value for later use
ELSE
:new.deptno := uldemo7.last_deptno; -- use last valid value
END IF; IF :new.job IS NOT NULL THEN
uldemo7.last_job := :new.job; -- save value for later use
ELSE
:new.job := uldemo7.last_job; -- use last valid value
END IF; IF :new.mgr IS NOT NULL THEN
uldemo7.last_mgr := :new.mgr; -- save value for later use
ELSE
:new.mgr := uldemo7.last_mgr; -- use last valid value
END IF; END;
/ EXIT
其实，两个脚本可以并在一起，即先删除，再创建
2. 控制文件
[oracle@node3 ulcase]$ cat ulcase7.ctl
-- Copyright (c) 1991, 2004 Oracle. All rights reserved.
-- NAME
-- ulcase7.ctl - Extracting Data From a Formatted Report
--
-- DESCRIPTION
-- This case study demonstrates the following:
-- Use of SQL*Loader with an INSERT trigger.
--
-- Use of the SQL string to manipulate data.
--
-- Use of different initial and trailing delimiters.
--
-- Use of SYSDATE.
--
-- Use of the TRAILING NULLCOLS clause.
--
-- Ambiguous field length warnings.
--
-- Use of a discard file.
--
-- TO RUN THIS CASE STUDY:
-- 1. Before executing this control file, log in to SQL*Plus as
-- scott/tiger. Enter @ulcase7s to execute the SQL script for
-- this case study. This creates a BEFORE INSERT trigger that
-- is required to fill in the department number, job name,
-- and manager's number when these fields are not present on
-- a data line. When values are present, they should be saved
-- in a global variable. When values are not present, the
-- global variables are used.
--
-- 2. At the system prompt, invoke the case study as follows:
-- sqlldr USERID=scott/tiger CONTROL=ulcase7.ctl LOG=ulcase7.log
--
-- 3. After you have run the case study and finished with it, you
-- must run the ulcase7e.sql script before you can successfully
-- run other case studies. This script drops the INSERT trigger
-- and the global variables package. Log in to SQL*Plus as
-- scott/tiger. Enter @ulcase7e.
--
-- NOTES ABOUT THIS CONTROL FILE
-- The WHEN (57) = '.' clause indicates that the decimal point
-- in column 57 (the salary field) identifies a line with data
-- on it. All other lines in the report are discarded.
--
-- The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields
-- that are missing at the end of a record as null. Because the
-- commission field is not present for every record, this clause says
-- to load a null commission instead of rejecting the record when only
-- seven fields are found instead of the expected eight.
--
-- The hiredate is filled in using the current system date (SYSDATE).
--
-- The specification for deptno will generate a warning message in
-- the log file because the specified length does not agree with
-- the length determined by the field's position. The specified
-- length (3) is used. The length is in bytes with the default
-- byte-length semantics. If character-length semantics were used
-- instead, this length would be in characters.
--
-- The NULLIF clause says that because the report shows only department
-- number, job, and manager when the value changes, these fields may
-- be blank. This control file causes them to be loaded as null, and
-- an insert trigger fills in the last valid value.
--
-- For the job field, the SQL string changes the job name to
-- uppercase letters.
--
-- For the mgr field, it is necessary to specify starting position.
-- If the job field and the manager field were both blank, then the
-- job field's TERMINATED BY WHITESPACE clause would cause SQL*Loader
-- to scan forward to the employee name field. Without the POSITION
-- clause, the employee name field would be mistakenly interpreted
-- as the manager field.
--
-- For the sal field, the SQL string translates the field from a
-- formatted character string into a number. The numeric value takes
-- less space and can be printed with a variety of formatting options.
--
-- For the comm field, different initial and trailing delimiters pick the
-- numeric value out of a formatted field. The SQL string then converts
-- the value to its stored form.
--
LOAD DATA
INFILE 'ulcase7.dat'
DISCARDFILE 'ulcase7.dsc'
APPEND
INTO TABLE emp
WHEN (57)='.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
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 INTEGER EXTERNAL TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
3. 数据文件
[oracle@node3 ulcase]$ cat ulcase7.dat
Today's Newly Hired Employees Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission
---- -------- -------- ----- -------- ----- -----------------
20 Salesman Blake 7698 Shepard 8061 $1,600.00 (3%)
Falstaff 8066 $1,250.00 (5%)
Major 8064 $1,250.00 (14%) 30 Clerk Scott 7788 Conrad 8062 $1,100.00
Ford 7369 DeSilva 8063 $800.00
Manager King 7839 Provo 8065 $2,975.00
执行后结果：
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase7e.sql
[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase7s.sql
[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase7.ctl
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ------- ----- ------
8061 SHEPARD SALESMAN 7698 19-SEP-14 1600 300 20
8066 FALSTAFF SALESMAN 7698 19-SEP-14 1250 500 20
8064 MAJOR SALESMAN 7698 19-SEP-14 1250 1400 20
8062 CONRAD CLERK 7788 19-SEP-14 1100 30
8063 DESILVA CLERK 7369 19-SEP-14 800 30
8065 PROVO MANAGER 7839 19-SEP-14 2975 30 6 rows selected.
查看日志文件：
[oracle@node3 ulcase]$ cat ulcase7.log
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 03:20:19 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: ulcase7.ctl
Data File: ulcase7.dat
Bad File: ulcase7.bad
Discard File: ulcase7.dsc
(Allow all discards) Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional Table EMP, loaded when 57:57 = 0X2e(character '.')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
HIREDATE SYSDATE
DEPTNO 1:2 2 CHARACTER
NULL if DEPTNO = BLANKS
JOB 7:14 8 WHT CHARACTER
NULL if JOB = BLANKS
SQL string for column : "UPPER(:job)"
MGR 28:31 4 WHT CHARACTER
NULL if MGR = BLANKS
ENAME 34:41 8 WHT CHARACTER
SQL string for column : "UPPER(:ename)"
EMPNO NEXT * WHT CHARACTER
SAL 51 * WHT CHARACTER
SQL string for column : "TO_NUMBER(:sal,'$99,999.99')"
COMM NEXT * ( CHARACTER
%
SQL string for column : ":comm * 100" Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 10: Discarded - failed all WHEN clauses. Table EMP:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
7 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null. Space allocated for bind array: 51456 bytes(64 rows)
Read buffer bytes: 1048576 Total logical records skipped: 0
Total logical records read: 13
Total logical records rejected: 0
Total logical records discarded: 7 Run began on Fri Sep 19 03:20:19 2014
Run ended on Fri Sep 19 03:20:19 2014 Elapsed time was: 00:00:00.28
CPU time was: 00:00:00.17
查看Discard file：
[oracle@node3 ulcase]$ cat ulcase7.dsc
Today's Newly Hired Employees Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission
---- -------- -------- ----- -------- ----- -----------------
总结：本例中
1> TRAILING NULLCOLS指的是在一条记录中，如果最后的一列或若干列没有提供数据，则将这些列的值置为NULL.
2> WHEN (57) = '.'指的是第57位如果是个点，则代表这行是数据，没有点的行将被抛弃。
3> UPPER(:job)和UPPER(:ename)将该列值该为大写。