Friday, September 6, 2013

Handle Table Type in PLSQL

This post is all about how to populate and retrieve data from Table type in PLSQL.

Sample Object Type:
CREATE OR REPLACE
TYPE type_employee AS OBJECT
(
   EMPLOYEE_ID NUMBER (6),
   FIRST_NAME VARCHAR2 (20),
   LAST_NAME VARCHAR2 (25),
   SALARY NUMBER (8),
   EMAIL VARCHAR2 (25)
);

Sample Table Array Type:
CREATE OR REPLACE
TYPE employee_array AS TABLE OF type_employee;

PLSQL Anonymous Block:
DECLARE
   emp_array employee_array;

BEGIN

-- Use Object Type to Cast the data from table and use Bulk Collect to fetch large volumes of data at once
   SELECT type_employee(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, EMAIL)
   BULK COLLECT INTO emp_array
   FROM  EMPLOYEES;
   
-- Method 1 --retrieve data from Table Array
   FOR c1 IN (
      SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,EMAIL
      FROM TABLE( CAST( emp_array AS employee_array)) )
   LOOP
      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || c1.EMPLOYEE_ID);
      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || c1.FIRST_NAME || ' ' || c1.LAST_NAME);
      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| c1.SALARY);
      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| c1.Email);
   END LOOP;

-- Method 2 --retrieve data from Table Array
   FOR i IN 1..emp_array. COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Employee ID : ' || emp_array(i).EMPLOYEE_ID);
      DBMS_OUTPUT.PUT_LINE('Employee Name : ' || emp_array(i).FIRST_NAME || ' ' || emp_array(i).LAST_NAME);
      DBMS_OUTPUT.PUT_LINE('Employee Salary : '|| emp_array(i).SALARY);
      DBMS_OUTPUT.PUT_LINE('Employee Email : '|| emp_array(i).Email);   
   END LOOP;    

END;

No comments:

Post a Comment

Provide your thoughts !