第五章 PL/SQL集合与记录(4)

十四、利用BULK COLLECTION子句为集合赋值

关键字BULK COLLECT会通知SQL引擎在将数据返回给PL/SQL引擎之前，把输出的数据批量地绑定到一个集合。我们可以在SELECT INTO、FETCH INTO和RETURNING INTO子句中使用BULK COLLECT。语法如下：

... BULK COLLECT INTO collection_name[, collection_name] ...

SQL引擎能批量绑定出现在INTO列表后的所有集合。对应的字段可以保存为标量类型或复合类型的值，其中也包括对象类型。在下面的例子中，SQL引擎在把数据返回给PL/SQL引擎之前，它将完整的empno和ename绑定到嵌套表中：

DECLARE
  TYPE numtab IS TABLE OF emp.empno%TYPE;

  TYPE nametab IS TABLE OF emp.ename%TYPE;

  enums   numtab;   -- no need to initialize
  names   nametab;
BEGIN
  SELECT empno, ename
  BULK COLLECT INTO enums, names
    FROM emp;
  ...
END;

接下来的例子中，SQL引擎会批量地把对象字段的值放到嵌套表中：

CREATE TYPE coords AS OBJECT(
  x   NUMBER,
  y   NUMBER
);

CREATE     TABLE grid (num NUMBER, loc coords);
INSERT INTO grid
     VALUES (10, coords(1, 2));
INSERT INTO grid
     VALUES (20, coords(3, 4));

DECLARE
  TYPE coordstab IS TABLE OF coords;

  pairs   coordstab;
BEGIN
  SELECT loc
  BULK COLLECT INTO pairs
    FROM grid;
  -- now pairs contains (1,2) and (3,4)
END;

SQL引擎会为我们初始化和扩展集合(但是，它不能把变长数组的长度扩大到超过变长数组的最大长度值)。然后从索引1开始，连续地插入元素并覆盖先前已存在的元素。

SQL引擎能批量绑定整个字段。所以，如果一个数据表中有50000行记录，引擎就会一次性加载50000个值到目标集合中去。但是，我们可以使用伪列ROWNUM来限制要处理的行记录个数。下例中，我们把每次处理的记录个数限制为100：

DECLARE
  TYPE sallist IS TABLE OF emp.sal%TYPE;

  sals   sallist;
BEGIN
  SELECT sal
  BULK COLLECT INTO sals
    FROM emp
   WHERE ROWNUM <= 100;
  ...
END;

1、从游标中批量取得数据的例子

• 插入一个或多个集合

我们可以从游标中批量取得数据并绑定到一个或多个集合中去：

DECLARE
  TYPE namelist IS TABLE OF emp.ename%TYPE;

  TYPE sallist IS TABLE OF emp.sal%TYPE;

  CURSOR c1 IS
    SELECT ename, sal
      FROM emp
     WHERE sal > 1000;

  names   namelist;
  sals    sallist;
BEGIN
  OPEN c1;

  FETCH c1
  BULK COLLECT INTO names, sals;
END;

• 绑定记录类型的集合

我们可以批量取得数据并绑定到记录类型的集合中去：

DECLARE
  TYPE deptrectab IS TABLE OF dept%ROWTYPE;

  dept_recs   deptrectab;

  CURSOR c1 IS
    SELECT deptno, dname, loc
      FROM dept
     WHERE deptno > 10;
BEGIN
  OPEN c1;

  FETCH c1
  BULK COLLECT INTO dept_recs;
END;

2、使用LIMIT子句限制FETCH操作批量取得的数据个数

可选的LIMIT子句只允许出现在FETCH操作语句的批量中，它能够帮助我们限制批量取得的数据数量，语法如下：

FETCH ... BULK COLLECT INTO ... [LIMIT rows];

