在測試環境中，我將excel裡面的電話等資訊，update到了tb使用者下的tb_sta_emp表裡面，請測試資料更新是否正確。

如測試正常，後續會在生產環境按照此方法進行更新。

以下是我的實施方法（不知是否還有其他更好的方法），供稽核。
前提：先要備份，備份，備份！！！
expdp tb52/xxxx directory=EXPDP_DIR dumpfile=tb_sta_emp.dmp tables=tb_sta_emp
impdp \'/ as sysdba\' directory=PUMP_DIR dumpfile=TB52.201603221101.dmp17 logfile=TBimpdp.log REMAP_SCHEMA=TB52:TB52

1、將excel表格另存為csv（帶逗號）格式；

2、設定作業系統字符集和資料庫一致，防止匯入出現亂碼：

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3、將csv格式的表格，透過sqlldr工具匯入到oracle的t1表（臨時表）；
create table t1
(
C_CODE VARCHAR2(256),
C_NAME VARCHAR2(256),
C_OFFICE_TEL VARCHAR2(256),
C_SJ VARCHAR2(256),
C_GSYX VARCHAR2(256),
C_OID VARCHAR2(36)
);

4、excel表裡面的空值都用czxin填充。

[oracle@hr ~]$ cat /home/oracle/1.ctl
LOAD DATA
INFILE '/home/oracle/1.csv'
append INTO TABLE t1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(c_code,c_name, c_office_tel,c_sj,c_gsyx,C_OID)

備註：如果excel欄位有日期格式的，就在控制檔案裡面進行格式化，如optionally enclosed by '"'
(stat_date timestamp "yyyy-mm-dd hh24:mi:ss", stat_num)

[oracle@hr ~]$ sqlldr tb52/xxxxx control=/home/oracle/1.ctl bad=/home/oracle/bad.txt log=/home/oracle/log.txt

5、用PL/SQL的匿名塊，透過遍歷遊標的方式，將員工工號c_code將t1表（臨時表）和tb_sta_emp關聯，然後把t1表中的電話等資訊update到tb_sta_emp中：

declare
cursor c1 is select a.* from tb_sta_emp a,t1 b where a.C_CODE=b.C_CODE for update nowait; --這麼寫的原因是為了防止出現no data found的錯誤，否則進入異常處理，oracle不會再返回begin end部分執行了；主要防止出現tb_sta_emp表裡面有的，而t1表裡面沒有
v_C_CODE tb_sta_emp.C_CODE%type;
v_C_NAME t1.C_NAME%type;
v_C_SJ t1.C_SJ%type;
v_C_OFFICE_TEL t1.C_OFFICE_TEL%type;
v_C_GSYX t1.C_GSYX%type;
v_C_OID t1.C_OID%type;
i number(30);
begin
i:=0;
for k in c1 loop
v_C_CODE:=k.C_CODE;
select C_SJ into v_C_SJ from t1 where C_CODE=v_C_CODE and rownum=1; --透過員工編號C_CODE關聯t1表和tb_sta_emp兩張表
select C_GSYX into v_C_GSYX from t1 where C_CODE=v_C_CODE and rownum=1; --寫rownum=1是為了防止出現fetch too many rows的錯誤
select C_OFFICE_TEL into v_C_OFFICE_TEL from t1 where C_CODE=v_C_CODE and rownum=1;
i:=i+1;
dbms_output.put_line(v_C_CODE||' '||v_C_SJ||'正在處理第幾行：'||i);
if v_C_SJ!='czxin' --如果t1表的這列值為czxin，即表示excel表這列原來是空值，就不update tb_sta_emp表的值
then
update tb_sta_emp set C_SJ=v_C_SJ where C_CODE=v_C_CODE;--你遊標指誰我update 誰
end if;
if v_C_GSYX!='czxin'
then
update tb_sta_emp set C_GSYX=v_C_GSYX where C_CODE=v_C_CODE;--你遊標指誰我update 誰
end if;
if v_C_OFFICE_TEL!='czxin'
then
update tb_sta_emp set C_OFFICE_TEL=v_C_OFFICE_TEL where C_CODE=v_C_CODE;--你遊標指誰我update 誰
end if;
end loop;
commit;
end;
/

6、看t1（臨時表）裡面哪些資料在 tb_sta_emp裡面沒有，沒有的資料插入到tb_sta_emp裡面

select c_code,C_name from t1

minus
select c_code,C_name from tb_sta_emp ；

7、刪除臨時表：

drop table t1 purge

excel表的資料update到oracle裡面

相關文章