oracle 定义pkg,单独create type还是统一定义在一个package里好？

最新推荐文章于 2022-11-21 11:23:50 发布

橘子洲豪杰

最新推荐文章于 2022-11-21 11:23:50 发布

阅读量175

点赞数

文章标签： oracle 定义pkg

jxc_hn 发表于 2016-1-27 08:22 thread-2052029-1-1.html

为什么？在包里的集合对象不能用table函数转成虚拟表？

系统包应该可以

SQL> explain plan for select * from dual;

已解释。

SQL> set lines 120 pages 50000

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 272002086

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

已选择8行。

SQL> desc dbms_xplan

FUNCTION BUILD_PLAN_XML RETURNS XMLTYPE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

PLAN_ID NUMBER IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

PLAN_TAG VARCHAR2 IN DEFAULT

REPORT_REF VARCHAR2 IN DEFAULT

FUNCTION DIFF_PLAN RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_TEXT CLOB IN

OUTLINE CLOB IN

USER_NAME VARCHAR2 IN DEFAULT

FUNCTION DIFF_PLAN_AWR RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE1 NUMBER IN

PLAN_HASH_VALUE2 NUMBER IN

FUNCTION DIFF_PLAN_CURSOR RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN

CURSOR_CHILD_NUM1 NUMBER IN

CURSOR_CHILD_NUM2 NUMBER IN

FUNCTION DIFF_PLAN_OUTLINE RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_TEXT CLOB IN

OUTLINE1 CLOB IN

OUTLINE2 CLOB IN

USER_NAME VARCHAR2 IN DEFAULT

FUNCTION DIFF_PLAN_SQL_BASELINE RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

BASELINE_PLAN_NAME1 VARCHAR2 IN

BASELINE_PLAN_NAME2 VARCHAR2 IN

FUNCTION DISPLAY RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT

DB_ID NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_ID VARCHAR2 IN DEFAULT

CURSOR_CHILD_NO NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_PLAN RETURNS CLOB

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

TABLE_NAME VARCHAR2 IN DEFAULT

STATEMENT_ID VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FILTER_PREDS VARCHAR2 IN DEFAULT

TYPE VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQLSET RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQLSET_NAME VARCHAR2 IN

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER(38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

SQLSET_OWNER VARCHAR2 IN DEFAULT

FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

SQL_HANDLE VARCHAR2 IN DEFAULT

PLAN_NAME VARCHAR2 IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

FUNCTION FORMAT_NUMBER RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

NUM NUMBER IN

FUNCTION FORMAT_NUMBER2 RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

NUM NUMBER IN

FUNCTION FORMAT_SIZE RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

NUM NUMBER IN

FUNCTION FORMAT_SIZE2 RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

NUM NUMBER IN

FUNCTION FORMAT_TIME_S RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

NUM NUMBER IN

FUNCTION GET_PLANDIFF_REPORT_XML RETURNS XMLTYPE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

REPORT_REF VARCHAR2 IN DEFAULT

TID NUMBER IN

METHOD VARCHAR2 IN

FUNCTION PREPARE_PLAN_XML_QUERY RETURNS VARCHAR2

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

PLAN_QUERY VARCHAR2 IN

FUNCTION PREPARE_RECORDS RETURNS DBMS_XPLAN_TYPE_TABLE

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

PLAN_CUR REF CURSOR IN

I_FORMAT_FLAGS BINARY_INTEGER IN

FUNCTION VALIDATE_FORMAT RETURNS BOOLEAN

参数名称类型输入/输出默认值?

------------------------------ ----------------------- ------ --------

HASPLANSTATS BOOLEAN IN

FORMAT VARCHAR2 IN

FORMAT_FLAGS BINARY_INTEGER OUT

SQL>

橘子洲豪杰

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 定义pkg,单独create type还是统一定义在一个package里好？

jxc_hn 发表于 2016-1-27 08:22 为什么？在包里的集合对象不能用table函数转成虚拟表？系统包应该可以SQL> explain plan for select * from dual;已解释。SQL> set lines 120 pages 50000SQL> select * from table(dbms_xplan.display);PLAN_TAB...
复制链接

扫一扫