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

简介: [20140820]显示存储过程的参数.txt --上午写一个显示存储过程参数的例子,便于自己以后使用。 --主要是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

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DATA_TYPE            D
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- -------------------- -
SYS        DBMS_METADATA        GET_DDL                                 1                      CLOB                 OUT       CLOB                 N
                                                                        2 OBJECT_TYPE          VARCHAR2             IN        VARCHAR2             N
                                                                        3 NAME                 VARCHAR2             IN        VARCHAR2             N
                                                                        4 SCHEMA               VARCHAR2             IN        VARCHAR2             Y
                                                                        5 VERSION              VARCHAR2             IN        VARCHAR2             Y
                                                                        6 MODEL                VARCHAR2             IN        VARCHAR2             Y
                                                                        7 TRANSFORM            VARCHAR2             IN        VARCHAR2             Y


7 rows selected.

目录
相关文章
|
存储
SQLServer存储过程中的输出参数的使用
SQLServer存储过程中的输出参数的使用
59 0
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
MySQL数据库——存储过程-if条件判断、参数、case(介绍、用法、案例)
378 0
|
存储 SQL 关系型数据库
MySQL中不同类型参数存储过程示例
MySQL中不同类型参数存储过程示例
147 0
|
存储 SQL 关系型数据库
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
345 0
|
存储 SQL NoSQL
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
489 0
|
存储 SQL
sql server 存储过程传递表名参数及Dynamic SQL
sql server 存储过程传递表名参数及Dynamic SQL
sql server 存储过程传递表名参数及Dynamic SQL
|
存储 SQL 关系型数据库
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
2279 0
MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)
|
存储 关系型数据库 MySQL
【MySQL】使用pdo调用存储过程 --带参数输出
【MySQL】使用pdo调用存储过程 --带参数输出
195 0
【MySQL】使用pdo调用存储过程 --带参数输出
|
存储 SQL 数据库连接
SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值
原文:SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值     我们知道SQL SERVER建立链接服务器(Linked Server)可以选择的驱动程序非常多,最近发现使用ODBC 的 Microsoft OLE DB 驱动程序建立的链接服务器(Linked Server), 调用存储过程过程时,参数不能为NULL值。
889 0
|
存储 SQL
SQL得到任意一个存储过程的参数列表sp_procedure_params_rowset
SQL得到任意一个存储过程的参数列表sp_procedure_params_rowsetexec sp_procedure_params_rowset 'up_rpt营业收入汇总表' PROCEDURE_CATALOG PROCEDURE_SCHEMA PROCEDURE...
1096 0