SQLLoader 6(1つまたは複数のデータファイルが条件によって異なるテーブルにインポートされる)

テスト1、テーブルの作成:

SQL> create table tab_b(col1 varchar2(2), col2 number(2));



 。



SQL> create table tab_a(col1 varchar2(2), col2 number(2));



 。



SQL> COMMIT;



 。

2、データファイル:test.txt

A    1

A    2

A    3

B    3

B    2

B    1

3、制御ファイル:testSqlLdr 2.ctl

LOAD DATA

INFILE 'D:\oracletest\test.txt'

DISCARDFILE 'D:\oracletest\testSqlLdr2.dsc'

REPLACE

INTO TABLE TAB_A

WHEN COL1="A"

(COL1 position(1:1),COL2 position(3:4))

INTO TABLE TAB_B

WHEN COL1="B"

(COL1 position(1:1),COL2 position(3:4))

4、インポートの実行

D:\oracletest>sqlldr scott/tiger@orcl control=D:\oracletest\testSqlLdr2.ctl



SQL*Loader: Release 11.2.0.1.0 - Production on   11  30 21:27:16 2014



Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.



  -   5

  -   6

5、結果の表示:

SQL> SELECT * FROM TAB_A;



CO COL2

-- ----------

A 1

A 2

A 3



SQL> SELECT * FROM TAB_B;



CO COL2

-- ----------

B 3

B 2

B 1

試験二1、試験表の作成

SQL> CREATE TABLE EMP_DEPT10 AS SELECT * FROM EMP WHERE DEPTNO=10 AND 1=2;



 。



SQL> CREATE TABLE EMP_DEPT20 AS SELECT * FROM EMP WHERE DEPTNO=20 AND 1=2;



 。



SQL> CREATE TABLE EMP_DEPT30 AS SELECT * FROM EMP WHERE DEPTNO=30 AND 1=2;



 。



SQL> SELECT * FROM EMP_DEPT10;



 



SQL> SELECT * FROM EMP_DEPT20;



 



SQL> SELECT * FROM EMP_DEPT30;



 



SQL>

2、データファイル

--D:\oracletest\test1.txt

1 7782 CLARK MANAGER 7839 1981-06-09 2450 0 10

1 7839 KING PRESIDENT 1111 1981-11-17 5000 0 10

1 7934 MILLER CLERK 7782 1982-01-23 1300 0 10

--D:\oracletest\test2.txt

2 7369 SMITH CLERK 7902 1980-12-17 800 0 20

2 7566 JONES MANAGER 7839 1981-04-02 2975 0 20

2 7788 SCOTT ANALYST 7566 1987-04-19 3000 0 20

2 7876 ADAMS CLERK 7788 1987-05-23 1100 0 20

2 7902 FORD ANALYST 7566 1981-12-03 3000 0 20

--D:\oracletest\test3.txt

3 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30

3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30

3 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30

3 7698 BLAKE MANAGER 7839 1981-05-01 2850 123 30

3 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30

3 7900 JAMES CLERK 7698 1981-12-03 950 0 30

3、制御ファイル

LOAD DATA

INFILE 'D:\oracletest\test1.txt'

INFILE 'D:\oracletest\test2.txt'

INFILE 'D:\oracletest\test3.txt'

BADFILE 'D:\oracletest\testSqlLdr3.bad'

DISCARDFILE 'D:\oracletest\testSqlLdr3.dsc'

REPLACE

INTO TABLE EMP_DEPT10

WHEN FLAG="1" -- flag 

FIELDS TERMINATED BY WHITESPACE

(FLAG FILLER POSITION(1), -- POSITION(1) ， POSITION(1) ， 。

EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD" ,SAL,COMM,DEPTNO)

INTO TABLE EMP_DEPT20

WHEN FLAG="2"

FIELDS TERMINATED BY WHITESPACE

(FLAG FILLER POSITION(1), 

EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD",SAL,COMM,DEPTNO)

INTO TABLE EMP_DEPT30

WHEN FLAG="3"

FIELDS TERMINATED BY WHITESPACE

(FLAG FILLER POSITION(1), 

EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD",SAL,COMM,DEPTNO)

4、インポートコマンドの実行

D:\oracletest>sqlldr scott/tiger@orcl control=D:\oracletest\testSqlLdr3.ctl



SQL*Loader: Release 11.2.0.1.0 - Production on   11  30 22:32:49 2014



Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.



  -   2

  -   3

  -   7

  -   8

  -   13

  -   14

5、インポート結果の確認

SQL> select * from emp_dept10;



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7782 CLARK MANAGER 7839 1981-06-09 2450 0 10

7839 KING PRESIDENT 1111 1981-11-17 5000 0 10

7934 MILLER CLERK 7782 1982-01-23 1300 0 10



SQL> select * from emp_dept20;



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 800 0 20

7566 JONES MANAGER 7839 1981-04-02 2975 0 20

7788 SCOTT ANALYST 7566 1987-04-19 3000 0 20

7876 ADAMS CLERK 7788 1987-05-23 1100 0 20

7902 FORD ANALYST 7566 1981-12-03 3000 0 20



SQL> select * from emp_dept30;



EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------

7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 1250 500 30

7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 2850 123 30

7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30

7900 JAMES CLERK 7698 1981-12-03 950 0 30



 6 。