PL\SQL用户指南与参考5.2.2 转载

十五、什么是记录

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

%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 ;