SQL Records-plsql Records

PL/SQL Records

PL/SQL records are data structures that can accommodate different types of data items. Records are made from different fields, similar to rows in a database table.

For example, to keep track of books in libraries. You may want to track the following properties for each book similar to: title, author, subject, book ID. Contains a field for each of these item records that allows the processing of books as a logical unit of information.

PL/SQL can handle the following types of records:

• Based on data table

• Cursor-based Records

• User-defined Records

Table-based Records

The%rowtype property enables programmers to create table-based and cursor-based records.

The following example illustrates the concept of table-based records. Use the Customers table that we have created and used in the previous chapters:

DECLARECustomer_rec Customers%RowType;BEGIN SELECT * IntoCustomer_recFromCustomersWHEREId= 5;Dbms_output.Put_Line(' Customer ID: ' ||Customer_rec.Id);Dbms_output.Put_Line(' Customer Name: ' | |  Customer_rec. Dbms_output. ( ' Customer Address: '  | |  Customer_rec. Dbms_output. ( ' Customer Salary: '  | |  Customer_rec.end;               

When the above code is executed at the SQL prompt, it produces the following results:

Customer id:5customer Name:hardikcustomer Address:bhopalcustomer salary:9000pl/sql procedure successfully completed.

Cursor-based logging

The following example illustrates the concept of cursor-based logging. Use the Customers table that we have created and used in the previous chapters:

DECLARE   CURSORCustomer_curIs SELECTId,Name,AddressFromCustomers;Customer_rec Customer_cur%RowType;BEGIN OPENCustomer_cur;LOOPFETCHCustomer_curIntoCustomer_rec exit when customer_cur %notfound;. Put_line (customer_rec. ID | |   " | |  Customer_rec. end Loop;end;                

When the above code is executed at the SQL prompt, it produces the following results:

1 Ramesh2 Khilan3 kaushik4 Chaitali5 Hardik6 komalpl/sql procedure successfully completed.

User-defined Records

PL/SQL provides a user-defined record type that allows you to define a different record structure. Records are made up of different fields. Suppose you want to keep track of library books. You may want to track the following properties for each book:

• Title

• Author

• Subjects

• Book ID

Define a record

The record type is defined as:

Typetype_nameIsRECORD(Field_name1 Datatype1[Not Null] [:= default Expression ], field_name2 datatype2 [notnull] [:=  Expression],  ...  Field_namen datatypen [notnull] [:=  Expression            

Here is the way to declare the book's record:

DECLARE is RECORD(title varchar, author varchar),  Subject varchar(+), book_id number);  Book1 books;  Book2 books;                  

accessing fields

To access the fields of the record, we use the dot (.) operator. The member access operator is encoded as the record variable name and access field period. Here is an example to explain the use of records:

DECLAREType booksIsRecord(Title varchar(50),Author varchar(50),Subject varchar(100),BOOK_ID number);Book1 Books;BOOK2 Books;BEGIN --Book 1 specificationBook1.Title:= ' C programming ';Book1.Author:= ' Nuha Ali ';Book1.Subject:= ' C programming Tutorial ';Book1.book_id:= 6495407; --Book 2 specificationBook2.Title:= ' Telecom billing ';Book2.Author:= ' Zara Ali ';Book2.Subject:= ' Telecom billing Tutorial ';Book2.book_id:= 6495700; --Print Book 1 recordDbms_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 recordDbms_output.Put_Line(' Book 2 title: '||Book2.Title);Dbms_output.put_line ( ' Book 2 author: ' | |  Book2. Dbms_output. ( ' Book 2 subject: ' | |  Book2. Dbms_output. ( ' Book 2 book_id: ' | |  Book2.end;               

When the above code is executed at the SQL prompt, it produces the following results:

Book 1 title:c programmingbook 1 Author:nuha alibook 1 subject:c programming Tutorialbook 1 Book_id:6495407book 2 title:telecom Billingbook 2 Author:zara alibook 2 subject:telecom billing Tutorialbook 2 Book_id:6495700pl/sql Pro Cedure successfully completed.

Record as subroutine parameter

You can pass any other variable in a very similar way by logging it as a subroutine parameter. Access can record fields like in the example above-a similar way has been accessed:

DECLAREType booksIsRecord(Title varchar(50),Author varchar(50),Subject varchar(100),BOOK_ID number);Book1 Books;BOOK2 Books;PROCEDUREPrintbook(Book Books) IsBEGINDbms_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 specificationBook1.Title:= ' C programming ';Book1.Author:= ' Nuha Ali ';Book1.Subject:= ' C programming Tutorial ';Book1.book_id:= 6495407; --Book 2 specificationBook2.Title:= ' Telecom billing ';Book2.Author:=  ' Zara Ali ' ; book2< Span class= "pun". subject :=  ' Telecom billing Tutorial '  Book2.:= 6495700; Span class= "com" >--use procedure to print book Info Printbook ( Book1 Printbook (book2end;                

When the above code is executed at the SQL prompt, it produces the following results:

Book  title:c programmingbook  author:nuha alibook subject:c programming Tutorialbook  Book_id:6495407boo K title:telecom Billingbook Author:zara alibook subject:telecom billing Tutorialbook book_id:6495700pl/sql procedu Re successfully completed.

SQL Records-plsql Records