Explicitly declares a table or view definition in a program. For
tables named in a DECLARE TABLE statement, SQL does not check the
schema to compare the definition with the explicit declaration.
An explicit table declaration is useful to:
o Document the definition in the source code of the program
o Allow references to tables that do not exist when SQL
precompiles the program, including:
- Tables created in other modules of the program
- Tables created dynamically
o Improve precompiler performance because SQL does not need to
attach to the schema to retrieve the table definition
o Make it easier to check that the declaration correctly
corresponds to a host structure the program uses to hold
values from or for the table
o Declare only a subset of columns contained in the schema
definition of the table if the program needs to use only some
of the columns
1 – Environment
You can use the DECLARE TABLE statement:
o Embedded in host language programs to be precompiled
o In a context file
o As part of the DECLARE section in an SQL module
2 – Format
DECLARE --+-> <table-name> -+-> TABLE --+
+-> <view-name> --+ |
+-------------------------------------+
+-> ( -++-> declare-col-definition -++-> ) -->
|+-> table-constraint -------+|
+---------- , <---------------+
declare-col-definition =
--> <column-name> --> data-type -++------------------------++->
|+-> col-constraint ------+|
|+-> sql-and-dtr-clause --+|
+----------- <-------------+
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> --+
col-constraint=
----+--------------------------------+-+
+> CONSTRAINT <constraint-name> -+ |
+---------------<--------------------+
+-> PRIMARY KEY -----------------+
+-> UNIQUE ----------------------+
+-> NOT NULL --------------------+
+-> NULL ------------------------+
+-> CHECK (predicate) -----------+
+-> references-clause -----------+
+--------------->----------------+
+---------------<----------------+
+----+----------------------------+-->
+--> constraint-attributes --+
constraint-attributes =
-+-> DEFERRABLE -------------+------------------------------+-+->
| +-> INITIALLY +-> IMMEDIATE --++ |
| +-> DEFERRED ---+ |
+-> NOT DEFERRABLE ---------+-------------------------+------+
| +-> INITIALLY IMMEDIATE --+ |
+-> INITIALLY IMMEDIATE ----+-------------------+------------+
| +-> DEFERRABLE -----+ |
| +-> NOT DEFERRABLE -+ |
+-> INITIALLY DEFERRED -----+-------------------+------------+
+-> DEFERRABLE -----+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +-------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> -------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> -+
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS literal ----+
+-> DATATRIEVE -+
table-constraint =
---+-----------------------------------+----+
+-> CONSTRAINT <constraint-name> ---+ |
+------------------------------------------+
+--> table-constraint-clause --------------+
+------------------------------------------+
+---+---------------------------+------------->
+-> constraint-attributes --+
table-constraint-clause =
-+----------------------------------------------+-->
+-> PRIMARY KEY -> ( -+> <column-name> +-> ) --+
| +------- , <-----+ |
+-> UNIQUE -> ( -+> <column-name> +-> ) -------+
| +------- , <-----+ |
+-> CHECK (predicate) -------------------------+
+-> FOREIGN KEY -> ( -+> <column-name> +-> ) + |
+------- , <-----+ | |
+--------------------<---------------------+ |
+-> references-clause ---------->------------+
3 – Arguments
3.1 – character-set-name
A valid character set name. See the Oracle Rdb SQL Reference
Manual for more information on character sets.
3.2 – col-constraint
A column constraint. See the CREATE TABLE statement for more
information about column constraints.
3.3 – column-name
The name of the column you want to define.
3.4 – data-type
The data type of the column you want to define. See the Data_
Types HELP topic for more information on data types.
3.5 – date-time-data-types
Data types for dates, times, and intervals. See the Data_Types
HELP topic for more information on date-time data types.
3.6 – declare-col-definition
The definition for a column in the table. The column definition
must correspond to the table definition in the schema.
See the CREATE TABLE statement for more information about column
definitions.
However, you cannot refer to domain names in a DECLARE TABLE
statement. For tables whose definitions refer to domain names,
you must substitute the data type and size of the domain for the
domain name.
3.7 – frac
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
3.8 – interval-qualifier
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
3.9 – prec
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
3.10 – references-clause
See the CREATE TABLE statement for more information.
3.11 – seconds-prec
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
3.12 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE
HELP topic for more information about formatting clauses.
3.13 – table-name
The name of the table definition you want to declare.
3.14 – view-name
The name of the view definition you want to declare.
3.15 – table-constraint
A constraint definition that applies to the whole table. See
the CREATE TABLE statement for more information about specifying
table constraints.
4 – Examples
Example 1: Declaring the table EMPLOYEES in a COBOL program
EXEC SQL
DECLARE EMPLOYEES TABLE
(EMPLOYEE_ID CHAR (5)
CONSTRAINT EMP_EMPLOYEE_ID_NOT_NULL
NOT NULL,
LAST_NAME CHAR (14),
FIRST_NAME CHAR (10),
MIDDLE_INITIAL CHAR (1),
ADDRESS_DATA_1 CHAR (25),
ADDRESS_DATA_2 CHAR (25),
CITY CHAR (20),
STATE CHAR (2),
POSTAL_CODE CHAR (5),
SEX CHAR (1),
CONSTRAINT EMP_SEX_VALUES
CHECK (
SEX IN ('M', 'F') OR SEX IS NULL
),
BIRTHDAY DATE ,
STATUS_CODE CHAR (1)
CONSTRAINT EMP_STATUS_CODE_VALUES
CHECK (
STATUS_CODE IN ('0', '1', '2')
OR STATUS_CODE IS NULL
)
)
END_EXEC