oracle11G的資料裝載的幾種方式，oracle11g裝載幾種

oracle11G的資料裝載的幾種方式，oracle11g裝載幾種
資料裝載

資料的裝載:

     •SQL*LOADER

     •外部表格

     •匯入/匯出

SQL*LOADER:SQL*LOADER是一個ORACLE工具，能夠將資料從外部資料檔案裝載到資料庫中。運行SQL*LOADER的命令是sqlldr。Sqlldr的兩種使用方式：     1. 只使用一個控制檔案，在這個控制檔案中包含資料     2. 使用一個控制檔案(作為模板) 和一個資料檔案一般採用第二種方式，資料檔案可以是 CSV 檔案、txt檔案或者以其他分割符分隔的。

說明：操作類型 可用以下中的一值：
1) insert     --為預設方式，在資料裝載開始時要求表為空白
2) append  --在表中追加新記錄
3) replace  --刪除舊記錄(用 delete from table 語句)，替換成新裝載的記錄
4) truncate --刪除舊記錄(用 truncate table 語句)，替換成新裝載的記錄

通過spool來製作資料檔案：--可以查詢協助文檔的範例程式碼 SQL> spool /u01/app/oracle/test_data_loader/student.txt--開啟spool匯出資料檔案 SQL> select id ||',' || name ||',' || age ||',' || inner_date from student;--匯出資料 ID||','||NAME||','||AGE||','||INNER_DATE -------------------------------------------------------------------------------- 1,zhangsan,21,23-JAN-15 2,lisi,22,23-JAN-15 3,wangwu,23,23-JAN-15 SQL> spool off;--關閉 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost test_data_loader]$ cat student.txt--可以查看到匯出的資料記錄 SQL> select id ||',' || name ||',' || age ||',' || inner_date from student; ID||','||NAME||','||AGE||','||INNER_DATE -------------------------------------------------------------------------------- 1,zhangsan,21,23-JAN-15 2,lisi,22,23-JAN-15 3,wangwu,23,23-JAN-15 SQL> spool off; 寫設定檔： [oracle@localhost test_data_loader]$ vi student.ctl [oracle@localhost test_data_loader]$ cat student.ctl options(skip=4)--表示前面的四行 load data--匯入資料 infile 'student.txt'--通過該檔案匯入資料 into table student--匯入的表 insert--執行的是插入操作 fields terminated by ','--記錄中的分割符 ( id char,--注意雖然表中是number類型，但是要寫char類型 name char, age char, inner_date date nullif (inner_date = "null")) [oracle@localhost test_data_loader]$ 既然是insert操作所以： SQL> truncate table student;--清空表，由於執行的是插入操作 Table truncated. SQL> select * from student; no rows selected 執行sqlldr操作： [oracle@localhost test_data_loader]$ sqlldr hr/hr control= student.ctl log = student.log SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Commit point reached - logical record count 4 [oracle@localhost test_data_loader]$ cat student.log SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jan 23 23:11:08 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. Control File:   student.ctl Data File:      student.txt   Bad File:     student.bad   Discard File:  none specified (Allow all discards) Number to load: ALL Number to skip: 4 Errors allowed: 50 Bind array:     64 rows, maximum of 256000 bytes Continuation:    none specified Path used:      Conventional Table STUDENT, loaded from every logical record. Insert option in effect for this table: INSERT    Column Name                  Position   Len  Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ID                                  FIRST     *   ,       CHARACTER NAME                                 NEXT     *   ,       CHARACTER AGE                                  NEXT     *   ,       CHARACTER INNER_DATE                           NEXT     *   ,       DATE DD-MON-RR     NULL if INNER_DATE = 0X6e756c6c(character 'null') Record 4: Rejected - Error on table STUDENT, column ID. Column not found before end of logical record (use TRAILING NULLCOLS) Table STUDENT:   3 Rows successfully loaded.   1 Row not loaded due to data errors.   0 Rows not loaded because all WHEN clauses were failed.   0 Rows not loaded because all fields were null. Space allocated for bind array:                  66048 bytes(64 rows) Read   buffer bytes: 1048576 Total logical records skipped:          4 Total logical records read:             4 Total logical records rejected:         1 Total logical records discarded:        0 Run began on Fri Jan 23 23:11:08 2015 Run ended on Fri Jan 23 23:11:08 2015 Elapsed time was:     00:00:00.10 CPU time was:         00:00:00.01 [oracle@localhost test_data_loader]$ sqlplus hr/hr SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 23 23:13:14 2015 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from student;         ID NAME                        AGE INNER_DATE ---------- -------------------- ---------- ------------          1 zhangsan                     21 23-JAN-15          2 lisi                         22 23-JAN-15          3 wangwu                       23 23-JAN-15 SQL> exit

外部表格：外部表格中的資料不裝入資料庫中，資料庫中只儲存外部表格的定義資訊，實際的資料位元於作業系統的一般檔案中，但是，可以在資料庫中，像訪問正常表那樣，通過select語句來訪問作業系統中的一般檔案中所含有的資料。外部表格是唯讀。可以使用SQL，PL/SQL和JAVA訪問外部表格。外表表分為2種：使用資料泵引擎產生的外部表格、

SQL> create table student_re(re_id,re_name,re_age,re_inner_date) organization external ( type oracle_datapump--外部表格產生方式是資料泵 default directory test_impdp_expdp--預設的路徑 location('student.dmp')--通過路徑和檔案名稱產生外部表格在作業系統的平面資料檔案 ) parallel--並行 as select * from student;  2    3    4    5    6    7    8    9   10 Table created. SQL> !ls student.dmp  STUENT_17109.log SQL>  select * from stuent;      RE_ID RE_NAME                  RE_AGE RE_INNER_DAT ---------- -------------------- ---------- ------------          1 zhangsan                     21 23-JAN-15          2 lisi                         22 23-JAN-15          3 wangwu                       23 23-JAN-15 SQL>

根據文字檔建立的外部表格。

SQL> spool student.txt ID||','||NAME||','||AGE||','||INNER_DATE -------------------------------------------------------------------------------- 1,zhangsan,21,23-JAN-15 2,lisi,22,23-JAN-15 3,wangwu,23,23-JAN-15 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost test_impdp_expdp]$ vi student.txt [oracle@localhost test_impdp_expdp]$ sqlplus hr/hr SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 24 00:05:18 2015 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table student_re_2--該外部表格的名稱下面列是外部表格的建表語句，而上面通過資料泵的表是通過as select from tab來建立的。   2  (id number,name varchar2(20),age number(10),inner_date date)   3  organization external   4  ( type oracle_loader--外部檔案方式   5    default directory test_impdp_expdp--預設的目錄   6    access parameters--訪問外部檔案所遵守的格式   7    (   records delimited by newline--記錄分隔字元   fields terminated by ','--欄位分隔符號   8    ) location ('student.txt')--位置   );  9   10   11 Table created. SQL> select * from student_re_2;         ID NAME                        AGE INNER_DATE ---------- -------------------- ---------- ------------          1 zhangsan                     21 23-JAN-15          2 lisi                         22 23-JAN-15          3 wangwu                       23 23-JAN-15 SQL>