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

本文链接：https://blog.youkuaiyun.com/hpdlzu80100/article/details/83796488

本章内容：

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

2. 嵌套记录

3. 记录集合

代码如下：

1. 使用基于表的记录

SQL> -- For Example ch16_1a.sql
SQL> /* Formatted on 2018/11/10 17:01:38 (QP5 v5.256.13226.35538) */
SQL> DECLARE
  2     course_rec   course%ROWTYPE;
  3  BEGIN
  4     SELECT *
  5       INTO course_rec
  6       FROM course
  7      WHERE course_no = 25;
  8
  9     DBMS_OUTPUT.PUT_LINE ('Course No: ' || course_rec.course_no);
 10     DBMS_OUTPUT.PUT_LINE ('Course Description: ' || course_rec.description);
 11     DBMS_OUTPUT.PUT_LINE ('Prerequisite: ' || course_rec.prerequisite);
 12  EXCEPTION
 13     WHEN NO_DATA_FOUND
 14     THEN
 15        DBMS_OUTPUT.PUT_LINE ('The Course No. entered doesn''t exist');
 16  END;
 17  /
Course No: 25
Course Description: Intro to Programming
Prerequisite: 140

2. 使用基于游标的记录

SQL> -- For Example ch16_2a.sql
SQL> DECLARE
  2     CURSOR student_cur IS
  3        SELECT first_name, last_name, registration_date
  4          FROM student
  5         WHERE rownum <= 4;
  6
  7     student_rec student_cur%ROWTYPE;
  8  BEGIN
  9     OPEN student_cur;
 10     LOOP
 11        FETCH student_cur INTO student_rec;
 12        EXIT WHEN student_cur%NOTFOUND;
 13
 14        DBMS_OUTPUT.PUT_LINE
 15           ('Name: '||student_rec.first_name||' '||student_rec.last_name);
 16        DBMS_OUTPUT.PUT_LINE
 17           ('Registration Date: '||to_char(student_rec.registration_date, 'MM/DD/YYYY'));
 18     END LOOP;
 19  END;
 20  /
Name: George Kocka
Registration Date: 02/08/2003
Name: Janet Jung
Registration Date: 02/08/2003
Name: Kathleen Mulroy
Registration Date: 02/08/2003
Name: Joel Brendler
Registration Date: 02/08/2003

注意，游标声明要位于记录声明之前，否则会抛错。

SQL> -- For Example ch16_2b.sql
SQL> DECLARE
  2     student_rec student_cur%ROWTYPE;
  3
  4     CURSOR student_cur IS
  5        SELECT first_name, last_name, registration_date
  6        FROM student
  7        WHERE rownum <= 4;
  8
  9  BEGIN
 10     OPEN student_cur;
 11     LOOP
 12        FETCH student_cur INTO student_rec;
 13        EXIT WHEN student_cur%NOTFOUND;
 14
 15        DBMS_OUTPUT.PUT_LINE
 16           ('Name: '||student_rec.first_name||' '||student_rec.last_name);
 17        DBMS_OUTPUT.PUT_LINE
 18        ('Registration Date: '|| to_char(student_rec.registration_date, 'MM/DD/YYYY'));
 19     END LOOP;
 20  END;
 21  /
   student_rec student_cur%ROWTYPE;
               *
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 16:
PL/SQL: Item ignored
ORA-06550: line 12, column 30:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 12, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 21:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 15, column 7:
PL/SQL: Statement ignored
ORA-06550: line 18, column 40:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 17, column 7:
PL/SQL: Statement ignored

3. 用户定义的记录

SQL> -- For Example ch16_3a.sql
SQL> DECLARE
  2     TYPE time_rec_type IS RECORD
  3        (curr_date DATE,
  4         curr_day  VARCHAR2(12),
  5         curr_time VARCHAR2(8) := '00:00:00');
  6
  7     time_rec TIME_REC_TYPE;
  8  BEGIN
  9     SELECT sysdate
 10       INTO time_rec.curr_date
 11       FROM dual;
 12
 13     time_rec.curr_day  := TO_CHAR(time_rec.curr_date, 'DAY');
 14     time_rec.curr_time := TO_CHAR(time_rec.curr_date, 'HH24:MI:SS');
 15
 16     DBMS_OUTPUT.PUT_LINE ('Date: '||to_char(time_rec.curr_date, 'MM/DD/YYYY HH24:MI:SS'));
 17     DBMS_OUTPUT.PUT_LINE ('Day:  '||time_rec.curr_day);
 18     DBMS_OUTPUT.PUT_LINE ('Time: '||time_rec.curr_time);
 19  END;
 20  /
