记录是可以容纳不同种类的数据项的数据结构。 记录由不同的字段组成,类似于数据库表的一行。
例如,想要在图书馆中跟踪记录图书信息。可能希望跟踪每本书的以下属性,例如标题,作者,主题,图书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