1 – ALIAS
Specifies the name and the source of the database definitions to
be used for module compilation, and makes the named alias part of
the implicit environment of an application. You can name either
a file or a repository path name to be used for the database
definitions.
1.1 – Environment
You can use the DECLARE ALIAS 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
The alias that you declare must be different from any other alias
specified in the module.
1.2 – Format
DECLARE -+------------------+-+
+-> scope-options -+ |
+-----------------------------+
+-+------------+- ALIAS FOR COMPILETIME -+
+-> <alias> -+ |
+-------------------------<---------------+
+-+-> FILENAME --> 'attach-spec ' -+------+
+-> PATHNAME --> <path-name> ----+ |
+------------------------<----------------+
+-+------------------------------------+--+
+-> lit-or-def-user-authentication --+ |
+------------------------<----------------+
+-+----------------------------+-+
+-> RUNTIME runtime-options -+ |
+-------------------------<------+
+-+-+--------------------------------------------+-+->
| +-> database-options ------------------------+ |
| +-> attach-options --------------------------+ |
| +-> DEFAULT CHARACTER SET support-char-set --+ |
| +-> NATIONAL CHARACTER SET support-char-set -+ |
| +-> DISPLAY CHARACTER SET support-char-set --+ |
+----------------------- <-----------------------+
lit-or-def-user-authentication =
--> USER -+-> '<username>' -+--+--------------------------------+-->
+-> DEFAULT -----+ +--> USING -+-> '<password>' --+-+
+-> DEFAULT ------+
scope-options =
-+-> LOCAL --------------+-->
+-> GLOBAL -------------+
+-> EXTERNAL -----------+
attach-spec =
--+----------------+-> <file-spec> ----->
+-> <node-spec> -+
node-spec =
-+-> <nodename> -+-------------------+-+->
| +-> <access-string> + |
+------------------ :: <--------------+
access-string =
-+-> " <user-name> <password> " --+->
+-> " <VMS-proxy-user-name> " ---+
runtime-options =
-+-> FILENAME --+-> '<attach-spec>' +-+--->
| +-> <parameter> ---+ |
+-> PATHNAME --+-> <path-name> --+--+
| +-> <parameter> --+ |
+-> runtime-string ------------------+
runtime-string =
-+> ' -+> FILENAME <attach-spec> -+-+---------------------+> ' +->
| +> PATHNAME <pathname> ----+ +> literal-user-auth -+ |
+> parameter -------------------------------------------------+
database-options =
--+--> ELN ---------------------+-->
+--> NSDS --------------------+
+--> rdb-options -------------+
+--> VIDA --------------------+
+--> VIDA V1 -----------------+
+--> VIDA V2 -----------------+
+--> VIDA V2N ----------------+
+--> NOVIDA ------------------+
+--> DBIV1 -------------------+
+--> DBIV31 ------------------+
+--> DBIV70 ------------------+
rdb-options =
-+-> RDBVMS --+-->
+-> RDB030 --+
+-> RDB031 --+
+-> RDB040 --+
+-> RDB041 --+
+-> RDB042 --+
+-> RDB050 --+
+-> RDB051 --+
+-> RDB060 --+
+-> RDB061 --+
+-> RDB070 --+
+-> RDB071 --+
attach-options =
-+-> DBKEY -+-> SCOPE IS -+-> ATTACH -------+-----------------------+->
+-> ROWID -+ +-> TRANSACTION --+ |
+-> MULTISCHEMA IS -+-> ON --+-------------------------------------+
| +-> OFF -+ |
+-> PRESTARTED TRANSACTIONS ARE -+-> ON --+------------------------+
| +-> OFF -+ |
+-+-------+-> RESTRICTED ACCESS -----------------------------------+
+-> NO -+
1.3 – Arguments
1.3.1 – alias ALIAS
Specifies a name for the attach to the database. Specifying an
alias lets your program refer to more than one database.
You do not have to specify an alias in the DECLARE ALIAS
statement. The default alias in interactive SQL and in
precompiled programs is RDB$DBHANDLE. In the SQL module language,
the default is the alias specified in the module header. Using
the default alias (either by specifying it explicitly in the
DECLARE ALIAS statement or by omitting any alias) makes the
database part of the default environment. Specifying a default
database means that statements that refer to the default database
do not need to use an alias.
If a default alias was already declared and you specify the
default alias in the alias clause (or specify any alias that
was already declared), you receive an error when you precompile
the program or process it with the SQL module processor.
1.3.2 – database-options
By default, SQL uses only the database options used to compile
a program as valid options for that program. If you want to use
the program with other supported databases, you can override the
default options by specifying database options in the ATTACH or
DECLARE ALIAS statement.
For more information on database options, see the Database_
Options HELP topic.
1.3.3 – DBKEY_SCOPE
Syntax options:
DBKEY SCOPE IS ATTACH | DBKEY SCOPE IS TRANSACTION
Controls when the database key of a deleted row can be used again
by SQL.
o The default DBKEY SCOPE IS TRANSACTION means that SQL can
reuse the database key of a deleted table row (to refer to a
newly inserted row) as soon as the transaction that deleted
the original row completes with a COMMIT statement. (If the
user who deleted the original row enters a ROLLBACK statement,
then the database key for that row cannot be used again by
SQL.)
During the connection of the user who entered the DECLARE
ALIAS statement, the DBKEY SCOPE IS TRANSACTION clause
specifies that a database key is guaranteed to refer to the
same row only within a particular transaction.
o The DBKEY SCOPE IS ATTACH clause means that SQL cannot use the
database key again (to refer to a newly inserted row) until
all users who have attached with DBKEY SCOPE IS ATTACH have
detached from the database.
It only requires one process to attach with DBKEY SCOPE
IS ATTACH to force all database users to assume this
characteristic.
o Oracle Corporation recommends using DBKEY SCOPE IS TRANSACTION
to prevent excessive consumption of storage area space by
overhead space needed to support DBKEY SCOPE IS ATTACH, and to
prevent performance problems when storing new rows.
During the connection of the user who entered the DECLARE
ALIAS statement, the DBKEY SCOPE IS ATTACH clause specifies
that a database key is guaranteed to refer to the same row
until the user detaches from the database.
See the DBKEY HELP topic for more information.
1.3.4 – DEFAULT_CHARACTER_SET
Specifies the default character set of the alias at compile
time. For a list of allowable character set names, see Supported
Character Sets.
1.3.5 – DISPLAY CHARACTER SET support-char-set
Specifies the character set encoding and characteristics expected
of text strings returned from Oracle Rdb.
1.3.6 – FILENAME
A quoted string containing full or partial information needed to
access a database.
For an Oracle Rdb database, an attach specification contains the
file specification of the .rdb file.
When you use the FILENAME argument, any changes you make to
database definitions are entered only to the database system
file, not to the repository. If you specify FILENAME, your
application attaches to the database with that file name at run
time.
If you specify FILENAME:
- During compilation, your application attaches to the specified
database and reads metadata from the database definitions.
- At run time, your application attaches to the specified
database.
For information regarding node-spec and file-spec, see Oracle Rdb
Attach Specifications.
1.3.7 – FOR_COMPILETIME
Optional keyword provided for upward compatibility: DECLARE ALIAS
specifies the compile-time environment by default. Specifies that
the alias declared is the source of the database definition for
program compiling and execution.
1.3.8 – lit-or-def-user-authentication
Specifies the user name and password to enable access to
databases, particularly remote databases.
You can use this clause to explicitly provide user name and
password information in the DECLARE ALIAS statement.
1.3.9 – literal-user-auth
Specifies the user name and password for the specified database
to be accessed at run time. For more information about when to
use this clause, see the ATTACH statement.
1.3.10 – MULTISCHEMA_IS
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
The MULTISCHEMA IS ON clause enables multischema naming for the
duration of the database attach. The MULTISCHEMA IS OFF clause
disables multischema naming for the duration of the database
attach. Multischema naming is disabled by default.
1.3.11 – NATIONAL_CHARACTER_SET
Specifies the national character set of the alias at compile
time. For a list of allowable character set names, see Supported
Character Sets.
1.3.12 – PATHNAME
A full or relative repository path name that specifies the
source of the database definitions. When you use the PATHNAME
argument, any changes you make to database definitions are
entered in both the repository and the database system file.
Oracle Rdb recommends using the PATHNAME argument if you have the
repository on your system and you plan to use any data definition
statements.
If you specify PATHNAME:
o During compilation, your application attaches to the
repository database definition and reads metadata from the
dictionary definitions. SQL extracts the file name of the
Oracle Rdb database from the dictionary and saves it for use
at run time.
o At run time, your application attaches to the Oracle
Rdb database file name extracted from the dictionary at
compilation.
1.3.13 – PRESTARTED_TRANSACTIONS_ARE
Syntax options:
PRESTARTED TRANSACTIONS ARE ON | PRESTARTED TRANSACTIONS ARE OFF
Specifies whether Oracle Rdb enables or disables prestarted
transactions.
Use the PRESTARTED TRANSACTIONS ARE OFF clause only if your
application uses a server process that is attached to the
database for long periods of time and causes the snapshot file
to grow excessively. If you use the PRESTARTED TRANSACTIONS ARE
OFF clause, Oracle Rdb may require additional I/O as each SET
TRANSACTION statement must reserve a transaction sequence number
(TSN).
For most applications, Oracle Rdb recommends that you enable
prestarted transactions. The default is PRESTARTED TRANSACTIONS
ARE ON. If you use the PRESTARTED TRANSACTIONS ARE ON clause or
do not specify the PRESTARTED TRANSACTIONS clause, the COMMIT
or ROLLBACK statement for the previous read/write transaction
automatically reserves the TSN for the next transaction and
reduces I/O.
You can use ALTER DATABASE . . . PRESTARTED TRANSACTIONS clause
to establish a default setting for all applications using the
database. You can also define the RDMS$BIND_PRESTART_TXN logical
name to define the default setting for prestarted transactions
outside of an application. The PRESTARTED TRANSACTION clause
overrides this logical name and database setting. For more
information, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
1.3.14 – RESTRICTED_ACCESS
Syntax options:
RESTRICTED ACCESS | NO RESTRICTED ACCESS
Restricts access to the database. This allows you to access the
database but locks out all other users until you disconnect from
the database. Setting restricted access to the database requires
DBADM privileges.
The default is NO RESTRICTED ACCESS if not specified.
1.3.15 – ROWID_SCOPE
Syntax options:
ROWID SCOPE IS ATTACH | ROWID SCOPE IS TRANSACTION
The ROWID keyword is a synonym for the DBKEY keyword. See the
DBKEY_SCOPE argument for more information.
1.3.16 – RUNTIME
Specifies the source of the database definitions when the program
is run.
1.3.17 – runtime-string
A quoted string or parameter that specifies the file name or path
name of the database to be accessed at run time, and optionally,
the user name and password of the user accessing the database at
run time.
1.3.18 – scope-options
LOCAL | GLOBAL | EXTERNAL
Specifies the scope of the alias declaration in precompiled SQL
or SQL module language.
The scope-option declarations are:
o LOCAL declares an alias that is local to procedures in the
module in which it is declared, or local to dynamic statements
prepared in the module in which it is declared.
SQL attaches to a database with LOCAL scope only when you
execute a procedure in the same module without a session. The
alias of a database with LOCAL scope pertains only to that
module.
If the execution of a procedure in another module has attached
to the implicit environment and that procedure subsequently
calls another procedure that references a local database, SQL
attempts to attach to that local database. If no transaction
is active, SQL adds the local database to the implicit
environment for this module. If a transaction is active, SQL
returns an error message.
o GLOBAL declares an alias definition that is global to
procedures in the application. GLOBAL is the default.
o EXTERNAL declares an external reference to a global alias that
is defined in another module.
In single-image applications, the distinction between alias
definitions and alias references is often unimportant. It is
only necessary that each alias have at least one definition.
For this reason, Oracle Rdb has treated all alias references
(declared with the EXTERNAL keyword) the same as alias
definitions (declared with the GLOBAL keyword or the default.)
For compatibility with previous versions, this remains the
default.
However, applications that share aliases between multiple
images require a distinction between alias definitions and
alias references. All definitions of any aliases shared between
multiple OpenVMS images must be defined in one image, generally
the shareable image against which you link the other images.
Oracle Rdb recommends that you distinquish alias definitions
from alias references in any new source code. Use the GLOBAL (or
default) scope keyword for alias definitions and the EXTERNAL
keyword for alias references. If you share aliases between
multiple OpenVMS images, use the NOEXTERNAL_GLOBALS command line
qualifier to override the default and cause SQL to properly treat
alias references as references.
If you use the EXTERNAL_GLOBAL command line qualifier, SQL treats
aliases declared with the EXTERNAL keyword as GLOBAL. That is,
SQL initializes alias references as well as alias definitions.
If you use the NOEXTERNAL_GLOBAL command line qualifier, SQL
treats aliases declared with the EXTERNAL keyword as alias
references and does not initialize them. It initializes all other
aliases.
The EXTERNAL_GLOBAL qualifier is the default.
The [NO]INITIALIZE_HANDLES command line qualifiers also affect
the initialization of aliases, but they are recommended only for
use in versions prior to V7.0.
See the SQL Module Language and SQL Precompiler help topics for
more information about the command line qualifiers.
1.3.19 – USER
Syntax options:
username | DEFAULT
Specifies the operating system user name that the database system
uses for privilege checking.
You can specify a character string literal for the user name or
you can specify the DEFAULT keyword. The DEFAULT keyword allows
you to avoid placing the user name in a program's source code.
If you specify the DEFAULT keyword, you pass the user name to the
program by using a command line qualifier when you compile an SQL
module or precompiled program. You use the USERNAME qualifier.
1.3.20 – USING
Syntax options: USING 'password' | DEFAULT
Specifies the user's password for the user name specified in the
USER clause.
You can specify a character string literal for the PASSWORD or
you can specify the DEFAULT keyword. The DEFAULT keyword allows
you to avoid placing the user name in a program's source code.
If you specify the DEFAULT keyword, you pass the password to the
program by using a command line qualifier when you compile an SQL
module or precompiled program. You use the PASSWORD qualifier.
1.4 – Examples
Example 1: Specifying a database and an alias in embedded SQL
This statement declares the database defined by the file
specification personnel. The precompiler uses this definition
when compiling the program and SQL uses the file personnel when
the program runs. This name may be a logical name or the name
portion of the file personnel.rdb.
EXEC SQL
DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel
END-EXEC
Example 2: Specifying a database with restricted access
This statement is the same as Example 1, but specifies restricted
access to the database.
EXEC SQL
DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel
RESTRICTED ACCESS
END-EXEC
Example 3: Specifying the DECLARE ALIAS statement
This portion of an application program declares the databases
MIA1 and MIA_CHAR_SET. The precompiler uses the MIA1 database
when compiling the program and SQL uses the MIA_CHAR_SET database
when the program runs.
EXEC SQL
DECLARE ALIAS
COMPILETIME FILENAME MIA1
RUNTIME FILENAME MIA_CHAR_SET
DEFAULT CHARACTER SET DEC_KANJI
NATIONAL CHARACTER SET KANJI;
Example 4: Specifying the DEFAULT user authentication
The following example shows how to use the DEFAULT clause for
user name and password in an SQL module:
MODULE TEST_DECLARE
DIALECT SQL99
LANGUAGE C
PARAMETER COLONS
ALIAS RDB$DBHANDLE
-------------------------------------------------------
-----------------------declarations--------------------
DECLARE ALIAS COMPILETIME FILENAME mf_personnel
USER DEFAULT
USING DEFAULT
RUNTIME :run_time_spec
.
.
.
You pass the compile-time user name and password to the program
by using command line qualifiers. For example, to compile the
program use the following command line:
$ SQLMOD TESTDEC /USER=heleng /PASS= helenspasswd
At run time, the host language program can prompt the run-
time user to specify only the file specification or the file
specification and the user name and password at run time. The
host language program can build the run time string.
For example, if the host language program uses only the file
specification, the value of the variable passed to the program
can be the following:
FILENAME "mf_personnel"
If the host language program uses the file specification, user
name and password, the value of the variable passed to the
program can be the following:
FILENAME "mf_personnel 'USER heleng' USING 'mypassword' "
You must enclose the string in quotation marks; whether you
use single (') or double quotation marks (") depends upon the
programming language.
If you use the following DECLARE ALIAS statement, the host
language program can only prompt the run-time user to specify
the file name.
DECLARE ALIAS COMPILETIME FILENAME mf_personnel
USER DEFAULT
USING DEFAULT
RUNTIME FILENAME :foo
2 – CURSOR
Declares a cursor.
With cursors, the conditions that define the result table
are specified by the select expression in the DECLARE CURSOR
statement. SQL creates the result table when it executes an OPEN
statement. The result table for a cursor exists until a CLOSE,
COMMIT, or ROLLBACK statement executes, the program stops, or you
exit from interactive SQL. However, the result table can exist
across transactions if you define a holdable cursor. A holdable
cursor can remain open and retain its position when a new SQL
transaction begins.
Host language programs require cursors because programs must
perform operations one row or element at a time, and therefore
may execute statements more than once to process an entire result
table or list.
The scope of a cursor describes the portion of a module or
program where the cursor is valid. The extent of a cursor tells
how long it is valid. All cursors in SQL have the scope of the
entire module.
You can create three classes of cursors, depending on which
DECLARE CURSOR statement you use:
o The DECLARE CURSOR statement is executed immediately. A cursor
that you create with this statement, sometimes called a static
cursor, exists only within the scope and extent of its module.
Both the cursor name and SELECT statement are known to your
application at compile time.
o The dynamic DECLARE CURSOR statement is executed immediately.
The cursor name is known at compile time, and the SELECT
statement is determined at run time. You must supply a name
for the SELECT statement that is generated at run time. A
dynamic cursor exists within the scope of its module, but
its extent is the entire run of the program or image. For
information about the dynamic DECLARE CURSOR statement, see
the DECLARE Dynamic_CURSOR statement.
o The extended dynamic DECLARE CURSOR statement must be
precompiled or used as part of a procedure in an SQL module.
You must supply parameters for the cursor name and for the
identifier of a prepared SELECT statement that is generated at
run time. An extended dynamic cursor exists within the scope
and extent of the entire module. For information about the
extended dynamic DECLARE CURSOR statement, see the DECLARE
Extended_Dynamic_CURSOR statement.
Within each class, you can create two types of cursors:
o Table cursors are a method that SQL provides to access
individual rows of a result table. (A result table is a
temporary collection of columns and rows from one or more
tables or views.)
o List cursors are a method that SQL provides to access
individual elements in a list.
A list is an ordered collection of elements, or segments, of
the data type LIST OF BYTE VARYING. For more information about
the LIST OF BYTE VARYING data type, see the Data_Types HELP
topic.
List cursors enable users to scan through a very large data
structure from within a language that does not provide support
for objects of such size. Because lists exist as a set of
elements within a row of a table, a list cursor must refer
to a table cursor because the table cursor provides the row
context.
Cursors are further divided according to the modes of operations
that they can perform. Table cursors have four modes:
o Update cursors are the default table cursor. Rows are first
read and locked for SHARED READ or PROTECTED READ and then
later, when an UPDATE is performed, the rows are locked for
EXCLUSIVE access. If the table is reserved for EXCLUSIVE
access, the subsequent update lock is not required.
o Read-only cursors can be used to access row information from
a result table whenever you do not intend to update the
database. For example, you could use a read-only cursor to
fetch row and column information for display.
o Insert-only cursors position themselves on a row that has just
been inserted so that you can load lists into that row.
o Update-only cursors are used whenever you intend to modify
many rows in the result table. When the UPDATE ONLY option
is used, SQL uses a more aggressive lock mode that locks
the rows for EXCLUSIVE access when first read. This mode
avoids a lock promotion from SHARED READ or PROTECTED READ to
EXCLUSIVE access. It may, therefore, avoid deadlocks normally
encountered during the lock promotion.
List cursors have two modes:
o Read-only cursors are the default list cursor. They enable you
to read existing lists. By adding the SCROLL keyword to the
read-only list cursor clause, you enable Oracle Rdb to scroll
forward and backward through the list segments as needed.
o Insert-only cursors enable you to insert data into a list.
The following table lists the classes, types, and modes of
cursors that SQL provides.
Table 1-2 Classes, Types, and Modes of Cursors
Dynamic Extended Dynamic
DECLARE CURSOR DECLARE CURSOR DECLARE CURSOR
Table List Table List Table List
Insert- Insert- Insert- Insert- Insert- Insert-
only only only only only only
Read- Read- Read- Read- Read- Read-
only only only only only only
Update- Update- Update-
only only only
For example, you must declare an insert-only table cursor to
insert data into a table. If the table includes lists, use the
table cursor to position on the correct row, and declare an
insert-only list cursor to load the lists into that row. For
details about using cursors to load data into your database, see
the INSERT statement.
To process the rows of a result table formed by a DECLARE CURSOR
statement, you must use the OPEN statement to position the cursor
before the first row. Subsequent FETCH statements retrieve the
values of each row for display on the terminal or processing in a
program. (You must close the cursor before you attempt to reopen
it.)You can similarly process the elements of a list by using an
OPEN statement to position the cursor before the first element
in the list and repeating FETCH statements to retrieve successive
elements.
2.1 – Environment
You can use the DECLARE CURSOR statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of the DECLARE section in an SQL module
o In a context file
2.2 – Format
DECLARE <cursor-name> --------------------+
+-----------------------------------------+
+-+-+----------------+--> TABLE CURSOR ---+----------------+-+
| +-> INSERT ONLY -+ +-> with-clause -+ |
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +--------------------------------------------------------+
| +-> FOR -> select-expr --+----------------------+--+
| +-> for-update-clause -+ |
| +---------------------------------------------+
| +-+--------------------+----------------------------+-->
| +-> optimize-clause -+ |
++---------------++---------+-+-> LIST CURSOR FOR SELECT + |
+> READ ONLY ---++> SCROLL + | | |
+> INSERT ONLY --------------+ | |
+--------------------------------------------------------+ |
+> <column-name> WHERE CURRENT OF <table-cursor-name> -----+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
select-expr =
-+-+-> select-clause ------------+-+------+
| +-> ( select-expr ) -----------+ | |
| +-> TABLE table-ref ----------+ | |
+------ select-merge-clause <-------+ |
+------------------- <-------------------+
+-+--------------------+--+------------------+--+--------------------+->
+-> order-by-clause -+ +-> offset-clause -+ +-> limit-to-clause -+
for-update-clause =
--> FOR UPDATE -+-------------------------+->
+-+-> OF <column-name> -+-+
+-------- , <---------+
optimize-clause =
--+---------------------------------------------------------------+--->
+-> OPTIMIZE --+-+-> FOR -+-> FAST FIRST --------+----------+-+-+
| | +-> TOTAL TIME --------+ | |
| | +-> SEQUENTIAL ACCESS -+ | |
| +-> USING <outline-name> ------------------+ |
| +-> WITH -+-> DEFAULT --+-> SELECTIVITY -+ |
| | +-> SAMPLED --+ | |
| | +-> AGGRESSIVE + | |
| +-> AS <query-name> -----------------------+ |
+---------------- <----------------------------+
2.3 – Arguments
2.3.1 – cursor-name
Specifies the name of the cursor you want to declare. Use a name
that is unique among all the cursor names in the module. Use any
valid SQL name. See the User_Supplied_Names HELP topic. for more
information on user-supplied names.
You can use a parameter to specify the cursor name at run time
in an extended dynamic DECLARE CURSOR statement. See the DECLARE
Extended_Dynamic_CURSOR statement for more information on the
extended dynamic DECLARE CURSOR statement.
2.3.2 – FOR select expr
A select expression that defines which columns and rows of which
tables SQL includes in the cursor. See the Select_Expressions
HELP topic for more information on select expressions.
2.3.3 – FOR_UPDATE_OF
Specifies the columns in a cursor that you or your program might
later modify with an UPDATE statement. The column names in the
FOR UPDATE clause must belong to a table or view named in the
FROM clause.
You do not have to specify the FOR UPDATE clause of the DECLARE
CURSOR statement to later modify rows using the UPDATE statement:
o If you do specify a FOR UPDATE clause and later specify
columns in the UPDATE statement that are not in the FOR UPDATE
clause, SQL issues a warning message and proceeds with the
update modifications.
o If you do not specify a FOR UPDATE clause, you can update
any column using the UPDATE statement. SQL does not issue any
messages.
The FOR UPDATE OF clause in a SELECT statement provides UPDATE
ONLY CURSOR semantics by locking all the rows selected.
2.3.4 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
If you specify a list cursor but do not specify the INSERT ONLY
clause, SQL declares a read-only list cursor by default.
If you specify a table cursor but do not specify the INSERT ONLY
clause, SQL declares an update cursor by default.
When you specify an insert-only cursor, all the value expressions
in the select list must be read/write. When you declare an
insert-only table cursor to insert lists, you must specify both
table column and list column names in the FROM clause.
For more information about how to use insert-only cursors, see
the INSERT statement.
2.3.5 – LIST_CURSOR
Specifies a cursor that is used to manipulate columns of the data
type LIST OF BYTE VARYING.
2.3.6 – OPTIMIZE AS query name
Assigns a name to the query. You must define the SET FLAGS
'STRATEGY' statement to see the access methods used to produce
the results of the query.
2.3.7 – OPTIMIZE_FOR
The OPTIMIZE FOR clause specifies the preferred optimizer
strategy for statements that specify a select expression. The
following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If optimization strategy is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
The following examples illustrate the DECLARE CURSOR syntax
for setting a preferred optimization mode:
SQL> DECLARE TEMP1 TABLE CURSOR
cont> FOR
cont> SELECT *
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID > '00400'
cont> OPTIMIZE FOR FAST FIRST;
SQL> --
SQL> DECLARE TEMP2 TABLE CURSOR
cont> FOR
cont> SELECT LAST_NAME, FIRST_NAME
cont> FROM EMPLOYEES
cont> ORDER BY LAST_NAME
cont> OPTIMIZE FOR TOTAL TIME;
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
2.3.8 – OPTIMIZE USING outline name
Explicitly names the query outline to be used with the select
expression even if the outline IDs for the select expression and
for the outline are different.
See the CREATE OUTLINE statement for more information on creating
an outline.
2.3.9 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
2.3.10 – preserve-clause
Syntax options:
PRESERVE ON COMMIT
PRESERVE ON ROLLBACK
PRESERVE ALL
PRESERVE NONE
Specifies when a cursor remains open.
o PRESERVE ON COMMIT
On commit, all cursors close except those defined with the
WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors
close including those defined with the WITH HOLD PRESERVE ON
COMMIT syntax.
This is the same as specifying the WITH HOLD clause without
any preserve options.
o PRESERVE ON ROLLBACK
On rollback, all cursors close except those defined with the
WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors
close including those defined with the WITH HOLD PRESERVE ON
ROLLBACK syntax.
o PRESERVE ALL
All cursors remain open after commit or rollback. Cursors
close with the CLOSE statement or when the session ends.
o PRESERVE NONE
All cursors close after a CLOSE, COMMIT, or ROLLBACK
statement, when the program stops, or when you exit from
interactive SQL.
This is the same as not specifying the WITH HOLD clause at
all.
2.3.11 – READ_ONLY
Specifies that the cursor is not used to update the database.
2.3.12 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random. The SCROLL keyword
must be used if the following fetch options are desired:
o NEXT
o PRIOR
o FIRST
o LAST
o RELATIVE
o ABSOLUTE
If SCROLL is not specified, the default for FETCH is NEXT. SCROLL
is only supported for LIST cursors.
2.3.13 – TABLE_CURSOR
Specifies that the cursor you want to declare is a table cursor,
rather than a list cursor. If you do not specify a cursor type,
SQL declares a table cursor by default.
2.3.14 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
Use an update-only cursor when you plan to update most of the
rows you are fetching. The update-only cursor causes Oracle
Rdb to apply more restrictive locking during the initial read
operation, so that locks do not need to be upgraded later from
READ to exclusive WRITE. This reduces the total number of lock
requests per query, and may help to avoid deadlocks.
Use update-only table cursors to modify table rows. SQL does not
allow update-only list cursors.
2.3.15 – WHERE_CURRENT_OF
Specifies the table cursor that provides the row context for
the list cursor. The table cursor named must be defined using a
DECLARE CURSOR statement.
2.3.16 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
2.4 – Examples
Example 1: Declaring a table cursor in interactive SQL
The following example declares a cursor named SALARY_INFO. The
result table for SALARY_INFO contains the names and current
salaries of employees and is sorted by last name.
SQL> --
SQL> DECLARE SALARY_INFO CURSOR FOR
cont> SELECT E.FIRST_NAME, E.LAST_NAME, S.SALARY_AMOUNT
cont> FROM EMPLOYEES E, SALARY_HISTORY S
cont> WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID
cont> AND
cont> S.SALARY_END IS NULL
cont> ORDER BY
cont> E.LAST_NAME ASC;
SQL> --
SQL> -- Use an OPEN statement to open the cursor and
SQL> -- position it before the first row of the
SQL> -- result table:
SQL> OPEN SALARY_INFO;
SQL> --
SQL> -- Finally, use two FETCH statements to see the
SQL> -- first two rows of the cursor:
SQL> FETCH SALARY_INFO;
E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT
Louie Ames $26,743.00
SQL> FETCH SALARY_INFO;
E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT
Leslie Andriola $50,424.00
Example 2: Declaring a table cursor in a C program
This simple program uses embedded DECLARE CURSOR, OPEN, and FETCH
statements to retrieve and print the names and departments of
managers.
#include <stdio.h>
void main ()
{
int SQLCODE;
char FNAME[15];
char LNAME[15];
char DNAME[31];
/* Declare the cursor: */
exec sql
DECLARE MANAGER CURSOR FOR
SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.EMPLOYEE_ID = D.MANAGER_ID ;
/* Open the cursor: */
exec sql
OPEN MANAGER;
/* Start a loop to process the rows of the cursor: */
for (;;)
{
/* Retrieve the rows of the cursor
and put the value in host language variables: */
exec sql
FETCH MANAGER INTO :FNAME, :LNAME, :DNAME;
if (SQLCODE != 0) break;
/* Print the values in the variables: */
printf ("%s %s %s\n", FNAME, LNAME, DNAME);
}
/* Close the cursor: */
exec sql
CLOSE MANAGER;
}
Example 3: Using table and list cursors to retrieve list data in
interactive SQL
The following example declares a table and list cursor to
retrieve list information:
SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR
cont> SELECT EMPLOYEE_ID, RESUME FROM RESUMES;
SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME WHERE
CURRENT OF TBLCURSOR;
SQL> OPEN TBLCURSOR;
SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID) VALUES ('00164');
1 row inserted
SQL> OPEN LSTCURSOR;
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('This is the resume for 00164');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Boston, MA');
SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Oracle Corporation');
SQL> CLOSE LSTCURSOR;
SQL> CLOSE TBLCURSOR;
SQL> COMMIT;
SQL> DECLARE TBLCURSOR2 CURSOR FOR SELECT EMPLOYEE_ID,
cont> RESUME FROM RESUMES;
SQL> DECLARE LSTCURSOR2 LIST CURSOR FOR SELECT RESUME WHERE
CURRENT OF TBLCURSOR2;
SQL> OPEN TBLCURSOR2;
SQL> FETCH TBLCURSOR2;
00164
SQL> OPEN LSTCURSOR2;
SQL> FETCH LSTCURSOR2;
RESUME
This is the resume for 00164
SQL> FETCH LSTCURSOR2;
RESUME
Boston, MA
SQL> FETCH LSTCURSOR2;
RESUME
Oracle Corporation
SQL> FETCH LSTCURSOR2;
RESUME
%RDB-E-STREAM_EOF, attempt to fetch past end of record stream
SQL> CLOSE LSTCURSOR2;
SQL> SELECT * FROM RESUMES;
EMPLOYEE_ID RESUME
00164 1:701:2
1 row selected
SQL> CLOSE TBLCURSOR2;
SQL> COMMIT;
Example 4: Using the scroll attribute for a list cursor
The following example declares a table and read-only scrollable
list cursor to retrieve list information by scrolling back and
forth between segments of the list:
SQL> DECLARE CURSOR_ONE
cont> TABLE CURSOR FOR
cont> (SELECT EMPLOYEE_ID,RESUME FROM RESUMES);
SQL> --
SQL> DECLARE CURSOR_TWO
cont> READ ONLY
cont> SCROLL
cont> LIST CURSOR
cont> FOR SELECT RESUME
cont> WHERE CURRENT OF CURSOR_ONE;
Example 5: Declaring a holdable cursor
SQL> -- Declare a holdable cursor that remains open on COMMIT
SQL> --
SQL> DECLARE curs1 CURSOR
cont> WITH HOLD PRESERVE ON COMMIT
cont> FOR SELECT e.first_name, e.last_name
cont> FROM employees e
cont> ORDER BY e.last_name;
SQL> OPEN curs1;
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Louie Ames
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Leslie Andriola
SQL> COMMIT;
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Joseph Babbin
SQL> FETCH curs1;
FIRST_NAME LAST_NAME
Dean Bartlett
SQL> ROLLBACK;
SQL> FETCH curs1;
%SQL-F-CURNOTOPE, Cursor CURS1 is not opened
SQL> --
SQL> -- Declare another holdable cursor that remains open always
SQL> --
SQL> DECLARE curs2 CURSOR
cont> WITH HOLD PRESERVE ALL
cont> FOR SELECT e.first_name, e.last_name
cont> FROM employees e
cont> ORDER BY e.last_name;
SQL> OPEN curs2;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Louie Ames
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Leslie Andriola
SQL> COMMIT;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Joseph Babbin
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Dean Bartlett
SQL> ROLLBACK;
SQL> FETCH curs2;
FIRST_NAME LAST_NAME
Wes Bartlett
3 – Dynamic CURSOR
Declares a cursor where the SELECT statement is supplied at run
time in a parameter.
Refer to the DECLARE CURSOR for a detailed description of
statement elements that apply to both dynamic and nondynamic
DECLARE CURSOR statements.
3.1 – Environment
You can use the dynamic DECLARE CURSOR statement:
o Embedded in host language programs to be precompiled
o As part of the DECLARE statement section in an SQL module
3.2 – Format
DECLARE <cursor-name> ----------------------+
+-------------------------------------------+
+-+-+----------------+-> TABLE CURSOR -+----------------+-+
| +-> INSERT ONLY -+ +-> with-clause -+ |
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +-----------------------------------------------------+
| +-> FOR -> <statement-name> ------------------------+-->
++--------------++----------++-> LIST CURSOR ------+ |
+-> READ ONLY -++> SCROLL -+| | |
+-> INSERT ONLY ------------+ | |
+-------------------------------------------------+ |
+-> FOR -> <statement-name> -------------------------+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
3.3 – Arguments
3.3.1 – cursor-name
The name of the cursor you want to declare. Use a name that is
unique among all the cursor names in the module. Use any valid
SQL name. See the User_Supplied_Names HELP topic for more
information on identifiers.
3.3.2 – FOR statement name
A name that identifies a prepared SELECT statement that is
generated at run time.
3.3.3 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
3.3.4 – LIST_CURSOR
Specifies that you are declaring a cursor to access the elements
in a list.
3.3.5 – preserve-clause
Syntax options:
PRESERVE ON COMMIT
PRESERVE ON ROLLBACK
PRESERVE ALL
PRESERVE NONE
Specifies when a cursor remains open.
o PRESERVE ON COMMIT
On commit, all cursors close except those defined with the
WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors
close including those defined with the WITH HOLD PRESERVE ON
COMMIT syntax.
This is the same as specifying the WITH HOLD clause without
any preserve options.
o PRESERVE ON ROLLBACK
On rollback, all cursors close except those defined with the
WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors
close including those defined with the WITH HOLD PRESERVE ON
ROLLBACK syntax.
o PRESERVE ALL
All cursors remain open after commit or rollback. Cursors
close with the CLOSE statement or when the session ends.
o PRESERVE NONE
All cursors close after a CLOSE, COMMIT, or ROLLBACK
statement, when the program stops, or when you exit from
interactive SQL.
This is the same as not specifying the WITH HOLD clause at
all.
3.3.6 – READ_ONLY
Specifies that the cursor is not used to update the database.
3.3.7 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random.
3.3.8 – TABLE_CURSOR
Specifies that you are declaring a cursor to access the rows in a
table.
3.3.9 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
3.3.10 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
3.4 – Examples
Example 1: Using a parameter for a statement name
.
.
.
* This program prepares a statement for dynamic execution from the string
* passed to it, and uses a dynamic cursor to fetch a row from a table.
*
*/
#include <stdio.h>
#include <descrip.h>
struct SQLDA_STRUCT {
char SQLDAID[8];
int SQLDABC;
short SQLN;
short SQLD;
struct {
short SQLTYPE;
short SQLLEN;
char *SQLDATA;
short *SQLIND;
short SQLNAME_LEN;
char SQLNAME[30];
} SQLVAR[];
} *SQLDA;
main()
{
/*
* General purpose locals
*/
int i;
long sqlcode;
char command_string[256];
/*
* Allocate SQLDA structures.
*/
SQLDA = malloc(500);
SQLDA->SQLN = 20;
/* Get the SELECT statement at run time. */
printf("\n Enter a SELECT statement.\n");
printf("\n Do not end the statement with a semicolon.\n");
gets(command_string);
/* Prepare the SELECT statement. */
PREP_STMT( &sqlcode, &command_string, SQLDA );
if (sqlcode != 0)
goto err;
/* Open the cursor. */
OPEN_CURSOR( &sqlcode );
if (sqlcode != 0)
goto err;
/* Allocate memory. */
for (i=0; i < SQLDA->SQLD; i++) {
SQLDA->SQLVAR[i].SQLDATA = malloc( SQLDA->SQLVAR[i].SQLLEN );
SQLDA->SQLVAR[i].SQLIND = malloc( 2 );
}
/* Fetch a row. */
FETCH_CURSOR( &sqlcode, SQLDA );
if (sqlcode != 0)
goto err;
/* Use the SQLDA to determine the data type of each column in the row
and print the column. For simplicity, test for only two data types.
CHAR and INT. */
for (i=0; i < SQLDA->SQLD; i++) {
switch (SQLDA->SQLVAR[i].SQLTYPE) {
case SQLDA_CHAR; /* Character */
printf( "%s", SQLDA->SQLVAR[i].SQLDATA );
break;
case SQLDA_INTEGER: /* Integer */
printf( "%d", SQLDA->SQLVAR[i].SQLDATA );
break;
default:
printf( "Some other datatype encountered\n");
}
}
/* Close the cursor. */
CLOSE_CURSOR( &sqlcode );
ROLLBACK(&sqlcode );
return;
.
.
.
}
Example 2: SQL module file that the preceding program calls
-- This program uses dynamic cursors to fetch a row.
--
--
MODULE C_MOD_DYN_CURS
LANGUAGE C
AUTHORIZATION RDB$DBHANDLE
DECLARE ALIAS FOR FILENAME personnel
-- Declare the dynamic cursor. Use a statement name to identify a
-- prepared SELECT statement.
DECLARE CURSOR1 CURSOR FOR STMT_NAME
-- Prepare the statement from a statement entered at run time
-- and specify that SQL write information about the number and
-- data type of select list items to the SQLDA.
PROCEDURE PREP_STMT
SQLCODE
COMMAND_STRING CHAR (256)
SQLDA;
PREPARE STMT_NAME SELECT LIST INTO SQLDA FROM COMMAND_STRING;
PROCEDURE OPEN_CURSOR
SQLCODE;
OPEN CURSOR1;
PROCEDURE FETCH_CURSOR
SQLCODE
SQLDA;
FETCH CURSOR1 USING DESCRIPTOR SQLDA;
PROCEDURE CLOSE_CURSOR
SQLCODE;
CLOSE CURSOR1;
PROCEDURE ROLLBACK
SQLCODE;
ROLLBACK;
4 – Extended Dynamic CURSOR
Declares an extended dynamic cursor. An extended dynamic DECLARE
CURSOR statement is a DECLARE CURSOR statement in which both the
cursor name and the SELECT statement are supplied in parameters
at run time.
See the DECLARE CURSOR for a detailed description of statement
elements that apply to both dynamic and nondynamic DECLARE CURSOR
statements.
4.1 – Environment
You can use the extended dynamic DECLARE CURSOR statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
4.2 – Format
DECLARE <cursor-name-parameter> ---------------+
+----------------------------------------------+
++---+----------------+-> TABLE CURSOR -+----------------++
| +-> INSERT ONLY -+ +-> with-clause -+|
| +-> READ ONLY ---+ |
| +-> UPDATE ONLY -+ |
| +------------------------------------------------------+
| +--> FOR ---> <statement-id-parameter> ------------+------->
+-+--------------++----------++-> LIST CURSOR FOR -+ |
+-> READ ONLY -++> SCROLL -+| | |
+-> INSERT ONLY ------------+ | |
+--------------------------------------------------+ |
+-> <statement-id-parameter> ------------------------+
with-clause =
---> WITH --> HOLD -+-------------------------------+->
+-> PRESERVE --+-> ON COMMIT ---+
+-> ON ROLLBACK -+
+-> ALL ---------+
+-> NONE --------+
4.3 – Arguments
4.3.1 – cursor-name-parameter
Contains the name of the cursor you want to declare. Use a
character string parameter to hold the cursor name that the
program supplies at run time.
4.3.2 – FOR statement id parameter
A parameter that contains an integer that identifies a prepared
SELECT statement. Use an integer parameter to hold the statement
identifier that SQL generates and assigns to the parameter when
SQL executes a PREPARE statement.
4.3.3 – INSERT_ONLY
Specifies that a new list or a new row is created or opened.
4.3.4 – LIST_CURSOR_FOR
Specifies that you are declaring a cursor to access the elements
in a list.
4.3.5 – preserve-clause
Syntax options:
PRESERVE ON COMMIT
PRESERVE ON ROLLBACK
PRESERVE ALL
PRESERVE NONE
Specifies when a cursor remains open.
o PRESERVE ON COMMIT
On commit, all cursors close except those defined with the
WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors
close including those defined with the WITH HOLD PRESERVE ON
COMMIT syntax.
This is the same as specifying the WITH HOLD clause without
any preserve options.
o PRESERVE ON ROLLBACK
On rollback, all cursors close except those defined with the
WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors
close including those defined with the WITH HOLD PRESERVE ON
ROLLBACK syntax.
o PRESERVE ALL
All cursors remain open after commit or rollback. Cursors
close with the CLOSE statement or when the session ends.
o PRESERVE NONE
All cursors close after a close, commit, or rollback
statement, when the program stops, or when you exit from
interactive SQL.
This is the same as not specifying the WITH HOLD clause at
all.
4.3.6 – READ_ONLY
Specifies that the cursor is not used to update the database.
4.3.7 – SCROLL
Specifies that Oracle Rdb can read the items in a list from
either direction (up or down) or at random.
4.3.8 – TABLE_CURSOR_FOR
Specifies that you are declaring a cursor to access the rows in a
table.
4.3.9 – UPDATE_ONLY
Specifies that the cursor is used to update the database.
4.3.10 – WITH_HOLD
Indicates that the cursor remain open and maintain its position
after the transaction ends. This is called a holdable cursor.
4.4 – Example
Example 1: Using parameters for statement and cursor names
The following example shows two procedures from the online sample
program SQL$MULTI_STMT_DYN.SQLADA. These procedures show the use
of parameters for statement and cursor names.
.
.
.
-- This procedure prepares a statement for dynamic execution from the string
-- passed to it. This procedure can prepare any number of statements
-- because the statement is passed to it as the parameter, cur_procid.
procedure PREPARE_SQL is
CUR_CURSOR : string(1..31) := (others => ' ');
CUR_PROCID : integer := 0;
CUR_STMT : string(1..1024) := (others => ' ');
begin
-- Allocate separate SQLDAs for parameter markers (sqlda_in) and select list
-- items (sqlda_out). Assign the value of the constant MAXPARMS (set in the
-- declarations section) to the SQLN field of both SQLDA structures. SQLN
-- specifies to SQL the maximum size of the SQLDA.
sqlda_in := new sqlda_record;
sqlda_in.sqln := maxparms;
sqlda_out := new sqlda_record;
sqlda_out.sqln := maxparms;
-- Assign the SQL statement that was constructed in the procedure
-- CONSTRUCT_SQL to the variable cur_stmt.
cur_stmt := sql_stmt;
-- Use the PREPARE...SELECT LIST statement to prepare the dynamic statement
-- and write information about any select list items in it to sqlda_out.
-- It prepares a statement for dynamic execution from the string passed to
-- it. It also writes information about the number and data type of any
-- select list items in the statement to an SQLDA (specifically, the
-- sqlda_out SQLDA specified).
--
-- Note that the PREPARE statement could have prepared the statement without
-- writing to an SQLDA. Instead, a separate DESCRIBE...SELECT LIST statement
-- would have written information about any select list items to an SQLDA.
EXEC SQL PREPARE :cur_procid SELECT LIST INTO :sqlda_out FROM :cur_stmt;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- Use the DESCRIBE...MARKERS statement to write information about any
-- parameter markers in the dynamic statement to sqlda_in. This statement
-- writes information to an SQLDA (specifically, the sqlda_in SQLDA
-- specified) about the number and data type of any parameter markers in
-- the prepared dynamic statement. Note that SELECT statements may also
-- have parameter markers.
EXEC SQL DESCRIBE :cur_procid MARKERS INTO sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
-- If the operation is "Read," create a unique name for the cursor name
-- so that the program can pass the cursor name to the dynamic DECLARE
-- CURSOR statement.
if cur_op(1) = 'R' then
cur_cursor(1) := 'C';
cur_cursor(2..name_strlng) := cur_name(1..name_strlng - 1);
-- Declare the dynamic cursor.
EXEC SQL DECLARE :cur_cursor CURSOR FOR :cur_procid;
case sqlca.sqlcode is
when sql_success => null;
when others => raise syntax_error;
end case;
end if;
number_of_procs := number_of_procs + 1;
sqlda_in_array(number_of_procs) := sqlda_in;
sqlda_out_array(number_of_procs) := sqlda_out;
procedure_names(number_of_procs) := cur_name;
procedure_ids(number_of_procs) := cur_procid;
if cur_op(1) = 'R' then
cursor_names(number_of_procs) := cur_cursor;
end if;
exception
when syntax_error =>
sql_get_error_text(get_error_buffer,get_error_length);
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
new_line;
end PREPARE_SQL;
.
.
.
begin -- procedure body DISPLAY_DATA
-- Before displaying any data, allocate buffers to hold the data
-- returned by SQL.
--
allocate_buffers;
-- Allocate and assign SQLDAs for the requested SQL procedure.
--
sqlda_in := new sqlda_record;
sqlda_in := sqlda_in_array(stmt_index);
sqlda_out := new sqlda_record;
sqlda_out := sqlda_out_array(stmt_index);
cur_cursor := cursor_names(stmt_index);
-- Open the previously declared cursor. The statement specifies
-- an SQLDA (specifically, sqlda_in) as the source of addresses for any
-- parameter markers in the cursor's SELECT statement.
--
EXEC SQL OPEN :cur_cursor USING DESCRIPTOR sqlda_in;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
-- Fetch the first row from the result table. This statement fetches a
-- row from the opened cursor and writes it to the addresses specified
-- in an SQLDA (specifically, sqlda_out).
--
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
-- Check to see if the result table has any rows.
when sql_success => null;
when stream_eof =>
put_line("No records found.");
new_line;
when others => raise unexpected_error;
end case;
-- Set up a loop to display the first row, then fetch and display second
-- and subsequent rows.
rowcount := 0;
while sqlca.sqlcode = 0 loop
rowcount := rowcount + 1;
-- Execute the DISPLAY_ROW procedure.
display_row;
-- To only display 5 rows, exit the loop if the loop counter
-- equals MAXROW (coded as 5 in this program).
if rowcount = maxrows then exit; end if;
-- Fetch another row, exit the loop if no more rows.
EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out;
case sqlca.sqlcode is
when sql_success => null;
when stream_eof => exit;
when others => raise unexpected_error;
end case;
end loop;
-- Close the cursor.
EXEC SQL CLOSE :cur_cursor;
case sqlca.sqlcode is
when sql_success => null;
when others => raise unexpected_error;
end case;
exception
when unexpected_error =>
sql_get_error_text(get_error_buffer,get_error_length);
EXEC SQL ROLLBACK;
put_line("This condition was not expected.");
put_line(get_error_buffer(1..integer(get_error_length)));
put("Press RETURN to continue. ");
get_line(terminal,release_screen,last);
-- Stop and let the user look before returning.
skip;
put_line("Press RETURN to proceed. ");
get_line(terminal,release_screen,last);
end DISPLAY_DATA;
5 – FUNCTION
Declares an external function interface for use in database
definition statements.
The DECLARE FUNCTION statement is documented under the DECLARE
Routine. For complete information on declaring a procedure, see
the DECLARE ROUTINE Help topic.
6 – PROCEDURE
Declares a procedure interface for use in database definition
statements.
The DECLARE PROCEDURE statement is documented under the DECLARE
Routine. For complete information on declaring a procedure, see
the DECLARE ROUTINE Help topic.
7 – Routine
Declares a routine interface for use in database definition
statements. A routine is either a function or a procedure.
The declared routine acts as a template for calls to the function
or procedure in DDL statements such as CREATE TABLE, CREATE VIEW
and CREATE MODULE. The template allows Rdb to validate that
the routine is correctly named, is passed the correct number
of parameters and that those parameters are passed compatible
arguments. For functions the returned data type is used to
calculate data types for COMPUTED BY, AUTOMATIC and other stored
value expressions.
7.1 – Environment
You can use the DECLARE Routine statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
7.2 – Format
DECLARE --+-> FUNCTION ---+-> <routine-name> ------------------+
+-> PROCEDURE --+ |
+----------------------------- <-----------------------------+
+-+--------------------------------+-------------------------+
+-> STORED NAME IS <identifier> -+ |
+--------------------------- <-------------------------------+
+-> ( -+-----------------------+-> ) ------------------------+
++-> parameter-list --+-+ |
+-------- , <--------+ |
+--------------------------- <-------------------------------+
+-+-------------------+--> --+------------------+---------------->
+-> returns-clause -+ +-> LANGUAGE SQL --+
parameter-list =
-+----------+-+-------------------+-+-> data-type -----+----------+
+-> IN ----+ +> <parameter-name> + +-> <domain-name> -+ |
+-> OUT ---+ |
+-> INOUT -+ |
+-----------------------------------------------------------------+
+-+---------------------------+--+---------------------+----------+
+-> DEFAULT value-expr -----+ +-> mechanism-clause -+ |
+------------------------------<---------------------------------+
++---------------------------------+------------------------------->
+-> COMMENT IS -+-> 'string' ---+-+
+------ / <-----+
mechanism-clause =
----> BY --+-> DESCRIPTOR -+--->
+-> LENGTH -----+
+-> REFERENCE --+
+-> VALUE ------+
returns-clause =
---> RETURNS -+> result-data-type -++---------------------+-->
+> <domain-name> ----++-> mechanism-clause -+
7.3 – Arguments
7.3.1 – DEFAULT value-expr
Specifies the default value of a parameter for a function or
procedure defined with mode IN. If you omit this parameter or if
the CALL statement argument list or function invocation specifies
the DEFAULT keyword, then the value-expr specified with this
clause is used. The parameter uses NULL as the default if you do
not specify a value expression explicitly.
7.3.2 – FUNCTION
Declares a function definition.
A function optionally accepts a list of IN parameters, always
returns a value, and is referenced by name as an element of a
value expression.
7.3.3 – LANGUAGE_SQL
Names the language that calls the routine.
7.3.4 – mechanism-clause
Defines the passing mechanism for an external routine. The
following list describes the passing mechanisms.
o BY DESCRIPTOR
Allows passing character data with any parameter access mode
to routines compiled by language compilers that implement the
OpenVMS calling standard.
o BY LENGTH
The LENGTH passing mechanism is the same as the DESCRIPTOR
passing mechanism.
o BY REFERENCE
Allows passing data with any parameter access mode as a
reference to the actual data.
This is the default passing mechanism for parameters. This
is also the default passing mechanism for a function value
returning character data.
o BY VALUE
Allows passing data with the IN parameter access mode to a
routine as a value and allows functions to return a value.
This is the default passing mechanism for a function value
returning noncharacter data.
7.3.5 – parameter-list
The optional parameters of the routine. For each parameter
you can specify a parameter access mode (IN, OUT, and INOUT),
a parameter name, a data type, and a passing mechanism (by
DESCRIPTOR, LENGTH, REFERENCE, or VALUE).
The parameter access mode (IN, OUT, and INOUT) is optional and
specifies how the parameter is accessed (whether it is read,
written, or both). IN signifies read only, OUT signifies write
only, and INOUT signifies read and write. The parameter access
mode defaults to IN.
Only the IN parameter access mode may be specified with
parameters to a function. Any of the parameter access modes (IN,
OUT, and INOUT) may be specified with parameters to a procedure.
The parameter name is prefixed with a colon (:). The parameter
name must be unique within the routine parameters.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a parameter as the LIST OF BYTE VARYING data
type.
7.3.6 – PROCEDURE
Declares a procedure definition.
A procedure optionally accepts a list of IN, OUT, or INOUT
parameters, and is referenced by name in a CALL statement.
7.3.7 – RETURNS
Describes a function (returned) value. You can specify a data
type and a passing mechanism (BY DESCRIPTOR, LENGTH, REFERENCE,
or VALUE). The function value is, by definition, an OUT access
mode value.
The data type is required and describes the type of parameter
using either an SQL data type or a domain name.
You cannot declare a function value as the LIST OF BYTE VARYING
data type.
7.3.8 – routine-name
The name of the external routine. The name must be unique among
external and stored routines in the schema and can be qualified
with an alias or, in a multischema database, a schema name.
7.3.9 – STORED_NAME_IS
The name that Oracle Rdb uses to access the routine when defined
in a multischema database. The stored name allows you to access
multischema definitions using interfaces that do not recognize
multiple schemas in one database. You cannot specify a stored
name for a routine in a database that does not allow multiple
schemas. For more information about stored names, see Stored
Names.
7.4 – Examples
Example 1: Definining a domain and referencing an external
function
SQL> create domain MONEY as integer (2);
SQL>
SQL> create function INTEREST_PAID
cont> (in :amt MONEY)
cont> returns MONEY;
cont> external
cont> language C
cont> parameter style GENERAL;
SQL>
SQL> alter domain MONEY
cont> add
cont> check (INTEREST_PAID (value) > 0)
cont> not deferrable;
Once the ALTER DOMAIN is completed, neither the function nor the
domain can be defined before the other. Here is a fragment of the
result of executing the output from the RMU Extract command.
SQL> create domain MONEY
cont> INTEGER (2)
cont> check((INTEREST_PAID(value) > 0))
cont> not deferrable;
%SQL-F-RTNNOTDEF, function or procedure INTEREST_PAID is not defined
SQL>
SQL> commit work;
SQL> create function INTEREST_PAID (
cont> in :AMT
cont> MONEY
cont> by reference)
cont> returns
cont> MONEY by value
cont> language SQL;
cont> external
cont> language C
cont> parameter style GENERAL
cont> deterministic
cont> called on null input
cont> ;
%SQL-F-NO_SUCH_FIELD, Domain MONEY does not exist in this database or schema
SQL> commit work;
This problem is avoided for RMU Extract by adding the FORWARD_
REFERENCES item to the command line:
$ RMU/EXTRACT/ITEM=(ALL,FORWARD_REFERENCES) databasename/OUTPUT=script.SQL
The script now contains a forward declaration of the function
INTEREST_PAID so that execution of the script can succeed.
SQL> declare function INTEREST_PAID (
cont> in :AMT
cont> INTEGER (2))
cont> returns
cont> INTEGER (2)
cont> ;
SQL>
SQL> create domain MONEY
cont> INTEGER (2)
cont> check((INTEREST_PAID(value) > 0))
cont> not deferrable;
SQL>
SQL> commit work;
SQL> create function INTEREST_PAID (
cont> in :AMT
cont> MONEY
cont> by reference)
cont> returns
cont> MONEY by value
cont> language SQL;
cont> external
cont> language C
cont> parameter style GENERAL
cont> deterministic
cont> called on null input
cont> ;
SQL> commit work;
8 – LOCAL_TEMPORARY_TABLE
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.
8.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
8.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> --+
8.3 – Arguments
8.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.
8.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.
8.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.
8.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.
8.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
9 – MODULE
Specifies characteristics, such as character sets, quoting rules,
and the default date format for a nonstored module.
9.1 – Environment
You can use the DECLARE MODULE statement:
o Embedded in host language programs to be precompiled
o In a context file
This command is not executable.
9.2 – Format
DECLARE MODULE <module-name> --+------------------------+-+
+-> DIALECT environment -+ |
+---------------------------------------------------------+
+-+---------------------+-+-----------------------------+-+
+-> char-set-options -+ +---> CATALOG <catalog-name> -+ |
+---------------------------------------------------------+
+-+------------------------++----------------------------++
+-> SCHEMA <schema-name> ++-> AUTHORIZATION <auth-id> -+|
+---------------------------------------------------------+
+-+--------------------------------+-+----------------------------+--->
+-> PRAGMA (module-pragma-list) -+ +-> module-language-options -+
environment =
--+--> SQL99 -----+->
+--> SQL92 -----+
+--> SQL89 -----+
+--> SQLV40 ----+
+--> MIA -------+
char-set-options =
--+----------------> -------------+-----+
+--> NAMES ARE names-char-set --+ |
+----------------- <--------------------+
++-+---------------> ------------------------------+-+->
| +--> LITERAL CHARACTER SET support-char-set ----+ |
| +--> NATIONAL CHARACTER SET support-char-set ---+ |
| +--> DEFAULT CHARACTER SET support-char-set ----+ |
| +--> IDENTIFIER CHARACTER SET names-char-set ---+ |
| +--> DISPLAY CHARACTER SET names-char-set ------+ |
+---------------------------<-----------------------+
module-pragma-list =
-----------------> IDENT string-literal ----------->
module-language-options =
-+-+---> ALIAS <alias-name> ------------------------+-+-->
| +---> CHARACTER LENGTH -+-> CHARACTERS -+--------+ |
| | +-> OCTETS -----+ | |
| +---> DEFAULT DATE FORMAT --+--> SQL99 -+--------+ |
| | +--> SQL92 -+ | |
| | +--> VMS ---+ | |
| +---> KEYWORD RULES environment -----------------+ |
| +---> PARAMETER COLONS --------------------------+ |
| +---> QUOTING RULES environment -----------------+ |
| +---> RIGHTS --+--> INVOKER ---+-----------------+ |
| | +--> RESTRICT --+ | |
| +---> VIEW UPDATE RULES environment -------------+ |
| +---> QUIET COMMIT -+-> ON ---+------------------+ |
| | +-> OFF --+ | |
| +---> COMPOUND TRANSACTIONS -+-> INTERNAL -+----+ |
| +-> EXTERNAL -+ |
+--------------------------- <-----------------------+
9.3 – Arguments
9.3.1 – ALIAS alias name
Specifies the module alias. If you do not specify a module alias,
the default alias is the authorization identifier for the module.
When the FIPS flagger is enabled, the ALIAS clause (by itself
or used with the AUTHORIZATION clause) is flagged as nonstandard
syntax.
If the application needs to refer to only one database across
multiple modules, it is good practice to use the same alias for
the default database in all modules that will be linked to make
up an executable image.
9.3.2 – AUTHORIZATION
Specifies the authorization identifier for the module. If you
do not specify a schema clause, the authorization identifier
specifies the default schema.
To comply with the ANSI/ISO 1989 standard, specify the
AUTHORIZATION clause without the schema name. Specify both the
AUTHORIZATION clause and the schema name to comply with the
ANSI/ISO SQL standard.
When you attach to a multischema database, the authorization
identifier for each schema is the user name of the user compiling
the module. This authorization identifier defines the default
alias and schema. You can use the SCHEMA clause and the DECLARE
ALIAS statement to override the defaults.
If you attach to a single-schema database or specify that
MULTISCHEMA IS OFF in your ATTACH or DECLARE ALIAS statements
and you specify both an AUTHORIZATION clause and an ALIAS clause,
the authorization identifier is ignored by SQL unless you use
the RIGHTS RESTRICT clause. The RIGHTS RESTRICT clause causes
SQL to use the authorization identifier specified in the module
AUTHORIZATION clause for privilege checking.
If procedures in the SQL module always qualify table names with
an authorization identifier, the AUTHORIZATION clause has no
effect on SQL statements in the procedures.
When the FIPS flagger is enabled, the omission of an
AUTHORIZATION clause is flagged as nonstandard ANSI syntax.
9.3.3 – CATALOG catalog name
Specifies the default catalog for the module. Catalogs are groups
of schemas within a multischema database. If you omit the catalog
name when specifying an object in a multischema database, SQL
uses the default catalog name RDB$CATALOG. Databases created
without the multischema attribute do not have catalogs. You
can use the SET CATALOG statement to change the current default
catalog name in dynamic or interactive SQL.
9.3.4 – CHARACTER_LENGTH
Syntax options:
CHARACTER LENGTH CHARACTERS | CHARACTER LENGTH OCTETS
Specifies whether the length of character string parameters,
columns, and domains are interpreted as characters or octets. The
default is octets.
9.3.5 – DEFAULT_CHARACTER_SET
Specifies the character set for parameters that are not qualified
by a character set. The default is DEC_MCS. This clause overrides
the character set specified in the NAMES ARE clause. See
Supported Character Sets for a list of the allowable character
sets.
9.3.6 – DEFAULT_DATE_FORMAT
Syntax options:
DEFAULT DATE FORMAT { SQL99 | SQL92 | VMS }
Controls the default interpretation for the data type of
the CURRENT_TIMESTAMP built in function and column or CAST
expressions with the DATE data type. The DATE and CURRENT_
TIMESTAMP data types can be either VMS or ANSI/ISO Standard
format.
If you specify VMS, both data types are interpreted as VMS
format. The VMS format DATE and CURRENT_TIMESTAMP contain YEAR
TO SECOND fields.
If you specify SQL99 or SQL92, both data types are interpreted as
SQL standard format. The SQL format DATE contains only the YEAR
TO DAY fields.
The default is VMS.
Use the DEFAULT DATE FORMAT clause, rather than the SQLOPTIONS =
ANSI_DATE qualifier because the qualifier will be deprecated in a
future release.
9.3.7 – DIALECT
Controls the following settings:
o Whether the length of character string parameters, columns,
and domains are interpreted as characters or octets
o Whether double quotation marks are interpreted as string
literals or delimited identifiers
o Whether or not identifiers can be keywords
o Which views are read-only
o Whether columns with the DATE or CURRENT_TIMESTAMP data type
are interpreted as VMS or SQL99 format
The DIALECT clause lets you specify the settings with one clause,
instead of specifying each setting individually. Because the
module processor processes the module clauses sequentially, the
DIALECT clause can override the settings of clauses specified
before it or be overridden by clauses specified after it.
The following statements are specific to the SQL99 dialect:
o The default constraint evaluation time setting changes from
DEFERRABLE to NOT DEFERRABLE.
o Conversions between character data types when storing data or
retrieving data will raise exceptions or warnings in certain
situations.
o You can specify DECIMAL or NUMERIC for formal parameters
in SQL modules, and declare host language parameters with
packed decimal or signed numeric storage format. SQL generates
an error message if you attempt to exceed the precision
specified.
o The USER keyword specifies the current active user name for a
request.
o A warning is generated when a NULL value is eliminated from a
SET function.
o The WITH CHECK OPTION clause on views returns a discrete error
code from an integrity constraint failure.
o An exception is generated with non-null terminated C strings.
See the SET_DIALECT statement for more information about
dialects.
9.3.8 – DISPLAY CHARACTER SET names-char-set
Specifies the character set encoding and characteristics expected
of text strings returned back to SQL from Oracle Rdb.
9.3.9 – IDENTIFIER_CHARACTER_SET
Specifies the character set used for database object names
such as table names and column names. This clause overrides the
character set specified in the NAMES ARE clause. See the Oracle
Rdb SQL Reference Manual for a list of allowable character sets
and option values.
The specified character set must contain ASCII characters.
9.3.10 – KEYWORD_RULES
Controls whether or not identifiers can be keywords. If you
specify SQL99, SQL92, SQL89, or MIA, you cannot use keywords as
identifiers, unless you enclose them in double quotation marks.
If you specify SQLV40, you can use keywords as identifiers. The
default is SQLV40.
Use the KEYWORD RULES clause, rather than the SQLOPTIONS = ANSI_
IDENTIFIER qualifier because the qualifier will be deprecated in
a future release.
9.3.11 – LITERAL_CHARACTER_SET
Specifies the character set for literals that are not qualified
by a character set or national character set. If you do not
specify a character set in this clause or in the NAMES ARE
clause, the default is DEC_MCS. This clause overrides the
character set for unqualified literals specified in the NAMES ARE
clause. See Supported Character Sets for a list of the allowable
character sets.
9.3.12 – MODULE module name
An optional name for the nonstored module. If you do not supply a
module name, the default name is SQL_MODULE.
Use any valid OpenVMS name. (See the User_Supplied_Names HELP
topic for more information on user-supplied names.) However, the
name must be unique among the modules that are linked together to
form an executable image.
9.3.13 – NAMES_ARE
Specifies the character set used for the default, identifier,
and literal character sets for the module. Also specifies the
character string parameters that are not qualified by a character
set or national character set. If you do not specify a character
set, the default is DEC_MCS.
You must ensure that the character set specified in this clause
matches the character set of all the databases attached to by
any particular connection and must contain ASCII characters. See
the Oracle Rdb SQL Reference Manual for a list of the allowable
character sets.
9.3.14 – NATIONAL_CHARACTER_SET
Specifies the character set for literals qualified by the
national character set. See Supported Character Sets for a list
of the allowable character sets.
9.3.15 – PARAMETER_COLONS
If you use the PARAMETER COLONS clause, all parameter names
must begin with a colon (:). This is valid in context files for
module language only. This rule applies to both declarations and
references of module language procedure parameters. If you do not
use this clause, no parameter name can begin with a colon.
The current default behavior is no colons are used. However,
this default is deprecated syntax. In the future, required colons
will be the default because it allows processing of ANSI/ISO SQL
standard modules.
Use the PARAMETER COLONS clause, rather than the SQLOPTIONS
deprecated in a future release.
9.3.16 – QUOTING_RULES
Controls whether double quotation marks are interpreted as string
literals or delimited identifiers. If you specify SQLV40, SQL
interprets double quotation marks as literals. All other dialects
interpret double quotation marks as delimited identifiers. The
default is SQLV40.
Use the QUOTING RULES clause, rather than the SQLOPTIONS = ANSI_
QUOTING qualifier because the qualifier will be deprecated in a
future release.
9.3.17 – RIGHTS
Syntax options:
RIGHTS INVOKER | RIGHTS RESTRICT
Specifies whether or not a module must be executed by a user
whose authorization identifier matches the module authorization
identifier.
If you specify RESTRICT, SQL bases privilege checking on the
default authorization identifier. The default authorization
identifier is the authorization identifier of the user who
compiles a module, unless you specify a different authorization
identifier using an AUTHORIZATION clause in the module. The
RESTRICT option causes SQL to compare the user name of the
person who executes a module with the default authorization
identifier and prevents any user other than one with the
correct authorization identifier from invoking that module.
All applications that use multischema restrict the invoker by
default.
If you specify INVOKER, SQL bases the privilege on the
authorization identifier of the user running the module. The
default is INVOKER.
Use the RIGHTS clause, rather than the SQLOPTIONS = ANSI_
AUTHORIZATION qualifier because the qualifier will be deprecated
in a future release.
9.3.18 – SCHEMA schema name
Specifies the default schema name for the module. The default
schema is the schema to which SQL statements refer if those
statements do not qualify table names and other schema names
with an authorization identifier. If you do not specify a
default schema name for a module, you must specify a default
authorization identifier.
Using the SCHEMA clause, separate modules can each declare
different schemas as default schemas. This can be convenient for
an application that needs to refer to more than one schema. By
putting SQL statements that refer to a schema in the appropriate
module's procedures, you can minimize tedious qualification of
schema element names in those statements.
When you specify SCHEMA schema-name AUTHORIZATION auth-id, you
specify the schema name and the schema authorization identifier
for the module. The schema authorization identifier is considered
the owner and creator of the schema and everything in it.
9.3.19 – VIEW_UPDATE_RULES
Specifies whether or not the SQL module processor applies the
ANSI/ISO SQL standard for updatable views to all views created
during compilation.
If you specify SQL99, SQL92, SQL89, or MIA, the SQL module
processor applies that ANSI/ISO SQL standard for updatable views
to all views created during compilation. Views that do not comply
with the specified ANSI/ISO SQL standard for updatable views
cannot be updated.
The specified ANSI/ISO standard for updatable views requires the
following conditions to be met in the SELECT statement:
o The DISTINCT keyword is not specified.
o Only column names can appear in the select list. Each column
name can appear only once. Functions and expressions such as
max(column_name) or column_name +1 cannot appear in the select
list.
o The FROM clause refers to only one table. This table must be
either a base table or a derived table that can be updated.
o The WHERE clause does not contain a subquery.
o The GROUP BY clause is not specified.
o The HAVING clause is not specified.
If you specify SQLV40, SQL does not apply the ANSI/ISO standard
for updatable views. Instead, SQL considers views that meet the
following conditions to be updatable:
o The DISTINCT keyword is not specified.
o The FROM clause refers to only one table. This table must be
either a base table or a derived table that can be updated.
o The WHERE clause does not contain a subquery.
o The GROUP BY clause is not specified.
o The HAVING clause is not specified.
9.4 – Example
Example 1: Declaring a module specifying character strings of
different character sets
Assuming that the character sets for the database match the
character sets specified in the program, the following example
shows a simple SQL precompiled C program that retrieves one row
from the COLOURS table.
/* This SQL precompiled program does some simple tests of character length
* and character sets.
*/
#include stdio
#include descrip
main()
{
/* Specify CHARACTER LENGTH CHARACTERS in the DECLARE MODULE statement.
* In addition, specify the NAMES, NATIONAL, and DEFAULT character sets.
*/
EXEC SQL DECLARE MODULE CCC_COLOURS
NAMES ARE DEC_KANJI
NATIONAL CHARACTER SET KANJI
SCHEMA RDB$SCHEMA
AUTHORIZATION SQL_SAMPLE
CHARACTER LENGTH CHARACTERS
DEFAULT CHARACTER SET DEC_KANJI
ALIAS RDB$DBHANDLE;
/* If you do not specify character sets in the DECLARE ALIAS statement, SQL
* uses the character sets of the compile-time database.
*/
EXEC SQL DECLARE ALIAS FILENAME MIA_CHAR_SET;
int SQLCODE;
/* Because the default character set is DEC_KANJI, you do not need to qualify
* the variable dec_kanji_p with the character set, but you must declare
* char in lowercase.
*/
char dec_kanji_p[31];
/* When you declare a parameter with lowercase char, SQL considers the
* character set unspecified and allocates single-octet characters.
*/
char english_p[31];
/* When you specify the character set, SQL allocates single- or multi-octet
* characters, depending upon the character set.
*/
char CHARACTER SET DEC_MCS french_p[31];
char CHARACTER SET KANJI japanese_p[31];
.
.
.
/* Select one row from the COLOURS table. */
EXEC SQL SELECT ENGLISH, FRENCH, JAPANESE, ROMAJI,
KATAKANA, HINDI, GREEK, ARABIC, RUSSIAN
INTO :english_p, :french_p, :japanese_p, :dec_kanji_p,
:katakana_p, :devanagari_p, :isolatingreek_p,
:isolatinarabic_p, :isolatincyrillic_p
FROM COLOURS LIMIT TO 1 ROW;
if (SQLCODE != 0)
SQL$SIGNAL();
printf ("\nENGLISH: %s", english_p);
printf ("\nFRENCH: %s", french_p);
printf ("\nJAPANESE: %s", japanese_p);
printf ("\nROMAJI: %s", dec_kanji_p);
printf ("\nKATAKANA: %s", katakana_p);
printf ("\nHINDI: %s", devanagari_p);
printf ("\nGREEK: %s", isolatingreek_p);
printf ("\nARABIC: %s", isolatinarabic_p);
printf ("\nRUSSIAN: %s", isolatincyrillic_p);
EXEC SQL ROLLBACK;
}
10 – STATEMENT
Documents a statement name later used in a PREPARE statement in
dynamic SQL. SQL does not require DECLARE STATEMENT statements
and does not generate any code when it precompiles them. They are
entirely optional.
10.1 – Environment
You can issue the DECLARE STATEMENT statement only in host
language programs to be precompiled.
10.2 – Format
DECLARE --+-> <statement-name> +-> STATEMENT
+------- , <--------+
10.3 – Arguments
10.3.1 – statement-name
Specifies the name of a statement later referred to in one of the
following embedded dynamic statements:
o PREPARE
o DECLARE CURSOR
o DESCRIBE
10.4 – Example
Example 1: Declaring a statement name in a PL/I program
This example shows a program line that declares a statement name
DYNAMIC_STATEMENT. Later lines in the example show how DECLARE
CURSOR, PREPARE, and DESCRIBE statements refer to it. Because
you do not have to declare a statement explicitly, the DECLARE
STATEMENT statement is always optional.
EXEC SQL DECLARE DYNAMIC_STATEMENT STATEMENT;
/* Declare the SQL Communications Area. */
EXEC SQL INCLUDE SQLCA;
/* Declare the SQL Descriptor Area. */
EXEC SQL INCLUDE SQLDA;
/* The program declares the host language variable
STATEMENT_STRING and stores in it the
character string containing a SELECT
statement to be executed dynamically. */
.
.
.
EXEC SQL DECLARE CURSOR1 CURSOR FOR DYNAMIC_STATEMENT;
EXEC SQL PREPARE OBJECT_STATEMENT FROM STATEMENT_STRING;
EXEC SQL DESCRIBE OBJECT_STATEMENT INTO SQLDA;
/* The program sets up pointers in the
SQLDATA field of the SQLDA to the data
area (host language variables or dynamic
memory, for example) to receive the data
from the cursor. */
.
.
.
EXEC SQL OPEN CURSOR1;
DO WHILE (SQLCODE = 0);
EXEC SQL FETCH CURSOR1 USING DESCRIPTOR SQLDA;
/* The program prints or otherwise
processes rows of the result tables. */
.
.
.
END;
EXEC SQL CLOSE CURSOR1;
11 – TABLE
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
11.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
11.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 ---------->------------+
11.3 – Arguments
11.3.1 – character-set-name
A valid character set name. See the Oracle Rdb SQL Reference
Manual for more information on character sets.
11.3.2 – col-constraint
A column constraint. See the CREATE TABLE statement for more
information about column constraints.
11.3.3 – column-name
The name of the column you want to define.
11.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.
11.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.
11.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.
11.3.7 – frac
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
11.3.8 – interval-qualifier
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
11.3.9 – prec
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
11.3.10 – references-clause
See the CREATE TABLE statement for more information.
11.3.11 – seconds-prec
Precision specifications for date-time data types. See the Data_
Types HELP topic for more information.
11.3.12 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE
HELP topic for more information about formatting clauses.
11.3.13 – table-name
The name of the table definition you want to declare.
11.3.14 – view-name
The name of the view definition you want to declare.
11.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.
11.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
12 – TRANSACTION
Specifies the characteristics for a default transaction. A
transaction is a group of statements whose changes can be made
permanent or undone only as a unit.
A transaction ends with a COMMIT or ROLLBACK statement. If you
end the transaction with the COMMIT statement, all changes made
to the database by the statements are made permanent. If you end
the transaction with the ROLLBACK statement, the statements do
not take effect.
The characteristics specified in a DECLARE TRANSACTION statement
affect all transactions (except those started by the SET
TRANSACTION or START TRANSACTION statement) until you issue
another DECLARE TRANSACTION statement. The characteristics
specified in a SET TRANSACTION or START TRANSACTION statement
affect only that transaction.
A DECLARE TRANSACTION statement does not start a transaction.
The declarations made in a DECLARE TRANSACTION statement do not
take effect until SQL starts a new transaction. SQL starts a new
transaction with the first executable data manipulation or data
definition statement following a DECLARE TRANSACTION, COMMIT,
or ROLLBACK statement. In the latter case (following a COMMIT or
ROLLBACK statement), SQL applies the transaction characteristics
you declared for the transaction that just ended to the next one
you start.
In addition to the DECLARE TRANSACTION statement, you can specify
the characteristics of a transaction in one of two ways:
o If you specify the SET TRANSACTION or START TRANSACTION
statement, the declarations in the statement take effect
immediately and SQL starts a new transaction.
o You can retrieve and update data without declaring or
setting a transaction explicitly. If you omit the DECLARE
TRANSACTION, SET TRANSACTION or START TRANSACTION statements,
SQL automatically starts a transaction (using the read/write
option) with the first executable data manipulation or data
definition statement following a COMMIT or ROLLBACK statement.
See the Oracle Rdb SQL Reference Manual for examples of when you
would want to use the DECLARE TRANSACTION statement instead of
the SET TRANSACTION or START TRANSACTION statement.
You can specify many options with the DECLARE TRANSACTION
statement, including:
o A transaction mode (READ ONLY/READ WRITE/BATCH UPDATE)
o A lock specification clause (RESERVING options)
o A wait mode (WAIT/NOWAIT)
o An isolation level
o A constraint evaluation specification clause
o Multiple sets of all the preceding options for each database
involved in the transaction (ON clause)
12.1 – Environment
You can use the DECLARE TRANSACTION statement:
o In interactive SQL
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
o As part of the module header in a CREATE MODULE statement
o In dynamic SQL as a statement to be dynamically executed
In host language programs, you can have only a single DECLARE
TRANSACTION statement in each separately compiled source file.
See the Oracle Rdb SQL Reference Manual for more information.
The DECLARE TRANSACTION statement is an extension to standard
SQL syntax. If your program must adhere to standard SQL syntax,
you can isolate a DECLARE TRANSACTION statement by putting it in
a context file. For more information on context files, see the
Oracle Rdb Guide to SQL Programming.
12.2 – Format
DECLARE TRANSACTION --+---------------+->
+-> tx-options -+
+-> db-txns ----+
tx-options =
--+-+----------------------------------------+-+->
| +-> NAME 'quoted-string' ----------------+ |
| +-> EVALUATING -+- evaluating-clause --+-+ |
| | +-------> , <----------+ | |
| +-> RESERVING --+-> reserving-clause --+-+ |
| | +-------- , <----------+ | |
| +-> isolation-level ---------------------+ |
| +-> transaction-mode --------------------+ |
| +-> wait-option -------------------------+ |
+----------------+-------+-------------------+
+-- , <-+
evaluating-clause =
-+-------------+-> <constraint-name> -> AT -+-> VERB TIME ---+-->
+-> <alias.> -+ +-> COMMIT TIME -+
reserving-clause =
-+-+-> <view-name> ------------------------------------------------+-+-+
| +-> <table-name> -+-------------------------------------------+-+ | |
| +-> PARTITION --> ( -+-> <part-num> -+-> ) -+ | |
| +------ , <-----+ | |
+----------------------------------- , <----------------------------+ |
+---------------------------------------------------------------------+
+-> FOR -+--------------+--+-> READ ------------+--------------------->
+-> EXCLUSIVE -+ +-> WRITE -----------+
+-> PROTECTED -+ +-> DATA DEFINITION -+
+-> SHARED ----+
isolation-level =
---> ISOLATION LEVEL --+-> READ COMMITTED ----+-->
+-> REPEATABLE READ ---+
+-> SERIALIZABLE ------+
transaction-mode =
--+-> READ ONLY ----+->
+-> READ WRITE ---+
+-> BATCH UPDATE -+
wait-option =
--+-> WAIT -+---------------------+-+->
| +-> <timeout-value> --+ |
+-> NOWAIT -----------------------+
db-txns =
--+--------------------------------------------------------------+-->
++-> ON -+-> <alias> -+-> USING --> ( +-> tx-options --+-> ) -++
| +---- , <----+ +-> DEFAULTS ----+ |
+-------------------------- AND <----------------------------+
12.3 – Arguments
The DECLARE TRANSACTION arguments are the same as the arguments
for the SET TRANSACTION statement. See the SET_TRANSACTION
statement for more information about the arguments for both
statements.
12.4 – Defaults
The DECLARE TRANSACTION defaults are the same as the defaults for
the SET TRANSACTION statement. See the SET_TRANSACTION statement
for complete information.
In general, you should not rely on default transaction
characteristics. Use explicit DECLARE TRANSACTION statements,
specifying read/write, read-only, or batch-update options;
a list of tables in the RESERVING clause; and a share mode
and lock type for each table. The more specific you are in a
DECLARE TRANSACTION statement, the more efficient your database
operations will be.
When a transaction starts using characteristics specified in a
DECLARE TRANSACTION statement, any transaction characteristics
unspecified in the DECLARE TRANSACTION statement take the SQL
defaults. This is true even if the characteristics unspecified in
DECLARE TRANSACTION were specified in an earlier SET or DECLARE
TRANSACTION statement.
12.5 – Examples
Example 1: Illustrating DECLARE and SET TRANSACTION differences
In the following example, the first executable statement
following the DECLARE TRANSACTION statement starts a transaction.
In contrast, the subsequent SET TRANSACTION statement itself
starts a transaction.
SQL> DECLARE TRANSACTION READ WRITE NOWAIT;
SQL> --
SQL> -- Notice the "no transaction is in progress" message:
SQL> --
SQL> SHOW TRANSACTION
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
no transaction is in progress
- session ID number is 80
SQL> --
SQL> -- The first executable statement following the
SQL> -- DECLARE TRANSACTION statement starts the transaction.
SQL> -- In this case, SELECT is the first executable statement.
SQL> --
SQL> SELECT LAST_NAME FROM CURRENT_SALARY;
LAST_NAME
Toliver
Smith
Dietrich
.
.
.
SQL> --
SQL> -- Note the transaction inherits the read/write characteristics
SQL> -- specified in the DECLARE TRANSACTION statement:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
a read-write transaction is in progress
- updates have not been performed
- transaction sequence number (TSN) is 416
- snapshot space for TSNs less than 416 can be reclaimed
- session ID number is 80
SQL> --
SQL> ROLLBACK;
SQL> --
SQL> -- Again, notice the "no transaction is in progress" message:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Nowait
Read Write
Transaction information returned by base system:
no transaction is in progress
- transaction sequence number (TSN) 416 is reserved
- snapshot space for TSNs less than 416 can be reclaimed
- session ID number is 80
SQL> --
SQL> -- Unlike DECLARE TRANSACTION, the SET TRANSACTION statement
SQL> -- immediately starts a transaction:
SQL> --
SQL> SET TRANSACTION READ ONLY WAIT;
SQL> --
SQL> -- Note the transaction characteristics show the
SQL> -- read-only characteristics:
SQL> --
SQL> SHOW TRANSACTION;
Transaction information:
Statement constraint evaluation is off
On the default alias
Transaction characteristics:
Wait
Read only
Transaction information returned by base system:
a snapshot transaction is in progress
- all transaction sequence numbers (TSNs) less than 416 are visible
- TSN 416 is invisible
- all TSNs greater than or equal to 417 are invisible
- session ID number is 80
Example 2: Using a DECLARE TRANSACTION statement in a context
file
The following example shows a context file, test_declares.sql,
that contains declarations for precompiling source file test.sco:
DECLARE ALIAS FOR FILENAME personnel;
DECLARE TRANSACTION READ WRITE
RESERVING EMPLOYEES FOR PROTECTED WRITE,
JOB_HISTORY FOR PROTECTED WRITE,
DEPARTMENTS FOR SHARED READ,
JOBS FOR SHARED READ;
The section in the Oracle Rdb Guide to SQL Programming about
program transportability explains when you may need an SQL
context file to support a program that includes SQL statements.
Example 3: Explicitly setting the isolation level in a DECLARE
TRANSACTION statement
In this example, you declare the default characteristics for
a read/write transaction, which includes changing the default
ISOLATION LEVEL SERIALIZABLE to ISOLATION LEVEL REPEATABLE READ.
SQL> DECLARE TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ;
Example 4: Reserving a Partition
SQL> -- Determine the ordinal position of the EMPLOYEES
SQL> -- partitions.
SQL> SELECT RDB$MAP_NAME, RDB$AREA_NAME, RDB$ORDINAL_POSITION
cont> FROM RDB$STORAGE_MAP_AREAS
cont> WHERE RDB$MAP_NAME='EMPLOYEES_MAP';
RDB$MAP_NAME RDB$AREA_NAME
RDB$ORDINAL_POSITION
EMPLOYEES_MAP EMPIDS_LOW
1
EMPLOYEES_MAP EMPIDS_MID
2
EMPLOYEES_MAP EMPIDS_OVER
3
3 rows selected
SQL> --
SQL> -- Reserve EMPIDS_MID and EMPIDS_OVER for
SQL> -- exclusive write.
SQL> --
SQL> DECLARE TRANSACTION
cont> RESERVING EMPLOYEES PARTITION (2,3)
cont> FOR EXCLUSIVE WRITE;
13 – Variable
Declares variables that you can use in interactive and dynamic
SQL for invoking stored procedures and for testing procedures in
modules or embedded SQL programs. For information on declaring
variables in compound statements, see the Compound_Statement HELP
topic.
13.1 – Environment
You can use the DECLARE statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
13.2 – Format
DECLARE -+-> :<variable-name> +--+---------------+-+
+-------- , <--------+ +--> CONSTANT --+ |
+--> UPDATABLE -+ |
+--------------------------------------------------+
+-+-> data-type ----+-+--------------------+------->
+-> <domain-name> + +-> default-clause -+
default-clause =
--+-> DEFAULT --+--+--> date-time-literal -+----->
+-> = ------+ +--> interval-literal -+
+--> numeric-literal --+
+--> string-literal ---+
+--> : <variable-name> -+
13.3 – Arguments
13.3.1 – CONSTANT
Syntax options:
CONSTANT | UPDATABLE
CONSTANT changes the variable into a declared constant that
cannot be updated. If you specify CONSTANT, you must also have
specified the DEFAULT clause to ensure the variable has a value.
CONSTANT also indicates that the variable cannot be used as
the target of an assignment or be passed as an expression to a
procedure's INOUT or OUT parameter.
UPDATABLE is the default and allows the variable to be modified.
An update of a variable can occur due to a SET assignment, an
INTO assignment (as part of an INSERT ... RETURNING, UPDATE ...
RETURNING, or SELECT statement), or as a procedure's OUT or INOUT
parameter on a CALL statement.
13.3.2 – data-type
Specifies the data type assigned to the variable. See the Data_
Types HELP topic for more information on data types.
13.3.3 – default-clause
You can only use references to simple literal values and other
declared variables as a default.
13.3.4 – domain-name
Specifies the domain name assigned to the variable. The domain
supplies the data type and, for interactive SQL, the edit string
of the variable.
See the User_Supplied_Names HELP topic for more information on
domain names.
13.3.5 – variable-name
Specifies the local variable.
13.4 – Example
Example 1: Declaring variables in interactive SQL
SQL> DECLARE :X INTEGER;
SQL> DECLARE :Y CHAR(10);
SQL>
SQL> BEGIN
cont> SET :X = 100;
cont> SET :Y = 'Active';
cont> END;
SQL> PRINT :X, :Y ;
X Y
100 Active
SQL> SHOW VARIABLES;
X INTEGER
Y CHAR(10)
Example 2: Using the values of SQLSTATE in an interactive SQL
script
The following simple script uses the named SQLSTATE variable with
the SIGNAL statement to make the script easier to read.
@SYS$LIBRARY:SQLSTATE
set verify;
begin
signal :SQLSTATE_DATA_ASSIGN ('Error in assignment');
end;
When executed the output appears as shown below.
SQL> begin
cont> signal :SQLSTATE_DATA_ASSIGN ('Error in assignment');
cont> end;
%RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "22005"
-RDB-I-TEXT, Error in assignment
SQL>