Table Type in Oracle PL SQL Example

PL/SQL table types—also known as collections—function similarly to arrays in other programming languages. Using PL/SQL collections, you can process large volumes of data efficiently and perform bulk operations with ease. In this article, I will introduce some of the most basic and commonly used commands for working with PL/SQL table-type collections. The following examples will help you understand how to declare, assign, delete, and populate table-type collections in Oracle PL/SQL.

Declaring PL/SQL Table Types

Before you can use a table-type collection, you must declare it in the DECLARE section of your PL/SQL block. Here is the syntax and an example:

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

In this example, a new PL/SQL table type called any_table_type is declared. This type is defined as a collection of rows from the emp table, indexed by integers. After defining the type, a variable named emp_rec is declared, which will hold the collection. Once declared, you can manually assign values to the collection elements.

Assigning Values to PL/SQL Table Types

After declaring your table-type collection, you can assign values to individual elements by referencing their index. Here is an example that demonstrates how to assign and access values in a PL/SQL table type:

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 behave like arrays, you must assign values using an array index (as shown above). This allows you to manage multiple records within a single collection variable.

Deleting Elements from PL/SQL Table Types

You can delete specific elements from a PL/SQL table-type collection using the DELETE method with the appropriate index. Below is an example that demonstrates how to remove an element and iterate through the remaining items:

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); -- Deletes the first element
    FOR i IN emp_rec.FIRST .. emp_rec.LAST LOOP -- Loop through the collection
        DBMS_OUTPUT.PUT_LINE(emp_rec(i).ename);
    END LOOP;
END;

In this example, the first element is deleted, and the loop prints only the second element. When you run this block, the output will be:

xyz
PL/SQL procedure successfully completed.

If you wish to delete all elements from the collection, you can use the following command:

emp_rec.DELETE;

This will remove all items from the collection, effectively clearing it.

Populating PL/SQL Table Types Using Bulk Collect

You can also populate a PL/SQL table-type collection using the BULK COLLECT statement to efficiently fetch data from a table. Once the collection is populated, you can perform bulk operations, such as updating records using the FORALL statement. Below is an example demonstrating this approach:

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;

In this example:

• A cursor is defined to select all records from the emp table.
• The data is fetched into the collection emp_rec using BULK COLLECT.
• The FORALL statement is then used to update the comm field for each employee, setting it to 10% of their salary.

Additional Tools

If you need to generate scripts for your development tasks, you can also use various PL/SQL procedure tools designed for this purpose. These tools can help automate the creation of PL/SQL code, making development faster and more efficient.

By understanding and using PL/SQL table-type collections, you can efficiently manage and process bulk data operations within Oracle databases. The techniques demonstrated above are fundamental for any PL/SQL developer working with collections.