还是:怎样才能把条件传到oracle存贮过程中去?有详细点的例子最好,谢谢!!
jsp生成“and rq>sysdate”
存储过程中有一个
select * from test where 这里要加上jsp的and语句
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 z.djbh from zwdjdxg z where 1=1 ||Str;
--游标,这里需要加入传入的条件
begin
Num:=0;
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;
end djdxgtodjd;