PolarDB：巢狀表格

巢狀表格具有以下特徵：

• 必須定義巢狀表格類型，之後可以為該巢狀表格型別宣告巢狀表格變數。使用巢狀表格變數（或簡稱為“表”）進行資料操作。
• 聲明巢狀表格變數時，巢狀表格最初不存在（它是一個null集合）。必須使用建構函式初始化null表。您還可以使用指派陳述式初始化表，其中賦值的右側是相同類型的初始化表。註：巢狀表格的初始化在Oracle中是必需的，但在SPL中是可選的。
• 鍵是正整數。
• 建構函式確定表中的元素數。EXTEND方法向表中添加了其他元素。註：使用建構函式來確定表中的元素數量以及使用EXTEND方法向表中添加其他元素在Oracle中是必需的，但在SPL中是可選的。
• 表可能是稀疏的-在索引值的賦值中可能存在間隙。
• 嘗試引用超出其初始化大小或擴充大小的表元素將導致SUBSCRIPT_BEYOND_COUNT異常。

TYPE IS TABLE語句用於在SPL程式的聲明部分中定義巢狀表格類型。

TYPE tbltype IS TABLE OF { datatype | rectype | objtype };

tbltype是分配給巢狀表格類型的標識符。datatype是純量資料型別，例如VARCHAR2或NUMBER。rectype是先前定義的記錄類型。objtype是先前定義的物件類型。

為了使用該表，必須聲明該巢狀表格類型的變數。以下是聲明表變數的文法。

• table tbltype

  table是分配給巢狀表格的標識符。tbltype是以前定義的巢狀表格類型的標識符。

  使用巢狀表格類型的建構函式初始化巢狀表格。

• tbltype ([ { expr1 | NULL } [, { expr2 | NULL } ] [, ...] ])

  tbltype是巢狀表格類型的建構函式的標識符，它與巢狀表格類型具有相同的名稱。expr1, expr2, … 是與表的元素類型相容的運算式。如果指定NULL，則會將相應的元素設定為null。如果參數列表為空白，則返回空的巢狀表格，這意味著表中沒有元素。如果表是從物件類型定義的，則exprn必須返回該物件類型的對象。對象可以是函數的傳回值或物件類型的建構函式，或者對象可以是同一類型的另一個巢狀表格的元素。

如果將EXISTS之外的集合方法應用於未初始化的巢狀表格，則會引發COLLECTION_IS_NULL異常。

以下是巢狀表格的建構函式樣本：

DECLARE
    TYPE nested_typ IS TABLE OF CHAR(1);
    v_nested        nested_typ := nested_typ('A','B');

使用以下文法參考資料表的元素。

table(n)[.element ]

table是先前聲明的表的標識符。n是正整數。如果從屬記錄類型或物件類型定義表的表類型，則[.element ]必須引用定義巢狀表格類型時所依據的物件類型中的記錄類型或屬性內的單個欄位。或者，可以通過省略[.element ]來引用整個記錄或對象。

以下是巢狀表格的樣本，已知其中將有四個元素。

DECLARE
    TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
    dname_tbl       dname_tbl_typ;
    CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dname_tbl := dname_tbl_typ(NULL, NULL, NULL, NULL);
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dname_tbl(i) := r_dept.dname;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME');
    DBMS_OUTPUT.PUT_LINE('----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(dname_tbl(j));
    END LOOP;
END;

上面的樣本產生以下輸出：

DNAME
----------
ACCOUNTING
OPERATIONS
RESEARCH
SALES

以下樣本從emp表中讀取前十名員工的姓名，將它們儲存在一個巢狀表格中，然後顯示錶中的結果。編寫的SPL代碼假設事先不知道要返回的員工數量。

DECLARE
    TYPE emp_rec_typ IS RECORD (
        empno       NUMBER(4),
        ename       VARCHAR2(10)
    );
    TYPE emp_tbl_typ IS TABLE OF emp_rec_typ;
    emp_tbl         emp_tbl_typ;
    CURSOR emp_cur IS SELECT empno, ename FROM emp WHERE ROWNUM <= 10;
    i               INTEGER := 0;
BEGIN
    emp_tbl := emp_tbl_typ();
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    FOR r_emp IN emp_cur LOOP
        i := i + 1;
        emp_tbl.EXTEND;
        emp_tbl(i) := r_emp;
    END LOOP;
    FOR j IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(emp_tbl(j).empno || '     ' ||
            emp_tbl(j).ename);
    END LOOP;
END;

請注意建立一個空表，其中建構函式emp_tbl_typ()作為匿名塊的可執行部分中的第一個語句。然後使用EXTEND集合方法針對從結果集返回的每個員工向表中添加元素。

以下是輸出。

EMPNO    ENAME
-----    -------
7369     SMITH
7499     ALLEN
7521     WARD
7566     JONES
7654     MARTIN
7698     BLAKE
7782     CLARK
7788     SCOTT
7839     KING
7844     TURNER

以下樣本顯示如何使用物件類型的巢狀表格。首先，使用部門名稱和位置的屬性建立物件類型。

CREATE TYPE dept_obj_typ AS OBJECT (
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);

以下匿名塊定義一個巢狀表格類型，其元素由dept_obj_typ物件類型組成。聲明、初始化巢狀表格變數，然後從dept表填充該變數。最後，顯示巢狀表格中的元素。

DECLARE
    TYPE dept_tbl_typ IS TABLE OF dept_obj_typ;
    dept_tbl        dept_tbl_typ;
    CURSOR dept_cur IS SELECT dname, loc FROM dept ORDER BY dname;
    i               INTEGER := 0;
BEGIN
    dept_tbl := dept_tbl_typ(
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL),
        dept_obj_typ(NULL,NULL)
    );
    FOR r_dept IN dept_cur LOOP
        i := i + 1;
        dept_tbl(i).dname := r_dept.dname;
        dept_tbl(i).loc   := r_dept.loc;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DNAME          LOC');
    DBMS_OUTPUT.PUT_LINE('----------     ----------');
    FOR j IN 1..i LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD(dept_tbl(j).dname,14) || ' ' ||
            dept_tbl(j).loc);
    END LOOP;
END;

以下是匿名塊的輸出。

DNAME          LOC
----------     ----------
ACCOUNTING     NEW YORK
OPERATIONS     BOSTON
RESEARCH       DALLAS
SALES          CHICAGO