對象表與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 陳述式中不允許使用本地收集類型