其中rows可以是文字，变量或表达式，但它的计算结果必须是一个数字。否则的话，PL/SQL就会抛出预定义异常VALUE_ERROR。如果 rows是非正数，PL/SQL会抛出INVALID_NUMBER异常。在必要的时候，PL/SQL还会将数字四舍五入到rows最接近的整数。

在下面的例子中，每次FETCH操作都会取出10条记录放到索引表empno中去，之前的数据内容会被当前的数据所覆盖：

DECLARE
  TYPE numtab IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  CURSOR c1 IS
    SELECT empno
      FROM emp;

  empnos   numtab;
  ROWS     NATURAL := 10;
BEGIN
  OPEN c1;

  LOOP
    /* The following statement fetches 10 rows (or less). */
    FETCH c1
    BULK COLLECT INTO empnos LIMIT ROWS;

    EXIT WHEN c1%NOTFOUND;
    ...
  END LOOP;

  CLOSE c1;
END;

3、使用RETURNING INTO子句将DML的操作结果绑定到集合

我们还可以在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT来进行数据绑定，示例如下：

DECLARE
  TYPE numlist IS TABLE OF emp.empno%TYPE;

  enums   numlist;
BEGIN
  DELETE FROM emp
        WHERE deptno = 20
    RETURNING       empno
  BULK COLLECT INTO enums;
  -- if there were five employees in department 20,
  -- then enums contains five employee numbers
END;

4、BULK COLLECT的限制

下面是使用BULK COLLECT的一些限制：

1. 不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
2. 只能在服务器端的程序中使用BULK COLLECT，如果在客户端使用，就会产生一个不支持这个特性的错误。
3. BULK COLLECT INTO的目标对象必须是集合类型，如下例所示：

   DECLARE
     TYPE namelist IS TABLE OF emp.ename%TYPE;
   
     names    namelist;
     salary   emp.sal%TYPE;
   BEGIN
     SELECT ename, sal
     BULK COLLECT INTO names, salary   -- illegal target
       FROM emp
      WHERE ROWNUM < 50;
     ...
   END;

4. 复合目标(如对象类型)不能在RETURNING INTO子句中使用。
5. 如果有多个隐式的数据类型转换的情况存在，多重复合目标就不能在BULK COLLECT INTO子句中使用。
6. 如果有一个隐式的数据类型转换，复合目标的集合(如对象类型集合)就不能用于BULK COLLECT INTO子句中。

5、把FORALL和BULK COLLECTION结合起来使用

我们可以把BULK COLLECT和FORALL语句结合起来使用，这时，SQL引擎会批量绑定字段值。下例中，如果集合depts有三个元素，每个元素都能执行五次DELETE操作，当语句执行完毕的时候，enums中就会有十五个元素：

FORALL j IN depts.FIRST .. depts.LAST
  DELETE FROM emp
        WHERE empno = depts(j)
    RETURNING       empno
  BULK COLLECT INTO enums;

我们不能在FORALL语句中使用SELECT ... BULK COLLECT语句。否则，就会得到一条错误消息：不能在SELECT语句中同时使用FORALL和BULK COLLECT INTO(cannot use FORALL and BULK COLLECT INTO together in SELECT statements)。

6、使用主数组进行批量绑定

客户端程序可以使用匿名PL/SQL块来把数据批量地从主数组中输入或批量地输出到主数组。实际上，这是与服务器端交互传递集合的最高效的方法。

主数组是声明在主环境中的，如OCI或Pro*C程序，并且必须以冒号为前缀，以区别于PL/SQL集合。在下面的例子中，DELETE语句中使用到一个输入主数组。运行时，匿名PL/SQL块被发送到数据库服务器端执行：

DECLARE
  ...
BEGIN
  -- assume that values were assigned to the host array
  -- and host variables in the host environment
  FORALL i IN :lower..:upper
    DELETE FROM emp 
          WHERE deptno = :depts(i);
  ...
END;

十五、什么是记录

