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;
/