包-SYS_PACKAGES
📄字数 1.2K
👁️阅读量 加载中...
功能描述
SYS_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 SYS_PACKAGES WHERE pack_name='DBMS_OUTPUT';
+---------+-------------+-------------------------------------------------------------+----------------------------------------------
| PACK_ID | PACK_NAME | SPEC | BODY
+---------+-------------+-------------------------------------------------------------+----------------------------------------------
| 8193 | DBMS_OUTPUT | +|
| | | +| ----包体
| | | /***********创建DBMS_OUTPUT包*****************************/+| CREATE OR REPLACE PACKAGE BODY SYSDBA.DBMS_OU
| | | CREATE OR REPLACE PACKAGE SYSDBA.DBMS_OUTPUT +| IS
| | | COMMENT '输出打印信息包' +|
| | | IS +| PROCEDURE PUT_LINE(str VARCHAR)
| | | PROCEDURE PUT_LINE(str VARCHAR); +| IS
| | | END SYSDBA.DBMS_OUTPUT; | 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 SYS_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; | | |
+---------+-----------+----------------------------------+------------------------------------+-------+--------+