這麽寫是沒有問題的: create or replace procedure ppp isrownumber number(1);cursor cur_ppp is select deptno,dname from dept; ln_deptno number; lv_dname varchar2(14); begin open cur_ppp; loop fetch cur_ppp into ln_deptno,lv_dname; exit when cur_ppp %NOTFOUND;
select count(*) into rownumber from emp where deptno=ln_deptno; if rownumber >0 then update emp set ename=lv_dname where deptno=ln_deptno; else insert into emp(empno,ename) values (ln_deptno,lv_dname); end if ; end loop; end ppp;這句話出問題的可能性比較大: fetch cur_ppp into a_t1,a_t2 ; 你的數值變量的長度可能不夠。另外,這麽寫會提高不少效率: create or replace procedure ppp isrownumber number(1);cursor cur_ppp is select deptno,dname from dept; ln_deptno number; lv_dname varchar2(14); begin open cur_ppp; loop fetch cur_ppp into ln_deptno,lv_dname; exit when cur_ppp %NOTFOUND; update emp set ename=lv_dname where deptno=ln_deptno; if SQL%rowcount =0 then insert into emp(empno,ename) values (ln_deptno,lv_dname); end if ; end loop; end ppp;
非常感谢 为if SQL%rowcount =0 then insert into emp(empno,ename) values (ln_deptno,lv_dname); end if ; 一直为此困惑,感觉有其他方法,初学ORACLE,请多指教,问题不在a_t1,a_t2 ,我追踪过;我先用你的方法试试。
create or replace procedure ppp isrownumber number(1);cursor cur_ppp is select deptno,dname from dept;
ln_deptno number;
lv_dname varchar2(14);
begin
open cur_ppp;
loop
fetch cur_ppp into ln_deptno,lv_dname;
exit when cur_ppp %NOTFOUND;
select count(*) into rownumber from emp where deptno=ln_deptno; if rownumber >0 then
update emp set ename=lv_dname where deptno=ln_deptno;
else
insert into emp(empno,ename) values (ln_deptno,lv_dname);
end if ;
end loop;
end ppp;這句話出問題的可能性比較大:
fetch cur_ppp into a_t1,a_t2 ;
你的數值變量的長度可能不夠。另外,這麽寫會提高不少效率:
create or replace procedure ppp isrownumber number(1);cursor cur_ppp is select deptno,dname from dept;
ln_deptno number;
lv_dname varchar2(14);
begin
open cur_ppp;
loop
fetch cur_ppp into ln_deptno,lv_dname;
exit when cur_ppp %NOTFOUND; update emp set ename=lv_dname where deptno=ln_deptno; if SQL%rowcount =0 then
insert into emp(empno,ename) values (ln_deptno,lv_dname);
end if ;
end loop;
end ppp;
为if SQL%rowcount =0 then
insert into emp(empno,ename) values (ln_deptno,lv_dname);
end if ;
一直为此困惑,感觉有其他方法,初学ORACLE,请多指教,问题不在a_t1,a_t2 ,我追踪过;我先用你的方法试试。