10.PL_SQL——PL_SQL中的复合数据类型之RECORDS

一、记录和集合概述

         目前为止所介绍的变量的数据类型都属于Scalar，即标量，如×××，字符串，日期等等。任何一种编程语言都需要提供一些手段让用户创建更复杂的变量，PL/sql也不例外，本章就来介绍一下PL/SQL中的复合型变量。

PL/SQL的复合型变量主要包括两类：

              1. Records：记录

              2. Collection：集合。Collection又可以分为三种，

                              a. Associative  Array（INDEXTBY table）: 关联数组（或称索引表，和SQL中的INDEX OF TABLE不是一个概念）

                              b. Nested Table: 嵌套表

                              c. Varray: 可变长度列表。

二、记录和集合的区别

         Records可以形象的理解为一个容器，用来保存很多其他的数据类型，但每种数据类型只在记录中出现一次，例如一个记录中保存了如下数据类型：

PL/SQL Record：

这个记录中保存了布尔型、日期型、字符型和BLOB型变量，且每个变量都只出现了一次，每个存放其他数据类型的空间称为域（Field）

SQL> edit

DECLARE

    TYPE myrecord IS RECORD

        (id number(8),

            name varchar2(20)default 'Wilson',

            birthday DATE

        ); 

        person myrecord;

BEGIN  

        person.id :=12345;

        person.birthday :=SYSDATE;

       DBMS_OUTPUT.PUT_LINE('Name: '|| person.name);

END;   

/

SQL> /

Name: Wilson

===============================

SQL> edit

DECLARE

    person employees%ROWTYPE；

BEGIN

    SELECT * INTO person

    FROM employees WHEREemployee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Name: '|| person.first_name);

END;

/

SQL> @notes/s37.sql

Name: Steven

PL/SQL procedure successfully completed.

===============================================

eg3.

SQL> edit

DECLARE

    TYPE t_rec IS RECORD

        (v_sal number(8),

         v_minsal number(8)default 1000,

         v_hire_dateemployees.hire_date%TYPE,

         v_reclemployees%ROWTYPE);

    v_myrec t_rec;

BEGIN

    v_myrec.v_sal := v_myrec.v_minsal+ 500;

    v_myrec.v_hire_date :=SYSDATE;

    SELECT * INTO v_myrec.v_recl

    FROM employees WHEREemployee_id = 100;

   DBMS_OUTPUT.PUT_LINE(v_myrec.v_recl.last_name || ' ' ||     TO_CHAR(v_myrec.v_hire_date) || ' '||     TO_CHAR(v_myrec.v_sal));

END;

/

SQL> @notes/s38.sql

King 24-AUG-14 1500

PL/SQL procedure successfully completed.

CREATE TABLE retired_emps (

        EMPNO NUMBER(4),

        ENAME VARCHAR2(25),

        JOB   VARCHAR2(9),

        MGR   NUMBER(4),

        HIREDATE DATE,

        LEAVEDATE DATE,

        SAL   NUMBER(7,2),

        COMM  NUMBER(7,2),

        DEPTNO NUMBER(2)

    );

SQL> @notes/s39.sql

Table created.

SQL> select * from retired_emps where empno = 124;

no rows selected

SQL> edit

DECLARE

    v_employee_number NUMBER :=124;

    v_emp_rec   employees%ROWTYPE;

BEGIN

    SELECT * INTO v_emp_rec FROMemployees

    WHERE employee_id =v_employee_number;

    INSERT INTOretired_emps(empno, ename, job, mgr,

                hiredate,leavedate, sal, comm, deptno)

    VALUES( v_emp_rec.employee_id,v_emp_rec.last_name,

            v_emp_rec.job_id,v_emp_rec.manager_id,

            v_emp_rec.hire_date,SYSDATE,

            v_emp_rec.salary,v_emp_rec.commission_pct,

           v_emp_rec.department_id);

    COMMIT;

END;

/

SQL> @notes/s40.sql

PL/SQL procedure successfully completed.

SQL> select * from retired_emps where empno = 124;

EMPNO ENAME         JOB              MGR HIREDATE  LEAVEDATE  SAL       COMM    DEPTNO

---------- ----------      --------------- --------- ------------------- ---------      ---------- ---------- ----------

       124 MourGos     ST_MAN     100    16-NOV-07 24-AUG-14 5800                    50

=========================INSERT============================

SQL> edit

DECLARE

    v_employee_number NUMBER := 127;

    v_emp_rec retired_emps%ROWTYPE;

BEGIN

    SELECT employee_id, last_name, job_id,manager_id,

    hire_date, hire_date, salary,commission_pct,

    department_id INTO v_emp_rec

    FROM employees

    WHERE employee_id = v_employee_number;

    INSERT INTO retired_emps

    VALUES v_emp_rec;

                -- 将查询结果打包到v_emp_rec中

END;

/

SELECT * FROMretired_emps;

SQL>@notes/s41.sql

PL/SQL proceduresuccessfully completed.

     EMPNO ENAME       JOB        MGR   HIREDATE  LEAVEDATE SAL       COMM     DEPTNO

----------------------------------- --------- ---------- --------- --------- -------------------- ----------

       124 Mourgos        ST_MAN 100   16-NOV-07  24-AUG-14  5800        50

       127 Landry           ST_CLERK  120  14-JAN-07  14-JAN-07     3200       50

=========================UPDATE============================

SQL> edit

SET VERIFY OFF

DECLARE

        v_employee_number NUMBER:= 127;

        v_emp_recretired_emps%ROWTYPE;

BEGIN

        SELECT *

        INTO v_emp_rec

        FROM retired_emps

        WHERE empno =v_employee_number;

        v_emp_rec.leavedate :=CURRENT_DATE;

        UPDATE retired_emps SETROW = v_emp_rec

        WHERE empno = v_employee_number;

END;

/

SELECT * FROM retired_emps;

SQL> @notes/s42.sql

PL/SQL procedure successfully completed.

     EMPNO ENAME     JOB              MGR HIREDATE  LEAVEDATE     SAL       COMM     DEPTNO

     ---------- --------------  ---------- --------- ---------- ------------------    ---------- ---------- ----------

       124 Mourgos      ST_MAN      100 16-NOV-07 24-AUG-14     5800                       50

       127 Landry        ST_CLERK      120 14-JAN-07 24-AUG-14      3200                    50

SQL> edit

DECLARE

    myrec employees%ROWTYPE;

BEGIN

    myrec := NULL;

END;

/

SQL> @notes/s43.sql

PL/SQL procedure successfully completed.