PL/SQL記錄就是可以容納不同類型的數(shù)據(jù)項(xiàng)的數(shù)據(jù)結(jié)構(gòu)。記錄由不同字段,類似于數(shù)據(jù)庫(kù)表的行。
例如,要保留跟蹤圖書館中的書籍。可能要跟蹤有關(guān)每本書下面的屬性類似:標(biāo)題,作者,主題,圖書ID。包含一個(gè)字段為每個(gè)這些項(xiàng)目記錄允許進(jìn)行處理書籍作為一個(gè)邏輯單元信息。
PL/SQL可以處理記錄的以下幾種類型:
基于數(shù)據(jù)表
基于游標(biāo)的記錄
用戶自定義記錄
在%ROWTYPE屬性使程序員創(chuàng)建基于表格和基于游標(biāo)記錄。
下面的例子將說(shuō)明基于表的記錄的概念。使用我們已經(jīng)創(chuàng)建,并在前面的章節(jié)中使用的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; /
當(dāng)上述代碼在SQL提示符執(zhí)行時(shí),它會(huì)產(chǎn)生了以下結(jié)果:
Customer ID: 5 Customer Name: Hardik Customer Address: Bhopal Customer Salary: 9000 PL/SQL procedure successfully completed.
下面的例子將說(shuō)明了基于游標(biāo)記錄的概念。使用我們已經(jīng)創(chuàng)建,并在前面的章節(jié)中使用的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; /
當(dāng)上述代碼在SQL提示符執(zhí)行時(shí),它產(chǎn)生了以下結(jié)果:
1 Ramesh 2 Khilan 3 kaushik 4 Chaitali 5 Hardik 6 Komal PL/SQL procedure successfully completed.
PL/SQL提供了一個(gè)用戶定義的記錄類型,允許定義不同的記錄結(jié)構(gòu)。記錄由不同的字段組成。假設(shè)要跟蹤圖書館的書籍。可能要跟蹤有關(guān)每本書以下屬性:
標(biāo)題
作者
科目
書籍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;
要訪問(wèn)記錄的字段,我們使用點(diǎn)(.)運(yùn)算符。成員訪問(wèn)運(yùn)算符編碼為記錄變量名和訪問(wèn)字段期間。以下為例子來(lái)解釋記錄的使用:
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;上一篇:PL/SQL游標(biāo)下一篇:PL/SQL異常