Oracle PL/SQL 循环批量执行存储过程

1. 查询存储过程

        根据数据字典USER_OBJECTS查询出所有存储过程。

2. 动态拼接字符串（参数等）

        根据数据字典USER_ARGUMENTS动态拼接参数。

3. 动态执行

        利用EXECUTE IMMEDIATE动态执行无名块。

4. 输出执行信息

        利用DBMS_OUTPUT.PUT_LINE输出执行成功与否信息。

SET SERVEROUTPUT ON;
DECLAREv_sql          varchar2(32767);v_head         varchar2(32767);v_tail         varchar2(32767);n_count        number := 0;crlf           constant    varchar2(4) := chr(13) || chr(10);
BEGINFOR rec1 IN (SELECT object_nameFROM USER_OBJECTSWHERE OBJECT_TYPE = 'PROCEDURE'ORDER BY 1)LOOPv_sql := null;v_head := null;v_tail := null;-------------------------------------------- Header------------------------------------------v_head := v_head || 'DECLARE' || crlf;FOR rec3 IN (SELECT CASE data_typeWHEN 'DATE' THEN'    d_out' || position || '    date;'ELSE'    v_out' || position || '    varchar2(1000);'END varFROM user_argumentsWHERE object_name = rec1.object_nameAND in_out <> 'IN'ORDER BY position)LOOPv_head := v_head || rec3.var || crlf;END LOOP;v_head := v_head || 'BEGIN' || crlf;v_head := v_head || '    ' || rec1.object_name || '('  || crlf;-------------------------------------------- Process------------------------------------------FOR rec2 IN (SELECT *FROM user_argumentsWHERE object_name = rec1.object_nameORDER BY position) LOOP--*****************************-- set in parameterIF rec2.in_out = 'IN' thenIF rec2.position = 1 thenIF rec2.data_type = 'DATE' THENv_sql := v_sql || '        ' || rec2.argument_name || ' => SYSDATE' || crlf;ELSEv_sql := v_sql || '        ' || rec2.argument_name || ' => 1'       || crlf;END IF;ELSEIF rec2.data_type = 'DATE' THENv_sql := v_sql || '      , ' || rec2.argument_name || ' => SYSDATE' || crlf;ELSEv_sql := v_sql || '      , ' || rec2.argument_name || ' => 1'       || crlf;END IF;END IF;-- set out parameterELSEIF rec2.position = 1 thenIF rec2.data_type = 'DATE' THENv_sql := v_sql || '        ' || rec2.argument_name || ' => d_out' || rec2.position || crlf;ELSEv_sql := v_sql || '        ' || rec2.argument_name || ' => v_out' || rec2.position || crlf;END IF;ELSEIF rec2.data_type = 'DATE' THENv_sql := v_sql || '      , ' || rec2.argument_name || ' => d_out' || rec2.position || crlf;ELSEv_sql := v_sql || '      , ' || rec2.argument_name || ' => v_out' || rec2.position || crlf;END IF;END IF;END IF;END LOOP;-------------------------------------------- Tail------------------------------------------v_tail := v_tail || '    );' || crlf;v_tail := v_tail || 'END;' || crlf;-------------------------------------------- Execute SQL--------------------------------------------dbms_output.put_line(v_head || v_sql || v_tail);BEGINn_count := n_count + 1;EXECUTE IMMEDIATE v_head || v_sql || v_tail;DBMS_OUTPUT.PUT_LINE(LPAD(n_count, 3, '0') || '_存储过程:' || rec1.object_name || '执行成功。');EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(LPAD(n_count, 3, '0') ||'_存储过程:' || rec1.object_name || '执行失败。');END;END LOOP;ROLLBACK;
EXCEPTIONWHEN OTHERS THENROLLBACK;RAISE;
END;
/