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 变量只能用在下面地方：
- UPDATE 语句中的 SET 子句的右边
- INSERT 语句的 VALUES 子句
- RETURNING 子句的 INTO 子句
Record 变量不能用在 SELECT 列表、WHERE 子句、GROUP BY 子句，或 ORDER BY 子句。
- 关键字 ROW 只能用在 SET 子句的左边。另外，不能使用带子查询的 ROW。
- 在 UPDATE 语句，若使用 ROW，则只能用一个 SET 子句
- 若 INSERT 语句的 VALUES 子句包含一个 Record 变量，则不能再使用其他变量或值。
- 若 RETURNING 子句的 INTO 子句包含一个 Record 变量，则不能再使用其他变量或值。
- 下面不支持：
- Nested record 类型
- 返回一个 Record 的函数
- 使用 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;
/
