OCA題目深入瞭解繼續，SQL*Loader和外部表

由於身體等因素，已經好幾天沒有針對OCA練習中的盲點進行單獨學習了

今天就按之前計劃，對兩個Oracle資料載入功能進行一定的瞭解

 

SQL*Loader，可以通過外部資料檔案獲取資料，並傳送到Oracle的表中

它可以通過網路獲取資料，從多個檔案中獲取資料，將獲取的資料傳入多個表

指定資料字符集，有選擇性的獲取資料，在讀取資料前對資料進行操作

從磁碟、磁帶、命名管道獲取資料，利用作業系統的檔案系統訪問資料檔案

產生錯誤報告以便進行問題解決，讀取LOB或集合資料

使用高度靈活的傳統讀取方式或高效能拿的直接路徑讀取方式獲取資料

這些是它的主要特點，在今後實際應用中，應該還會發現其它大大小小的好處

 

SQL*Loader並不是在SqlPlus環境中呼叫的(可被前臺程式呼叫)

而是一個單獨發行的小工具軟體，一般位於$ORACLE_HOME/bin/目錄下

在個人的RH AS 4下，直接輸入sqlldr可以顯示一些簡單幫助

命令使用的格式：SQLLDR keyword=value

keyword是引數關鍵字，value是引數值

 

SQL*Loader呼叫時，可以帶有control引數並指定一個control檔案

此檔案可以用來管理SQL*Loader的獲取資料、解析資料、匯入資料等操作的方式

檔案可以分三個部分

第一部分，指定session的範圍，比如指定行、列，輸入資料檔案位置，讀取的資料等

第二部分，指定輸出的表等相關資訊

第三部分，可選部分，要匯入的資料

 

對於資料檔案中記錄的解析，可以有固定、可變、流三種方式

解析的記錄作為物理記錄，一般一個物理記錄就是一個邏輯記錄

也可以由多個物理記錄組成一個邏輯記錄

另外也可指定記錄中每個欄位的型別

 

可以通過指定bad檔案，用來記錄被SQL*Loader或Oracle拒絕接受的資料記錄

比如，當輸入資料的格式不正確，或不符合資料資料insert規則的資料

而discard檔案用來儲存那些沒有通過控制檔案中設定的接受規則的記錄

另外，SQL*Loader在執行時候，會建立一個log日誌，記錄整個執行過程中的狀況

 

SQL*Loader有三種讀取資料的方式

conventional path load，資料檔案按指定標準進行解析，並記錄到相應的bind array

當bind array滿了或全部資料已經記錄到bind array中，array insert開始執行

direct path load，會將記錄欄位資料轉化為相應列型別並存放於列陣列column array

再被格式化為Oracle資料塊格式，直接寫到資料庫中

另外，Oracle提供direct path load API來幫助使用者開發相關程式功能

external table load，沒想到外部表是從屬於SQL*Loader的……

兩者有各自更適合的應用場合

如果偏向並行傳輸，並對資料進行一定的轉換，可以考慮外部表

而遠端獲取資料，不需要對資料進行什麼轉換，可以考慮SQL*Loader

外部表是通過SQL語句在create table時候建立的(ORGANIZATION EXTERNAL子句)

使用外部表，需要建立一個DIRECTORY資料庫物件，並給相應使用者設定許可權

還可通過設定access parameter來規定access driver的預設行為

access driver有兩種語法規則，ORACLE_LOADER和ORACLE_DATADUMP

 

做個簡單的外部表練習

CREATE OR REPLACE DIRECTORY ext_tab_dir AS '/home/oracle/oracle/oradata/test/';

GRANT READ ON DIRECTORY ext_tab_dir TO SCOTT;

在ext_tab_dir下放一個info.dat檔案，裡面有如下資訊

56november, 15, 1980 baker mary alice 09/01/2004

87december, 20, 1970 roper lisa marie 01/01/1999

建立外部表

CREATE TABLE emp_load

 (employee_number CHAR(5),

  employee_dob CHAR(20),

  employee_last_name CHAR(20),

  employee_first_name CHAR(15),

  employee_middle_name CHAR(15),

  employee_hire_date DATE)

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER

    DEFAULT DIRECTORY ext_tab_dir

    ACCESS PARAMETERS

    (

      RECORDS DELIMITED BY NEWLINE

      FIELDS (employee_number CHAR(2),

      employee_dob CHAR(20),

      employee_last_name CHAR(18),

      employee_first_name CHAR(11),

      employee_middle_name CHAR(11),

      employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"

    )

  )

 LOCATION ('info.dat')

 );

然後就可以利用select * from emp_load;

檢視到從外部檔案讀取過來的兩條記錄了

 

另外也簡單練習一下SQL*Loader

利用Office的Excel建立一個CSV檔案，內容如下

1	RICOH	97:27:00	74:12:00
2	MCC	97:34:22	74:43:00
3	MCC	97:34:22	79:59:74
4	SONY	97:26:51	79:59:74
5	RITEK	97:10:00	79:59:74
6	RITEK	97:27:10	74:41:00

拷到VM的RH AS4的/home/oracle/oracle/oradata/test/目錄下

在測試資料庫中建立表

  CREATE TABLE "SCOTT"."T_SL"

   ( "THEID" NUMBER NOT NULL ENABLE,

       "THENAME" NVARCHAR2(10),

       "THEPAR1" VARCHAR2(8 BYTE),

       "THEPAR2" VARCHAR2(10 BYTE)

   ) NOLOGGING;

建立一個control檔案，test.ctl，內容如下

LOAD DATA

INFILE '/home/oracle/oracle/oradata/test/test2.csv'

INSERT INTO TABLE t_sl

FIELDS TERMINATED BY ","

(THEID,THENAME,THEPAR1,THEPAR2)

在作業系統提示符下，執行命令 sqlldr userid=scott/scott@test control=test.ctl

執行完畢後，就可通過 select * from T_SL;

檢視到剛剛通過SQL*Loader匯入的記錄了

同時，工具自動生成了一個test.log檔案，記錄了整個過程的大致摘要

 

利用SQL*Loader和它的外部表功能，可以靈活實現多種資料來源的載入

雖然題目是深入瞭解，其實也不過是先對概念、應用有個大致上的體驗罷了

還有很多可以繼續深入的地方

但目前先以把Oracle各個基礎知識瞭解一二為主要目標，還有不少東西等著看呢

有關SQL*Loader和外部表的更多學習，放在以後找時間來弄吧～

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/556359/viewspace-526430/，如需轉載，請註明出處，否則將追究法律責任。