如何得到存储过程/函数的参数列表？

昨天一同事问我， oracle 中如何得到存储过程和函数的参数列表？

在SQL SERVER 2000中，可以通过查询INFORMATION_SCHEMA.PARAMETERS来实现：

SELECT
SPECIFIC_NAME,
PARAMETER_NAME,
DATA_TYPE,
PARAMETER_MODE
FROM INFORMATION_SCHEMA.PARAMETERS
ORDER BY SPECIFIC_NAME

SPECIFIC_NAME PARAMETER_NAME DATA_TYPE PARAMETER_MOD
———————————————- —————————— ——————- ————————
sp_QueryPONumDetailMaterialInfo @MaterialID varchar IN
sp_QueryPONumDetailMaterialInfo @StoredID varchar IN
sp_QueryPONumInfo @CustomerID char IN
sp_QueryPONumInfo @PONum varchar IN
sp_QueryPriceInfo @CustomerID char IN
sp_QueryPriceInfo @MaterialID varchar IN
sp_QueryStoredInfo @StoredID char IN
sp_QuerySubBomInfo @BomID varchar IN
sp_QueryUserAuth @LogonID varchar IN
sp_QueryUserAuth @Password varchar IN
sp_SelectSubBomInfo @BomID varchar IN
sp_SelectSubBomInfo @ProductNo varchar IN
sp_SelectSubBomInfo @CustomerID char IN
sp_SelectSubBomInfo @OrderTotal numeric IN
sp_UpdateAuthRight @LoginAccount varchar IN
sp_UpdateAuthRight @FunctionCode varchar IN

其实INFORMATION_SCHEMA.PARAMETERS是以sysobjects 和 syscolumns这两个系统表为基础的，因此通过查询这两个系统表也是可以达到同样的效果：

SELECT
so.name AS SPECIFIC_NAME,
sc.name AS PARAMETER_NAME,
st.name AS DATA_TYPE,
CASE WHEN sc.isoutparam = 1 then CASE WHEN sc.typestat > 0 THEN ‘INOUT’ ELSE ‘OUT’ END ELSE ‘IN’ END AS PARAMETER_MOD，—参数类型判断未必正确
FROM syscolumns sc,sysobjects so,systypes st
WHERE sc.id = so.id
AND sc.xtype = st.xtype
AND sc.xusertype = st.xusertype
AND so.xtype in(’FN’,'IF’,'RF’,'TF’,'P’)
ORDER BY so.name

在 oracle 中有两种方法来实现：

1．可用DBMS_DESCRIBE.DESCRIBE_PROCEDURE这个包来实现：

SQL> desc dbms_describe
PROCEDURE DESCRIBE_PROCEDURE
参数名称 类型 输入/输出默认值？
—————————— ———————– —— ——–
OBJECT_NAME VARCHAR2 IN
RESERVED1 VARCHAR2 IN
RESERVED2 VARCHAR2 IN
OVERLOAD TABLE OF NUMBER OUT
POSITION TABLE OF NUMBER OUT
LEVEL TABLE OF NUMBER OUT
ARGUMENT_NAME TABLE OF VARCHAR2(30) OUT
DATATYPE TABLE OF NUMBER OUT
DEFAULT_VALUE TABLE OF NUMBER OUT
IN_OUT TABLE OF NUMBER OUT
LENGTH TABLE OF NUMBER OUT
PRECISION TABLE OF NUMBER OUT
SCALE TABLE OF NUMBER OUT
RADIX TABLE OF NUMBER OUT
SPARE TABLE OF NUMBER OUT

其中：
OBJECT_NAME：你要描述的存储过程/函数的名称
OVERLOAD：如果你要描述的过程或函数是重载的，则会显示出不同的数字，以表示不同版本
POSITION：在参数列表中的位置，0表示该参数是函数的RETURN
LEVEL：如果参数是复合型的，比如：RECORD类型，则LEVEL为参数的级别
ARGUMENT_NAME：参数名称，如果是函数的返回，则为NULL
DATATYPE：参数的数据类型，用数字表示：
0 占位符，如果过程或函数没有参数的话
1 VARCHAR, VARCHAR, STRING
2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL
3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR (ANSI FIXED CHAR), CHARACTER
106 MLSLABEL
250 PL/SQL RECORD
251 PL/SQL TABLE
252 PL/SQL BOOLEAN

IN_OUT：表示参数的类型：
0 IN
1 OUT
2 IN OUT
LENGTH：参数的数据长度，跟数据类型对应

示例：

CREATE OR REPLACE PACKAGE describe_it IS

– Author : BWEN
– Created : 2006-3-8 11:15:01
– Purpose :– Public function and procedure declarations
FUNCTION dtcode_to_char(dtcode INTEGER) RETURN VARCHAR2;
PROCEDURE desc_proc(name VARCHAR2);
FUNCTION Pmode_to_char(pmode INTEGER) RETURN VARCHAR2;

END describe_it;
CREATE OR REPLACE PACKAGE BODY describe_it IS

