首页 > 代码库 > （十）PL/SQL记录
（十）PL/SQL记录
PL/SQL记录就是可以容纳不同类型的数据项的数据结构。记录由不同字段，类似于数据库表的行。例如，要保留跟踪图书馆中的书籍。可能要跟踪有关每本书下面的属性类似：标题，作者，主题，图书ID。包含一个字段为每个这些项目记录允许进行处理书籍作为一个逻辑单元信息。
PL/SQL可以处理记录的以下几种类型：
基于表的记录
基于游标的记录
用户自定义记录
附：
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
一、基于表的记录
在％ROWTYPE属性使程序员创建基于表格和基于游标记录。
下面的例子将说明基于表的记录的概念。使用CUSTOMERS表：
DECLARE customer_rec customers%rowtype; BEGIN SELECT * into customer_rec FROM customers WHERE id = 5; dbms_output.put_line(‘Customer ID: ‘ || customer_rec.id); dbms_output.put_line(‘Customer Name: ‘ || customer_rec.name); dbms_output.put_line(‘Customer Address: ‘ || customer_rec.address); dbms_output.put_line(‘Customer Salary: ‘ || customer_rec.salary); END; / 当上述代码在SQL提示符执行时，它会产生了以下结果： Customer ID: 5 Customer Name: Hardik Customer Address: Bhopal Customer Salary: 9000 PL/SQL procedure successfully completed.
二、基于游标记录
下面的例子将说明了基于游标记录的概念。使用CUSTOMERS表：
DECLARE CURSOR customer_cur is SELECT id, name, address FROM customers; customer_rec customer_cur%rowtype; BEGIN OPEN customer_cur; LOOP FETCH customer_cur into customer_rec; EXIT WHEN customer_cur%notfound; DBMS_OUTPUT.put_line(customer_rec.id || ‘ ‘ || customer_rec.name); END LOOP; END; / 当上述代码在SQL提示符执行时，它产生了以下结果： 1 Ramesh 2 Khilan 3 kaushik 4 Chaitali 5 Hardik 6 Komal PL/SQL procedure successfully completed.
三、用户自定义记录
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;
这里是声明图书的记录方式： DECLARE TYPE books IS RECORD (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books;
四、访问字段
要访问记录的字段，我们使用点（.）运算符。成员访问运算符编码为记录变量名和访问字段期间。以下为例子来解释记录的使用：
DECLARE type books is record (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books; BEGIN -- Book 1 specification book1.title := ‘C Programming‘; book1.author := ‘Nuha Ali ‘; book1.subject := ‘C Programming Tutorial‘; book1.book_id := 6495407;
-- Book 2 specification book2.title := ‘Telecom Billing‘; book2.author := ‘Zara Ali‘; book2.subject := ‘Telecom Billing Tutorial‘; book2.book_id := 6495700;
-- Print book 1 record dbms_output.put_line(‘Book 1 title : ‘|| book1.title); dbms_output.put_line(‘Book 1 author : ‘|| book1.author); dbms_output.put_line(‘Book 1 subject : ‘|| book1.subject); dbms_output.put_line(‘Book 1 book_id : ‘ || book1.book_id); -- Print book 2 record dbms_output.put_line(‘Book 2 title : ‘|| book2.title); dbms_output.put_line(‘Book 2 author : ‘|| book2.author); dbms_output.put_line(‘Book 2 subject : ‘|| book2.subject); dbms_output.put_line(‘Book 2 book_id : ‘|| book2.book_id); END; / 当上述代码在SQL提示符执行时，它产生了以下结果： Book 1 title : C Programming Book 1 author : Nuha Ali Book 1 subject : C Programming Tutorial Book 1 book_id : 6495407 Book 2 title : Telecom Billing Book 2 author : Zara Ali Book 2 subject : Telecom Billing Tutorial Book 2 book_id : 6495700 PL/SQL procedure successfully completed.
五、记录作为子程序参数
可以通过记录作为子程序参数，非常相似传递任何其他变量的方式。访问可以象在上面的例子 - 已访问类似的方式记录字段：
DECLARE type books is record (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books; PROCEDURE printbook (book books) IS BEGIN dbms_output.put_line (‘Book title : ‘ || book.title); dbms_output.put_line(‘Book author : ‘ || book.author); dbms_output.put_line( ‘Book subject : ‘ || book.subject); dbms_output.put_line( ‘Book book_id : ‘ || book.book_id); END; BEGIN -- Book 1 specification book1.title := ‘C Programming‘; book1.author := ‘Nuha Ali ‘; book1.subject := ‘C Programming Tutorial‘; book1.book_id := 6495407;
-- Book 2 specification book2.title := ‘Telecom Billing‘; book2.author := ‘Zara Ali‘; book2.subject := ‘Telecom Billing Tutorial‘; book2.book_id := 6495700;
-- Use procedure to print book info printbook(book1); printbook(book2); END; / 当上述代码在SQL提示符执行时，它产生了以下结果： Book title : C Programming Book author : Nuha Ali Book subject : C Programming Tutorial Book book_id : 6495407 Book title : Telecom Billing Book author : Zara Ali Book subject : Telecom Billing Tutorial Book book_id : 6495700 PL/SQL procedure successfully completed.
（十）PL/SQL记录