ALL_PACKAGES
📄字数 1.2K
👁️阅读量 加载中...
功能描述
ALL_PACKAGES系统视图用于存储和管理创建的包信息。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | DB_ID | INTEGER | 库ID | √ | √ |
| 1 | SCHEMA_ID | INTEGER | 模式ID | √ | √ |
| 2 | USER_ID | INTEGER | 属主ID | √ | √ |
| 3 | PACK_ID | INTEGER | 包ID | √ | √ |
| 4 | PACK_NAME | VARCHAR | 包名 | √ | √ |
| 5 | LANGUAGE | CHAR | 语言 | √ | √ |
| 6 | SPEC | CLOB | 包头定义 | √ | √ |
| 7 | BODY | CLOB | 包体定义 | √ | √ |
| 8 | CREATE_TIME | DATETIME | 创建时间 | √ | √ |
| 9 | VALID | BOOLEAN | 是否有效 | √ | √ |
| 10 | ALL_OK | BOOLEAN | 是否所有成员都有效 | √ | √ |
| 11 | COMMENTS | VARCHAR | 注释信息 | √ | √ |
| 12 | IS_SYS | BOOLEAN | 是否系统内建 | √ | √ |
| 13 | RESERVED1 | VARCHAR | 保留字段 | √ | √ |
| 14 | RESERVED2 | VARCHAR | 保留字段 | √ | √ |
| 15 | RESERVED3 | VARCHAR | 保留字段 | √ | √ |
应用举例
- 查询系统包的相关信息
sql
-- spec与body为clob字段，需用to_char查询
SQL> SELECT pack_id, pack_name, TO_CHAR(spec) spec, TO_CHAR(body) body, valid, is_sys FROM ALL_PACKAGES WHERE pack_name='DBMS_OUTPUT';
+---------+-------------+-------------------------------------------------------------+----------------------------------------------------+-------+--------+
| PACK_ID | PACK_NAME | SPEC | BODY | VALID | IS_SYS |
+---------+-------------+-------------------------------------------------------------+----------------------------------------------------+-------+--------+
| 8193 | DBMS_OUTPUT | +| +| T | T |
| | | +| ----包体 +| | |
| | | /***********创建DBMS_OUTPUT包*****************************/+| CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_OUTPUT +| | |
| | | CREATE OR REPLACE PACKAGE SYSDBA.DBMS_OUTPUT +| IS +| | |
| | | IS +| +| | |
| | | PROCEDURE PUT_LINE(str VARCHAR); +| PROCEDURE PUT_LINE(str VARCHAR) +| | |
| | | END SYSDBA.DBMS_OUTPUT; | IS +| | |
| | | | BEGIN +| | |
| | | | send_msg(str); +| | |
| | | | END; +| | |
| | | | +| | |
| | | | END SYSDBA.DBMS_OUTPUT; | | |
+---------+-------------+-------------------------------------------------------------+----------------------------------------------------+-------+--------+- 查询自定义包的相关信息
sql
SQL> CREATE PACKAGE pack_info IS
PROCEDURE put_line(msg VARCHAR);
END;
SQL> CREATE PACKAGE BODY pack_info IS
PROCEDURE put_line(msg VARCHAR) IS
BEGIN
SEND_MSG(msg);
END;
END;
SQL> SELECT pack_id, pack_name, TO_CHAR(spec) spec, TO_CHAR(body) body, valid, is_sys FROM ALL_PACKAGES WHERE pack_name='PACK_INFO';
+---------+-----------+----------------------------------+------------------------------------+-------+--------+
| PACK_ID | PACK_NAME | SPEC | BODY | VALID | IS_SYS |
+---------+-----------+----------------------------------+------------------------------------+-------+--------+
| 8211 | PACK_INFO | CREATE PACKAGE pack_info IS +| CREATE PACKAGE BODY pack_info IS +| T | F |
| | | PROCEDURE put_line(msg VARCHAR);+| PROCEDURE put_line(msg VARCHAR) IS+| | |
| | | END; | BEGIN +| | |
| | | | SEND_MSG(msg); +| | |
| | | | END; +| | |
| | | | END; | | |
+---------+-----------+----------------------------------+------------------------------------+-------+--------+