在测试环境中,我将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
前提:先要备份,备份,备份!!!
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填充。
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)
);
[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)
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;
/
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
minusselect c_code,C_name from tb_sta_emp ;
7、删除临时表:
drop table t1 purge
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2062809/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28916011/viewspace-2062809/