Oracle 11g Release 1 (11.1) PL/SQL_理解 Record 类型

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#i20479

本文内容

• 定义和声明 Record
• Record 作为子程序参数和函数返回值
• 给 Record 赋值
• 比较 Record
• 把 Record 插入到数据库
• 把 Record 更新到数据库
• Record 插入和更新的约束
• 把查询数据放到 Record

定义和声明 Record

若创建 records，需要先定义一个 RECORD 类型，再用该类型声明变量。也可以创建或查找一个表、视图，或 PL/SQL 游标，总之是你想要的值，使用 %ROWTYPE 属性来创建匹配的 Record。

你可以在任何 PL/SQL 块、子程序或包的声明部分定义 RECORD 类型。当你自定义 RECORD 类型时，不能在域上指定一个 NOT NULL 的约束，或给出它们的默认值。

示例 1：演示声明和初始化一个简单的 Record 类型

DECLARE
   TYPE DeptRecTyp IS RECORD (
      deptid NUMBER(4) NOT NULL := 99,
      dname  departments.department_name%TYPE,
      loc    departments.location_id%TYPE,
      region regions%ROWTYPE );
   dept_rec DeptRecTyp;
BEGIN
   dept_rec.dname := 'PURCHASING';
END;
/

示例 2：演示声明和初始化 Record 类型

DECLARE
-- Declare a record type with 3 fields.
  TYPE rec1_t IS RECORD
    (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);
-- For any fields declared NOT NULL, you must supply a default value.
  TYPE rec2_t IS RECORD (id INTEGER NOT NULL := -1,
  name VARCHAR2(64) NOT NULL := '[anonymous]');
-- Declare record variables of the types declared
  rec1 rec1_t;
  rec2 rec2_t;
-- Declare a record variable that can hold
-- a row from the EMPLOYEES table.
-- The fields of the record automatically match the names and
-- types of the columns.
-- Don't need a TYPE declaration in this case.
  rec3 employees%ROWTYPE;
-- Or mix fields that are table columns with user-defined fields.
  TYPE rec4_t IS RECORD (first_name employees.first_name%TYPE,
                         last_name employees.last_name%TYPE,
                         rating NUMBER);
  rec4 rec4_t;
BEGIN
-- Read and write fields using dot notation
  rec1.field1 := 'Yesterday';
  rec1.field2 := 65;
  rec1.field3 := TRUNC(SYSDATE-1);
-- Didn't fill name field, so it takes default value
  DBMS_OUTPUT.PUT_LINE(rec2.name);
END;
/

若在数据库存储一个 Record，你可以在 INSERT 或 UPDATE 语句指定，只要它的域与表的列匹配。

你可以使用 %TYPE 来指定 Record 域类型对应表的列的类型。即使列类型改变了，你的代码仍然可以运行。例如，增加了 VARCHAR2 字段的长度，或 NUMBER 字段的精度。

示例 3：演示使用 %ROWTYPE 来声明一个 Record，来保存 department 表的信息

DECLARE
-- Best: use %ROWTYPE instead of specifying each column.
-- Use <cursor>%ROWTYPE instead of <table>%ROWTYPE because
-- you only want some columns.
-- Declaring cursor doesn't run query or affect performance.
   CURSOR c1 IS
     SELECT department_id, department_name, location_id
     FROM departments;
   rec1 c1%ROWTYPE;
-- Use <column>%TYPE in field declarations to avoid problems if
-- the column types change.
   TYPE DeptRec2 IS RECORD
     (dept_id   departments.department_id%TYPE,
      dept_name departments.department_name%TYPE,
      dept_loc  departments.location_id%TYPE);
   rec2 DeptRec2;
-- Write each field name, specifying type directly
-- (clumsy and unmaintainable for working with table data
-- use only for all-PL/SQL code).
   TYPE DeptRec3 IS RECORD (dept_id NUMBER,
                            dept_name VARCHAR2(14),
                            dept_loc VARCHAR2(13));
   rec3 DeptRec3;
BEGIN
   NULL;
END;
/

