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

最新推荐文章于 2024-11-01 12:20:35 发布

iteye_4537

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量98

点赞数

文章标签：数据库

将多个逻辑上不相关列组合到一起形成了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_0612
7、几点注意事项：
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记录

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述