10.PL_SQL——PL_SQL中的復(fù)合數(shù)據(jù)類型之RECORDS

一、記錄和集合概述

         目前為止所介紹的變量的數(shù)據(jù)類型都屬于scalar，即標(biāo)量，如×××，字符串，日期等等。任何一種編程語言都需要提供一些手段讓用戶創(chuàng)建更復(fù)雜的變量，PL/SQL也不例外，本章就來介紹一下PL/SQL中的復(fù)合型變量。

PL/SQL的復(fù)合型變量主要包括兩類：

              1. Records：記錄

              2. Collection：集合。Collection又可以分為三種，

                              a. Associative  Array（INDEXTBY table）: 關(guān)聯(lián)數(shù)組（或稱索引表，和SQL中的INDEX OF TABLE不是一個(gè)概念）

                              b. Nested Table: 嵌套表

                              c. Varray: 可變長(zhǎng)度列表。

二、記錄和集合的區(qū)別

         Records可以形象的理解為一個(gè)容器，用來保存很多其他的數(shù)據(jù)類型，但每種數(shù)據(jù)類型只在記錄中出現(xiàn)一次，例如一個(gè)記錄中保存了如下數(shù)據(jù)類型：

PL/SQL Record：

這個(gè)記錄中保存了布爾型、日期型、字符型和BLOB型變量，且每個(gè)變量都只出現(xiàn)了一次，每個(gè)存放其他數(shù)據(jù)類型的空間稱為域（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;

                -- 將查詢結(jié)果打包到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.