如何确定Oracle软件包中子程序的类型 [英] How to determine types of subprograms in Oracle Package

问题描述

最好用一个例子来解释.给定以下数据库对象:

This is best explained with an example. Given the below database objects:

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE IS
BEGIN
  NULL;
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN NUMBER IS
BEGIN
  RETURN NULL;
END;
/

CREATE OR REPLACE PACKAGE TEST_PACKAGE IS

  PROCEDURE TEST_PROCEDURE;
  FUNCTION TEST_FUNCTION RETURN NUMBER;

END TEST_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE IS

  PROCEDURE TEST_PROCEDURE IS
  BEGIN
    NULL;
  END;

  FUNCTION TEST_FUNCTION RETURN NUMBER IS
  BEGIN
    RETURN NULL;
  END;

END TEST_PACKAGE;
/

如果我们向 USER_PROCEDURES 查询未打包的子程序，则会在 OBJECT_TYPE 列中报告其类型:

If we query USER_PROCEDURES for the unpackaged subprograms, we get their types reported in the OBJECT_TYPE column:

但是，如果我们查询包中的子程序，我们只会得到 PACKAGE 的 OBJECT_TYPE ，这并不是特别有用:

However if we query for the subprograms in the package, we only get an OBJECT_TYPE of PACKAGE, which is not especially useful:

是否可以通过查询来找出实际类型，而无需进行诸如 USER_SOURCE 的挖掘工作?我浏览了数据字典，但发现没有什么用.

Is it possible to query to find out the actual types without doing something like digging through USER_SOURCE? I've looked through the data dictionary but found nothing useful.

感谢您的帮助！

推荐答案

与@collapsar类似的想法，但是使用参数位置来区分过程和函数.除了从位置1开始的任何形式参数之外，函数在位置0处都有一个(未命名的)参数.过程没有位置为零的参数.

Similar idea to @collapsar, but using the argument position to differentiate between a procedure and a function. A function has an (unnamed) argument in position zero, in addition to any formal parameters which start at position one. Procedures don't have the position zero argument.

select up.object_name, up.procedure_name,
  case ua.position when 0 then 'FUNCTION' else 'PROCEDURE' end as type
from user_procedures up
left join user_arguments ua
on ua.object_id = up.object_id
and ua.subprogram_id = up.subprogram_id
and position = 0
where up.object_type = 'PACKAGE'
and up.object_name = 'TEST_PACKAGE'
and up.procedure_name is not null;

OBJECT_NAME                    PROCEDURE_NAME                 TYPE    
------------------------------ ------------------------------ ---------
TEST_PACKAGE                   TEST_FUNCTION                  FUNCTION 
TEST_PACKAGE                   TEST_PROCEDURE                 PROCEDURE

这篇关于如何确定Oracle软件包中子程序的类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！