查询 oracle 中存储过程内容的方法包括:直接查询数据字典视图,如 user_objects;使用 dbms_metadata 包,遍历 all_objects 视图中的过程;使用 dbms_utility 包,解析 all_source 视图中的过程和函数创建语句。
Oracle 查询所有存储过程内容的方法:
直接查询数据字典视图
SELECT OBJECT_NAME, OBJECT_TYPE, OBJECT_DEFINITION FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION');
使用 DBMS_METADATA 包
DECLARE cursor_pkg DBMS_METADATA.REF_CURSOR; BEGIN DBMS_METADATA.OPEN_REF_CURSOR(cursor_pkg, 'ALL_OBJECTS', 'PROCEDURE'); LOOP FETCH cursor_pkg INTO object_name, object_type, object_definition; EXIT WHEN cursor_pkg%NOTFOUND; DBMS_OUTPUT.PUT_LINE(object_name || ' ' || object_type); DBMS_OUTPUT.PUT_LINE('=' * LENGTH(object_name) || ' ' || '=' * LENGTH(object_type)); DBMS_OUTPUT.PUT_LINE(object_definition); DBMS_OUTPUT.PUT_LINE(''); END LOOP; DBMS_METADATA.CLOSE_REF_CURSOR(cursor_pkg); END;
使用 DBMS_UTILITY 包
DECLARE cursor_src DBMS_UTILITY.CURSOR_TYPE; line VARCHAR2(2000); type_owner VARCHAR2(30); type_name VARCHAR2(30); BEGIN DBMS_UTILITY.SET_SOURCE(cursor_src, 'all_source'); LOOP FETCH cursor_src INTO line; EXIT WHEN cursor_src%NOTFOUND; IF line LIKE '%CREATE PROCEDURE%' THEN DBMS_UTILITY.PARSE_INTO_HIERARCHY(cursor_src, type_owner, type_name); DBMS_OUTPUT.PUT_LINE('Owner: ' || type_owner); DBMS_OUTPUT.PUT_LINE('Object: ' || type_name); DBMS_OUTPUT.PUT_LINE('=' * LENGTH(type_owner) || ' ' || '=' * LENGTH(type_name)); DBMS_OUTPUT.PUT_LINE(line); ELSIF line LIKE '%CREATE FUNCTION%' THEN DBMS_UTILITY.PARSE_INTO_HIERARCHY(cursor_src, type_owner, type_name); DBMS_OUTPUT.PUT_LINE('Owner: ' || type_owner); DBMS_OUTPUT.PUT_LINE('Object: ' || type_name); DBMS_OUTPUT.PUT_LINE('=' * LENGTH(type_owner) || ' ' || '=' * LENGTH(type_name)); DBMS_OUTPUT.PUT_LINE(line); END IF; END LOOP; DBMS_UTILITY.CLOSE_CURSOR(cursor_src); END;