记录就是相关的数据项集中存储在一个单元中，每项都有它自己的名字和数据类型。假定我们有关于雇员的各种数据信息，如名字、薪水和雇佣日期，这些项在逻辑上是相关联的，但类型不相似。记录可以把它所拥有的每一项当作一个逻辑单元，这样就便于组织和表现信息。

%ROWTYPE属性能让我们声明代表数据表中一行记录的类型。但是我们不能利用它指定或声明自己的数据类型。不过没关系，RECORD关键字可以满足我们定义自己的记录的要求。

十六、定义和声明记录

要创建记录，我们就得先声明记录类型，然后声明该类型的记录。我们可以在PL/SQL块、子程序或包的声明部分使用下面的语法来定义RECORD类型：

TYPE type_name IS RECORD (field_declaration[,field_declaration]...);

其中field_declaration的形式如下：

field_name field_type [[NOT NULL] {:= | DEFAULT} expression]

type_name是声明记录用的类型区分符，field_type是除了REF CURSOR以外的任何PL/SQL数据类型，expression的结果值与field_type相同。

注意：与VARRAY类型和TABLE(嵌套)类型不同的是，RECORD是不能存在于数据库中的。

创建记录时也可以使用%TYPE和%ROWTYPE来指定记录各个域的类型。下例中，我们定义了一个名为DeptRec的记录类型：

DECLARE
  TYPE deptrec IS RECORD(
    dept_id     dept.deptno%TYPE,
    dept_name   VARCHAR2(14),
    dept_loc    VARCHAR2(13)
  );
BEGIN
  ...
END;

在下面的例子中，我们在记录类型中包含对象、集合和其他的记录(又叫嵌套记录)。但是对象类型中不能把RECORD类型作为它的属性。

DECLARE
  TYPE timerec IS RECORD(
    seconds   SMALLINT,
    minutes   SMALLINT,
    hours     SMALLINT
  );

  TYPE flightrec IS RECORD(
    flight_no      INTEGER,
    plane_id       VARCHAR2(10),
    captain        employee,   -- declare object
    passengers     passengerlist,   -- declare varray
    depart_time    timerec,   -- declare nested record
    airport_code   VARCHAR2(10)
  );
BEGIN
  ...
END;

下面的例子演示了如何将函数的返回类型指定为RECORD类型：

DECLARE
  TYPE emprec IS RECORD(
    emp_id      NUMBER(4),
    last_name   VARCHAR2(10),
    dept_num    NUMBER(2),
    job_title   VARCHAR2(9),
    salary      NUMBER(7, 2)
  );
  ...
  FUNCTION nth_highest_salary(n INTEGER)
    RETURN emprec IS ...
  BEGIN
    ...
  END;

1、声明记录

一旦定义了RECORD类型，我们就可以声明该类型的记录。如下例所示，标识符item_info代表了整条记录：

DECLARE
  TYPE stockitem IS RECORD(
    item_no       INTEGER(3),
    description   VARCHAR2(50),
    quantity      INTEGER,
    price         REAL(7, 2)
  );

  item_info   stockitem;   -- declare record
BEGIN
  ...
END;

同标量类型的变量一样，用户定义的记录也可以作为函数或过程的形式参数来使用：

DECLARE
  TYPE emprec IS RECORD(
    emp_id      emp.empno%TYPE,
    last_name   VARCHAR2(10),
    job_title   VARCHAR2(9),
    salary      NUMBER(7, 2)
  );

  ...
  PROCEDURE raise_salary(emp_info emprec);
BEGIN
  ...
END;

2、初始化记录

下面的例子演示了如何在定义记录的时候，同时进行初始化操作。当我们声明TimeRec类型的记录时，它的三个域都被初始化为零：

DECLARE
  TYPE timerec IS RECORD(
    secs   SMALLINT := 0,
    mins   SMALLINT := 0,
    hrs    SMALLINT := 0
  );
BEGIN
  ...
END;

我们可以为记录添加NOT NULL约束，对于有NOT NULL约束的字段，声明时必须进行初始化：

