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;