Temporary Tables

Temporary Tables are a great feature that lets user to store and process intermediate results by using the same selection, update, and join capabilities that user can use with typical SQL Server tables. The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates. Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The Basic syntax for creating a Temporary is as follow :
 CREATE GLOBAL TEMPORARY TABLE table_name (
     column_name1 datatype,
     column_name2 datatype,
     ..............
     column_nameN datatype) 
 ON COMMIT DELETE ROWS;
Where the ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction, or the end of the session.

For example lets create a temporary table goods with two columns :
 SQL> CREATE GLOBAL TEMPORARY TABLE goods (
          item varchar(20),
          quantity number(2)) 
      ON COMMIT DELETE ROWS;
We can put some data into the table
 SQL> INSERT INTO goods VALUES ('Books', 8);
 SQL> INSERT INTO goods VALUES ('Copy', 10);
 SQL> INSERT INTO goods VALUES ('Pencil', 12);
 SQL> INSERT INTO goods VALUES ('Pen', 7);
 SQL> SELECT * FROM goods;
  ITEM         QUANTITY
 ------------ ----------
  Books          8
  Copy          10
  Pencil        12
  Pen            7
Now to delete the temporary database we can use DROP TABLE statement.
 SQL> DROP TABLE goods;


Next Topic :



No comments:

Post a Comment