Oracle PL/SQL Records

Oracle PL/SQL Records Training in ChennaiOracle Oracle PL/SQL Records Training in Chennai from the Best Oracle PLSQL Training Institute in Chennai. Learn Oracle PL SQL from the Best Oracle PLSQL Training center in Chennai with the most experienced trainer in the field. Greens Technology Adyar provides Oracle SQL, PLSQL, Performance Tuning training in Chennai to professionals and corporates on advanced SQL, PL SQL with XML, Oracle Database 12c Analytic SQL for Data Warehousing and Performance Tuning.

Working with Oracle PL/SQL Records

A PL/SQL 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 like, 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 cursor-based records.

The following example would illustrate 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 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 would illustrate 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 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 different record structures. 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;

Here is the way you would declare the Book record:

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 the example to explain 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 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 in very similar way as you pass any other variable. You would access the record fields in the similar way as you have 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 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.

The Oracle PL/SQL language was designed to be a portable, high-performance transaction processing language that is tightly integrated with the SQL language. It is rare, indeed, to find a PL/SQL program that does not either read from or make changes to tables in a database. Tables are made up of rows of data, each consisting of one or more columns, so it stands to reason that Oracle Database would make it as easy as possible to work with those rows of data inside a PL/SQL program. And it does precisely that through its implementation of the record.

A record is a composite datatype, which means that it can hold more than one piece of information, as compared to a scalar datatype, such as a number or string. It’s rare, in fact, that the data with which you are working is just a single value, so records and other composite datatypes are likely to figure prominently in your PL/SQL programs.

This article explores how you declare records, populate them with rows from a table, and even insert or change an entire row in a table by using a record. It also takes a look at user-defined record types, which enable you to work with records that are not necessarily related to a relational table.

Declare a Record with %ROWTYPE

PL/SQL makes it very easy to declare records that have the same structure as a table, a view, or the result set of a cursor by offering the %ROWTYPE attribute.

Suppose I have an employees table in an application that looks like this:

SQL> DESCRIBE omag_employees
 Name         Null?      Type
 ———————————  —————————— —————————————————
 EMPLOYEE_ID  NOT NULL   NUMBER(38)
 LAST_NAME               VARCHAR2(100)
 SALARY                  NUMBER

 

Each row in the table consists of three columns, and each column has its own datatype. The following query retrieves all the columns in all the rows in the table:

 

SELECT employee_id, last_name, salary
  FROM omag_employees

 

I want to write a block of code that retrieves a single row of data from omag_employees for an employee ID and then work with the column values in that row. I could declare a variable for each column and then fetch into those variables, as follows:

 

CREATE PROCEDURE process_employee (
  employee_id_in IN 
    omag_employees.employee_id%TYPE)
IS
  l_employee_id   
     omag_employees.employee_id%TYPE;
  l_last_name     
     omag_employees.last_name%TYPE;
  l_salary        
     omag_employees.salary%TYPE;
BEGIN
  SELECT employee_id,
         last_name,
         salary
    INTO l_employee_id,
         l_last_name,
         l_salary
    FROM omag_employees
  WHERE employee_id = employee_id_in;
END;

 

(Note that I use suffixes in my parameters to indicate their mode. Here _in indicates an IN parameter.)

That is, however, an awful lot of code to write, read, and maintain. A much better approach is to fetch that row of data into a record, and the best way to declare that record is as follows:

 

CREATE PROCEDURE process_employee (
  employee_id_in IN 
     omag_employees.employee_id%TYPE)
IS
  l_employee   omag_employees%ROWTYPE;
BEGIN
  SELECT employee_id,
         last_name,
         salary
    INTO l_employee
    FROM omag_employees
  WHERE employee_id = employee_id_in;
END;

 

When this procedure is compiled, PL/SQL looks up the structure of the omag_employees table and defines a record that has a field for each column in the table, with the same name and datatype. By using %ROWTYPE to declare the record, I also tell Oracle Database that this procedure depends on the omag_employees table. If the database administrator changes the maximum length of the last_name column to 200, for instance, this procedure’s status will be changed to INVALID. When the procedure is recompiled, the compiler will update the definition of the record in this procedure to match the table’s new structure.

I can even shorten things further and write

 

CREATE PROCEDURE process_employee (
  employee_id_in IN 
     omag_employees.employee_id%TYPE)
IS
  l_employee   omag_employees%ROWTYPE;
BEGIN
  SELECT *
    INTO l_employee
    FROM omag_employees
   WHERE employee_id = employee_id_in;
END;

 

The SELECT * syntax tells Oracle Database to fetch all the columns in the table.

I can also use %ROWTYPE to declare a record that has the same structure as a SELECT statement in a cursor. This is especially helpful for fetching either a subset of columns from a table or columns from multiple tables. Here’s an example:

 

DECLARE
   CURSOR no_ids_cur
   IS
      SELECT last_name, salary
        FROM omag_employees;

   l_employee   no_ids_cur%ROWTYPE;

 

