《Oracle PL/SQL实例精讲》学习笔记17——记录（第二部分）

本章内容：

1. 记录类型（使用基于表和基于游标的记录，使用用户定义的记录，了解记录的兼容性）

2. 嵌套记录（即，包含其他记录和集合的记录）

3. 记录集合

 

代码如下：

1. 使用嵌套记录

SQL> -- For Example ch16_7a.sql
SQL> DECLARE
  2     TYPE name_type IS RECORD
  3        (first_name VARCHAR2(15),
  4         last_name  VARCHAR2(30));
  5
  6     TYPE person_type IS RECORD
  7        (name   name_type,
  8         street VARCHAR2(50),
  9         city   VARCHAR2(25),
 10         state  VARCHAR2(2),
 11         zip    VARCHAR2(5));
 12
 13     person_rec person_type;
 14
 15  BEGIN
 16     SELECT first_name, last_name, street_address, city, state, zip
 17       INTO person_rec.name.first_name, person_rec.name.last_name,
 18            person_rec.street, person_rec.city, person_rec.state,
 19            person_rec.zip
 20       FROM student
 21       JOIN zipcode USING (zip)
 22      WHERE rownum < 2;
 23
 24     DBMS_OUTPUT.PUT_LINE ('Name:   '||
 25        person_rec.name.first_name||' '||person_rec.name.last_name);
 26     DBMS_OUTPUT.PUT_LINE ('Street: '||person_rec.street);
 27     DBMS_OUTPUT.PUT_LINE ('City:   '||person_rec.city);
 28     DBMS_OUTPUT.PUT_LINE ('State:  '||person_rec.state);
 29     DBMS_OUTPUT.PUT_LINE ('Zip:    '||person_rec.zip);
 30  END;
 31  /
Name:   James E. Norman
Street: PO Box 809 Curran Hwy
City:   North Adams
State:  MA
Zip:    01247

以下代码包含关联数组类型、记录类型和嵌套的用户定义的记录。

SQL> -- For Example ch16_8a.sql
SQL> DECLARE
  2     TYPE last_name_type IS TABLE OF student.last_name%TYPE
  3        INDEX BY PLS_INTEGER;
  4
  5     TYPE zip_info_type IS RECORD
  6        (zip          VARCHAR2(5),
  7        last_name_tab last_name_type);
  8
  9     CURSOR name_cur (p_zip VARCHAR2) IS
 10        SELECT last_name
 11          FROM student
 12         WHERE zip = p_zip;
 13
 14     zip_info_rec zip_info_type;
 15     v_zip        VARCHAR2(5) := '&sv_zip';
 16     v_index      PLS_INTEGER := 0;
 17  BEGIN
 18     zip_info_rec.zip := v_zip;
 19     DBMS_OUTPUT.PUT_LINE ('Zip: '||zip_info_rec.zip);
 20
 21     FOR name_rec IN name_cur (v_zip)
 22     LOOP
 23        v_index := v_index + 1;
 24        zip_info_rec.last_name_tab(v_index) := name_rec.last_name;
 25        DBMS_OUTPUT.PUT_LINE
 26           ('Names('||v_index||'): '||zip_info_rec.last_name_tab(v_index));
 27     END LOOP;
 28  END;
 29  /
Enter value for sv_zip: 11368
old  15:    v_zip        VARCHAR2(5) := '&sv_zip';
new  15:    v_zip        VARCHAR2(5) := '11368';
Zip: 11368
Names(1): Lasseter
Names(2): Miller
Names(3): Boyd
Names(4): Griffen
Names(5): Hutheesing
Names(6): Chatman

2. 记录集合

