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

[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.

时间： 2024-07-30 11:00:03

[20140820]显示存储过程的参数.txt的相关文章

[20161123]failover与会话参数.txt

[20161123]failover与会话参数.txt --前几天itpub上有人问的问题,链接http://www.itpub.net/thread-2071933-1-1.html,开始我以为是会话没有退出. --实际上对方已经重启过数据库,不过后来我估计对方打开登录审计,从结果上猜测是正确的,不过我的问题是我想了解 --会话在支持failover时,重新连接上时会话参数是否还是原来的,还有审计是否还有效(在已经关闭相关审计的情况下). --测试是最好的证明,还是通过例子来说明问题. 1.环

[20130226]ls -l显示的时间问题.txt

[20130226]ls -l显示的时间问题.txt # touch --date='2013-01-01 07:01:00' aa.txt# touch --date='2012-09-01 07:01:00' bb.txt# touch a.txt b.txt c.txt #  ls -l-rw-r--r-- 1 root root    0 Jan  1 07:01 aa.txt-rw-r--r-- 1 root root    0 Feb 26 09:20 a.txt-rw-r--r--

MySql存储过程 带参数处理方式

下文介绍的MySql存储过程是带参数的存储过程(动态执行SQL语句),该MySql存储过程是根据用户输入的条件和排序方式查询用户的信息,排序条件可以没有调用方式: call GetUsersDynamic('age<=30',''); /********动态查询用户的信息********/     CREATE PROCEDURE GetUsersDynamic(WhereCondition varchar(500),OrderByExpress varchar(100))     begin 

SQL 数据库中的存储过程的参数问题

  1.SQL 数据库中的存储过程的参数问题 怎么将SQL数据库中的存储过程中的参数既作为输出变量又作为输出变量? [sql] view plaincopy --drop proc proc_test --go create proc dbo.proc_test @in int, @out int out, @in_out int output as select @out = @in + @in_out, --1 + 2 = 3 @in_out = @out + 1 --3 + 1 = 4 g

关于ASP.NET 调用存储过程传参数的问题

问题描述 关于ASP.NET 调用存储过程传参数的问题 /// /// 执行存储过程 /// /// 存储过程名 /// 存储过程参数 /// SqlDataReader public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = OpenSqlConnection(); SqlCommand command =

sqlserver-写MSSQL存储过程 输入参数N（1……100之间）的整数，输出1+2+……N的和

问题描述 写MSSQL存储过程 输入参数N(1--100之间)的整数,输出1+2+--N的和 写MSSQL存储过程 输入参数N(1--100之间)的整数,输出1+2+--N的和.用sql server存储过程怎么写 解决方案 create PROCEDURE [dbo].exp1002 AS BEGIN set @tsum=@n while @n-1>=0 begin set @n=@n-1 set @tsum=@tsum+@n end print '得到的结果是:'+ convert(varc

oracle数据库存储过程带输入参数建表的语法和在oracle中调用语法

问题描述 oracle数据库存储过程带输入参数建表的语法和在oracle中调用语法 大神们,随便给几个例子参照一下啊,oracle数据库存储过程带输入参数建表的语法和在oracle中调用语法 解决方案 差不多这个意思,http://zhidao.baidu.com/link?url=lYFB7BBbSoybi5mTurmVbI88Q5R78nnhHyQUiMAhF3B4iyA1OiPCxY4xsRS7v_v0kBdbtCVvVlPHhfY9Rerx7K 解决方案二: create or repl

SQL SERVER使用ODBC 驱动建立的链接服务器调用存储过程时参数不能为NULL值

   我们知道SQL SERVER建立链接服务器(Linked Server)可以选择的驱动程序非常多,最近发现使用ODBC 的 Microsoft OLE DB 驱动程序建立的链接服务器(Linked Server), 调用存储过程过程时,参数不能为NULL值. 否则就会报下面错误提示: 对应的英文错误提示为: EXEC xxx.xxx.dbo.Usp_Test NULL,NULL,'ALL' Msg 7213, Level 16, State 1, Line 1 The attempt by

字符集-mysql 存储过程输出参数中文返回乱码

问题描述 mysql 存储过程输出参数中文返回乱码 我用C++ 通过ODBC访问mysql 数据库,mysql 数据库版本是5.6.12 odbc驱动版本5.2.5 数据库的字符集全部设置为utf8 驱动的字符集也设置为utf8. 程序中通过select直接从表中查出,中文不会乱码, 但通过存储过程的输出参数返回就是乱码. 求大神们帮忙.谢谢 解决方案 在存储过程里查询的时候,指定查询字符集