DECLARE
  TYPE stockitem IS RECORD(
    item_no       INTEGER(3)   NOT NULL := 999,
    description   VARCHAR2(50),
    quantity      INTEGER,
    price         REAL(7, 2)
  );
BEGIN
  ...
END;

3、引用记录

同集合中的元素不同，它们的引用方式是使用下标索引，而记录对于它的域的引用要使用名称。语法如下：

record_name.field_name

例如，我们想访问记录emp_info下的hire_date域，那么就要使用：

emp_info.hire_date ...

在调用一个返回用户定义的记录类型的函数时，要使用下面的语法：

function_name(parameter_list).field_name

例如，下例对函数nth_highest_sal的调用就引用到记录类型emp_info的salary域：

DECLARE
  TYPE emprec IS RECORD(
    emp_id      NUMBER(4),
    job_title   VARCHAR2(9),
    salary      NUMBER(7, 2)
  );

  middle_sal   NUMBER(7, 2);

  FUNCTION nth_highest_sal(n INTEGER)
    RETURN emprec IS
    emp_info   emprec;
  BEGIN
    ...
    RETURN emp_info;   -- return record
  END;
BEGIN
  middle_sal    := nth_highest_sal(10).salary;   -- call function
  ...
END;

对于一个无参数的返回类型为记录的函数来说，要使用下面的语法引用记录中的字段：

function_name().field_name   -- note empty parameter list

而对于返回类型是一个包含嵌套域的记录的函数来说，引用字段的语法如下：

function_name(parameter_list).field_name.nested_field_name

下面看一个记录包含记录的例子：

DECLARE
  TYPE timerec IS RECORD(
    minutes   SMALLINT,
    hours     SMALLINT
  );

  TYPE agendaitem IS RECORD(
    priority   INTEGER,
    subject    VARCHAR2(100),
    DURATION   timerec
  );

  FUNCTION item(n INTEGER)
    RETURN agendaitem IS
    item_info   agendaitem;
  BEGIN
    ...
    RETURN item_info;   -- return record
  END;
BEGIN
  NULL;
  IF item(3).duration.minutes > 30 THEN ...   -- call function
END;

同样，对于包含在记录中的对象的引用方法也类似：

DECLARE
  TYPE flightrec IS RECORD(
    flight_no      INTEGER,
    plane_id       VARCHAR2(10),
    captain        employee,   -- declare object
    passengers     passengerlist,   -- declare varray
    depart_time    timerec,   -- declare nested record
    airport_code   VARCHAR2(10)
  );

  flight   flightrec;
BEGIN
  ...
  IF flight.captain.name = 'H Rawlins' THEN ...
END;

4、为记录赋控值

要把记录中的所有字段都设置成空值，只需用一个未初始化的同类型记录为它赋值即可，例如：

DECLARE
  TYPE emprec IS RECORD(
    emp_id      emp.empno%TYPE,
    job_title   VARCHAR2(9),
    salary      NUMBER(7, 2)
  );

  emp_info   emprec;
  emp_null   emprec;
BEGIN
  emp_info.emp_id       := 7788;
  emp_info.job_title    := 'ANALYST';
  emp_info.salary       := 3500;
  emp_info              := emp_null;   -- nulls all fields in emp_info
   ...
END;

5、为记录赋值

我们可以把表达式的值赋给记录中特定的域，语法如下：

record_name.field_name := expression;

下例中，我们把雇员的名字转成大写形式：

emp_info.ename := UPPER(emp_info.ename);

除了每个域单独赋值之外，我们还可以一次性为整个记录进行赋值。一次性赋值有两种方法，第一个方法是把同类型的一个记录赋值给另外一个记录：

DECLARE
  TYPE deptrec IS RECORD(
    dept_num    NUMBER(2),
    dept_name   VARCHAR2(14)
  );

  TYPE deptitem IS RECORD(
    dept_num    NUMBER(2),
    dept_name   VARCHAR2(14)
  );

  dept1_info   deptrec;
  dept2_info   deptitem;
