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.
1 – Environment
You can use the DECLARE Routine statement:
o In interactive SQL
o In dynamic SQL as a statement to be dynamically executed
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 -+
3 – Arguments
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.
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.
3.3 – LANGUAGE_SQL
Names the language that calls the routine.
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.
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.
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.
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.
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.
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.
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;