ASA SQL Reference
  SQL Statements

DECLARE LOCAL TEMPORARY TABLE statement

Description 

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 

ON COMMIT    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    A table created using this clause is not affected by either COMMIT or ROLLBACK. The clause is useful if procedures that use the temporary table are called repeatedly with no intervening COMMITs or ROLLBACKs.

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 may be useful if procedures that use the temporary table are called repeatedly with no intervening COMMITs or ROLLBACKs.

PCTFREE    Specifies the percentage of free space you want to reserve for each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation.

The value percent-free-space is an integer between 0 and 100.The former specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, 200 bytes are reserved in each page.

Usage 

The DECLARE LOCAL TEMPORARY TABLE statement declares a temporary table. For definitions of column-definition, column-constraint, and table-constraint, see CREATE TABLE statement.

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.

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

Permissions 

None.

Side effects 

None.

See also 

CREATE TABLE statement

Using compound statements

Standards and compatibility 

• SQL/92    Conforms to the SQL/92 standard.

• SQL/99    SQL/foundation feature outside of core SQL.

• Sybase    Adaptive Server Enterprise does not support DECLARE TEMPORARY TABLE.

Example 

The following example illustrates how to declare a temporary table in Embedded SQL:

EXEC SQL DECLARE LOCAL TEMPORARY TABLE MyTable (
  number INT
   );

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

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