(Note that I usually add a “_cur” suffix to the names of my explicitly declared cursors.)

Whenever you are fetching data from a cursor into PL/SQL variables, you should declare a record based on that cursor with %ROWTYPE and fetch into that record. This way, when and if the SELECT list of the cursor changes, the number and type of fields in the record will change accordingly and everything will stay in sync.

Working with Record Variables

Once you have declared a record in your block, you can both read and change the record’s value. You can do this at the record level or by referencing individual fields of that record, with the same dot notation used in SQL to refer to the column of a table.

So if I declare a record as follows,

 

DECLARE
   l_employee omag_employees%ROWTYPE;

 

I will be able to display the value of the last_name field of l_employee in the executable section of the block as follows:

 

DBMS_OUTPUT.put_line (
   l_employee.last_name);

 

I can change the value of a field, using an assignment operator:

 

l_employee.last_name := 'Picasso';

 

I can also perform the following record-level operations: 

  1. Set a record to NULL. This simple assignment will set the values of all fields to NULL. 

     

    l_employee := NULL;
    
  2.  

     

  3. Assign one record to another. 

     

    DECLARE
       l_employee1 omag_employees%ROWTYPE;
       l_employee2 omag_employees%ROWTYPE;
    BEGIN
       l_employee1 := l_employee2;
    END;
    

 

Populating Records with Data

Most of the time when you work with records, you will be assigning a row from a table to a record. You can also, however, assign values directly to individual fields or even to the record as a whole by using the PL/SQL assignment operator (:=). Let’s look at examples of the ways to populate a record.

 

  1. Declare a record with the same structures as those of the omag_employees table and then fill the record with the contents of one row from that table: 

     

    DECLARE
      l_employee  omag_employees%ROWTYPE;
    BEGIN
      SELECT *
        INTO l_employee
        FROM omag_employees
       WHERE employee_id = 100;
    END;
    
  2. Declare a cursor that fetches the last name and salary of all employees. Then use %ROWTYPE to declare a record that contains two fields: l_employee.last_name and l_employee.salary. Finally, open the cursor, fetch one row into the record, and close the cursor.

      

    DECLARE
      CURSOR no_ids_cur
      IS
         SELECT last_name, salary
           FROM omag_employees;
    
      l_employee   no_ids_cur%ROWTYPE;
    BEGIN
      OPEN no_ids_cur;
      FETCH no_ids_cur INTO l_employee;
      CLOSE no_ids_cur;
    END;
    /
    
  3. Populate a record by using native dynamic SQL. (Note: the SELECT statement is not dynamic; this is just to show that it is possible to populate a record with an EXECUTE IMMEDIATE . . . INTO statement). 

     

    DECLARE
      l_employee  omag_employees%ROWTYPE;
    BEGIN
      EXECUTE IMMEDIATE
         'SELECT * FROM omag_employees'
         INTO l_employee;
    END;
    
  4. Populate the fields of a record by using assignments. 

     

    DECLARE
      l_employee  omag_employees%ROWTYPE;
    BEGIN
      l_employee.last_name := 'Renoir';
      l_employee.salary := 1500;
    END;
    

     

    Note that even though I define the record based on the table, I do not have to set the values of the record’s fields from the table. I might, for example, want to insert a new row into the employees table by using the record (see “Inserting and Updating with Records” for details).

  5. Assign one record to another. Oracle Database supports record-level assignments, even the assignment of NULL to a record. 

     

    DECLARE
      l_old_employee  omag_employees%ROWTYPE;
      l_new_employee  omag_employees%ROWTYPE;
    BEGIN
      l_new_employee := l_old_employee;
      l_old_employee := NULL;
    END;
    

 

Cursor FOR Loops and Implicitly Declared Records

Suppose I want to write a program to display the last names of all employees. An elegant and simple way to do this in PL/SQL is to take advantage of the cursor FOR loop (which I discussed in part 2 of this PL/SQL 101 series). The cursor FOR loop is a variation on the numeric FOR loop, which looks like this:

 

FOR index IN low_value .. high_value
LOOP
   loop_body_statements
END LOOP;

 

The index is implicitly declared by Oracle Database as an integer and can be referenced only inside the body of this loop.

A cursor FOR loop has a similar structure but replaces a numeric range with a query:

 

FOR index IN ( SELECT_statement )
LOOP
   loop_body_statements
END LOOP;

 

Oracle Database also implicitly declares this loop index as well, but in the case of a cursor FOR loop, it declares the index as a record by using %ROWTYPE against the query in the loop header.

The following block uses a cursor FOR loop to fetch only the last name of each employee, deposit that name into a record, and then display the value of the last_name field of that record:

 

BEGIN
   FOR employee_rec
      IN (SELECT last_name
            FROM omag_employees
           ORDER BY last_name)
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;
/

 

Passing Records as Parameters

You can define parameters based on record types, and you can therefore pass records as arguments to subprograms. Suppose I need to write a procedure that displays an employee. I could implement it as follows:

 