BEGIN
  ...
  dept1_info    := dept2_info;   -- illegal; different datatypes
END;

下面再看一个例子，第一个是自定义记录，第二个是使用%ROWTYPE获取的记录，由于这两个记录中的字段数量和顺序相匹配，而且类型兼容，所以可以用其中的一个为另一个赋值：

DECLARE
  TYPE deptrec IS RECORD(
    dept_num    NUMBER(2),
    dept_name   VARCHAR2(14),
    LOCATION    VARCHAR2(13)
  );

  dept1_info   deptrec;
  dept2_info   dept%ROWTYPE;
BEGIN
  SELECT *
    INTO dept2_info
    FROM dept
   WHERE deptno = 10;

  dept1_info    := dept2_info;
  ...
END;

一次性赋值的第二个方法就是使用SELECT或FETCH语句把对应的字段值放入记录中去：

DECLARE
  TYPE deptrec IS RECORD(
    dept_num    NUMBER(2),
    dept_name   VARCHAR2(14),
    LOCATION    VARCHAR2(13)
  );

  dept_info   deptrec;
BEGIN
  SELECT *
    INTO dept_info
    FROM dept
   WHERE deptno = 20;
  ...
END;

但像下面这样的赋值方法是不允许的:

record_name := (value1, value2, value3, ...);   -- not allowed

下面的例子演示了如何把一个嵌套记录赋给另一个，这里要保证的是被嵌套的记录类型是相同的。这样的赋值方法是允许的，即使封闭记录有着不同的数据类型：

DECLARE
  TYPE timerec IS RECORD(
    mins   SMALLINT,
    hrs    SMALLINT
  );

  TYPE meetingrec IS RECORD(
    DAY       DATE,
    time_of   timerec,   -- nested record
    room_no   INTEGER(4)
  );

  TYPE partyrec IS RECORD(
    DAY       DATE,
    time_of   timerec,   -- nested record
    place     VARCHAR2(25)
  );

  seminar   meetingrec;
  party     partyrec;
BEGIN
  ...
  party.time_of    := seminar.time_of;
END;

6、比较记录

记录不能用于空值、等值或不等的比较。例如，下面IF的条件表达式是不允许的：

BEGIN
  ...
  IF emp_info IS NULL THEN ...   -- illegal
  IF dept2_info > dept1_info THEN ...   -- illegal
END;

十七、操作记录

RECORD类型能让我们把事物的属性信息收集起来。这些信息很容易操作，因为我们在集合中把它们当作一个整体来处理。如下例中，我们可以从数据表asserts和liabilities中收集accounting数，然后用比率分析来比较两个子公司的生产效率：

DECLARE
  TYPE FiguresRec IS RECORD (cash REAL, notes REAL, ...);
  sub1_figs FiguresRec;
  sub2_figs FiguresRec;
  FUNCTION acid_test (figs FiguresRec) RETURN REAL IS ...
BEGIN
  SELECT cash, notes, ...
    INTO sub1_figs
    FROM assets, liabilities
   WHERE assets.sub = 1 
     AND liabilities.sub = 1;

  SELECT cash, notes, ...
    INTO sub2_figs
    FROM assets, liabilities
   WHERE assets.sub = 2 
     AND liabilities.sub = 2;
  IF acid_test(sub1_figs) > acid_test(sub2_figs) THEN ...
  ...
END;

注意，向函数acid_test传递收集到的数字是一件很容易的事情，函数能够计算出一个财务比率。

假设我们在SQL*Plus中定义了对象类型Passenger：

SQL> CREATE TYPE Passenger AS OBJECT(
2 flight_no NUMBER(3),
3 name VARCHAR2(20),
4 seat CHAR(5));

下一步定义VARRAY类型PassengerList，用来存放Passenger对象：

