操作環(huán)境
客戶端 Oracle 10g
服務(wù)端 Oracle 11g
1、數(shù)據(jù)準備
1.1、在sqlplus下將相關(guān)表將相關(guān)待導(dǎo)入數(shù)據(jù)導(dǎo)出為txt文件
--sqlplus腳本
Set linesize 3000 --每行長3000
Set pagesize 0 --指定不分頁
Set heading off --不輸出標題行
Set feedback off --不輸出反饋信息
Set echo off --不顯示命令本身
Set termout off --不顯示終端信息
Set trimout On --截斷終端顯示空格
Set trimspool On --截斷輸出至日志空格
spool c:\a.txt --指定屏幕輸出到相關(guān)文件
spool off --關(guān)閉指定輸出
以上腳本控制sqlplus控制臺輸出打印效果, 同時指定屏幕的打印輸出將轉(zhuǎn)錄入到何文件中
實際腳本
sqlplus zhsj/zhsj@zhsj
Set linesize 3000
Set pagesize 0
Set heading off
Set feedback off
Set echo off
Set termout off
Set trimout On
Set trimspool On
spool c:\a.txt
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
spool off
以上會將t_employee表中的 employee_id、employee_name、employee_salary 三列數(shù)據(jù)以如下形式寫入c:\a.txt 文件中
1|znp|80000.00
2|zhsj|40000.00
3|xxx|30000.00
數(shù)據(jù)準備完成后編寫sqlldr要用到的ctl文件，用于將txt文件中的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，腳本如下 （--部分實際腳本中須刪險)
-- a.ctl
Load data --加載數(shù)據(jù)
infile a.txt --加載數(shù)據(jù)文件名稱(現(xiàn)在是相對路徑,可以寫成絕對路徑c:\bak.txt)
badfile a.bad --失敗數(shù)據(jù)寫入文件
append -- append 追加 truncate truncate delete 刪除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY '|' --根據(jù)指定標識符隔斷各字段值 OPTIONALLY ENCLOSED BY '"' 過濾掉指定的標識符,如"等
(employee_id, --指定插入字段及數(shù)據(jù)
employee_name,
employee_salary)
準備完成后，在cmd下輸入
sqlldr -userid zhsj/zhsj@zhsj control=c:\a.ctl
執(zhí)行，在t_employee_bak表中可以見到a.txt中的數(shù)據(jù)已導(dǎo)入
在執(zhí)行過程如果出現(xiàn)數(shù)據(jù)沒有導(dǎo)入的情況，可能原因會有以下幾種情況
1、在導(dǎo)出的數(shù)據(jù)文件中存在同時打印輸出的命令行，將命令行刪除后重新保存文件即可將數(shù)據(jù)正常插入到數(shù)據(jù)庫中
2、雖然文本中已是正確的數(shù)據(jù)但仍然無法導(dǎo)入,此時在執(zhí)行的SQL腳本中增加一個間隔符的輸出，即
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
改為
select employee_id||'|'||employee_name||'|'||employee_salary||'|' from t_employee;
此時將會正常導(dǎo)入，這是因為列值存在空數(shù)據(jù)導(dǎo)致間隔計算問題
以上為txt格式數(shù)據(jù)文件導(dǎo)出
對于異質(zhì)數(shù)據(jù)庫，可以采用sqldeveloper等工具將相關(guān)數(shù)據(jù)庫中數(shù)據(jù)以csv格式導(dǎo)出，此時在編寫執(zhí)行腳本時須變更如下
-- a.ctl
Load data --加載數(shù)據(jù)
infile a.txt --加載數(shù)據(jù)文件名稱(現(xiàn)在是相對路徑,可以寫成絕對路徑c:\bak.txt)
badfile a.bad --失敗數(shù)據(jù)寫入文件
append -- append 追加 truncate truncate delete 刪除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --根據(jù)指定標識符隔斷各字段值 OPTIONALLY ENCLOSED BY '"' 過濾掉指定的標識符,如"等
(employee_id, --指定插入字段及數(shù)據(jù)
employee_name,
employee_salary)
因為csv格式文件在用txt打開可以看到數(shù)據(jù)文件格式為"1","znp","80000.00"，需要將' " '標識符去掉，才可以正常導(dǎo)入
大數(shù)據(jù)量時csv格式文件會比txt格式文件大3倍左右，且導(dǎo)入時可能出現(xiàn)問題，所以盡量采用txt文件方式導(dǎo)入