在本章中,无涯教程将讨论PL/SQL中的记录(Records)。 记录是一种数据结构,可以容纳不同种类的数据项。记录由不同的字段组成,类似于数据库表的一行。
PL/SQL可以处理以下类型的记录-
%ROWTYPE属性使程序员可以创建基于基于表的和基于 cursor的的记录。
以下示例说明了基于表的记录的概念。无涯教程将使用在先前各章中创建并使用的CUSTOMERS表-
链接:https://www.learnfk.comhttps://www.learnfk.com/plsql/plsql-records.html
来源:LearnFk无涯教程网
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提供了用户定义的记录类型,使您可以定义不同的记录结构,这些记录包含不同的字段,假设您想跟踪图书馆中的书籍。您可能需要跟踪有关每本书的以下属性-
Record记录类型定义为-
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.
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)
HarmonyOS快速入门与实战 -〔QCon+案例研习社〕