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

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

转自：http://blog.youkuaiyun.com/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.youkuaiyun.com/robinson_0612