PL/SQL嵌套记录与记录集合

PL/SQL嵌套记录与记录集合

转自：http://blog.csdn.net/leshami/article/details/7569673

将多个逻辑上不相关列组合到一起形成了PL/SQL的记录类型，从而可以将记录类型作为一个整体对待来处理。
而且PL/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 ), '' ) || ',  
                                                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;  


7、几点注意事项：  

a、不能测试记录是否为NULL、是否相等或不等，下面的操作都是非法的。  
              IF dept_rec IS NULL THEN ...  
              IF dept_rec1 = dept_rec2 THEN ...  
 
b、记录类型不同于变长数组与嵌套表，不能存储在数据库中 

---->Author : Robinson Cheng
---->Blog : http://blog.csdn.net/robinson_0612