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;
SET SERVEROUTPUT ON; BEGIN prc_dept; END; /
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.