Local Table Declaration

table_declaration ::= DECLARE TABLE name (element_description {, element_description}... )
element_description ::= column_description | constraint_description
constraint_description ::= [CONSTRAINT constraint_name] { index_description | internal_integrity_constraint	| referential integrity constraint }

This statement declares a local temporary table inside a begin ... end block that can be viewed only by a client inside the block. The table is created when entering the block and destroyed when leaving the block. Existence of such a table is set by the block boundaries, not with the transaction. Writing data to a local table does not affect data in the database. The table definition is not stored in the system table of tables.

The SQL syntax of the element_description is similar to the CREATE TABLE statement.

You must keep in mind the time of integrity constraint check when declaring a local table. If the check is postponed until the transaction ends (default state, unless changed by setting the compatibility attribute SQLOPT_CONSTRS_DEFERRED (16384)), you can insert and update records in the block (e.g. procedure) that could break this conditions. The check is never done since the table is destroyed before the transaction is complete. You can force the check by either setting the INITIALLY INTERMEDIATE clause (not applicable to number columns) or by a call to the explicit COMMIT action after each block statement. The unique index check is always done immediately (cannot be postponed).

Indexes may be declared in a local table only if the unique index checks the uniqueness of inserted data. Indexes are not taken into account when optimizing queries on a temporary table.

A local table can be used anywhere a table name is expected, except for statements modifying table structure (ALTER TABLE, CREATE INDEX, DROP INDEX) and the DROP TABLE statement.

Usage Example

A local table called Test:

DECLARE TABLE Test (number INTEGER PRIMARY KEY,
  name CHAR(12) IS NOT NULL, 
  date DATE,
  dom_sl DOMAIN_TIME,
  CONSTRAINT ch1_date CHECK (date>CURRENT_DATE) INITIALLY IMMEDIATE, 
  CONSTRAINT ch2_NotNull CHECK (number IS NOT NULL) INITIALLY IMMEDIATE);

INSERT examples:

// correct (NOT NULL check for name is done at the end of the transaction)
INSERT INTO Test (number,name,date) VALUES (0,NULL,CURRENT_DATE+1);
// incorrect, raises an exception (integrity constraint ch1_date is done immediately)
INSERT INTO Test (number,name,date) VALUES (1,'Tony',CURRENT_DATE);  
// incorrect, raises an exception (uniqueness check of the primary key is done immediately)
INSERT INTO Test (number,name,date) VALUES (2,'Tony',CURRENT_DATE+1);  
INSERT INTO Test (number,name,date) VALUES (2,'Jane',CURRENT_DATE+2);

Cursor Declaration

Object Declaration in SQL