PL/SQL - 记录(Records)

PL/SQL - 记录(Records) 首页 / PL/SQL入门教程 / PL/SQL - 记录(Records)

在本章中,无涯教程将讨论PL/SQL中的记录(Records)。 记录是一种数据结构,可以容纳不同种类的数据项。记录由不同的字段组成,类似于数据库表的一行。

PL/SQL可以处理以下类型的记录-

  • 基于表的记录    (Table-based records)
  • 基于游标的记录(Cursor-based records)
  • 用户定义的记录(User-defined records)

表记录

%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提供了用户定义的记录类型,使您可以定义不同的记录结构,这些记录包含不同的字段,假设您想跟踪图书馆中的书籍。您可能需要跟踪有关每本书的以下属性-

  • 标题
  • 作者
  • 主题
  • 图书ID

定义记录

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. 

祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)

技术教程推荐

现代C++编程实战 -〔吴咏炜〕

分布式协议与算法实战 -〔韩健〕

Django快速开发实战 -〔吕召刚〕

如何落地业务建模 -〔徐昊〕

说透区块链 -〔自游〕

HarmonyOS快速入门与实战 -〔QCon+案例研习社〕

React Native 新架构实战课 -〔蒋宏伟〕

超级访谈:对话玉伯 -〔玉伯〕

云原生架构与GitOps实战 -〔王炜〕

好记忆不如烂笔头。留下您的足迹吧 :)