oracle 查询当前用户下的存储过程,获取当前用户下的所有存储过程调用脚本

本帖最后由 qingyun 于 2012-1-4 17:58 编辑

花了2小时写了下面这个视图，项目中用到，用来在程序启动的时候，加载数据库调用的配置；

CREATE OR REPLACE VIEW PROC_SCRIPT_VW

AS

WITH A AS

(SELECT NVL2(A.PACKAGE_NAME, A.PACKAGE_NAME || '.', NULL) || OBJECT_NAME ||

NVL2(A.OVERLOAD, ';' || A.OVERLOAD, NULL) AS PROC_NAME,

MAX(DATA_TYPES) AS DATA_TYPES,

MAX(ARGUMENT_NAMES) AS ARGUMENT_NAMES,

MAX(IN_OUTS) AS IN_OUTS,

MIN(POSITIONS) AS POSITION

FROM (select PACKAGE_NAME,

OBJECT_NAME,

OVERLOAD,

POSITION,

ltrim(sys_connect_by_path(case

when instr(DATA_TYPE, 'CHAR') > 0 THEN

'String'

when instr(DATA_TYPE, 'NUMBER') > 0 THEN

case

when nvl(DATA_SCALE, 0) > 0 THEN

'Float'

else

'Integer'

end

when instr(DATA_TYPE, 'INTEGER') > 0 THEN

'Integer'

WHEN DATA_TYPE = 'REF CURSOR' THEN

'Cursor'

else

DATA_TYPE

end,

','),

',') DATA_TYPES,

ltrim(sys_connect_by_path(case

when  IN_OUT='IN' THEN

'Input'

when  IN_OUT='OUT' THEN

'OutPut'

when  IN_OUT='IN/OUT' THEN

'InputOutput'

ELSE

IN_OUT

end,

','),

',') IN_OUTS,

ltrim(sys_connect_by_path(a.ARGUMENT_NAME, ','), ',') ARGUMENT_NAMES,

ltrim(sys_connect_by_path(a.POSITION, ','), ',') POSITIONS

from (select a.*,

row_number() over(partition by PACKAGE_NAME, OBJECT_NAME, OVERLOAD order by POSITION) as rn

from SYS.DBA_ARGUMENTS a

WHERE OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')

order by PACKAGE_NAME,

OBJECT_NAME,

OVERLOAD

) a

start with a.rn = 1

connect by prior a.rn = a.rn - 1

and prior nvl(a.PACKAGE_NAME, 0) = nvl(a.PACKAGE_NAME, 0)

and prior a.OBJECT_NAME = a.OBJECT_NAME

and prior NVL(a.OVERLOAD, 0) = NVL(a.OVERLOAD, 0)) A

GROUP BY PACKAGE_NAME,

OBJECT_NAME,

OVERLOAD)

SELECT PROC_NAME, 'BEGIN ' || DECODE(POSITION, 0, ':RESULT=:') || PROC_NAME || NVL2(ARGUMENT_NAMES, '(' || ARGUMENT_NAMES || ')', NULL) || '; END;' AS PROC_SQL,

DATA_TYPES,IN_OUTS

FROM A

以上是11g的写法，其他版本可能会有差异，没测试过；

如果权限不够，需要在sysdba模式下，执行：Grant all ON  SYS.DBA_ARGUMENTS TO 当前用户；

当然，这个脚本还有个小bug ;就是对于没有任何参数的存储过程(不是包和函数)，是显示不了的；

如果非要显示，就需要用 dba_producres 和它 left join ;