n this chapter, we will discuss Records in PL/SQL. A record is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.
For example, you want to keep track of your books in a library. You might want to track the following attributes about each book, such as Title, Author, Subject, Book ID. A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.
PL/SQL can handle the following types of records −
- Table-based
- Cursor-based records
- User-defined records
Table-Based Records
The %ROWTYPE attribute enables a programmer to create table-based and cursorbased records.
The following example illustrates the concept of table-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters −
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; /
When the above code is executed at the SQL prompt, it produces the following result −
Customer ID: 5 Customer Name: Hardik Customer Address: Bhopal Customer Salary: 9000 PL/SQL procedure successfully completed.
Cursor-Based Records
The following example illustrates the concept of cursor-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters −
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; /
When the above code is executed at the SQL prompt, it produces the following result −
1 Ramesh 2 Khilan 3 kaushik 4 Chaitali 5 Hardik 6 Komal PL/SQL procedure successfully completed.
User-Defined Records
PL/SQL provides a user-defined record type that allows you to define the different record structures. These records consist of different fields. Suppose you want to keep track of your books in a library. You might want to track the following attributes about each book −
- Title
- Author
- Subject
- Book ID
Defining a Record
The record type is defined as −
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;
The Book record is declared in the following way −
DECLARE TYPE books IS RECORD (title varchar(50), author varchar(50), subject varchar(100), book_id number); book1 books; book2 books;
Accessing Fields
To access any field of a record, we use the dot (.) operator. The member access operator is coded as a period between the record variable name and the field that we wish to access. Following is an example to explain the usage of record −
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; /
When the above code is executed at the SQL prompt, it produces the following result −
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.
Records as Subprogram Parameters
You can pass a record as a subprogram parameter just as you pass any other variable. You can also access the record fields in the same way as you accessed in the above example −
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; /
When the above code is executed at the SQL prompt, it produces the following result −
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.
Great post!! This can be one particular of the most useful blogs We’ve ever arrive across on this subject. Basically Wonderful. I am also a specialist in this topic so I can understand your hard work.
ReplyDeletePeer Reviewed Journals
Journal Of Mathematics
Conference publication
Journal Of Physics
Journal Of Education
Wow! this is Amazing! I got free 100$ Gift cards! Click here to get your free gift cards!
ReplyDeleteVery nice work, really appreciable. Good to know something new today, keep it up.
ReplyDelete1000 free youtube subscribers
SMM service
"InstabuyJ is an absolute lifesaver! I needed a replacement screen for my Samsung phone urgently, and they delivered it to me faster than I expected. The quality of the spare part is top-notch, and my phone looks good as new now. Plus, their customer service team was incredibly helpful throughout the process. Highly recommend InstabuyJ for all your Samsung mobile spare part needs!"
ReplyDeleteEnquiry Now -Samsung mobile spare parts in Goa