一直以来，对这两种类型一直存有疑惑，现在将自己的一些想法实验记录下来，以便以后查看跟踪改进。

 PLSQL表变量类型

TYPE typ_id_record IS RECORD(

      gid NUMBER(10),

      gno NUMBER(5),

      co NUMBER(5));

TYPE typ_id_table1 IS TABLE OF typ_id_record;

 

 对象表变量类型

CREATE OR REPLACE TYPE typ_id_object AS OBJECT

                                    (gid NUMBER(10),

                                     gno NUMBER(5),

                                     co NUMBER(5));

CREATE OR REPLACE TYPE typ_id_table AS TABLE OF typ_id_object;

 

1.bulk collect的使用的区别

PLSQL变量可以直接接收bulk collect，但是对象表变量就必须先进行转换

如下过程 p_f2中定义了一个对象表变量 tab_ids，直接将查询结果集一次性bulk collect放置到该变量时，会提示：

ORA-00947: 没有足够的值.如果定义的object变量是单个字段的话，则编译时是提示：

ORA-00932: 数据类型不一致

CREATE OR REPLACE PROCEDURE p_f2 IS

   tab_ids typ_id_table;

BEGIN

   SELECT gp.gid, gp.gno, gp.co   BULK COLLECT

     INTO tab_ids

     FROM p_table_test gp;

END p_f2;

 

解决办法：将object变量进行转换，如下所示：

CREATE OR REPLACE PROCEDURE p_f2 IS

   tab_ids typ_id_table;

BEGIN

   SELECT typ_id_object(gp.gid, gp.gno, gp.co)   BULK COLLECT

     INTO tab_ids

     FROM p_table_test gp;

END p_f2;

 

 如果采用的是PLSQL表变量，则直接接受查询的结果集就可以了，如下所示：

 

CREATE OR REPLACE PROCEDURE p_f4 IS

   tab_ids typ_id_table1;

BEGIN

   SELECT gp.gid, gp.gno, gp.co BULK COLLECT

     INTO tab_ids

     FROM p_table_test gp;

END p_f4;

 

在此，要感谢itpub的windtalker_cs，是他的回答让我明白了一直困扰我的一个问题，之前一直以为如果采用object

对象表，就无法使用bulk collect批量获取结果集。

 

2. 是否可以使用table函数

函数返回的若是PLSQL类型变量则无法使用table函数，而object类型变量可以使用table函数直接获取函数返回的结果集

 

CREATE OR REPLACE FUNCTION p_f2 RETURN typ_id_table IS

  

tab_fids typ_id_table := typ_id_table(); --object对象表类型

BEGIN

   FOR i IN 1 .. 100 LOOP

      tab_fids.extend;

      tab_fids(tab_fids.count) := typ_id_object(i, i + 1, i + 2);

   END LOOP;

   RETURN tab_fids;

END p_f2;

SQL> SELECT * FROM TABLE(p_f2);

        FID    GNO     CO

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

          1      2      3

          2      3      4

          3      4      5

          4      5      6

          5      6      7

          6      7      8

  ........................

 

 

 

如果函数返回的是record表类型，则无法使用table函数，如下所示：

CREATE OR REPLACE PACKAGE pkg_f2 IS

   TYPE typ_id_record IS RECORD(

      gid NUMBER(10),

      gno NUMBER(5),

      co  NUMBER(5));

   TYPE typ_id_table1 IS TABLE OF typ_id_record;  --在包头定义PLSQL表类型

   FUNCTION f_f2 RETURN typ_id_table1;

END pkg_f2;

CREATE OR REPLACE PACKAGE BODY pkg_f2 IS

   FUNCTION f_f2 RETURN typ_id_table1 IS

      tab_fids typ_id_table1;

   BEGIN

      SELECT rownum, rownum + 1, rownum + 2 BULK COLLECT

        INTO tab_fids

        FROM dual;

      RETURN tab_fids;

   END;

END pkg_f2;

SELECT * FROM TABLE(pkg_f2.f_f2);

提示：ORA-00902：无效数据类型

 

注：PLSQL表变量类型必须在schma级进行定义，即必须在包头定义该类型，否则会提示：

Error: PLS-00642: 在 SQL 语句中不允许使用本地收集类型