PL/SQL 可以定义包含对象、集合和其他 Record（内置 Record）的 Record。但 Record 不能是对象类型的属性。

若声明一个 Record，表示数据库表的一行，则无需列出列，使用 %ROWTYPE 属性。

当表添加列后，你的代码仍然可以运行。若你想表示一个表列的一个子集，或是不同表的列，则你可以定义一个视图或声明一个游标，来选择右边的列，执行任何需要的连接，再在视图或游标上应用 %ROWTYPE。

Record 作为子程序参数和函数返回值

Record 很容易用子程序来处理，因为，你只需传递一个 Record 参数，而不是 Record 每个单独的域。例如，你可以从 EMPLOYEES 表获得一行到一个 Record，再把这个行作为参数传递给一个函数，计算该员工的假期津贴。这个函数通过 Record 域，可以访问员工的所有信息。

示例 4：演示从函数返回一个 Record

若使 Record 类型对多个存储子程序可见，则在包规范中声明 Record 类型。

DECLARE
   TYPE EmpRecTyp IS RECORD (
     emp_id       NUMBER(6),
     salary       NUMBER(8,2));
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT employee_id, salary
      FROM employees
      ORDER BY salary DESC;
   emp_rec     EmpRecTyp;
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
   BEGIN
      OPEN desc_salary;
      FOR i IN 1..n LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;
      CLOSE desc_salary;
      RETURN emp_rec;
   END nth_highest_salary;
BEGIN
   NULL;
END;
/

示例 5：演示 Record 作为存储过程的参数

DECLARE
   TYPE EmpRecTyp IS RECORD (
      emp_id       NUMBER(6),
      emp_sal      NUMBER(8,2) );
   PROCEDURE raise_salary (emp_info EmpRecTyp) IS
   BEGIN
      UPDATE employees SET salary = salary + salary * .10
             WHERE employee_id = emp_info.emp_id;
   END raise_salary;
BEGIN
   NULL;
END;
/

示例 6：演示声明一个 nested Record，一个 Record 由另一个 Record 组成

DECLARE
   TYPE TimeTyp IS RECORD ( minutes SMALLINT, hours SMALLINT );
   TYPE MeetingTyp IS RECORD (
      day     DATE,
      time_of TimeTyp,             -- nested record
      dept    departments%ROWTYPE,
        -- nested record representing a table row
      place   VARCHAR2(20),
      purpose VARCHAR2(50) );
   meeting MeetingTyp;
   seminar MeetingTyp;
BEGIN
-- Can assign one nested record to another
-- if they are of the same data type
   seminar.time_of := meeting.time_of;
END;
/

即使 Record 有不同的数据类型，这样的赋值也是允许的。

给 Record 赋值

若为一个 Record 的所有域设置默认值， 则赋值为一个相同类型的、未初始化的 Record 给它。

示例 7：演示为一个 Record 设置默认值

DECLARE
   TYPE RecordTyp IS RECORD (field1 NUMBER,
                             field2 VARCHAR2(32) DEFAULT 'something');
   rec1 RecordTyp;
   rec2 RecordTyp;
BEGIN
-- At first, rec1 has the values you assign.
   rec1.field1 := 100; rec1.field2 := 'something else';
