13、PL/SQL记录
记录是可以容纳不同种类的数据项的数据结构。 记录由不同的字段组成，类似于数据库表的一行。
例如，想要在图书馆中跟踪记录图书信息。可能希望跟踪每本书的以下属性，例如标题，作者，主题，图书ID。 包含每个这些项目的字段的记录允许将图书视为逻辑单元，并允许以更好的方式组织和表示其信息。
PL/SQL可以处理以下类型的记录 -
- 基于表的记录
- 基于游标的记录
- 用户定义的记录
基于表的记录
%ROWTYPE属性使程序员能够创建基于表和基于游标的记录。
以下示例说明了基于表的记录的概念。这里将使用前面章节中创建和使用的customers表，表结构和数据如下 -
使用表记录示例代码 -
SQL> declare
2 customer_rec customers%rowtype;
3 begin
4 select * into customer_rec
5 from customers
6 where id=5;
7 dbms_output.put_line('客户ID: ' || customer_rec.id );
8 dbms_output.put_line('客户姓名: ' || customer_rec.name );
9 dbms_output.put_line('客户地址: ' || customer_rec.address );
10 dbms_output.put_line('客户薪资: ' || customer_rec.salary );
11 end;
12 /
客户ID: 5
客户姓名: Hardik
客户地址: Bhopal
客户薪资: 8500
PL/SQL procedure successfully completed
Executed in 0.016 seconds
基于游标的记录
以下示例说明了基于游标的记录的概念，下面将使用在前面创建和使用的CUSTOMERS表，参考示例代码如下 -
SQL> declare 2 cursor customer_cur is 3 select id,name,address 4 from customers; 5 customer_rec customer_cur%rowtype; 6 begin 7 open customer_cur; 8 loop 9 fetch customer_cur into customer_rec; 10 exit when customer_cur%notfound; 11 dbms_output.put_line(customer_rec.id || ' ' || customer_rec.name); 12 end loop; 13 end; 14 / 1 Ramesh 2 Khilan 3 kaushik 4 Chaitali 5 Hardik 6 Komal PL/SQL procedure successfully completed Executed in 0.016 seconds
用户定义的记录
PL/SQL提供了一个用户定义的记录类型，允许程序员定义不同的记录结构。这些记录由不同的字段组成。假设要跟踪记录图书信息，例如可能要跟踪每本书的以下属性 -
- 标题
- 作者
- 学科
- 图书ID
定义一个记录
记录类型被定义为如下 -
TYPE type_name IS RECORD ( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION], field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION], ... field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION); record-name type_name;
图书(Book)记录按以下方式声明 -
DECLARE TYPE books IS RECORD ( title varchar(50), author varchar(50), subject varchar(100), book_id number ); book1 books; book2 books;
访问字段
要访问记录的任何字段，可通过使用点(.)运算符。成员访问操作符被编码为记录变量名称和希望访问的字段。看盾以下一个例子中如何使用记录 -
SQL> DECLARE
2 type books is record
3 (title varchar(50),
4 author varchar(50),
5 subject varchar(100),
6 book_id number);
7 book1 books;
8 book2 books;
9 BEGIN
10 -- Book 1 specification
11 book1.title := 'C Programming';
12 book1.author := 'TanHao';
13 book1.subject := 'C Programming Tutorial';
14 book1.book_id := 1920122;
15 -- Book 2 specification
16 book2.title := 'Telecom Billing';
17 book2.author := 'LiDawei';
18 book2.subject := 'Telecom Billing Tutorial';
19 book2.book_id := 2032942;
20
21 -- Print book 1 record
22 dbms_output.put_line('Book 1 title : '|| book1.title);
23 dbms_output.put_line('Book 1 author : '|| book1.author);
24 dbms_output.put_line('Book 1 subject : '|| book1.subject);
25 dbms_output.put_line('Book 1 book_id : ' || book1.book_id);
26
27 -- Print book 2 record
28 dbms_output.put_line('Book 2 title : '|| book2.title);
29 dbms_output.put_line('Book 2 author : '|| book2.author);
30 dbms_output.put_line('Book 2 subject : '|| book2.subject);
31 dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
32 END;
33 /
Book 1 title : C Programming
Book 1 author : TanHao
Book 1 subject : C Programming Tutorial
Book 1 book_id : 1920122
Book 2 title : Telecom Billing
Book 2 author : LiDawei
Book 2 subject : Telecom Billing Tutorial
Book 2 book_id : 2032942
PL/SQL procedure successfully completed
Executed in 0.016 seconds
将记录作为子程序参数
可以像传递任何其他变量一样将记录作为子程序参数传递。还可以像访问上面的示例一样访问记录字段，参考下示例代码 -
SQL> DECLARE
2 type books is record
3 (title varchar(50),
4 author varchar(50),
5 subject varchar(100),
6 book_id number);
7 book1 books;
8 book2 books;
9 PROCEDURE printbook (book books) IS
10 BEGIN
11 dbms_output.put_line ('Book title : ' || book.title);
12 dbms_output.put_line('Book author : ' || book.author);
13 dbms_output.put_line( 'Book subject : ' || book.subject);
14 dbms_output.put_line( 'Book book_id : ' || book.book_id);
15 END;
16
17 BEGIN
18 -- Book 1 specification
19 book1.title := 'C Programming';
20 book1.author := 'Haoqiang Tang';
21 book1.subject := 'C Programming Tutorial';
22 book1.book_id := 8321407;
23
24 -- Book 2 specification
25 book2.title := 'Telecom Billing';
26 book2.author := 'Maxsu';
27 book2.subject := 'Telecom Billing Tutorial';
28 book2.book_id := 921300;
29
30 -- Use procedure to print book info
31 printbook(book1);
32 printbook(book2);
33 END;
34 /
Book title : C Programming
Book author : Haoqiang Tang
Book subject : C Programming Tutorial
Book book_id : 8321407
Book title : Telecom Billing
Book author : Maxsu
Book subject : Telecom Billing Tutorial
Book book_id : 921300
PL/SQL procedure successfully completed
Executed in 0.016 seconds