Oracle SQL*Loader
-- Start
SQL*Loaderは、ファイル内のデータを1つ以上のテーブルにインポートするためのツールです.C:oraclexeapporacleproduct11.2.0\server\bin\sqlldr.exe
現在、データを転送するために最も一般的に使用されているファイルフォーマットは2種類あります.1つはセパレータファイル(CSVなど)で、もう1つは定長ファイルです.次に、この2つのファイルをインポートする場合の例を見てみましょう.
まず、次の表を作成します.
セパレータファイル例1
次のファイルがあるとします(C:logemployees.txt)
次のコマンドでEMPLOYESテーブルにファイルをインポートしてみましょう.
ああ、使いにくいですね.それはあなたがまだemployeesを持っていないからです.ctl制御ファイル、それがなければSQL*Loaderはどのようにデータをインポートするか分かりません.さあ、次の内容をemployeesに保存しましょう.ctlはもう一度やってみます.
上の制御ファイルでは、EMAIL列のインポート方法を指定していないので、NULLです.カラムをNULLにしたい場合は、カラムを指定しないでください.
セパレータファイル例2
セパレータファイルのフィールドの中には、二重引用符で囲まれているものもあれば、フィールドの前または後ろにスペースがあるものもありますが、このようなファイルをどのように処理しますか?
このようなファイルをインポートするには,制御ファイルにOPTIONALLY ENCLOSED BY句を入れるだけでよい.
セパレータファイル例3
上の2つのファイルは比較的完璧で、実際の状況は非常に複雑な場合があります.例えば、次のファイルを次の表にインポートするように要求されています.要求1.最初の行と最後の行を無視します.EMPLOYEE_IDはデータベースによって自動的に生成する.NAMEはFIRST_からNAMEとLAST_NAMEは、SHANGBO 4のように大文字に変換されます.ENGLISHを無視NAME列5.もしHIRE_DATEが空の場合はNULL 6を挿入する.SALARYが空の場合は0 7を挿入する.ENTER_USER挿入デフォルト値'SYSTEM'8.ENTER_DATE_TIMEは現在のシステム時間9を挿入する.ACTIVEがnの行であることを無視
インポートするファイル
インポートするテーブル
コントロールファイル
インポートコマンド
定長ファイルの例
次に、「区切り文字ファイル例3」のファイルを定長ファイルに変更し、定長ファイルをインポートする方法を見てみましょう.
インポートするファイル
インポートするテーブル
コントロールファイル
ダイレクトパスインポート
上記の例のインポート方法は本質的にINSERT文を構築します.Oracleでは、インポートするファイルをデータベースファイルに直接フォーマットするため、より高速な直接パスインポート方法も提供されています.
ダイレクトパスを使用してインポートする前に、次のSQLを実行してデータベースを設定する必要があります.このSQLは一度だけ設定する必要があります.
しかし、直接パスインポートを使用すると、データベースがプライマリ・キーを自動的に生成したり、CHECKや外部キー制約を実行したりすることができず、インデックスが使用できなくなりやすくなります.したがって、通常はインデックスを削除し、インポート後にインデックスを再構築することで、インポート効率を向上させることができます.では、ダイレクトパスインポートはどのように使用しますか?簡単なのでDIRECTパラメータをtrueに設定すれば良いのですが、さらに効率を上げるためにPARALLELパラメータをtrueに設定することもできます.
次のコマンドは、直接パスを使用して「区切り文字ファイル例1」のファイルをインポートします.
--詳細は、「Oracle Extreme」を参照してください.
--声明:転載は出典を明記してください
-- Last edited on 2015-02-21
-- Created by ShangBo on 2015-02-20
-- End
SQL*Loaderは、ファイル内のデータを1つ以上のテーブルにインポートするためのツールです.C:oraclexeapporacleproduct11.2.0\server\bin\sqlldr.exe
現在、データを転送するために最も一般的に使用されているファイルフォーマットは2種類あります.1つはセパレータファイル(CSVなど)で、もう1つは定長ファイルです.次に、この2つのファイルをインポートする場合の例を見てみましょう.
まず、次の表を作成します.
CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6,0) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(50) NULL,
HIRE_DATE DATE NULL,
SALARY NUMBER(8,2) NULL,
UPDATE_DATE_TIME TIMESTAMP NULL
);セパレータファイル例1
次のファイルがあるとします(C:logemployees.txt)
1,bo,shang,2015-01-01,8888.88,2015-02-19 22:15:00.000
2,san,zhang,2015-02-02,9999.99,2015-02-19 22:15:00.000次のコマンドでEMPLOYESテーブルにファイルをインポートしてみましょう.
sqlldr
USERID=DBuser/shangbo -- ，
ROWS=1000 -- 1000
BAD=C:\log\employees.bad -- ，
DISCARD=C:\log\employees.dsc --
LOG=C:\log\employees.log --
CONTROL=C:\log\employees.ctl -- ， SQL*Loader ああ、使いにくいですね.それはあなたがまだemployeesを持っていないからです.ctl制御ファイル、それがなければSQL*Loaderはどのようにデータをインポートするか分かりません.さあ、次の内容をemployeesに保存しましょう.ctlはもう一度やってみます.
--
LOAD DATA INFILE 'C:\log\employees.txt'
-- APPEND
-- REPLACE DELETE ，
-- TRUNCATE TRUNCATE ，
-- INSERT ，
APPEND INTO TABLE employees
FIELDS TERMINATED BY ',' --
(
EMPLOYEE_ID CHAR, --
FIRST_NAME , -- ， CHAR
LAST_NAME , -- ， SQL*Loader ，
HIRE_DATE DATE "YYYY-MM-DD", -- DATE
SALARY CHAR, -- SQL*Loader CHAR
UPDATE_DATE_TIME TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3"
)上の制御ファイルでは、EMAIL列のインポート方法を指定していないので、NULLです.カラムをNULLにしたい場合は、カラムを指定しないでください.
セパレータファイル例2
セパレータファイルのフィールドの中には、二重引用符で囲まれているものもあれば、フィールドの前または後ろにスペースがあるものもありますが、このようなファイルをどのように処理しますか?
1,"bo","shang","2015-01-01",8888.88,"2015-02-19 20:15:00.000"
2,"san","zhang","2015-02-02",9999.99,"2015-02-18 21:15:00.000"
3 ,"si" ,"li" ,"2015-03-01" ,6666.66 ,"2015-02-17 22:15:00.000"
4, "wu", "wang", "2015-04-02", 7777.77, "2015-02-16 23:15:00.000"このようなファイルをインポートするには,制御ファイルにOPTIONALLY ENCLOSED BY句を入れるだけでよい.
LOAD DATA INFILE 'C:\log\employees.txt'
REPLACE INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
EMPLOYEE_ID ,
FIRST_NAME ,
LAST_NAME ,
HIRE_DATE DATE "YYYY-MM-DD",
SALARY ,
UPDATE_DATE_TIME TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3"
)セパレータファイル例3
上の2つのファイルは比較的完璧で、実際の状況は非常に複雑な場合があります.例えば、次のファイルを次の表にインポートするように要求されています.要求1.最初の行と最後の行を無視します.EMPLOYEE_IDはデータベースによって自動的に生成する.NAMEはFIRST_からNAMEとLAST_NAMEは、SHANGBO 4のように大文字に変換されます.ENGLISHを無視NAME列5.もしHIRE_DATEが空の場合はNULL 6を挿入する.SALARYが空の場合は0 7を挿入する.ENTER_USER挿入デフォルト値'SYSTEM'8.ENTER_DATE_TIMEは現在のシステム時間9を挿入する.ACTIVEがnの行であることを無視
インポートするファイル
FIRST_NAME,LAST_NAME,ENGLISH_NAME,HIRE_DATE,SALARY,ACTIVE
bo,shang,scott,2015-01-01,8888.88,y
san,zhang,mike,,,y
si ,li ,darren , , ,y
wu, wang, eric, 2015-04-02, 7777.77, n
END OF FILEインポートするテーブル
CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6,0) NOT NULL,
NAME VARCHAR2(20 BYTE) NOT NULL,
HIRE_DATE DATE NULL,
SALARY NUMBER(8,2) DEFAULT 0 NOT NULL,
ENTER_USER VARCHAR2(20 BYTE) NULL,
ENTER_DATE_TIME TIMESTAMP(6) NULL
);コントロールファイル
LOAD DATA INFILE 'C:\log\employees.txt'
REPLACE INTO TABLE employees
WHEN (ACTIVE!='n')
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
EMPLOYEE_ID SEQUENCE(1, 1),
FIRST_NAME BOUNDFILLER, -- BOUNDFILLER ， EXPRESSION
LAST_NAME BOUNDFILLER,
NAME EXPRESSION "UPPER(RTRIM(:LAST_NAME) || RTRIM(:FIRST_NAME))", -- EXPRESSION SQL
ENGLISH_NAME FILLER, -- FILLER ， BOUNDFILLER EXPRESSION
HIRE_DATE DATE "YYYY-MM-DD" NULLIF HIRE_DATE=BLANKS, -- NULLIF HIRE_DATE ， NULL
SALARY "NVL(:SALARY, 0)", -- :SALARY SALARY
ACTIVE FILLER, --
ENTER_USER CONSTANT 'SYSTEM', -- CONSTANT
ENTER_DATE_TIME EXPRESSION "CURRENT_TIMESTAMP"
)インポートコマンド
-- SKIP=1
sqlldr USERID=DBuser/shangbo ROWS=1000 BAD=C:\log\employees.bad DISCARD=C:\log\employees.dsc LOG=C:\log\employees.log CONTROL=C:\log\employees.ctl SKIP=1定長ファイルの例
次に、「区切り文字ファイル例3」のファイルを定長ファイルに変更し、定長ファイルをインポートする方法を見てみましょう.
インポートするファイル
FIRST_NAME LAST_NAME ENGLISH_NAME HIRE_DATE SALARY ACTIVE
bo shang scott 2015-01-01 8888.88 y
san zhang mike y
si li darren y
wu wang eric 2015-04-02 7777.77 n
END OF FILE
インポートするテーブル
CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6,0) NOT NULL,
NAME VARCHAR2(20 BYTE) NOT NULL,
HIRE_DATE DATE NULL,
SALARY NUMBER(8,2) DEFAULT 0 NOT NULL,
ENTER_USER VARCHAR2(20 BYTE) NULL,
ENTER_DATE_TIME TIMESTAMP(6) NULL
);コントロールファイル
-- fix ，62 ，
LOAD DATA INFILE 'C:\log\employees.txt' "fix 62"
REPLACE INTO TABLE employees
WHEN (ACTIVE='y')
(
EMPLOYEE_ID SEQUENCE(1, 1),
FIRST_NAME BOUNDFILLER POSITION(1:11), -- 1
LAST_NAME BOUNDFILLER POSITION(12-21), -- : -
NAME EXPRESSION "UPPER(:LAST_NAME || :FIRST_NAME)",
ENGLISH_NAME FILLER POSITION(*:34), -- *
HIRE_DATE POSITION(*:45) DATE "YYYY-MM-DD" NULLIF HIRE_DATE=BLANKS,
SALARY POSITION(*:54) "NVL(:SALARY, 0)",
ACTIVE FILLER POSITION(*),
ENTER_USER CONSTANT 'SYSTEM',
ENTER_DATE_TIME EXPRESSION "CURRENT_TIMESTAMP"
)ダイレクトパスインポート
上記の例のインポート方法は本質的にINSERT文を構築します.Oracleでは、インポートするファイルをデータベースファイルに直接フォーマットするため、より高速な直接パスインポート方法も提供されています.
ダイレクトパスを使用してインポートする前に、次のSQLを実行してデータベースを設定する必要があります.このSQLは一度だけ設定する必要があります.
@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catldr.sqlしかし、直接パスインポートを使用すると、データベースがプライマリ・キーを自動的に生成したり、CHECKや外部キー制約を実行したりすることができず、インデックスが使用できなくなりやすくなります.したがって、通常はインデックスを削除し、インポート後にインデックスを再構築することで、インポート効率を向上させることができます.では、ダイレクトパスインポートはどのように使用しますか?簡単なのでDIRECTパラメータをtrueに設定すれば良いのですが、さらに効率を上げるためにPARALLELパラメータをtrueに設定することもできます.
次のコマンドは、直接パスを使用して「区切り文字ファイル例1」のファイルをインポートします.
-- DIRECT=true
-- PARALLEL=true
sqlldr USERID=DBuser/shangbo DIRECT=true PARALLEL=true ROWS=10000 BAD=C:\log\employees.bad DISCARD=C:\log\employees.dsc LOG=C:\log\employees.log CONTROL=C:\log\employees.ctl--詳細は、「Oracle Extreme」を参照してください.
--声明:転載は出典を明記してください
-- Last edited on 2015-02-21
-- Created by ShangBo on 2015-02-20
-- End