包裹声明的内容 [英] content of the package declaration

问题描述

如何查看程序包的描述aria和特定类型的内容?(pl sql)

例如在p1包中，我有3个函数f1, f2, f3； 2个过程p1, p2和2个变量v1, v2.

我需要一个包含两列的列表:一列用于名称(f1, f2等)，一列用于类型(函数，过程，变量等).

解决方案

您可以通过查询 解决方案

You can find details about the functions and procedures in a package by querying the ALL_ARGUMENTS data dictionary view, or its brethren USER_ARGUMENTS and DBA_ARGUMENTS.

For an example I created the following package:

CREATE OR REPLACE PACKAGE demo AS
    PROCEDURE p_none;
    PROCEDURE p_two(a INTEGER, b INTEGER);

    FUNCTION f_none RETURN INTEGER;
    FUNCTION f_three(c INTEGER, q INTEGER, z INTEGER) RETURN INTEGER;
END;

I then ran the following query against it:

SQL> select object_name, argument_name, sequence, in_out
  2    from all_arguments
  3   where package_name = 'DEMO'
  4   order by object_name, sequence;

OBJECT_NAME                    ARGUMENT_NAME                    SEQUENCE IN_OUT
------------------------------ ------------------------------ ---------- ---------
F_NONE                                                                 1 OUT
F_THREE                                                                1 OUT
F_THREE                        C                                       2 IN
F_THREE                        Q                                       3 IN
F_THREE                        Z                                       4 IN
P_NONE                                                                 0 IN
P_TWO                          A                                       1 IN
P_TWO                          B                                       2 IN

Here you can see all of the arguments to the functions and procedures in our package. Note that there is an extra entry with a null argument name for the return value for each of the two functions. Also, the procedure that has no arguments has a row with a null argument name and a zero SEQUENCE value.

So, to list all functions, you could search for all entries in this view with a null argument name and a SEQUENCE value not equal to 0:

SQL> select distinct object_name
  2    from all_arguments
  3   where package_name = 'DEMO'
  4     and argument_name is null
  5     and sequence != 0;

OBJECT_NAME
------------------------------
F_THREE
F_NONE

Listing procedures in a similar way is a little trickier:

SQL> select distinct object_name
  2    from all_arguments a1
  3   where package_name = 'DEMO'
  4     and (   sequence = 0
  5          or not exists (select 0
  6                           from all_arguments a2
  7                          where a2.package_name = 'DEMO'
  8                            and a2.object_name = a1.object_name
  9                            and a2.argument_name is null));

OBJECT_NAME
------------------------------
P_TWO
P_NONE

While this approach appears to work with procedures and functions, I don't know how to list the package-scope variables, types and other things declared within a package header without parsing the package spec, as suggested by @wweicker.

这篇关于包裹声明的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！