– Function and procedure implementations
FUNCTION dtcode_to_char(dtcode INTEGER) RETURN VARCHAR2 IS
dty VARCHAR2(20);
BEGIN
CASE dtcode
WHEN 0 THEN dty := ‘Placeholder’;
WHEN 1 THEN dty := ‘VARCHAR’;
WHEN 2 THEN dty := ‘NUMBER’;
WHEN 3 THEN dty := ‘POSITIVE’;
WHEN 8 THEN dty := ‘LONG’;
WHEN 11 THEN dty := ‘ROWID’;
WHEN 12 THEN dty := ‘DATE’;
WHEN 23 THEN dty := ‘RAW’;
WHEN 24 THEN dty := ‘LONG RAW’;
WHEN 96 THEN dty := ‘CHAR’;
WHEN 106 THEN dty := ‘MLSLABEL’;
WHEN 250 THEN dty := ‘PL/SQL RECORD’;
WHEN 251 THEN dty := ‘PL/SQL TABLE’;
WHEN 252 THEN dty := ‘PL/SQL BOOLEAN’;
ELSE dty := ‘UNKONW’;
END CASE;
RETURN dty;
END;FUNCTION Pmode_to_char(pmode INTEGER) RETURN VARCHAR2 IS
c_pmode VARCHAR2(6);
BEGIN
CASE pmode
WHEN 0 THEN c_pmode := ‘IN’;
WHEN 1 THEN c_pmode := ‘OUT’;
WHEN 2 THEN c_pmode := ‘INOUT’;
END CASE;
RETURN c_pmode;
END;

PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
n INTEGER;
BEGIN
n := isize - LENGTHB(val);
IF n < 0 THEN
n := 0;
END IF;
DBMS_OUTPUT.PUT(val);
FOR i in 1..n LOOP
DBMS_OUTPUT.PUT(’ ‘);
END LOOP;
END prt_value;PROCEDURE desc_proc (name VARCHAR2) IS
overload DBMS_DESCRIBE.NUMBER_TABLE;
position DBMS_DESCRIBE.NUMBER_TABLE;
c_level DBMS_DESCRIBE.NUMBER_TABLE;
arg_name DBMS_DESCRIBE.VARCHAR2_TABLE;
dty DBMS_DESCRIBE.NUMBER_TABLE;
def_val DBMS_DESCRIBE.NUMBER_TABLE;
p_mode DBMS_DESCRIBE.NUMBER_TABLE;
length DBMS_DESCRIBE.NUMBER_TABLE;
precision DBMS_DESCRIBE.NUMBER_TABLE;
scale DBMS_DESCRIBE.NUMBER_TABLE;
radix DBMS_DESCRIBE.NUMBER_TABLE;
spare DBMS_DESCRIBE.NUMBER_TABLE;
idx INTEGER := 0;

结果如下：
SQL> execute describe_it.desc_proc(’F_GET_AVERAGE_COST’);
Position Name DTY Mode
——————————————————————————————-
0 RETURN PARAMETER NUMBER OUT
1 PI_ORG_SEQ NUMBER IN
2 PI_PROD_SEQ NUMBER IN
3 PI_LAST_DATE DATE IN
4 PI_CAL_DAYS NUMBER IN

2．通过查询ALL_ARGUMENTS或USER_ARGUMENTS来实现：
ALL_ARGUMENTS列出了所有当前用户能访问的存储过程和函数的参数
USER_ARGUMENTS列出了所有当前用户所拥有的存储过程和函数的参数

SQL> desc all_arguments;
名称 是否为空? 类型
————————————————————— ————- —————————-
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULT_VALUE LONG
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)

其中：
OBJECT_NAME：对象名称
PACKAGE_NAME：如果对象是PACKAGE成员，则会显示PACKAGE的名称
ARGUMENT_NAME：参数名称，如果是函数的返回，则该栏位为空
DATA_TYPE：参数的数据类型
IN_OUT：参数类型（IN/OUT/INOUT）

示例：
SQL>SELECT OBJECT_NAME,ARGUMENT_NAME,DATA_TYPE,IN_OUT FROM USER_ARGUMENTS;
OBJECT_NAME ARGUMENT_NAME DATA_TYPE IN_OUT
————————————– —————————— —————————— ———
DTCODE_TO_CHAR VARCHAR2 OUT
DTCODE_TO_CHAR DTCODE NUMBER IN
DESC_PROC NAME VARCHAR2 IN
PMODE_TO_CHAR VARCHAR2 OUT
PMODE_TO_CHAR PMODE NUMBER IN

当然ALL_ARGUMENTS也包含所有的内部函数，可通过此表查找出所有的内部函数名称：

SQL>SELECT DISTINCT OBJECT_NAME FROM ALL_ARGUMENTS WHERE PACKAGE_NAME= ‘STANDARD’;

OBJECT_NAME
——————————
ABS
ACOS
ADD_MONTHS
ASCII
ASCIISTR
ASIN
ATAN
ATAN2
BFILENAME
BITAND
CEIL
CHARTOROWID
CHR

OBJECT_NAME
——————————
COALESCE
COMMIT
COMMIT_CM
COMPOSE
CONCAT
CONVERT
COS
COSH
CUBE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP