oracle存储过程的相关视图

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