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;
/