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