一 描述
Case Study 5: Loading Data into Multiple Tables: Loads data into multiple tables in one run.
Case 5 demonstrates:
- Loading multiple tables.
- Using SQL*Loader to break down repeating groups in a flat file and to load the data into normalized tables. In this way, one file record may generate multiple database rows.
- Deriving multiple logical records from each physical record.
- Using a WHEN clause.
- Loading the same field (empno) into multiple tables.
二 操作环境
OS info
windows server 2003 32bit
DB info
连接到:Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL 9.0.1.1.1 Production
Oracle9i Enterprise Edition 9.0.1.1.1 Production
PL/SQL 9.0.1.1.1 Production
TNS for 32-bit Windows: 9.0.1.1.0 Production
SQL>
other
目录结构:C:\oracle\product\9.2.0\db_1\rdbms\demo三 过程设计
1.准备的源flat数据 ulcase5.dat1234 BAKER 10 9999 101 102 103
1234 JOKER 10 9999 777 888 999
2664 YOUNG 20 2893 425 abc 102
5321 OTOOLE 10 9999 321 55 40
2134 FARMER 20 4555 236 456
2414 LITTLE 20 5634 236 456 40
6542 LEE 10 4532 102 321 14
2849 EDDS xx 4555 294 40
4532 PERKINS 10 9999 40
1244 HUNT 11 3452 665 133 456
123 DOOLITTLE 12 9940 132
1453 MACDONALD 25 5532 200
2.相关文件ulcase5.sql
rem
rem $Header: ulcase5.sql 20-jul-99.18:06:21 cmlim Exp $
rem
rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved.
rem
rem NAME
rem ulcase5.sql -
rem DESCRIPTION
rem
rem RETURNS
rem
rem NOTES
rem
rem MODIFIED (MM/DD/YY)
Rem cmlim 07/20/99 - add unique index on empno
rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem jstenois 06/17/99 - cleanup tables before load and show feedback
rem ksudarsh 03/11/93 - comment out vms specific host command
rem ksudarsh 12/29/92 - Creation
rem cheigham 08/28/91 - Creation
rem
set termout off
rem host write sys$output "Building case 5 demonstration tables. Please wait"
drop table emp;
drop table proj;
create table emp
(empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));
create unique index empix on emp(empno);
create table proj
(empno number,
projno number);
exit
3.相关文件ulcase5.ctl
-- Copyright (c) 1991 by Oracle Corporation
-- NAME
-- ulcase5.ctl -
-- DESCRIPTION
--
-- RETURNS
--
-- NOTES
--
-- MODIFIED (MM/DD/YY)
-- ksudarsh 04/08/94 - merge changes from branch 1.3.710.1
-- ksudarsh 02/21/94 - quote dat file
-- ksudarsh 03/11/93 - make filename lowercase
-- ksudarsh 11/06/92 - infile is ulcase5
-- cheigham 08/28/91 - Creation
--
-- $Header: ulcase5.ctl,v 1.4 1994/04/08 13:44:31 ksudarsh Exp $ case5.ctl
--
-- Loads EMP records from first 23 characters
-- Creates and loads PROJ records for each PROJO listed
-- for each employee
LOAD DATA
INFILE 'ulcase5.dat'
BADFILE 'ulcase5.bad'
DISCARDFILE 'ulcase5.dis'
REPLACE
INTO TABLE EMP
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
ENAME POSITION(6:15) CHAR,
DEPTNO POSITION(17:18) CHAR,
MGR POSITION(20:23) INTEGER EXTERNAL)
INTO TABLE PROJ
-- PROJ has two columns, both not null: EMPNO and PROJNO
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
INTO TABLE PROJ
WHEN PROJNO != ' '
(EMPNO POSITION(1:4) INTEGER EXTERNAL,
PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
4.以scott用户连接数据库
5.scott用户运行ulcase5.sql初始化环境
6.在cmd命令下执行sqlldr加载ulcase5.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad文件信息
9.查看数据库加载成功的数据
四 详细步骤操作
1.以scott用户连接数据库C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期二 8月 7 23:35:21 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL>
2.scott用户运行ulcase5.sql初始化环境
SQL> @ulcase5
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开
C:\oracle\product\9.2.0\db_1\rdbms\demo>
3.在cmd命令下执行sqlldr加载ulcase5.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr "scott/tiger" control=ulcase5.ctl
SQL*Loader: Release 9.0.1.1.1 - Production on 星期二 8月 7 23:38:26 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
达到提交点,逻辑记录计数12
C:\oracle\product\9.2.0\db_1\rdbms\demo>
4.查看sqlldr日志信息
SQL*Loader: Release 9.0.1.1.1 - Production on 星期二 8月 7 23:38:26 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
控制文件: ulcase5.ctl
数据文件: ulcase5.dat
错误文件: ulcase5.bad
废弃文件: ulcase5.dis:
(可废弃所有记录)
加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行,最大 256000 字节
继续: 未作指定
所用路径: 常规
表EMP
已加载从每个逻辑记录
插入选项对此表REPLACE生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
DEPTNO 17:18 2 CHARACTER
MGR 20:23 4 CHARACTER
表PROJ
已加载在PROJNO !=0X202020(字符' ')
插入选项对此表REPLACE生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
PROJNO 25:27 3 CHARACTER
表PROJ
已加载在PROJNO !=0X202020(字符' ')
插入选项对此表REPLACE生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
PROJNO 29:31 3 CHARACTER
表PROJ
已加载在PROJNO !=0X202020(字符' ')
插入选项对此表REPLACE生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
PROJNO 33:35 3 CHARACTER
记录 2: 被拒绝 - 表 EMP 出现错误。
ORA-00001: 违反唯一约束条件 (SCOTT.EMPIX)
记录 8: 被拒绝 - 表 EMP 的列 DEPTNO 出现错误。
ORA-01722: 无效数字
记录 3: 被拒绝 - 表 PROJ 的列 PROJNO 出现错误。
ORA-01722: 无效数字
表EMP:
9 行加载成功
由于数据错误, 3 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。
表PROJ:
7 行加载成功
由于数据错误, 2 行没有加载。
由于所有 WHEN 子句失败, 3 行没有加载。
由于所有字段都为空的, 0 行没有加载。
表PROJ:
7 行加载成功
由于数据错误, 3 行没有加载。
由于所有 WHEN 子句失败, 2 行没有加载。
由于所有字段都为空的, 0 行没有加载。
表PROJ:
6 行加载成功
由于数据错误, 3 行没有加载。
由于所有 WHEN 子句失败, 3 行没有加载。
由于所有字段都为空的, 0 行没有加载。
为结合数组分配的空间: 4096字节(64行)
读取 缓冲区字节数: 1048576
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 12
拒绝的逻辑记录总数: 3
废弃的逻辑记录总数: 0
从星期二 8月 07 23:38:26 2012开始运行
在星期二 8月 07 23:38:27 2012处运行结束
经过时间为: 00: 00: 00.95
CPU 时间为: 00: 00: 00.03(可?
5.查看sqlldr bad文件信息
1234 JOKER 10 9999 777 888 999
2849 EDDS xx 4555 294 40
2664 YOUNG 20 2893 425 abc 102
6.查看数据库加载成功的数据
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期二 8月 7 23:42:22 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL>
SQL> SELECT empno, ename, mgr, deptno FROM emp;
EMPNO ENAME MGR DEPTNO
---------- ---------- ---------- ----------
1234 BAKER 9999 10
5321 OTOOLE 9999 10
2134 FARMER 4555 20
2414 LITTLE 5634 20
6542 LEE 4532 10
4532 PERKINS 9999 10
1244 HUNT 3452 11
123 DOOLITTLE 9940 12
1453 MACDONALD 5532 25
已选择9行。
SQL> SELECT * from PROJ order by EMPNO;
EMPNO PROJNO
---------- ----------
123 132
1234 101
1234 102
1234 103
1244 665
1244 133
1244 456
1453 200
2134 236
2134 456
2414 236
EMPNO PROJNO
---------- ----------
2414 40
2414 456
4532 40
5321 321
5321 40
5321 55
6542 102
6542 14
6542 321
已选择20行。
SQL>
五 个人总结
六 资料参考引用
Oracle9i Database Utilities
Release 1 (9.0.1)
Part Number A90192-01
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-740315/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-740315/