create or replace procedure xt_add_xm ( xmbh in varchar2, --项目号 xmxz in varchar2 , -- 项目性质 outvalues out varchar2, --返回的状态值,1错误,见错误代码表,0 正确 outstr out varchar2 --返回的错误说明 ) as sql_stat varchar2(200); curdate varchar2(8); a Exception; xmnum int; begin /*检查项目存在?*/ xmnum :=0; SELECT TO_CHAR (SYSDATE, 'YYYYMMDD') into curdate FROM DUAL; sql_stat := 'select count(*) into :1 from xt.trust_prj_info where PROJECTID =:2'; --sql_stat := 'insert into xt.trust_USR_grp values (:1,:2)'; execute immediate sql_stat using xmnum,xmbh; if xmnum <=0 then begin outstr := ""; outvalues :=1; Raise a ; end; end if; sql_stat :='select count(*) into :1 from xt.cw_havexm where FXMH=:2'; execute immediate sql_stat using xmnum,xmbh; if xmnum >0 then begin outstr := ""; outvalues :=1; Raise a ; end; end if;
/*异常处理*/ EXCEPTION WHEN a then begin outvalues :=1; outstr :=""; ROLLBACK; end; WHEN OTHERS THEN BEGIN outvalues :=1; outstr :=""; ROLLBACK; END; end; /
execute immediate sql_stat using xmnum,xmbh; 这句话有问题,你的sql_stat语句并没有定义需要使用的变量,xmnum,xmbh是用来给被你注释的sql_stat语句重的:1,:2赋值的,现在没有用了,你要改成 execute immediate sql_stat;
(
xmbh in varchar2, --项目号
xmxz in varchar2 , -- 项目性质
outvalues out varchar2, --返回的状态值,1错误,见错误代码表,0 正确
outstr out varchar2 --返回的错误说明
)
as
sql_stat varchar2(200);
curdate varchar2(8);
a Exception;
xmnum int;
begin
/*检查项目存在?*/
xmnum :=0;
SELECT TO_CHAR (SYSDATE, 'YYYYMMDD') into curdate FROM DUAL;
sql_stat := 'select count(*) into :1 from xt.trust_prj_info where PROJECTID =:2';
--sql_stat := 'insert into xt.trust_USR_grp values (:1,:2)';
execute immediate sql_stat using xmnum,xmbh;
if xmnum <=0 then
begin
outstr := "";
outvalues :=1;
Raise a ;
end;
end if;
sql_stat :='select count(*) into :1 from xt.cw_havexm where FXMH=:2';
execute immediate sql_stat using xmnum,xmbh;
if xmnum >0 then
begin
outstr := "";
outvalues :=1;
Raise a ;
end;
end if;
/*异常处理*/
EXCEPTION
WHEN a then
begin
outvalues :=1;
outstr :="";
ROLLBACK;
end;
WHEN OTHERS THEN
BEGIN
outvalues :=1;
outstr :="";
ROLLBACK;
END;
end;
/
这句话有问题,你的sql_stat语句并没有定义需要使用的变量,xmnum,xmbh是用来给被你注释的sql_stat语句重的:1,:2赋值的,现在没有用了,你要改成
execute immediate sql_stat;