Table Type in Oracle Stored Procedure Example

The following is an example of Oracle stored procedure, in which table type collection is used to process the data.

Process Data Using Table Type in Oracle Stored Procedure Example

Create the following tables and insert the data to test in your schema.

CREATE TABLE EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
/
CREATE TABLE EMP_BONUS
(
EMPNO NUMBER(4),
BONUS_AMOUNT NUMBER
)
/
SET DEFINE OFF;
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)
Values
(7369, 'SMITH', 'CLERK', 7902, 
TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, 20);
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7499, 'ALLEN', 'SALESMAN', 7698, 
TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 300, 30);
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7521, 'WARD', 'SALESMAN', 7698, 
TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 500, 30);
COMMIT;

Create Stored Procedure

CREATE OR REPLACE PROCEDURE process_bonus
IS
CURSOR c_emp
IS
SELECT EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM emp;

/* declare table type in the procedure declare section as below */
TYPE t_emp_table IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;

t_emp t_emp_table;
BEGIN
OPEN c_emp;

/* fetch cursor data into table type using bulk collect */
FETCH c_emp
BULK COLLECT INTO t_emp;

CLOSE c_emp;

FOR i IN t_emp.FIRST .. t_emp.LAST
LOOP
/* insert 5% bonus of employee's salary into emp_bonus table */
INSERT INTO emp_bonus (empno, bonus_amount)
VALUES (t_emp (i).empno, t_emp (i).sal * 5 / 100);
END LOOP;

COMMIT;
END;

Test Using PL/SQL Block

BEGIN
process_bonus;
END;

Check The Bonus Table for Processed Data

SELECT * FROM EMP_BONUS;

Output:

EMPNO

BONUS_AMOUNT

7369

40

7499

80

7521

62.5

See also:

Vinish Kapoor
Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.