Here is the SQL command to create the table:
SQL> create global temporary table subtotal_hrs
2 (emp_id number,
3 proj_hrs number)
4 on commit delete rows;
Table created.
For the purposes of this example, you will create a permanent table that holds the total hours
by employee by project for a given day. Here is the SQL command for the permanent table:
SQL> create table total_hours (emp_id number, wk_dt date, tot_hrs number);
In the following scenario, you will use the global temporary table to keep the intermediate
results, and at the end of the transaction, store the totals in the TOTAL_HOURS table. Here is the
sequence of commands:
SQL> insert into subtotal_hrs values (101, 20);
1 row created.
SQL> insert into subtotal_hrs values (101, 10);
1 row created.
SQL> insert into subtotal_hrs values (120, 15);
1 row created.
SQL> select * from subtotal_hrs;
EMP_ID PROJ_HRS
---------- ----------
101 20
101 10
120 15
SQL> insert into total_hours
2 select emp_id, sysdate, sum(proj_hrs) from subtotal_hrs
3 group by emp_id;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from subtotal_hrs;
no rows selected
SQL> select * from total_hours;
Chapter 16: Managing Large Databases 555
EMP_ID WK_DT TOT_HRS
---------- --------- ----------
101 19-AUG-04 30
120 19-AUG-04 15
SQL>
Notice that after the commit, the rows are retained in TOTAL_HOURS but are not retained in
SUBTOTAL_HRS because we specified on commit delete rows when we created the table.
Pages:
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830