create or replace procedure delorg
is
sqlt varchar2(200);
tt NVARCHAR2(80);
CURSOR tb is select * from orgdel;
R_emp tb%ROWTYPE;
begin
open tb;
loop
fetch tb into R_emp;
exit when tb% notfound;
tt := R_emp.Fnumber;
sqlt := 'delete from orgbak where fnumber = ' || ' tt ' ;
execute immediate sqlt;
commit;
end loop;
close tb;
end;
要将orgbak里面的编号与orgdel编码相同的记录删除?
编译成功,但执行时提示错误:
ORA-00904: "TT": invalid identifier
ORA-06512: at "HOAU.DELORG", line 14
ORA-06512: at line 2
is
sqlt varchar2(200);
tt NVARCHAR2(80);
CURSOR tb is select * from orgdel;
R_emp tb%ROWTYPE;
begin
open tb;
loop
fetch tb into R_emp;
exit when tb% notfound;
tt := R_emp.Fnumber;
sqlt := 'delete from orgbak where fnumber = ' || ' tt ' ;
execute immediate sqlt;
commit;
end loop;
close tb;
end;
要将orgbak里面的编号与orgdel编码相同的记录删除?
编译成功,但执行时提示错误:
ORA-00904: "TT": invalid identifier
ORA-06512: at "HOAU.DELORG", line 14
ORA-06512: at line 2
sqlt := 'delete from orgbak where fnumber = ' || tt;
execute immediate sqlt; tt是一个变量,不能''这样引用。另外,看你只需要Fnumber, CURSOR最好只取该列值。
报另外一个错:
ORA-01722: invalid number
ORA-06512: at "HOAU.DELORG", line 14
ORA-06512: at line 2