SQL> CREATE TYPE PassengerList AS VARRAY(300) OF Passenger;

最后创建关系表flights，其中的一个字段的类型为PassengerList：

SQL> CREATE TABLE flights (
2 flight_no NUMBER(3),
3 gate CHAR(5),
4 departure CHAR(15),
5 arrival CHAR(15),
6 passengers PassengerList);

在字段passengers中的每一项都是一个储存给定航班的旅客名单的变长数组。现在，我们为数据表flights添加一些数据：

BEGIN
  INSERT INTO flights
       VALUES (109, '80', 'DFW 6:35PM', 'HOU 7:40PM',
               passengerlist(passenger(109, 'Paula Trusdale', '13C'),
                             passenger(109, 'Louis Jemenez', '22F'),
                             passenger(109, 'Joseph Braun', '11B'), ...));

  INSERT INTO flights
       VALUES (114, '12B', 'SFO 9:45AM', 'LAX 12:10PM',
               passengerlist(passenger(114, 'Earl Benton', '23A'),
                             passenger(114, 'Alma Breckenridge', '10E'),
                             passenger(114, 'Mary Rizutto', '11C'), ...));

  INSERT INTO flights
       VALUES (27, '34', 'JFK 7:05AM', 'MIA 9:55AM',
               passengerlist(passenger(27, 'Raymond Kiley', '34D'),
                             passenger(27, 'Beth Steinberg', '3A'),
                             passenger(27, 'Jean Lafevre', '19C'), ...));
END;

下例中，我们从数据表flights中取出数据放到记录flight_into中去。那样，我们就可以把一个航班的所有的信息，包括它的旅客名单，作为一个逻辑单元来处理。

DECLARE
  TYPE flightrec IS RECORD(
    flight_no    NUMBER(3),
    gate         CHAR(5),
    departure    CHAR(15),
    arrival      CHAR(15),
    passengers   passengerlist
  );

  flight_info          flightrec;

  CURSOR c1 IS
    SELECT *
      FROM flights;

  seat_not_available   EXCEPTION;
BEGIN
  OPEN c1;

  LOOP
    FETCH c1
     INTO flight_info;

    EXIT WHEN c1%NOTFOUND;

    FOR i IN 1 .. flight_info.passengers.LAST LOOP
      IF flight_info.passengers(i).seat = 'na' THEN
        DBMS_OUTPUT.put_line(flight_info.passengers(i).NAME);
        RAISE seat_not_available;
      END IF;

      ...
    END LOOP;
  END LOOP;

  CLOSE c1;
EXCEPTION
  WHEN seat_not_available THEN
    ...
END;

1、向数据库插入PL/SQL记录

PL/SQL对INSERT语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表来插入一条数据。这样才可以让我们的代码更具可读性，更容易维护。

记录中域的个数必须和INTO子句后面列出的字段个数相等，对应的域和字段的类型必须兼容。这样可以保证记录与数据表兼容。

• 利用%ROWTYPE插入PL/SQL记录

这个例子用%ROWTYPE声明了一个记录类型变量。我们可以使用这个变量直接插入数据而不用指定字段列表。%ROWTYPE声明能保证记录属性的名称和类型与数据表字段完全一致。

DECLARE
  dept_info   dept%ROWTYPE;
BEGIN
  -- deptno, dname, and loc are the table columns.
  -- The record picks up these names from the %ROWTYPE.
  dept_info.deptno    := 70;
  dept_info.dname     := 'PERSONNEL';
  dept_info.loc       := 'DALLAS';

  -- Using the %ROWTYPE means we can leave out the column list
  -- (deptno, dname, loc) from the INSERT statement.
  INSERT INTO dept
       VALUES dept_info;
END;

2、使用记录更新数据库

PL/SQL对UPDATE语句的唯一的扩展就是能让我们使用一个独立RECORD类型或是%ROWTYPE类型变量，来代替域列表更新一条数据。

