9.14. PL/SQL记录 - PL/SQL教程

编辑时间: 2016-04-22 23:49:57    关键字:

PL/SQL记录就是可以容纳不同类型的数据项的数据结构。记录由不同字段,类似于数据库表的行。

例如,要保留跟踪图书馆中的书籍。可能要跟踪有关每本书下面的属性类似:标题,作者,主题,图书ID。包含一个字段为每个这些项目记录允许进行处理书籍作为一个逻辑单元信息。

PL/SQL可以处理记录的以下几种类型:

  • 基于数据表

  • 基于游标的记录

  • 用户自定义记录

基于表的记录

在%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. 

推荐热图

合作推荐

2010-2018 可思数据版权所有 About SYKV | ICP备案:京ICP备14056871号