Table Type in Oracle PL SQL Example

PL SQL table types are actually collections, like an array. With the help of PL SQL collections, you can process bulk data efficiently. In this post, I will teach you very basic and most frequently used commands for PL SQL table type collections. Below is the table type in Oracle PL SQL example.

First you need to declare it in the declare section of PL SQL block. Here is the syntax with example:

Table Type in Oracle PL SQL Example

1. Declare PL SQL Table Types

Type any_table_type is table of emp%rowtype index by binary_integer;
emp_rec any_table_type;

The above commands will declare the table type any_table_type of emp table row type and then initialize with emp_rec. After declaration, you can manually assign the values to it. Below is the example:

2. Assign Values To PL SQL Table Types

set serveroutput on;
declare
Type any_table_type is table of emp%rowtype 
    index by binary_integer;
emp_rec any_table_type;
begin
emp_rec(1).ename := 'abc'; /* assigning values */
emp_rec(2).ename := 'xyz';
dbms_output.put_line(emp_rec(1).ename); /* accessing values */
dbms_output.put_line(emp_rec(2).ename);
end;

Because PL SQL tables are arrays like, so the values should be assign by giving array element number, as shown above. Now below is the example to how to Delete from PL SQL table type collections.

3. Deleting Elements From PL SQL Table Types

set serveroutput on;
declare
Type any_table_type is table of emp%rowtype index by binary_integer;
emp_rec any_table_type;
begin
emp_rec(1).ename := 'abc';
emp_rec(2).ename := 'xyz';
emp_rec.delete(1); /* will delete first element */
for i in emp_rec.first .. emp_rec.last loop /* loop through the array */
dbms_output.put_line(emp_rec(i).ename);
end loop;
end;

The above example will delete the first element and will print only the second. When you will execute the above block, the output will be:

xyz
PL/SQL procedure successfully completed.

To delete all the elements from PL SQL table type, below is the example:

emp_rec.delete;

4. Populating PL SQL Table Types Using Bulk Collect

Below is the table type in oracle PL SQL example to fetch the data from emp table and then it will populate the PL SQL table type using Bulk Collect and then it will update the emp table using FORALL. Here is the example:

set serveroutput on;

DECLARE
   CURSOR c
   IS
      SELECT * FROM emp;

   TYPE any_table_type IS TABLE OF emp%ROWTYPE
                             INDEX BY BINARY_INTEGER;

   emp_rec   any_table_type;
BEGIN
   OPEN c;

   FETCH c
   BULK COLLECT INTO emp_rec;

   CLOSE c;

   FORALL i IN emp_rec.FIRST .. emp_rec.LAST
      UPDATE emp
         SET comm = emp_rec (i).sal * 10 / 100
       WHERE empno = emp_rec (i).empno;

   COMMIT;
END;

You can also check my PL SQL Procedure Tool to generate the script for your development.