Explicitly declares a local temporary table.
The metadata for a declared local temporary table is not stored
in the database and cannot be shared by other modules. These
tables are sometimes called scratch tables.
The data stored in the table cannot be shared between SQL
sessions or modules in a single session. Unlike persistent
base tables, the metadata and data do not persist beyond an SQL
session.
In addition to declared local temporary tables, there are two
other types of temporary tables:
o Global temporary tables
o Local temporary tables
See the CREATE TABLE statement for additional information on
global and local temporary tables.
1 – Environment
You can use the DECLARE LOCAL TEMPORARY TABLE statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
o In a stored module
2 – Format
DECLARE LOCAL TEMPORARY TABLE -+-----------------+> MODULE . -+
+-> alias-name . -+ |
+--------------------------------------------------------------+
+-> <table-name> --> dec-local-table-body -------------------+
+-------------------------------------------------------------+
+---+----+--> COMPRESSION IS -+-> ENABLED --+-------+-----+---->
| | +-> DISABLED -+ | |
| +--> ON COMMIT -+-> DELETE ----+--> ROWS --+ |
| +-> PRESERVE --+ |
+---------------------------<-------------------------+
dec_local_table_body
-+-> (dec_local_col_list) ---------------------------------+->
| |
+-> LIKE <other-table-name> --+--------------------------++
+--> (dec_local_col_list) -+
dec-local-col-list =
--+-> <column-name> --+
| +-----------------+
| +-+-> data-type ----++---------------------------+-+--+->
| | +-> <domain-name> ++-> DEFAULT default-value --+ | |
| +--> COMPUTED BY value-expr -----------------------+ |
+-----------------------, <-----------------------------+
data-type =
-+-> char-data-types -----------------------------------------+-->
+-> TINYINT --------------+-----+------------+---------------+
+-> SMALLINT -------------+ +-> ( <n> ) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> FLOAT ----------------+ |
+-> NUMBER -+----------------------------------+-------------+
| +-> ( -+-> <p> -+-+----------+-> ) + |
| +-> * ---+ +-> , <d> -+ |
+-> LIST OF BYTE VARYING --+------------+--+--------------+--+
| +-> ( <n> ) -+ +-> AS BINARY -+ |
| +-> AS TEXT ---+ |
+-> DECIMAL -++------------------------------+---------------+
+-> NUMERIC -++-> ( --> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> REAL ----------------------------------------------------+
+-> DOUBLE PRECISION ----------------------------------------+
+-> date-time-data-types ------------------------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
frac =
--+---------------------------+->
+--> ( <numeric-literal> ) -+
interval-qualifier =
--+-> YEAR ---> prec --+-------------+-----------------+->
| +-> TO MONTH -+ |
+-> MONTH --> prec ----------------------------------+
+-> DAY ----> prec --+-------------------------------+
| +-> TO -+-> HOUR ---------------+
| +-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> HOUR ---> prec --+-------------------------------+
| +-> TO -+-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> MINUTE -> prec --+-------------------------------+
| +-> TO SECOND ------> frac -----+
+-> SECOND -> seconds-prec --------------------------+
prec =
--+---------------------------+->
+--> ( <numeric-literal> ) -+
seconds-prec =
--+--------------------------------------+-->
+-> ( <numeric-literal-1> ---+ |
+-------------------------+ |
++--------------------------+-> ) --+
+> , <numeric-literal-2> --+
3 – Arguments
3.1 – dec-local-col-definition
The definition for a column in the table. SQL gives you two ways
to specify column definitions:
o By directly specifying a data type to associate with a column
name
o By naming a domain that indirectly specifies a data type to
associate with a column name
See the CREATE TABLE for more information about column
definitions. See the Data_Types HELP topic for more information
about data types.
3.2 – COMPRESSION_IS
Syntax options:
COMPRESSION IS ENABLED | COMPRESSION IS DISABLED
Specifies whether run-length compression is enabled or disabled
for rows inserted into the declared local temporary table.
In some cases, the data inserted into a local temporary table may
not compress and so incur only overhead in the row. This overhead
is used by Rdb to describe the sequence of uncompressible data.
Use COMPRESSION IS DISABLED to prevent Rdb from attempting the
compression of such data.
The default is COMPRESSION IS ENABLED.
3.3 – ON_COMMIT
Syntax options:
ON COMMIT PRESERVE ROWS | ON COMMIT DELETE ROWS
Specifies whether data is preserved or deleted after a COMMIT
statement for declared local temporary tables.
The default, if not specified, is ON COMMIT DELETE ROWS.
3.4 – table-name
The name of the table you want to declare. You can optionally
precede the table-name with an alias-name and a period (.). You
must, however, precede the table-name with the keyword MODULE and
a period (.), for example, MODULE.EMPL_PAYROLL.
4 – Examples
Example 1: Declaring and using a declared local temporary table
in interactive SQL
SQL> DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_INT
cont> (EMPLOYEE_ID ID_DOM,
cont> LAST_NAME CHAR(14),
cont> HOURS_WORKED INTEGER,
cont> HOURLY_SAL INTEGER(2),
cont> WEEKLY_PAY INTEGER(2))
cont> ON COMMIT PRESERVE ROWS;
SQL> --
SQL> INSERT INTO MODULE.PAYCHECK_DECL_INT
cont> (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY)
cont> SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED,
cont> P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL
cont> FROM EMPLOYEES E, PAYROLL P
cont> WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
cont> AND P.WEEK_DATE = DATE '1995-08-01';
100 rows inserted
SQL> SELECT * FROM MODULE.PAYCHECK_DECL_INT LIMIT TO 2 ROWS;
EMPLOYEE_ID LAST_NAME HOURS_WORKED HOURLY_SAL WEEKLY_PAY
00165 Smith 40 30.50 1220.00
00166 Dietrich 40 36.00 1440.00
2 rows selected
Example 2: Creating a stored module that contains the following:
o A declared local temporary table, MODULE.PAYCHECK_DECL_TAB
o A procedure, PAYCHECK_INS_DECL, that inserts weekly
salary records into the declared local temporary table,
MODULE.PAYCHECK_DECL_TAB
o A procedure, LOW_HOURS_DECL, that counts the number of
employees with less than 40 hours worked
The following example also demonstrates that you can access the
declared local temporary table only from within the module.
SQL> -- Create the module containing a declared temporary table.
SQL> --
SQL> CREATE MODULE PAYCHECK_DECL_MOD
cont> LANGUAGE SQL
cont> DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_TAB
cont> (EMPLOYEE_ID ID_DOM,
cont> LAST_NAME CHAR(14) ,
cont> HOURS_WORKED INTEGER, HOURLY_SAL INTEGER(2),
cont> WEEKLY_PAY INTEGER(2))
cont> ON COMMIT PRESERVE ROWS
cont> --
cont> -- Create the procedure to insert rows.
cont> --
cont> PROCEDURE PAYCHECK_INS_DECL;
cont> BEGIN
cont> INSERT INTO MODULE.PAYCHECK_DECL_TAB
cont> (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY)
cont> SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED,
cont> P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL
cont> FROM EMPLOYEES E, PAYROLL P
cont> WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
cont> AND P.WEEK_DATE = DATE '1995-08-01';
cont> END;
cont> --
cont> -- Create the procedure to count the low hours.
cont> --
cont> PROCEDURE LOW_HOURS_DECL (:cnt INTEGER);
cont> BEGIN
cont> SELECT COUNT(*) INTO :cnt FROM MODULE.PAYCHECK_DECL_TAB
cont> WHERE HOURS_WORKED < 40;
cont> END;
cont> END MODULE;
SQL> --
SQL> -- Call the procedure to insert the rows.
SQL> --
SQL> CALL PAYCHECK_INS_DECL();
SQL> --
SQL> -- Declare a variable and call the procedure to count records with
SQL> -- low hours.
SQL> --
SQL> DECLARE :low_hr_cnt integer;
SQL> CALL LOW_HOURS_DECL(:low_hr_cnt);
LOW_HR_CNT
2
SQL> --
SQL> -- Because the table is a declared local temporary table, you cannot
SQL> -- access it from outside the stored module that contains it.
SQL> --
SQL> SELECT * FROM MODULE.PAYCHECK_DECL_TAB;
%SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or
environment
Example 3: Disabling Compression for a Declard Local Temporary
Table
The following example shows a declared local temporary table
that will not benefit from compression. The clause COMPRESSION IS
DISABLED is used to reduce the CPU overhead for the table as well
as preventing a possible row size increase because of compression
notations.
SQL> declare local temporary table module.scratch0
cont> (averages double precision)
cont> compression is DISABLED
cont> on commit PRESERVE rows
cont> ;
SQL>
SQL> insert into module.scratch0
cont> select avg (char_length (a)) from module.scratch1;
1 row inserted
SQL>
SQL> select * from module.scratch0;
AVERAGES
2.100000000000000E+001