对象表与PLSQL表类型 比较

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

 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 语句中不允许使用本地收集类型