Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

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.

See also
Standards and compatibility
  • SQL/2003   SQL/foundation feature outside 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