create or replace procedure djdxgtodjd (Str varchar2) is
executesql varchar2(200) ;--内部存储sql变量
zdjbh zwdjdxg.djbh%type;--单据编号用于确定游标,出现错误写日志
v_ErrorMsg varchar2(200);--错误代码编码
Num number;--测试用,记录总数
cursor zwdjdxg_cur is
select a.djbh from zwdjdxg a where a.gxcs=0 || Str;--游标
begin
Num:=0;
executesql := 'select a.djbh from zwdjdxg a where 1=1 ' || Str;
execute immediate executesql;
open zwdjdxg_cur ;--打开游标
fetch zwdjdxg_cur into zdjbh;
while zwdjdxg_cur%found loop
begin
if Num<>0 then
fetch zwdjdxg_cur into zdjbh;
end if;
Num:=Num+1;
insert into zwdjd a select * from zwdjdxg b where b.djbh=zdjbh;
commit;
update zwdjd a set a.gxcs='0' where a.djbh=zdjbh;
delete from zwdjdxg b where b.djbh=zdjbh;
EXCEPTION
WHEN DUP_VAL_ON_INDEX OR NO_DATA_FOUND OR VALUE_ERROR THEN
--产生错误处理
-- Assign values to the log variables, using built-in
-- functions.
v_ErrorMsg := SQLERRM;
-- Insert the log message into log_table.
INSERT INTO log l (l.workerid,l.czrq,l.funcid,l.actor,l.operate)
VALUES ('8888',sysdate,'批量确认入网数据失败','单据编号为+'||zdjbh,v_ErrorMsg);
WHEN OTHERS THEN
NULL;
end;
end loop;
commit;
close zwdjdxg_cur;
纠正一下错误
如果是select 语句就用open for
sql_text1 := ' and rq > sysdate';
sql_text := 'select * from test where '||sql_text1;
open c_Temp for sql_text;--c_Temp是一个油标变量
如果时其他语句 insert ,update,delete 用execute immediate