记录中域的个数必须和SET子句后面列出的字段个数相等，对应的域和字段的类型也必须兼容。

• 用记录更新行记录

我们可以使用关键字ROW代表完整的一行数据：

/* Formatted on 2006/08/30 20:27 (Formatter Plus v4.8.7) */
DECLARE
  dept_info   dept%ROWTYPE;
BEGIN
  dept_info.deptno    := 30;
  dept_info.dname     := 'MARKETING';
  dept_info.loc       := 'ATLANTA';

  -- The row will have values for the filled-in columns, and null
  -- for any other columns.
  UPDATE dept
     SET ROW = dept_info
   WHERE deptno = 30;
END;

关键字ROW只允许出现在SET子句的左边。

• 不能在子查询中使用SET ROW

我们不能在子查询中使用ROW。例如，下面的UPDATE语句是不允许的：

UPDATE emp SET ROW = (SELECT * FROM mgrs);   -- not allowed

• 使用包含对象的记录更新行数据

包含对象类型的记录是可以使用的：

CREATE TYPE worker AS OBJECT(
  NAME   VARCHAR2(25),
  dept   VARCHAR2(15)
);
/

CREATE TABLE teams (team_no NUMBER, team_member worker);

DECLARE
  team_rec   teams%ROWTYPE;
BEGIN
  team_rec.team_no        := 5;
  team_rec.team_member    := worker('Paul Ocker', 'Accounting');

  UPDATE teams
     SET ROW = team_rec;
END;
/

• 使用包含集合的记录更新行数据

记录可以包含集合：

CREATE TYPE worker AS OBJECT(
  NAME   VARCHAR2(25),
  dept   VARCHAR2(15)
);
/

CREATE TYPE roster AS TABLE OF worker;
/

CREATE TABLE teams (team_no NUMBER, members roster)
NESTED TABLE members STORE AS teams_store;
INSERT INTO teams
     VALUES (1,
             roster(worker('Paul Ocker', 'Accounting'),
                    worker('Gail Chan', 'Sales'),
                    worker('Marie Bello', 'Operations'),
                    worker('Alan Conwright', 'Research')));

DECLARE
  team_rec   teams%ROWTYPE;
BEGIN
  team_rec.team_no    := 3;
  team_rec.members    := roster(worker('William Bliss', 'Sales'),
                                worker('Ana Lopez', 'Sales'),
                                worker('Bridget Towner', 'Operations'),
                                worker('Ajay Singh', 'Accounting'));

  UPDATE teams
     SET ROW = team_rec;
END;
/

• 使用RETURNING子句

INSERT，UPDATE和DELETE语句都可以包含RETURNING子句，返回的字段值来自于被影响到的行，它们被放到PL/SQL记录变量中。这就可以省掉在插入、更新操作之后或删除操作之前执行SELECT查找被影响到的数据。我们只能在对一行数据进行操作时使用这个子句。

下面的例子中，我们更新一个雇员的工资，同时，检索雇员的姓名、职别和把新的工资值放进记录变量：

DECLARE
  TYPE emprec IS RECORD(
    emp_name    VARCHAR2(10),
    job_title   VARCHAR2(9),
    salary      NUMBER(7, 2)
  );

  emp_info   emprec;
  emp_id     NUMBER(4);
BEGIN
  emp_id    := 7782;

  UPDATE    emp
        SET sal = sal * 1.1
      WHERE empno = emp_id
  RETURNING ename,
            job,
            sal
       INTO emp_info;
END;

3、记录类型插入/更新操作的约束

1. 记录类型变量只在下面几种情况下才允许使用：
   1. 在UPDATE语句中SET子句的右边
   2. 在INSERT语句中VALUES子句的后面
   3. 在RETURNING语句中INTO子句的后面
   记录变量是不允许出现在SELECT列表、WHERE子句、GROUP BY子句或ORDER BY子句中的。
