使用ORACLE外部表裝載復雜數據
來源：程序員人生 發布時間：2015-01-15 08:26:51 閱讀次數：4042次
原文：http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13asktom⑴886639.html
I am using SQL Loader to load data into tables from a flat file. Some sample data in the file might be:
我打算使用SQL Loader裝載來自平面文件數據。樣例數據以下：
12,smith,1234556@@1234567 @@876556612,1200
14,John,1234@@4567@@56789 @@12345@@45778@@34566@@23456,1345
The field values are empno, empname, phone numbers, and salary. The table structures are
表結構以下：
create table emp ( empno number(5) primary key, ename varchar2(10), Sal number(10,2) )
create table emp_contact ( empno references emp, phone_no number(10) )
I want to insert the data into the emp and emp_contact tables. I don’t know how many values for phone_no the file contains (the number of values for phone_no is not fixed). How do I insert the data into the tables?
我想將以上數據分別裝入2個表中: emp和emp_contact
問題是：我沒法肯定第3列（電話號碼）有多少個，此列不固定。我將如何將數據裝載進表？
這個問題很狡猾，看上去我們必須將第3列拆成多行插入。
對號稱“
21世紀奇異數據裝載工具”
--SQL Loader 來說也沒法直接實現！??！
此處，我建議使用21世紀數據裝載工具
--外部表 解決。思路是：將平面文件裝入外部表，然后通過編寫SQL進行拆分，最后按特定插入規則1次性插入指定表中。
下面，看我演示：
-⑴）進行數據裝載
create or replace directory my_dir as '/home/tkyte'
/
CREATE TABLE et
( "EMPNO" VARCHAR2(10),
"ENAME" VARCHAR2(20),
"TELNOS" VARCHAR2(1000),
"SAL" VARCHAR2(10) )
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'MY_DIR':'t.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ENAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"TELNOS" CHAR(1000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SAL" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
't.dat'
)
)
/
SQL> select * from et;
EMPNO ENAME TELNOS SAL
――――― ――――― ――――――――――――――――――――――――――――――――――――――――――――― ――――
12 smith 1234556@@1234567@@876556612 1200
14 John 1234@@4567@@56789@@12345@@45778@@34566@@23456 1345
-⑵）編寫拆分SQL
SQL> select empno, ename, sal, i,
substr( tnos,
instr( tnos, '@@', 1, i )+2,
instr( tnos, '@@', 1, i+1 )
-instr( tnos, '@@', 1, i) - 2 ) tno
from (
select to_number(et.empno) empno,
et.ename,
to_number(et.sal) sal,
column_value i,
'@@'||et.telnos||'@@' tnos
from et,
table( cast( multiset(
select level
from dual
connect by level <=
(length(et.telnos)
-length(replace(et.telnos,'@@','')))/2+1 )
as sys.odciNumberList ) )
)
/
EMPNO ENAME SAL I TNO
――――― ――――― ―――― ―― ―――――――
12 smith 1200 1 1234556
12 smith 1200 2 1234567
12 smith 1200 3 876556612
14 John 1345 1 1234
14 John 1345 2 4567
14 John 1345 3 56789
14 John 1345 4 12345
14 John 1345 5 45778
14 John 1345 6 34566
14 John 1345 7 23456
10 rows selected.
--注意：這里使用了cast multiset語法，column_value是TABLE(odciNumberList)中1列
-⑶）編寫插入SQL
SQL> create table emp
2 ( empno number primary key,
3 ename varchar2(10),
4 sal number
5 );
Table created.
SQL> create table emp_contact
2 ( empno number references emp,
3 phone_no number
4 );
Table created.
SQL> insert all
when (i = 1) then into emp (empno,ename,sal) values (empno,ename,sal)
when (i > 0) then into emp_contact(empno,phone_no) values (empno,tno)
select empno, ename, sal, i,
substr( tnos,
instr( tnos, '@@', 1, i )+2,
instr( tnos, '@@', 1, i+1 )
-instr( tnos, '@@', 1, i) - 2 ) tno
from (
select to_number(et.empno) empno,
et.ename,
to_number(et.sal) sal,
column_value i,
'@@'||et.telnos||'@@' tnos
from et,
table( cast( multiset(
select level
from dual
connect by level <=
(length(et.telnos)
-length(replace(et.telnos,'@@','')))/2+1 )
as sys.odciNumberList ) )
)
/
12 rows created.
SQL> select * from emp;
EMPNO ENAME SAL
――――― ―――――― ―――――
12 smith 1200
14 John 1345
SQL> select * from emp_contact;
EMPNO PHONE_NO
――――――― ―――――――――
12 1234556
12 1234567
12 876556612
14 1234
14 4567
14 56789
14 12345
14 45778
14 34566
14 23456
10 rows selected.
------------------------------------
Dylan Presents.
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