CREATE PROCEDURE show_employee (
  employee_id_in   IN 
    omag_employees.employee_id%TYPE,
  last_name_in     IN 
    omag_employees.last_name%TYPE,
  salary_in        IN 
    omag_employees.salary%TYPE)
IS
BEGIN
  DBMS_OUTPUT.put_line (
        employee_id_in
     || '-'
     || last_name_in
     || '-'
     || salary_in);
END;

 

But I can also avoid having to declare each of those individual parameters (imagine a 100-column table!) by passing a record:

 

CREATE PROCEDURE show_employee (
   employee_in IN 
      omag_employees%ROWTYPE)
IS
BEGIN
   DBMS_OUTPUT.put_line (
         employee_in.employee_id
      || '-'
      || employee_in.last_name
      || '-'
      || employee_in.salary);
END;
/

 

Of course, as new columns are added to the table, their contents will not automatically be displayed by this procedure. So when you use %ROWTYPE to pass arguments to subprograms, make sure to review the subprogram logic after any change to the table.

Inserting and Updating with Records

As you have seen, PL/SQL makes it very easy to populate a record from a row in a table. But what if you want to change the contents of a row in a table by using a record? PL/SQL offers special syntax in both the INSERT and UPDATE statements so that you can easily use records to perform those data manipulation language (DML) operations as well.

The most common form of an INSERT statement is

 

INSERT INTO table_name (column_list) 
   VALUES (expression_list)

 

where column_list is the list of columns that will be populated on insert and expression_list is the list of expressions that will be assigned to their respective columns.

If I want to provide a value for each column in a table that has, say, 500 columns, writing and managing that code can become quite tedious. Inserting with a record comes in very handy in such a scenario.

Code Listing 1: Insert of a single row with each column specified

 

DECLARE
   l_employee_id   omag_employees.employee_id%TYPE
      := 500;
   l_last_name     omag_employees.last_name%TYPE
      := 'Mondrian';
   l_salary        omag_employees.salary%TYPE
      := 2000;
BEGIN
   INSERT
     INTO omag_employees (employee_id,
                          last_name,
                          salary)
   VALUES (
             l_employee_id,
             l_last_name,
             l_salary);
END; 

 

To perform a record-level insert, simply leave off the parentheses around the record in the VALUES clause. Listing 1 demonstrates an insert of a single row into the omag_employees table that specifies each column individually. The following demonstrates the same insert, using a record:

 

DECLARE
   l_employee   omag_employees%ROWTYPE;
BEGIN
   l_employee.employee_id := 500;
   l_employee.last_name := ‘Mondrian’;
   l_employee.salary := 2000;

   INSERT
     INTO omag_employees 
   VALUES l_employee;
END;
/

 

So if you ever find yourself typing what feels like an endless list of variables in the VALUES clause of your INSERT statement, try using a record instead.

For updates, use SET ROW to update all the columns in a row from the record:

 

DECLARE
  l_employee  omag_employees%ROWTYPE;
BEGIN
  l_employee.employee_id := 500;
  l_employee.last_name := 'Mondrian';
  l_employee.salary := 2000;

  UPDATE omag_employees
     SET ROW = l_employee
   WHERE employee_id = 100;
END;

 

Remember: this UPDATE sets the value of every column in the table, including your primary key, so you should use the SET ROW syntax with great care.

User-Defined Record Types

So far you’ve seen how to declare a record variable based on a table or a cursor by using the %ROWTYPE attribute. You can also declare your own, user-defined record types by using the TYPE. . . RECORD statement.

User-defined record types come in handy when you find yourself declaring “sets” of individual variables, such as

 

DECLARE
   l_name1           VARCHAR2 (100);
   l_total_sales1    NUMBER;
   l_deliver_pref1   VARCHAR2 (10);
   --
   l_name2           VARCHAR2 (100);
   l_total_sales2    NUMBER;
   l_deliver_pref2   VARCHAR2 (10);

 

Instead, why not create your own record type and then declare two records:

 

DECLARE
   TYPE customer_info_rt IS RECORD
   (
      name           VARCHAR2 (100),
      total_sales    NUMBER,
      deliver_pref   VARCHAR2 (10)
   );

   l_customer1   customer_info_rt;
   l_customer2   customer_info_rt;

 

(Note that when I declare types, I use a root “t” suffix and then add the “type of type.” Here I added “_rt” for record type.)

With this approach, you do more than avoid writing repetitive statements. You also document that those three pieces of information are all related to a customer. And once you’ve “moved up” to using a record, you can pass that record as an argument or perform record-level operations, further reducing the volume of code needed to implement your requirements.

Greens Technology Trainers:

Worked earlier in Oracle Corporation, IBM, Google, Verizon, CSC, Satyam etc.

Oracle Training Reviews

Greens Technology Reviews given by our students already completed the training with us. Please give your feedback as well if you are a student.

for More Reviews please continue to, Oracle Reviews (Page 2) >>