DECLARE LOCAL TEMPORARY TABLE statement

Use this statement to declare a local temporary table.

Syntax

DECLARE LOCAL TEMPORARY TABLE table-name
( { column-definition [ column-constraint ... ] | table-constraint | pctfree }, ... )
[ ON COMMIT { DELETE | PRESERVE } ROWS
   | NOT TRANSACTIONAL ]

pctfree : PCTFREE percent-free-space

percent-free-space : integer

Parameters

For definitions of column-definition, column-constraint, table-constraint, and pctfree, see CREATE TABLE statement.

ON COMMIT clause By default, the rows of a temporary table are deleted on a COMMIT. You can use the ON COMMIT clause to preserve rows on a COMMIT.
NOT TRANSACTIONAL clause A table created using this clause is not affected by either COMMIT or ROLLBACK. The NOT TRANSACTIONAL clause provides performance improvements in some circumstances because operations on non-transactional temporary tables do not cause entries to be made in the rollback log. For example, NOT TRANSACTIONAL can be useful if procedures that use the temporary table are called repeatedly with no intervening COMMITs or ROLLBACKs.

Remarks

The DECLARE LOCAL TEMPORARY TABLE statement declares a temporary table.

The rows of a declared temporary table are deleted when the table is explicitly dropped or when the table goes out of scope. You can also explicitly delete rows using TRUNCATE or DELETE.

Declared local temporary tables within compound statements exist within the compound statement. (See Using compound statements). Otherwise, the declared local temporary table exists until the end of the connection.

If you want a procedure to create a local temporary table that persists after the procedure completes, use the CREATE LOCAL TEMPORARY TABLE statement instead. (See CREATE LOCAL TEMPORARY TABLE statement).

Permissions

None.

Side effects

None.

Standards and compatibility

SQL/2003 SQL/foundation feature outside of core SQL.

Example

The following example illustrates how to declare a temporary table in a stored procedure:

BEGIN
  DECLARE LOCAL TEMPORARY TABLE TempTab ( number INT );
  ...
END

Send feedback about this page via email or DocCommentXchange