PL/SQL-->PL/SQL记录

PL/SQL-->PL/SQL记录

      PL/SQL记录有着类似于表的数据结构，是一个或多个字段且拥有数据类型的集合体。定义了PL/SQL记录类型之后，可以定义PL/SQL记录变量。
      声明一个PL/SQL记录变量相当于定义了多个标量变量，简化了变量的声明，从而大大节省了内存资源。
      PL/SQL记录多用于简化单行多列的数据处理。

一、定义PL/SQL记录

      1.直接定义PL/SQL记录(首先定义记录类型，类型中包含记录成员，然后定义记录类型变量)

              TYPE type_name IS RECORD                                                                          --type_name用于指定自定义记录类型的名称
              (field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION],    --定义记录的成员、数据类型及缺省值
              field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION],
              ...
              field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]);
              record_name TYPE_NAME;                                                                              --定义记录变量名record_name

      2.使用%rowtype定义PL/SQL记录

              使用%rowtype时，记录成员名称和类型与所依赖对象(表，视图，游标)名称和类型完全相同
              对于表和视图，游标所有列定义时，使用%rowtype定义将大大节省内存空间
              record_name table_name%rowtype                          --基于不同的对象定义PL/SQL记录，此处为表
              record_name view_name%rowtype
              reocrd_name cursor_name%rowtype

二、PL/SQL记录的使用

      1. SELECT INTO语句中使用

              --使用PL/SQL记录变量

                      undefine no
                      DECLARE
                              TYPE emp_record_type IS RECORD
                              (
                                      name emp.ename%TYPE,
                                      salary emp.sal%TYPE,
                                      dno emp.deptno%TYPE
                              );
                              emp_record emp_record_type;
                      BEGIN                                          --下面的查询到的数据插入到记录变量，注意列之间的顺序与声明顺序保持一致

                              SELECT ename, sal, deptno INTO emp_record  
                              FROM emp WHERE empno = &no;
                              dbms_output.put_line(emp_record.name);    --输出时仅仅输出记录变量的一个成员emp_record.name
                      END;                     
                      Enter value for no: 7788
                      SCOTT

              --使用PL/SQL记录成员

                      undefine no
                      DECLARE
                              TYPE emp_record_type IS RECORD
                              (
                                      name emp.ename%TYPE,
                                      salary emp.sal%TYPE,
                                      dno emp.deptno%TYPE
                              );
                              emp_record emp_record_type;
                      BEGIN
                              SELECT ename, sal INTO emp_record.name, emp_record.salary  --此处仅仅使用到了name和salary 成员
                              FROM emp WHERE empno = &no;
                              dbms_output.put_line(emp_record.name);    --输出时仅仅输出记录变量的一个成员emp_record.name
                      END;
                      /                   

      2.INSERT语句中使用记录变量及成员

              --使用PL/SQL记录变量

                      DECLARE
                              dept_record dept%ROWTYPE;
                      BEGIN
                              dept_record.deptno := 50;
                              dept_record.dname := 'ADMINISTRATOR';
                              dept_record.loc := 'BEIJING';
                              INSERT INTO dept VALUES dept_record;
                      END;
                      /           

                      scott@ORCL> select * from dept where deptno=50;

                              DEPTNO DNAME                  LOC
                      ---------- -------------- -------------
                                      50 ADMINISTRATOR  BEIJING                   

           
              --使用PL/SQL记录成员

                      scott@ORCL> DECLARE
                          2  dept_record dept%ROWTYPE;
                          3  BEGIN
                          4  dept_record.deptno := 60;
                          5  dept_record.dname := 'SALES';
                          6  INSERT INTO dept(deptno, dname) VALUES(dept_record.deptno, dept_record.dname);
                          7  END;
                          8  /
                      PL/SQL procedure successfully completed.     


                      scott@ORCL> select * from dept where deptno in (50,60);

                              DEPTNO DNAME                  LOC
                      ---------- -------------- -------------
                                      50 ADMINISTRATOR  BEIJING
                                      60 SALES                             

      3.UPDATE语句中使用记录变量及成员

              --使用PL/SQL记录变量(使用ROW来更新整行)

                      DECLARE
                              dept_record dept%ROWTYPE;
                      BEGIN
                              dept_record.deptno := 50;
                              dept_record.dname := 'SERVICE';
                              dept_record.loc := 'GuangZhou';
                              UPDATE dept SET ROW = dept_record WHERE deptno = 50;--注意update时，使用ROW来表示整行
                      END;
                      /                           

              --使用PL/SQL记录成员(使用成员记录更新单列或多列)

                      scott@ORCL> DECLARE
                          2  dept_record dept%ROWTYPE;
                          3  BEGIN
                          4          dept_record.loc:='ShangHai';
                          5          UPDATE dept SET loc=dept_record.loc WHERE deptno=60;
                          6  END;
                          7  /                 
 

      4.DELETE语句中使用记录变量及成员

                      DECLARE
                              dept_record dept%ROWTYPE;
                      BEGIN
                              dept_record.deptno:=60;
                              DELETE FROM dept WHERE deptno=dept_record.deptno;
                      END;
                      /   