Date: 11/10/2018 17:14:07
Day:  SATURDAY
Time: 17:14:07

注意：为单个字段指定NOT NULL约束时，必须对这些字段进行初始化，否则会抛错。

SQL> -- For Example ch16_4a.sql
SQL> DECLARE
  2     TYPE sample_type IS RECORD
  3        (field1 NUMBER(3),
  4         field2 VARCHAR2(3) NOT NULL);
  5
  6     sample_rec sample_type;
  7
  8  BEGIN
  9     sample_rec.field1 := 10;
 10     sample_rec.field2 := 'ABC';
 11
 12     DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
 13     DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
 14  END;
 15  /
       field2 VARCHAR2(3) NOT NULL);
       *
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

修改后的代码：

SQL> -- For Example ch16_4b.sql
SQL> DECLARE
  2     TYPE sample_type IS RECORD
  3        (field1 NUMBER(3),
  4         field2 VARCHAR2(3) NOT NULL := 'ABC'); -- 琲nitialize a NOT NULL field
  5
  6     sample_rec sample_type;
  7
  8  BEGIN
  9     sample_rec.field1 := 10;
 10
 11     DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
 12     DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
 13  END;
 14  /
sample_rec.field1 = 10
sample_rec.field2 = ABC

4. 记录的兼容性（比较难理解）

SQL> -- For Example ch16_5a.sql
SQL> DECLARE
  2     TYPE name_type1 IS RECORD
  3        (first_name VARCHAR2(15),
  4         last_name  VARCHAR2(30));
  5
  6     TYPE name_type2 IS RECORD
  7        (first_name VARCHAR2(15),
  8         last_name  VARCHAR2(30));
  9
 10     name_rec1 name_type1;
 11     name_rec2 name_type2;
 12  BEGIN
 13     name_rec1.first_name := 'John';
 14     name_rec1.last_name  := 'Smith';
 15     name_rec2 := name_rec1; -- illegal assignment
 16  END;
 17  /
   name_rec2 := name_rec1; -- illegal assignment
                *
ERROR at line 15:
ORA-06550: line 15, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored

以下PL/SQL语句块没有语法错误：

SQL> -- For Example ch16_5b.sql
SQL> DECLARE
  2     TYPE name_type1 IS RECORD
  3        (first_name VARCHAR2(15),
  4         last_name  VARCHAR2(30));
  5
  6     name_rec1 name_type1;
  7     name_rec2 name_type1;
  8  BEGIN
  9     name_rec1.first_name := 'John';
 10     name_rec1.last_name  := 'Smith';
 11     name_rec2 := name_rec1; -- no longer illegal assignment
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> -- For Example ch16_6a.sql
SQL> DECLARE
  2     CURSOR course_cur IS
  3        SELECT *
  4          FROM course
  5         WHERE rownum < 2;
  6
  7     TYPE course_type IS RECORD
  8        (course_no     NUMBER(38)
  9        ,description   VARCHAR2(50)
 10        ,cost          NUMBER(9,2)
 11        ,prerequisite  NUMBER(8)
 12        ,created_by    VARCHAR2(30)
 13        ,created_date  DATE
 14        ,modified_by   VARCHAR2(30)
 15        ,modified_date DATE);
 16
 17     course_rec1 course%ROWTYPE;      -- table-based record
 18     course_rec2 course_cur%ROWTYPE;  -- cursor-based record
 19     course_rec3 course_type;         -- user-defined record
 20  BEGIN
 21     -- Populate table-based record
 22     SELECT *
 23       INTO course_rec1
 24       FROM course
 25      WHERE course_no = 10;
 26
 27     -- Populate cursor-based record
 28     OPEN course_cur;
 29     LOOP
 30        FETCH course_cur INTO course_rec2;
 31        EXIT WHEN course_cur%NOTFOUND;
 32     END LOOP;
 33
 34     -- Assign COURSE_REC2 to COURSE_REC1 and COURSE_REC3
 35     course_rec1 := course_rec2;
 36     course_rec3 := course_rec2;
 37
 38     DBMS_OUTPUT.PUT_LINE (course_rec1.course_no||' - '||course_rec1.description);
 39     DBMS_OUTPUT.PUT_LINE (course_rec2.course_no||' - '||course_rec2.description);
 40     DBMS_OUTPUT.PUT_LINE (course_rec3.course_no||' - '||course_rec3.description);
 41  END;
 42  /
10 - DP Overview
10 - DP Overview
10 - DP Overview