在本章中,我们将讨论PL/SQL中的记录. 记录是一种可以保存不同类型数据项的数据结构.记录由不同的字段组成,类似于数据库表的一行.
例如,您希望在库中跟踪您的书籍.您可能希望跟踪每本书的以下属性,例如标题,作者,主题,书籍ID.包含每个项目的字段的记录允许将BOOK视为逻辑单元,并允许您以更好的方式组织和表示其信息.
PL/SQL可以处理以下内容记录类型 :
基于表格
基于游标的记录
用户定义的记录
%ROWTYPE属性启用程序员创建基于表格的和基于游标的记录.
以下示例说明基于表格的
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提供用户定义的记录类型,允许您定义不同的记录结构.这些记录包含不同的字段.假设您想要在图书馆中跟踪您的图书.您可能希望跟踪每本书的以下属性 :
标题
作者
主题
图书编号
记录类型定义为 :
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;
要访问记录的任何字段,我们使用点(.)运营商.成员访问运算符被编码为记录变量名称和我们希望访问的字段之间的句点.下面是一个例子来解释记录和减去的用法;
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.