三、PL/SQL记录使用时的几个问题

      1.记录成员非空值的问题(非空值应当在初始化时赋值，而不是在使用时赋值)

              scott@ORCL> DECLARE
                  2  TYPE ex_type IS RECORD
                  3          (col1 NUMBER(3),
                  4            col2 VARCHAR2(5) NOT NULL);
                  5  ex_record ex_type;
                  6  BEGIN
                  7          ex_record.col1:=15;
                                ex_record.col1:=TO_CHAR(ex_record.col1);
                  8          ex_record.col2:='John';
                  9          DBMS_OUTPUT.PUT_LINE('ex_record.col1 is '||ex_record.col1);
                10          DBMS_OUTPUT.PUT_LINE('ex_record.col2 is '||ex_record.col2);
                11  END;
                12  /

                        col2 VARCHAR2(5) NOT NULL);
                        *
              ERROR at line 4:
              ORA-06550: line 4, column 6:
              PLS-00218: a variable declared NOT NULL must have an initialization assignment--错误消息指出非空值应当在初始化时指定


              DECLARE
                  TYPE ex_type IS RECORD(
                      col1 NUMBER(3),
                      col2 VARCHAR2(5) NOT NULL := 'John');  --注意对于非空值应当在初始化时赋值，而不是在使用时赋值
                  ex_record ex_type;
              BEGIN
                  ex_record.col1 := 15;
                  ex_record.col1 := TO_CHAR(ex_record.col1);
                  --ex_record.col2:='John';
                  DBMS_OUTPUT.PUT_LINE('ex_record.col1 is ' || ex_record.col1);
                  DBMS_OUTPUT.PUT_LINE('ex_record.col2 is ' || ex_record.col2);
              END;

              ex_record.col1 is 15
              ex_record.col2 is John
     

      2.记录变量之间的赋值问题

              下面的例子中两个PL/SQL变量name_rec1与name_rec2尽管具有表面上相同的定义，但两者之间不能相互赋值

                      DECLARE
                          TYPE ex_type1 IS RECORD(
                              first_name VARCHAR2(15),
                              last_name  VARCHAR2(30));
                          TYPE ex_type2 IS RECORD(
                              first_name VARCHAR2(15),
                              last_name  VARCHAR2(30));
                          ex_rec1 ex_type1;
                          ex_rec2 ex_type2;
                      BEGIN
                          ex_rec1.first_name := 'Robinson';
                          ex_rec1.last_name  := 'Cheng';
                          ex_rec2                      := ex_rec1;  -- 不合理的赋值方式
                      END;     

                          ex_rec2                      := ex_rec1;
                                                                      *
                      ERROR at line 13:
                      ORA-06550: line 13, column 25:
                      PLS-00382: expression is of wrong type
                      ORA-06550: line 13, column 3:
                      PL/SQL: Statement ignored

                 
              具有相同的记录类型时，不同记录变量之间可以相互赋值

                      DECLARE
                          TYPE ex_type1 IS RECORD(
                              first_name VARCHAR2(15),
                              last_name  VARCHAR2(30));
                          ex_rec1 ex_type1;
                          ex_rec2 ex_type1;
                      BEGIN
                          ex_rec1.first_name := 'Robinson';
                          ex_rec1.last_name  := 'Cheng';
                          ex_rec2                      := ex_rec1;
                          DBMS_OUTPUT.PUT_LINE('ex_rec1 is '||ex_rec1.first_name||' '||ex_rec1.last_name);
                          DBMS_OUTPUT.PUT_LINE('ex_rec2 is '||ex_rec2.first_name||' '||ex_rec2.last_name);
                      END;             

                      ex_rec1 is Robinson Cheng
                      ex_rec2 is Robinson Cheng

      3.基于表，基于游标，基于自定义PL/SQL记录的综合使用

              DECLARE
                  CURSOR dept_cur IS --声明游标
                      SELECT * FROM dept WHERE deptno = 30;
                  TYPE dept_type IS RECORD( --声明一个自定义的PL/SQL记录类型
                      deptno NUMBER(2),
                      dname  VARCHAR2(14),
                      loc      VARCHAR2(13));
                  dept_rec1 dept%ROWTYPE; --声明基于表dept的记录变量
                  dept_rec2 dept_cur%ROWTYPE; --声明基于游标dept_cur的记录变量
                  dept_rec3 dept_type; --声明基于自定义dept_type的记录变量
              BEGIN
                  SELECT * INTO dept_rec1 FROM dept WHERE deptno = 30; --将查询的结果插入到基于表dept的记录变量中
                  OPEN dept_cur;
                  LOOP
                      FETCH dept_cur
                          INTO dept_rec2; --将游标的内容插入到游标记录变量中
                      EXIT WHEN dept_cur%NOTFOUND;
                  END LOOP;
                  dept_rec1 := dept_rec2;
                  dept_rec3 := dept_rec2;
                  DBMS_OUTPUT.PUT_LINE(dept_rec1.deptno || ' ' || dept_rec1.dname);
                  DBMS_OUTPUT.PUT_LINE(dept_rec2.deptno || ' ' || dept_rec2.dname);
                  DBMS_OUTPUT.PUT_LINE(dept_rec3.deptno || ' ' || dept_rec3.dname);
              END;

              30 SALES
              30 SALES
              30 SALES

转自：http://blog.csdn.net/robinson_0612/article/details/6084390