1. 查询存储过程
根据数据字典USER_OBJECTS查询出所有存储过程。
2. 动态拼接字符串（参数等）
根据数据字典USER_ARGUMENTS动态拼接参数。
3. 动态执行
利用EXECUTE IMMEDIATE动态执行无名块。
4. 输出执行信息
利用DBMS_OUTPUT.PUT_LINE输出执行成功与否信息。
SET SERVEROUTPUT ON;
DECLARE
v_sql varchar2(32767);
v_head varchar2(32767);
v_tail varchar2(32767);
n_count number := 0;
crlf constant varchar2(4) := chr(13) || chr(10);
BEGIN
FOR rec1 IN (
SELECT object_name
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY 1)
LOOP
v_sql := null;
v_head := null;
v_tail := null;
------------------------------------------
-- Header
------------------------------------------
v_head := v_head || 'DECLARE' || crlf;
FOR rec3 IN (
SELECT CASE data_type
WHEN 'DATE' THEN
' d_out' || position || ' date;'
ELSE
' v_out' || position || ' varchar2(1000);'
END var
FROM user_arguments
WHERE object_name = rec1.object_name
AND in_out <> 'IN'
ORDER BY position)
LOOP
v_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_arguments
WHERE object_name = rec1.object_name
ORDER BY position)
LOOP
--*****************************
-- set in parameter
IF rec2.in_out = 'IN' then
IF rec2.position = 1 then
IF rec2.data_type = 'DATE' THEN
v_sql := v_sql || ' ' || rec2.argument_name || ' => SYSDATE' || crlf;
ELSE
v_sql := v_sql || ' ' || rec2.argument_name || ' => 1' || crlf;
END IF;
ELSE
IF rec2.data_type = 'DATE' THEN
v_sql := v_sql || ' , ' || rec2.argument_name || ' => SYSDATE' || crlf;
ELSE
v_sql := v_sql || ' , ' || rec2.argument_name || ' => 1' || crlf;
END IF;
END IF;
-- set out parameter
ELSE
IF rec2.position = 1 then
IF rec2.data_type = 'DATE' THEN
v_sql := v_sql || ' ' || rec2.argument_name || ' => d_out' || rec2.position || crlf;
ELSE
v_sql := v_sql || ' ' || rec2.argument_name || ' => v_out' || rec2.position || crlf;
END IF;
ELSE
IF rec2.data_type = 'DATE' THEN
v_sql := v_sql || ' , ' || rec2.argument_name || ' => d_out' || rec2.position || crlf;
ELSE
v_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);
BEGIN
n_count := n_count + 1;
EXECUTE IMMEDIATE v_head || v_sql || v_tail;
DBMS_OUTPUT.PUT_LINE(LPAD(n_count, 3, '0') || '_存储过程:' || rec1.object_name || '执行成功。');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(LPAD(n_count, 3, '0') ||'_存储过程:' || rec1.object_name || '执行失败。');
END;
END LOOP;
ROLLBACK;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/