create or replace procedure sp_ChangeDState(p_SysCode in varchar2, p_TableName in varchar2, p_StateId in varchar2) is
v_CURSORID INTEGER;
v_SQL VARCHAR(300);
v_Result INTEGER;
e_RecNotFound EXCEPTION;
begin
v_CURSORID := DBMS_SQL.OPEN_CURSOR;
v_SQL := 'update '||p_TableName||' set StateID = '''||p_StateID||''',State = (select State from StateInfo where TableName = '''||p_TableName||''' and StateID = '''||p_StateID||''') where SysCode = '''||p_SysCode||'''';
DBMS_SQL.PARSE(v_CURSORID,v_SQL,DBMS_SQL.V7);
v_Result := DBMS_SQL.EXECUTE(v_CURSORID);
if v_Result <= 0 then
Raise e_RecNotFound;
end if;
Exception
WHEN e_RecNotFound THEN
RAISE_APPLICATION_ERROR(-20000,'单据状态转换失败!-无此纪录'||SQLERRM);
end sp_ChangeDState;
v_CURSORID INTEGER;
v_SQL VARCHAR(300);
v_Result INTEGER;
e_RecNotFound EXCEPTION;
begin
v_CURSORID := DBMS_SQL.OPEN_CURSOR;
v_SQL := 'update '||p_TableName||' set StateID = '''||p_StateID||''',State = (select State from StateInfo where TableName = '''||p_TableName||''' and StateID = '''||p_StateID||''') where SysCode = '''||p_SysCode||'''';
DBMS_SQL.PARSE(v_CURSORID,v_SQL,DBMS_SQL.V7);
v_Result := DBMS_SQL.EXECUTE(v_CURSORID);
if v_Result <= 0 then
Raise e_RecNotFound;
end if;
Exception
WHEN e_RecNotFound THEN
RAISE_APPLICATION_ERROR(-20000,'单据状态转换失败!-无此纪录'||SQLERRM);
end sp_ChangeDState;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货