-- Assigning an empty record to rec1
-- resets fields to their default values.
-- Field1 is NULL and field2 is 'something'
-- due to the DEFAULT clause
   rec1 := rec2;
   DBMS_OUTPUT.PUT_LINE
     ('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
      field2 = ' || rec1.field2);
END;
/

可以用点记号为一个 Record 的其中一个域赋值：

emp_info.last_name := 'Fields';

值被单独赋给一个 Record 的每个域。不能使用值的列表为一个 Record 赋值。

只要两个 Record 类型相同，就可以赋值，这样一次性为 Record 每个域赋值。

示例 8：演示用一个语句为一个 Record 的每个域赋值

DECLARE
-- Two identical type declarations.
   TYPE DeptRec1 IS RECORD
     (dept_num  NUMBER(2), dept_name VARCHAR2(14));
   TYPE DeptRec2 IS RECORD
    (dept_num  NUMBER(2), dept_name VARCHAR2(14));
   dept1_info DeptRec1;
   dept2_info DeptRec2;
   dept3_info DeptRec2;
BEGIN
-- Not allowed; different data types,
-- even though fields are the same.
--      dept1_info := dept2_info;
-- This assignment is OK because the records have the same type.
   dept2_info := dept3_info;
END;
/

若用户自定义一个 Record 与 %ROWTYPE record 域的个数、顺序和类型都相同，则可以把一个 %ROWTYPE Record 赋值给自定义的 Record。

DECLARE
   TYPE RecordTyp IS RECORD (last employees.last_name%TYPE,
                             id employees.employee_id%TYPE);
   CURSOR c1 IS SELECT last_name, employee_id FROM employees;
-- Rec1 and rec2 have different types,
-- but because rec2 is based on a %ROWTYPE,
-- you can assign it to rec1 as long as they have
-- the right number of fields and
-- the fields have the right data types.
   rec1 RecordTyp;
   rec2 c1%ROWTYPE;
BEGIN
  SELECT last_name, employee_id INTO rec2
    FROM employees WHERE ROWNUM < 2;
    WHERE ROWNUM < 2;
  rec1 := rec2;
  DBMS_OUTPUT.PUT_LINE
    ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

也可以使用 SELECT 或 FETCH 语句，把列值获取到一个 Record。select 列出的字段顺序必须与 Record 相同。

示例 9：演示使用 SELECT INTO 为一个 Record 赋值

DECLARE
   TYPE RecordTyp IS RECORD (last employees.last_name%TYPE,
                             id employees.employee_id%TYPE);
   rec1 RecordTyp;
BEGIN
  SELECT last_name, employee_id INTO rec1
     FROM employees WHERE ROWNUM < 2;
     WHERE ROWNUM < 2;
   DBMS_OUTPUT.PUT_LINE
     ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

比较 Record

Record 不能用 NULL 来测试，不能用等号或不等号比较。若想比较，则需要自定义函数。

把 Record 插入到数据库

在 VALUES 子句，使用一个类型为 RECORD 或 %ROWTYPE 的变量，而不是列出 Record 的每个域，通过 PL/SQL INSERT 语句扩展可以将多个 Record 插入到数据库。这可以使代码更可读，更容易维护。

若通过 FORALL 语句执行 INSERT，则可以从 record 整个集合插入值。record 域的数量必须等于 INTO 子句列出的列数量，且域和列必须类型兼容。若确保 record 与 table 兼容，最方便的方法用 table_name%ROWTYPE 声明变量。

示例 10：演示通过 %ROWTYPE 标识符插入一个 Record

该方法无需指定列。%ROWTYPE 确保 Record 域与表列的名称和类型完全相同。

DECLARE
  dept_info departments%ROWTYPE;
BEGIN
  -- department_id, department_name, and location_id
  -- are the table columns
  -- The record picks up these names from the %ROWTYPE
     dept_info.department_id := 300;
     dept_info.department_name := 'Personnel';
     dept_info.location_id := 1700;
  -- Using the %ROWTYPE means you can leave out the column list
  -- (department_id, department_name, and location_id)
  -- from the INSERT statement
  INSERT INTO departments VALUES dept_info;
END;
/

把 Record 更新到数据库

在 SET 子句右边使用一个类型为 RECORD 或 %ROWTYPE 的单独变量，UPDATE 语句的 PL/SQL 扩展可以更新数据的行。

若通过 FORALL 语句执行 UPDATE，则可以从 record 整个集合更新值。另外，对于一个 UPDATE 语句，可以在 RETURNING 子句指定一个 Record，检索新数据到一个 Record，或 Record 的集合。

record 域的数量必须等于 SET 子句列出的列数量，且域和列必须类型兼容。

可以使用关键 ROW 来表示整个行。

示例 11：演示用一个 Record 更新一行

DECLARE
   dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id
-- are the table columns
-- The record picks up these names from the %ROWTYPE.
  dept_info.department_id := 300;
  dept_info.department_name := 'Personnel';
  dept_info.location_id := 1700;
-- The fields of a %ROWTYPE
-- can completely replace the table columns
-- The row will have values for the filled-in columns, and null
-- for any other columns
   UPDATE departments SET ROW = dept_info WHERE department_id = 300;
END;
/

关键字 ROW 只能用在 SET 子句的左边。SET ROW 的参数必须是一个真正的 PL/SQL Record，而不能是只返回一行的子查询。Record 也可以包含集合或对象。

INSERT、UPDATE 和 DELETE 语句可以包含 RETURNING 子句，返回受影响的行的列值到 PL/SQL Record 变量。这消除了插入或更新后，或删除前，重新 select 数据的需求。

默认情况下，当只操作一个行数据时，可以使用该子句。当你使用 bulk SQL 时，你可以使用 RETURNING BULK COLLECT INTO 把结果存储到一个或多个集合中。

示例 12：演示使用 RETURNING INTO 子句

更新指定员工的工资，并检索该员工的名字、职位和新的工资到一个 Record 变量。

DECLARE
   TYPE EmpRec IS RECORD (last_name  employees.last_name%TYPE,
                          salary     employees.salary%TYPE);
   emp_info EmpRec;
   emp_id   NUMBER := 100;
BEGIN
   UPDATE employees SET salary = salary * 1.1
     WHERE employee_id = emp_id
     RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
     ('Just gave a raise to ' || emp_info.last_name ||
      ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/

把 Record 插入和更新的约束

目前，下面约束用于 record inserts/updates：

• Record 变量只能用在下面地方：

1. UPDATE 语句中的 SET 子句的右边
2. INSERT 语句的 VALUES 子句
3. RETURNING 子句的 INTO 子句

Record 变量不能用在 SELECT 列表、WHERE 子句、GROUP BY 子句，或 ORDER BY 子句。

• 关键字 ROW 只能用在 SET 子句的左边。另外，不能使用带子查询的 ROW。
• 在 UPDATE 语句，若使用 ROW，则只能用一个 SET 子句
• 若 INSERT 语句的 VALUES 子句包含一个 Record 变量，则不能再使用其他变量或值。
• 若 RETURNING 子句的 INTO 子句包含一个 Record 变量，则不能再使用其他变量或值。
• 下面不支持：

1. Nested record 类型
2. 返回一个 Record 的函数
3. 使用 EXECUTE IMMEDIATE 语句进行 Record 插入和更新

把查询数据放到 Record

可以使用带 BULK COLLECT 子句的 SELECT INTO 或 FETCH 语句，来检索行集合到 Record。

示例 13：演示使用带 BULK COLLECT 的 SELECT INTO 语句

DECLARE
   TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
   underpaid EmployeeSet;
     -- Holds set of rows from EMPLOYEES table.
   CURSOR c1 IS SELECT first_name, last_name FROM employees;
   TYPE NameSet IS TABLE OF c1%ROWTYPE;
   some_names NameSet;
     -- Holds set of partial rows from EMPLOYEES table.
BEGIN
-- With one query,
-- bring all relevant data into collection of records.
   SELECT * BULK COLLECT INTO underpaid FROM employees
      WHERE salary < 5000 ORDER BY salary DESC;
-- Process data by examining collection or passing it to
-- eparate procedure, instead of writing loop to FETCH each row.
   DBMS_OUTPUT.PUT_LINE
     (underpaid.COUNT || ' people make less than 5000.');
   FOR i IN underpaid.FIRST .. underpaid.LAST
   LOOP
     DBMS_OUTPUT.PUT_LINE
       (underpaid(i).last_name || ' makes ' || underpaid(i).salary);
   END LOOP;
-- You can also bring in just some of the table columns.
-- Here you get the first and last names of 10 arbitrary employees.
   SELECT first_name, last_name
     BULK COLLECT INTO some_names
     FROM employees
     WHERE ROWNUM < 11;
   FOR i IN some_names.FIRST .. some_names.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE
        ('Employee = ' || some_names(i).first_name
         || ' ' || some_names(i).last_name);
   END LOOP;
END;
/