DB2 存储过程递归调用
一般方法是行不通的，例如：CREATE OR REPLACE PROCEDURE ZSICP1_DEPT_DEL(
IN pDeptNo varchar(40))
SPECIFIC ZSICP1_DEPT_DEL
P1:Begin
declare sqlcode integer default 0;
declare varDeptNo varchar(40);
declare C_ChildDept Cursor with return to client for
select DeptNo from ZSicT1_Dept where FatherNO=pDeptNo;
Open C_ChildDept;
currsorloop:
Loop
Fetch C_ChildDept into varDeptNo;
if sqlcode=100 then
leave currsorloop;
end if;
call ZSICP1_DEPT_DEL(varDeptNo);
End loop;
Close C_ChildDept;
delete ZSicT1_Dept where DeptNo=pDeptNo;
End P1
会报告：ERROR SQL20481N创建或重新验证 "XJXU.ZSICP1_DEPT_DEL" 对象将引起无效的直接或间接自引用
循环内的call ZSICP1_DEPT_DEL(varDeptNo) 必须改成==》execute immediate 'call ZSICP1_DEPT_DEL(''' || varDeptNo || ''')';
完整的语句如下：
CREATE OR REPLACE PROCEDURE ZSICP1_DEPT_DEL(
IN pDeptNo varchar(40))
SPECIFIC ZSICP1_DEPT_DEL
P1:Begin
declare sqlcode integer default 0;
declare varDeptNo varchar(40);
declare C_ChildDept Cursor with return to client for
select DeptNo from ZSicT1_Dept where FatherNO=pDeptNo;
Open C_ChildDept;
currsorloop:
Loop
Fetch C_ChildDept into varDeptNo;
if sqlcode=100 then
leave currsorloop;
end if;
execute immediate 'call ZSICP1_DEPT_DEL(''' || varDeptNo || ''')';
End loop;
Close C_ChildDept;
delete ZSicT1_Dept where DeptNo=pDeptNo;
End P1
页:
[1]