SQL*Loader之CASE7

CASE7

1. SQL脚本

case7包含两个SQL脚本，一个是删除脚本ulcase7e.sql，一个是创建脚本ulcase7s.sql

[oracle@node3 ulcase]$ cat ulcase7e.sql

set termout offrem 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 offrem 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 DATAINFILE 'ulcase7.dat'DISCARDFILE 'ulcase7.dsc'APPENDINTO 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 EmployeesDept  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         306 rows selected.

查看日志文件：

[oracle@node3 ulcase]$ cat ulcase7.log 

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 03:20:19 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.Control File:   ulcase7.ctlData File:      ulcase7.dat  Bad File:     ulcase7.bad  Discard File: ulcase7.dsc  (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     64 rows, maximum of 256000 bytesContinuation:    none specifiedPath used:      ConventionalTable EMP, loaded when 57:57 = 0X2e(character '.')Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------HIREDATE                                                  SYSDATEDEPTNO                                1:2     2           CHARACTER                NULL if DEPTNO = BLANKSJOB                                  7:14     8  WHT      CHARACTER                NULL if JOB = BLANKS    SQL string for column : "UPPER(:job)"MGR                                 28:31     4  WHT      CHARACTER                NULL if MGR = BLANKSENAME                               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: 1048576Total logical records skipped:          0Total logical records read:            13Total logical records rejected:         0Total logical records discarded:        7Run began on Fri Sep 19 03:20:19 2014Run ended on Fri Sep 19 03:20:19 2014Elapsed time was:     00:00:00.28CPU time was:         00:00:00.17

查看Discard file：

[oracle@node3 ulcase]$ cat ulcase7.dsc 

               Today's Newly Hired EmployeesDept  Job       Manager   MgrNo  Emp Name  EmpNo  Salary/Commission----  --------  --------  -----  --------  -----  -----------------

 总结：本例中

     1> TRAILING NULLCOLS指的是在一条记录中，如果最后的一列或若干列没有提供数据，则将这些列的值置为NULL.

     2> WHEN (57) = '.'指的是第57位如果是个点，则代表这行是数据，没有点的行将被抛弃。

     3> UPPER(:job)和UPPER(:ename)将该列值该为大写。