PL/SQL Record Type And Table Type

In this blog, I will explain about PLSQL Record Type and Table Type With Example.

Please don't forget to leave a comment/suggestion.

PLSQL Record Type

A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row or some columns from a table row. The fields correspond to table columns.

Understanding PL/SQL Records

Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns. Your code keeps working even after columns are added to the table. If you want to represent a subset of columns in a table, or columns from different tables, you can define a view or declare a cursor to select the right columns and do any necessary joins, and then apply %ROWTYPE to the view or cursor.

Defining a Record

The record type can be defined as  below :- 

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 "record-name" is object of "type_name". we can not access record type directly with type_name.

Example:- 

DECLARE

TYPE books IS RECORD

(title  varchar(50),

    author  varchar(50),

    subject varchar(100),

    book_id   number);

book1 books;

book2 books;

Assigning Values To PLSQL record Type:-

To access any field of a record we use (.) dot operator. 

SYNTAX:-

<type_name>.<field_name>

Example:-

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;

PLSQL Table Type

Both PLSQL Table Types i.e. Index-by tables and Nested tables have same structure and their rows are accessed using subscript notation.

Index-By Tables :-

Index-By Table is also called Associate Arrary. Index-by table is a set of key value pair, where its subscript is called as key and value assigned to it called as value. Each key in Index-by table is unique and it can either be Number or String.

Syntax :-

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

Declaring and Using Index-By Table:-

DECLARE

     -- Associative array indexed by string:   

     TYPE book IS TABLE OF NUMBER  -- Associative array type

       INDEX BY VARCHAR2(64);   

     book_price  book;        -- Associative array variable

     i                VARCHAR2(64);

   BEGIN

     -- Add new elements to associative array:

     book_price('ABC')  := 200;

     book_price('DEF')     := 750;

     book_price('GHI') := 1000;

     -- Change value associated with key 'ABC':

     book_price('Smallville') := 2001;

     -- Print associative array:

     i := book_price.FIRST;

     WHILE i IS NOT NULL LOOP

       DBMS_Output.PUT_LINE

         ('Price of ' || i || ' is ' || TO_CHAR(book_price(i)));

       i := book_price.NEXT(i);

     END LOOP;

   END;

Nested Tables :-

A nested table is one-dimensional array with arbitrary number of elements whose size can increase dynamically. Nested table are like Index-By Tables, but there is no INDEX BY clause in Nested Tables.

Syntax:-

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array(Index-By table) cannot be stored in the database.

Difference between Nested Table and Array:-

Nested tables are like arrays where in both the case data is stored in one dimension with below differences:-

1. An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
2. An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

Declaring and Using Nested table:-

DECLARE

   TYPE books_table IS TABLE OF VARCHAR2(10);

   TYPE price IS TABLE OF INTEGER;

   names books_table;

   rate price;

   total integer;

BEGIN

   names := books_table('ABC', 'DEF', 'GHI', 'JKL', 'MNO');

   marks:= rate(98, 97, 78, 87, 92);

   total := names.count;

   dbms_output.put_line('Total '|| total || ' books');

   FOR i IN 1 .. total LOOP

      dbms_output.put_line('Book:'||names(i)||', Price:' || rate(i));

   end loop;

END;

Using PLSQL Record Type and Table Type Together

DECLARE

    TYPE lr_order_detail_rec IS RECORD 

        ( l_order_number oe_order_headers_all.order_number%TYPE,

          l_line_number  VARCHAR2(20),

          l_ordered_item oe_order_lines_all.ordered_item%Type,

          l_line_status  oe_order_lines_all.flow_status_code%TYPE,

          l_order_status oe_order_headers_all.flow_status_code%TYPE);

    

    TYPE lt_order_detail_tab IS TABLE OF lr_order_detail_rec

        INDEX BY BINARY_INTEGER;

    

    lt_order_detail_tab_o     lt_order_detail_tab;

    

    CURSOR lc_order_cur

    IS

        SELECT order_number, oe_flex_util.get_concat_value (oola.line_number,

                                          oola.shipment_number,

                                          oola.option_number,

                                          oola.component_number,

                                          oola.service_number)

                                 line_num,

                oola.ordered_item,

                oola.flow_status_code,

                ooha.flow_status_code

        FROM oe_order_headers_all ooha, oe_order_lines_all oola

        WHERE ooha.header_id = oola.header_id

            AND ooha.order_number = '52424905';

        

BEGIN

    OPEN lc_order_cur;

        LOOP

            FETCH lc_order_cur BULK COLLECT INTO lt_order_detail_tab_o;

            EXIT WHEN lc_order_cur%NOTFOUND;

        END LOOP;

    CLOSE lc_order_cur;

    

    dbms_output.put_line('lt_order_detail_tab_o.count:- ' || lt_order_detail_tab_o.count);

    dbms_output.put_line('Order Number || Line Number || ordered Item || Order Status || Line Status ' ); 

    

    FOR i IN lt_order_detail_tab_o.FIRST..lt_order_detail_tab_o.LAST

    LOOP

        dbms_output.put_line( lt_order_detail_tab_o(i).l_order_number|| ' '|| 

                                lt_order_detail_tab_o(i).l_line_number || ' ' || 

                                lt_order_detail_tab_o(i).l_ordered_item ||' '||  

                                lt_order_detail_tab_o(i).l_order_status ||' ' ||

                                lt_order_detail_tab_o(i).l_line_status);

    END LOOP;

        

END;

Kindly leave a comment/suggestion.