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 [429C3] [IBM][DB2/NT64] 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