In Oracle, global temporary tables known as session tables and we use global temporary tables to keep the data temporarily in it while processing. The below is an example.
Create a Global Temporary Table
Here we will create a global temporary table to hold department wise total salary from the EMP table. You can download the EMP table and the data for the testing purpose from the following link SCOTT Schema Tables. Also, in the below example, we are creating the table with ON COMMIT DELETE ROWS clause, to delete the rows whenever a Commit statement executed in the procedure. You can also use the ON COMMIT PRESERVE ROWS clause to preserve the rows into the table until the session is active.
CREATE GLOBAL TEMPORARY TABLE temp_dept ( deptno NUMBER (4), dname VARCHAR2 (50), sal NUMBER ) ON COMMIT DELETE ROWS;
Example to Use Global Temporary Table in Oracle Procedure
The following procedure will take the total salary from each department and will populate the temp_dept table. Then will pick the records from a temp_dept table and will update the EMP table commission column with 2% of total department salary.
CREATE OR REPLACE PROCEDURE prc_dept
IS
CURSOR c_emp
IS
SELECT e.deptno, d.dname, SUM (e.sal) tot_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY e.deptno, d.dname;
n_count NUMBER := 0;
BEGIN
FOR c IN c_emp
LOOP
/* Inserting records into temp table */
INSERT INTO temp_dept (deptno, dname, sal)
VALUES (c.deptno, c.dname, c.tot_sal);
END LOOP;
/* Now get the records from temp table and update the EMP table */
FOR c IN (SELECT deptno, dname, sal FROM temp_dept)
LOOP
/* Updating the EMP table commission column to set 2% of total department wise salary*/
UPDATE emp
SET comm = c.sal * 2 / 100
WHERE emp.deptno = c.deptno;
DBMS_OUTPUT.put_line( 'Commission amount '
|| (c.sal * 2 / 100)
|| ' updated for department '
|| c.dname);
END LOOP;
/* Save the EMP table changes and this will also remove the records from temp_dept table*/
COMMIT;
/* Checking temporary table records count for testing */
SELECT COUNT ( * ) INTO n_count FROM temp_dept;
DBMS_OUTPUT.put_Line ('Records in Temp table: ' || n_count);
END;Test
SET SERVEROUTPUT ON; BEGIN prc_dept; END; /
Output
Commission amount 175 updated for department ACCOUNTING Commission amount 217.5 updated for department RESEARCH Commission amount 188 updated for department SALES Records in Temp table: 0 PL/SQL procedure successfully completed.
