oracle存储过程的相关视图
1 、user_objects
用户下的对象动态视图,对象类型PROCEDURE,查询用户下的存储过程情况:
SQL> select * from user_objects where object_type='PROCEDURE';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
INIT_DATA 89046 PROCEDURE 2021-10-4 2 2021-10-4 21: 2021-10-04:21:44:37 INVALID N N N 1
TB_PERFORMANCE 89064 PROCEDURE 2021-10-5 5 2021-10-5 5:5 2021-10-05:05:53:15 INVALID N N N 1
BATCHDATA_PARTITION 89124 PROCEDURE 2021-10-10 2021-10-10 21 2021-10-10:21:56:28 INVALID N N N 1
PRO_TEST 89127 PROCEDURE 2021-10-10 2021-10-11 2: 2021-10-11:02:22:18 VALID N N N 1
2、dbms_metadata.get_dd
通过dbms_metadata.get_ddl 包,可以查询存储过程内容:
SQL> SET PAGESIZE 1000;
SQL> SET LONG 10000;
SQL> select dbms_metadata.get_ddl('PROCEDURE','PRO_TEST') as proc from dual;
PROC
--------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE "TEST"."PRO_TEST" (t_name in varchar2,
s_column in varchar2) AS
v_TABLENAME varchar2(30);
v_STAND_CLOUMN varchar2(30);
v_CHECK_DAY date;
v_sql varchar2(2000);
begin
v_TABLENAME := t_name;
v_STAND_CLOUMN := s_column;
v_sql := 'SELECT MIN('|| s_column ||') from ' || v_TABLENAME;
dbms_output.put_line(v_sql);
execute immediate v_sql
into v_CHECK_DAY ;
dbms_output.put_line(v_check_day);
dbms_output.put_line(v_STAND_CLOUMN);
end;
3、user_source
视图中保存源代码,源代码按行保存。
按存储过程查询也可以获得存储过程源程序。
SQL> select text from user_source where name='PRO_TEST' ;
TEXT
--------------------------------------------------------------------------------
PROCEDURE pro_test(t_name in varchar2,
s_column in varchar2) AS
v_TABLENAME varchar2(30);
v_STAND_CLOUMN varchar2(30);
v_CHECK_DAY date;
v_sql varchar2(2000);
begin
v_TABLENAME := t_name;
v_STAND_CLOUMN := s_column;
v_sql := 'SELECT MIN('|| s_column ||') from ' || v_TABLENAME;
dbms_output.put_line(v_sql);
execute immediate v_sql
into v_CHECK_DAY ;
dbms_output.put_line(v_check_day);
dbms_output.put_line(v_STAND_CLOUMN);
end;
22 rows selected