(10) 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:
table-based Records
cursor-based Records
user-defined records
  
  
  
attached:
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | Age | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 |  Ramesh | 32 |  Ahmedabad | 2000.00 |
| 2 |  Khilan | 25 |  Delhi | 1500.00 |
| 3 |  Kaushik | 23 |  Kota | 2000.00 |
| 4 |  Chaitali | 25 |  Mumbai | 6500.00 |
| 5 |  Hardik | 27 |  Bhopal | 8500.00 |
| 6 |  Komal | 22 |  MP | 4500.00 |
+----+----------+-----+-----------+----------+  
  
  
  
First, 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. Using the Customers table:

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:5Customer name:hardikcustomer address:bhopalcustomer Salary:9000PL/sql procedure successfully completed.

Second, based on the cursor record
The following example illustrates the concept of cursor-based logging. Using the Customers table:

DECLARE CURSOR customer_cur isSELECT 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 results:1Ramesh2Khilan3Kaushik4Chaitali5Hardik6KOMALPL/sql procedure successfully completed.

  
third, 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, account, book ID.
the record type definition format is:
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;

here is a statement of how the book is recorded:declaretype books is record (title varchar, author varchar (), subject V Archar (+), book_id number); Book1 books;book2 Books;

Iv. 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:

DECLARE type books  isRecord (title varchar ( -), author varchar ( -), subject varchar ( -), book_id number);   Book1 Books; Book2 Books; BEGIN--Book1Specification Book1.title:='C Programming'; Book1.author:='Nuha Ali'; Book1.subject:='C Programming Tutorial'; BOOK1.BOOK_ID:=6495407;
--Book2Specification Book2.title:='Telecom Billing'; Book2.author:='Zara Ali'; Book2.subject:='Telecom Billing Tutorial'; BOOK2.BOOK_ID:=6495700;
--Print Book1Record 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 Book2Record 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: book1title:c Programmingbook1Author:nuha Alibook1SUBJECT:C Programming Tutorialbook1BOOK_ID:6495407 Book2title:telecom Billingbook2Author:zara Alibook2subject:telecom Billing Tutorialbook2BOOK_ID:6495700PL/sql procedure successfully completed.

Five, the record as the sub-program parameters
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:

DECLARE Type Books isRecord (title varchar ( -), author varchar ( -), subject varchar ( -), book_id number);   Book1 Books; BOOK2 Books; PROCEDURE Printbook (Book Books) Isbegin 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--Book1Specification Book1.title:='C Programming'; Book1.author:='Nuha Ali'; Book1.subject:='C Programming Tutorial'; BOOK1.BOOK_ID:=6495407;
--Book2Specification 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 results: Book Title:c Programmingbook Author:nuha alibook subject:c Programming Tutorialbook BOOK_ID:6495407Book title:telecom billingbook Author:zara alibook subject:telecom billing tutorialbook book_id:6495700PL/sql procedure successfully completed.

(10) PL/SQL Records