记录是可以容纳不同种类的数据项的数据结构。 记录由不同的字段组成,类似于数据库表的一行。
PL/SQL可以处理以下类型的记录:
- 基于表的记录
- 基于游标的记录
- 用户定义的记录
基于表的记录
%ROWTYPE属性使程序员能够创建基于表和基于游标的记录。
DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('客户ID: ' || customer_rec.id);
dbms_output.put_line('客户姓名: ' || customer_rec.name);
dbms_output.put_line('客户地址: ' || customer_rec.address);
dbms_output.put_line('客户薪资: ' || customer_rec.salary);
END;
/
基于游标的记录
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;
/
用户定义的记录
PL/SQL提供了一个用户定义的记录类型,允许程序员定义不同的记录结构。这些记录由不同的字段组成。
1、语法:
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;
2、实例
要访问记录的任何字段,可通过成员访问操作符(.)来访问。
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 := 'TanHao';
book1.subject := 'C Programming Tutorial';
book1.book_id := 1920122;
-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'LiDawei';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 2032942;
-- 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;
/
将记录作为子程序参数
可以像传递任何其他变量一样将记录作为子程序参数传递。
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 := 'HAOQIANG TANG';
BOOK1.SUBJECT := 'C PROGRAMMING TUTORIAL';
BOOK1.BOOK_ID := 8321407;
-- BOOK 2 SPECIFICATION
BOOK2.TITLE := 'TELECOM BILLING';
BOOK2.AUTHOR := 'MAXSU';
BOOK2.SUBJECT := 'TELECOM BILLING TUTORIAL';
BOOK2.BOOK_ID := 921300;
-- USE PROCEDURE TO PRINT BOOK INFO
PRINTBOOK(BOOK1);
PRINTBOOK(BOOK2);
END;
/