[20140820]显示存储过程的参数.txt

最新推荐文章于 2023-11-30 14:00:07 发布

转载最新推荐文章于 2023-11-30 14:00:07 发布 · 75 阅读

本文介绍了一种在Oracle中查询存储过程参数的方法，通过自定义SQL脚本desc_proc.sql，可以详细列出存储过程的所有参数及其类型、默认值等信息。这种方法克服了标准DESC命令显示信息过大的缺点。

[20140820]显示存储过程的参数.txt

--上午写一个显示存储过程参数的例子，便于自己以后使用。
--主要是desc显示的信息太大，查看不是很方便。

SCOTT@test> desc dbms_stats
PROCEDURE ALTER_DATABASE_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
MONITORING                     BOOLEAN                 IN     DEFAULT
SYSOBJS                        BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_SCHEMA_TAB_MONITORING
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN     DEFAULT
MONITORING                     BOOLEAN                 IN     DEFAULT
PROCEDURE ALTER_STATS_HISTORY_RETENTION
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
RETENTION                      NUMBER                  IN
....

PROCEDURE UPGRADE_STAT_TABLE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME                        VARCHAR2                IN
STATTAB                        VARCHAR2                IN

SCOTT@test> desc dbms_stats.gather_table_stats
ERROR:
ORA-04043: object dbms_stats.gather_table_stats does not exist
--如果支持这种显示方式就好了。

$ cat desc_proc.sql
column owner format a10
column package_name format a20
column object_name format a30
column data_type format a20
column ARGUMENT_NAME format a20
set verify off
break on owner on package_name on object_name skip 1
PROMPT INPUT OWNER PACKAGE_NAME OBJECT_NAME
PROMPT sample : @desc_proc sys dbms_stats gather_%_stats
Prompt
SELECT owner,
         package_name,
         object_name,
         sequence,
         argument_name,
         data_type,
         in_out,
         data_type,
         defaulted
    FROM dba_arguments
   WHERE     owner = NVL(upper('&1'),'SYS')
         AND package_name = NVL(upper('&2'),'DBMS_STATS')
         AND object_name like NVL(upper('&3'),object_name)
AND data_level = 0
ORDER BY owner,package_name,object_name,sequence;

SCOTT@test> @desc_proc sys dbms_stats gather_s%_stats
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DATA_TYPE            D
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- -
SYS        DBMS_STATS           GATHER_SCHEMA_STATS                     1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        1 OWNNAME              VARCHAR2             IN        VARCHAR2             N
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        2 ESTIMATE_PERCENT     NUMBER               IN        NUMBER               Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        3 BLOCK_SAMPLE         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        4 METHOD_OPT           VARCHAR2             IN        VARCHAR2             Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        5 DEGREE               NUMBER               IN        NUMBER               Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        6 GRANULARITY          VARCHAR2             IN        VARCHAR2             Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        7 CASCADE              PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        8 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        9 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       10 OPTIONS              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       11 OBJLIST              TABLE                OUT       TABLE                N
                                                                       12 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       13 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       14 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       15 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       16 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       17 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y
                                                                       18 STATOWN              VARCHAR2             IN        VARCHAR2             Y
                                                                       19 NO_INVALIDATE        PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       20 GATHER_TEMP          PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       21 GATHER_FIXED         PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       22 STATTYPE             VARCHAR2             IN        VARCHAR2             Y
                                                                       23 FORCE                PL/SQL BOOLEAN       IN        PL/SQL BOOLEAN       Y
                                                                       24 OBJ_FILTER_LIST      TABLE                IN        TABLE                Y

                                GATHER_SYSTEM_STATS                     1 GATHERING_MODE       VARCHAR2             IN        VARCHAR2             Y
                                                                        2 INTERVAL             NUMBER               IN        NUMBER               Y
                                                                        3 STATTAB              VARCHAR2             IN        VARCHAR2             Y
                                                                        4 STATID               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 STATOWN              VARCHAR2             IN        VARCHAR2             Y

40 rows selected.

SCOTT@test> @desc_proc sys dbms_metadata get_ddl
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

7 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1254433/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1254433/