SQL> -- For Example ch16_9a.sql
SQL> DECLARE
  2     CURSOR name_cur IS
  3        SELECT first_name, last_name
  4          FROM student
  5         WHERE ROWNUM <= 4;
  6
  7     TYPE name_type IS TABLE OF name_cur%ROWTYPE
  8        INDEX BY PLS_INTEGER;
  9
 10     name_tab name_type;
 11     v_index  INTEGER := 0;
 12  BEGIN
 13     FOR name_rec IN name_cur
 14     LOOP
 15        v_index := v_index + 1;
 16
 17        name_tab(v_index).first_name := name_rec.first_name;
 18        name_tab(v_index).last_name := name_rec.last_name;
 19
 20        DBMS_OUTPUT.PUT_LINE('First Name('||v_index ||'): '||
 21           name_tab(v_index).first_name);
 22        DBMS_OUTPUT.PUT_LINE('Last Name('||v_index ||'): '||
 23           name_tab(v_index).last_name);
 24     END LOOP;
 25  END;
 26  /
First Name(1): George
Last Name(1): Kocka
First Name(2): Janet
Last Name(2): Jung
First Name(3): Kathleen
Last Name(3): Mulroy
First Name(4): Joel
Last Name(4): Brendler

SQL> -- For Example ch16_9b.sql
SQL> DECLARE
  2     CURSOR name_cur IS
  3        SELECT first_name, last_name
  4          FROM student
  5         WHERE ROWNUM <= 4;
  6
  7     TYPE name_type IS TABLE OF name_cur%ROWTYPE;
  8
  9     name_tab name_type := name_type();
 10     v_index INTEGER  := 0;
 11  BEGIN
 12     FOR name_rec IN name_cur
 13     LOOP
 14        v_index := v_index + 1;
 15        name_tab.EXTEND;
 16
 17        name_tab(v_index).first_name := name_rec.first_name;
 18        name_tab(v_index).last_name := name_rec.last_name;
 19
 20        DBMS_OUTPUT.PUT_LINE('First Name('||v_index||'): '||
 21           name_tab(v_index).first_name);
 22        DBMS_OUTPUT.PUT_LINE('Last Name('||v_index||'): '||
 23           name_tab(v_index).last_name);
 24     END LOOP;
 25  END;
 26  /
First Name(1): George
Last Name(1): Kocka
First Name(2): Janet
Last Name(2): Jung
First Name(3): Kathleen
Last Name(3): Mulroy
First Name(4): Joel
Last Name(4): Brendler

SQL> -- For Example ch16_10a.sql
SQL> DECLARE
  2     CURSOR enroll_cur IS
  3        SELECT first_name, last_name, COUNT(*) total
  4          FROM student
  5          JOIN enrollment USING (student_id)
  6        GROUP BY first_name, last_name;
  7
  8     TYPE enroll_rec_type IS RECORD
  9        (first_name  VARCHAR2(15),
 10         last_name   VARCHAR2(30),
 11         enrollments INTEGER);
 12
 13     TYPE enroll_array_type IS TABLE OF enroll_rec_type
 14        INDEX BY PLS_INTEGER;
 15
 16     enroll_tab enroll_array_type;
 17     v_index    INTEGER := 0;
 18  BEGIN
 19     FOR enroll_rec IN enroll_cur
 20     LOOP
 21        v_index := v_index + 1;
 22
 23        enroll_tab(v_index).first_name  := enroll_rec.first_name;
 24        enroll_tab(v_index).last_name   := enroll_rec.last_name;
 25        enroll_tab(v_index).enrollments := enroll_rec.total;
 26
 27        IF v_index <= 4 THEN
 28           DBMS_OUTPUT.PUT_LINE('First Name('||v_index||'): '||
 29              enroll_tab (v_index).first_name);
 30           DBMS_OUTPUT.PUT_LINE('Last Name('||v_index||'): '||
 31              enroll_tab (v_index).last_name);
 32           DBMS_OUTPUT.PUT_LINE('Enrollments('||v_index||'): '||
 33              enroll_tab (v_index).enrollments);
 34           DBMS_OUTPUT.PUT_LINE ('--------------------');
 35        END IF;
 36     END LOOP;
 37  END;
 38  /
First Name(1): Judy
Last Name(1): Sethi
Enrollments(1): 1
--------------------
First Name(2): Larry
Last Name(2): Walter
Enrollments(2): 2
--------------------
First Name(3): Winsome
Last Name(3): Laporte
Enrollments(3): 2
--------------------
First Name(4): Hiedi
Last Name(4): Lopez
Enrollments(4): 1
--------------------