PL/SQL 嵌套記錄與記錄集合

    將多個邏輯上不相關列組合到一起形成了PL/SQL的記錄類型，從而可以將記錄類型作為一個整體對待來處理。而且PL/SQL記錄類型可以進行
嵌套以及基於PL/SQL記錄來定義聯合數組，巢狀表格等。本文首先回顧了PL/SQL記錄的幾種聲明形式，接下來主要描述PL/SQL記錄的嵌套以及基於
記錄的集合。
    有關PL/SQL 記錄文法、以及在SQL中使用PL/SQL記錄，請參考：PL/SQL --> PL/SQL 記錄

1、下面的樣本同時描述了基於表，基於遊標，以及基於使用者自訂的記錄DECLARE   rec_tab       dept%ROWTYPE;             -->基於表類型使用ROWTYPE來聲明記錄變數     v_counter     PLS_INTEGER := 0;   CURSOR cur_tab IS                       -->聲明遊標      SELECT dname, loc FROM dept;   rec_cur_tab   cur_tab%ROWTYPE;          -->基於定義的遊標使用ROWTYPE來聲明記錄變數     TYPE dept_rec_type IS RECORD            -->使用者自訂記錄類型   (      dname   dept.dname%TYPE              -->可以使用TYPE屬性，也可以使用自訂的資料類型     ,loc     dept.loc%TYPE   );   dept_rec      dept_rec_type;            -->基於自訂的記錄類型來聲明記錄變數  BEGIN   SELECT *   INTO   rec_tab                          -->使用select into為記錄變數賦值   FROM   dept   WHERE  deptno = 10;   DBMS_OUTPUT.put_line( '------- First print record based on table--------' );   DBMS_OUTPUT.put_line( 'Record is ' || rec_tab.dname || ',' || rec_tab.loc );   OPEN cur_tab;   DBMS_OUTPUT.put_line( '------- Next print record based on cursor--------' );   LOOP      FETCH cur_tab INTO rec_cur_tab;     -->使用fetch into為記錄變數賦值        EXIT WHEN cur_tab%NOTFOUND;      v_counter   := v_counter + 1;      DBMS_OUTPUT.put_line( 'Record ' || v_counter || ' is ' || rec_cur_tab.dname || ',' || rec_cur_tab.loc );   END LOOP;   CLOSE cur_tab;   SELECT dname, loc                     -->對自訂的記錄變數賦值        INTO   dept_rec   FROM   dept   WHERE  deptno = 20;   DBMS_OUTPUT.put_line( '------- Finally print record based on user defined record--------' );   DBMS_OUTPUT.put_line( 'Record is ' || dept_rec.dname || ',' || dept_rec.loc );END;2、記錄的賦值與引用DECLARE   TYPE rec1_t IS RECORD       -->聲明自訂記錄類型   (      field1   VARCHAR2( 16 )     ,field2   DATE   );   TYPE rec2_t IS RECORD       -->聲明自訂記錄類型   (      id     INTEGER NOT NULL:= -1    -->注意，此時使用NOT NULL約束，因此要賦初值，否則報錯     ,name   VARCHAR2( 64 ) NOT NULL:= '[anonymous]'   );   rec1   rec1_t;              -->聲明自訂記錄類型變數rec1和rec2   rec2   rec2_t;BEGIN   rec1.field1 := 'Yesterday';    -->賦值與引用時，使用record_name.field_name方式   rec1.field2 := TRUNC( SYSDATE - 1 );   DBMS_OUTPUT.put_line( 'rec1 values are ' || rec1.field1 || ',' || rec1.field2 );   DBMS_OUTPUT.put_line(  'rec2 values is '||rec2.name );END;3、為記錄賦預設值DECLARE   TYPE recordtyp IS RECORD   (      field1   NUMBER     ,field2   VARCHAR2( 32 ) DEFAULT 'something'   );   rec1   recordtyp;   rec2   recordtyp;BEGIN   -- 下面為變數rec1賦值   rec1.field1 := 100;   rec1.field2 := 'something else';   --下面通過使用變數rec2將其值賦給rec1，則rec1恢複到原始狀態，即Field1為NULL，field2為something   rec1        := rec2;   DBMS_OUTPUT.put_line( 'Field1 = ' || NVL( TO_CHAR( rec1.field1 ), '<NULL>' ) || ',                         field2 = ' || rec1.field2 );END;4、記錄類型作為過程的參數進行傳遞DECLARE   TYPE emp_rec_type IS RECORD                               -->自訂記錄類型   (      eno     NUMBER( 6 )     ,esal    NUMBER( 8, 2 )     ,ename   VARCHAR2( 10 )   );   emp_info   emp_rec_type;                                  -->聲明記錄類型變數   PROCEDURE raise_salary( emp_info IN OUT emp_rec_type ) IS -->本地過程用於增加僱員薪水,其參數為IN OUT 型記錄類型   BEGIN      UPDATE emp      SET    sal          = sal + sal * emp_info.esal      WHERE  empno = emp_info.eno      RETURNING ename, sal                                   -->使用returning 子句將ename以及更新後的薪水賦值給記錄變數        INTO   emp_info.ename, emp_info.esal;   END raise_salary;BEGIN                                                        -->主程式塊   emp_info.eno := 7788;                                     -->對記錄變數賦值，此時emp_info.ename為NULL，由調用過程產生    emp_info.esal := 0.5;   raise_salary( emp_info );   DBMS_OUTPUT.put_line( 'User ' || emp_info.ename || '''s new salary is ' || emp_info.esal );END;5、嵌套記錄可以在記錄類型中包含對象、集合和其他的記錄(又叫嵌套記錄)。但是物件類型中不能把RECORD 類型作為它的屬性。DECLARE   TYPE name_type IS RECORD                  -->定義記錄類型   (      first_name   VARCHAR2( 15 )     ,last_name    VARCHAR2( 20 )   );   TYPE person_info_type IS RECORD           -->定義記錄類型   (      id          NUMBER( 6 )     ,name        name_type                  -->name的類型為name_type，即嵌套      ,job_title   jobs.job_title%TYPE   );   person_rec   person_info_type;            -->聲明記錄變數BEGIN   SELECT employee_id         ,first_name         ,last_name         ,job_title   INTO   person_rec.id         ,person_rec.name.first_name    -->注意此時嵌套記錄中的引用方法         ,person_rec.name.last_name     -->enclosing_record.(nested_record或者nested_collection).field_name         ,person_rec.job_title   FROM   employees e JOIN jobs j ON e.job_id = j.job_id AND ROWNUM < 2;   DBMS_OUTPUT.put_line( 'First name is ' || person_rec.name.first_name );   DBMS_OUTPUT.put_line( 'Last name is ' || person_rec.name.last_name );END;6、記錄集合所有基於記錄的集合在此統統可以稱之為記錄集合，即該集合類型是基於記錄類型之上的。--下面的樣本是一個使用了基於遊標類型的聯合數組的記錄集合DECLARE   CURSOR cur_emp IS                        -->聲明一個遊標      SELECT empno, ename, hiredate      FROM   emp      WHERE  deptno = 20      ORDER BY 1;   TYPE emp_tab_type IS TABLE OF cur_emp%ROWTYPE    -->基於遊標類型定義了一個聯合數組                           INDEX BY BINARY_INTEGER;   emp_tab     emp_tab_type;                -->聲明複合變數   v_counter   INTEGER := 0;BEGIN   FOR emp_rec IN cur_emp   LOOP      v_counter   := v_counter + 1;        -->v_counter用於控制下標        emp_tab( v_counter ).empno := emp_rec.empno;   -->給複合變數賦值，注意引用方法      emp_tab( v_counter ).ename := emp_rec.ename;      emp_tab( v_counter ).hiredate := emp_rec.hiredate;      DBMS_OUTPUT.put_line('Recored '||v_counter||' is '||emp_tab(v_counter).ename|| ',' || emp_tab( v_counter ).hiredate );   END LOOP;END;--下面的樣本是一個基於自訂記錄類型的嵌條表，注意巢狀表格需要擴充--我們知道，遊標通常為單條多列的記錄，而聯合數組，巢狀表格以及變長數組為單列多行--因此記錄類型與集合類型的複合我們可以將其想象成一張二維表，因此對於這種類型的操作，更高效的是直接使用bulk collect子句來操縱--下面不再列出使用bulk collect 的樣本，注,使用bulk collect 子句使，集合類型不需要手動擴充DECLARE   TYPE rec_type IS RECORD                      -->定義記錄類型   (      ename      emp.ename%TYPE     ,empno      emp.empno%TYPE     ,hiredate   emp.hiredate%TYPE   );   TYPE emp_tab_type IS TABLE OF rec_type;      -->定義基於記錄類型的巢狀表格        emp_tab     emp_tab_type := emp_tab_type( ); -->初始化巢狀表格   v_counter   INTEGER := 0;BEGIN   FOR emp_rec IN (SELECT *                   FROM   emp                   WHERE  deptno = 20 )   LOOP      v_counter   := v_counter + 1;      emp_tab.EXTEND;                           -->需要使用extend方式來擴充       emp_tab( v_counter ).empno := emp_rec.empno;      emp_tab( v_counter ).ename := emp_rec.ename;      emp_tab( v_counter ).hiredate := emp_rec.hiredate;      DBMS_OUTPUT.put_line('Recored '||v_counter||' is '||emp_tab(v_counter).ename||','||emp_tab( v_counter ).hiredate );   END LOOP;END;---->Author : Robinson Cheng ---->Blog : http://blog.csdn.net/robinson_06127、幾點注意事項：a、不能測試記錄是否為NULL、是否相等或不等，下面的操作都是非法的。IF dept_rec IS NULL THEN ...IF dept_rec1 = dept_rec2 THEN ...b、記錄類型不同於變長數組與巢狀表格，不能儲存在資料庫中

更多參考：

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