存储过程使用变量循环调用

 

--以存储过程名字为变量调用

declare

vsql varchar2(200);

i_PROC USER_TABLES.TABLE_NAME%TYPE;

cursor yb is SELECT object_name FROM USER_PROCEDURES

  where object_name like 'PROC%' AND object_name NOT like 'PROC_ERR_LOG%' AND object_name NOT like 'PROC_TAB%';

begin

   i_PROC:='PROC_FMS_ACCENTRY2_HL';

  open yb;

  loop

  fetch yb into i_PROC;

  exit when yb%notfound;

vsql:='BEGIN '||i_PROC||'(DATE''2018-03-01''); END;';

 EXECUTE IMMEDIATE vsql;

 END LOOP;

 END;

 

--以存储过程传参为变量调用

 DECLARE

 vsql VARCHAR2(100);

 I_NU NUMBER;

 cursor yb is select a from aa;

 BEGIN

  I_NU:=1;

  open yb;

  loop

  fetch yb into I_NU;

  exit when yb%notfound;

vsql:='BEGIN PROC_FMS_ACCENTRY2_HL(DATE''2018-03-01''+'||I_NU||'); END;';

 EXECUTE IMMEDIATE vsql;

   COMMIT;  

END LOOP;

  END;