2. 关键字ROW只允许在SET子句的左面出现，并且不能和子查询连用。
3. UPDATE语句中，如果使用了ROW关键字，那么SET就只能使用一次。
4. 如果一个INSERT语句的VALUES子句中包含了记录变量，那么就不允许出现其他变量或值。
5. 如果RETURNING语句的INTO子句中包含了记录变量，那么就不允许出现其他变量或值。
6. 下面三种情况是不能使用记录的：
   1. 含有记录嵌套。
   2. 函数返回记录类型。
   3. 记录的插入/更新是用EXECUTE IMMEDIATE语句完成的。

4、用查询结果为记录类型的集合赋值

PL/SQL的绑定操作可以分为三类：

1. 定义：使用SELECT或FETCH语句为PL/SQL变量或主变量赋值。
2. 内绑定：用INSERT语句插入的或UPDATE语句更新的数据库值。
3. 外绑定：用INSERT、UPDATE或DELETE语句的RETURNING子句把值返回到PL/SQL变量或主变量中。

PL/SQL支持使用DML语句对记录类型的集合进行批量绑定。一个"定义"或"外绑定"变量可以是记录类型的集合，"内绑定"值可以保存到记录类型的集合中的。语法如下：

SELECT select_items BULK COLLECT 
  INTO record_variable_name
  FROM rest_of_select_stmt

FETCH { cursor_name
      | cursor_variable_name
      | :host_cursor_variable_name}
  BULK COLLECT INTO record_variable_name
  [LIMIT numeric_expression];

FORALL index IN lower_bound..upper_bound
  INSERT INTO { table_reference
              | THE_subquery} [{column_name[, column_name]...}]
       VALUES (record_variable_name(index)) rest_of_insert_stmt

FORALL index IN lower_bound..upper_bound
  UPDATE {table_reference | THE_subquery} [alias]
     SET (column_name[, column_name]...) = record_variable_name(index)
  rest_of_update_stmt

RETURNING row_expression[, row_expression]...
  BULK COLLECT INTO record_variable_name;

上面每个语句和子句中，记录变量存储一个记录类型的集合。记录中的域个数必须和SELECT、INSERT INTO、UPDATE ... SET或RETURNING相对应的列的个数相同。并且相对应的域和字段必须类型兼容。下面是几个例子：

CREATE TABLE tab1 (col1 NUMBER, col2 VARCHAR2(20));
/
CREATE     TABLE tab2 (col1 NUMBER, col2 VARCHAR2(20));
/

DECLARE
  TYPE rectabtyp IS TABLE OF tab1%ROWTYPE
    INDEX BY BINARY_INTEGER;

  TYPE numtabtyp IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;

  TYPE chartabtyp IS TABLE OF VARCHAR2(20)
    INDEX BY BINARY_INTEGER;

  CURSOR c1 IS
    SELECT col1, col2
      FROM tab2;

  rec_tab    rectabtyp;
  num_tab    numtabtyp  := numtabtyp(2, 5, 8, 9);
  char_tab   chartabtyp := chartabtyp('Tim', 'Jon', 'Beth', 'Jenny');
BEGIN
  FORALL i IN 1 .. 4
    INSERT INTO tab1
         VALUES (num_tab(i), char_tab(i));

  SELECT col1,
         col2
  BULK COLLECT INTO rec_tab
    FROM tab1
   WHERE col1 < 9;

  FORALL i IN rec_tab.FIRST .. rec_tab.LAST
    INSERT INTO tab2
         VALUES rec_tab(i);

  FOR i IN rec_tab.FIRST .. rec_tab.LAST LOOP
    rec_tab(i).col1    := rec_tab(i).col1 + 100;
  END LOOP;

  FORALL i IN rec_tab.FIRST .. rec_tab.LAST
    UPDATE tab1
       SET (col1, col2) = rec_tab(i)
     WHERE col1 < 8;

  OPEN c1;

  FETCH c1
  BULK COLLECT INTO rec_tab;

  CLOSE c1;
END;