1 – AUTHORIZATION auth id
A name that identifies the definer of a module and is used to
perform privilege validation for the module. the User_Supplied_
Names HELP topic for information about using authorization
identifiers.
2 – COMMENT_IS
Adds a comment about the module, routine, and parameter. SQL
displays the text of the comment when it executes a SHOW MODULE,
SHOW FUNCTION, or SHOW PROCEDURE statement. Enclose the comment
in single quotation marks (') and separate multiple lines in a
comment with a slash mark (/).
3 – compound-statement
Allows you to include more than one SQL statement in a stored
routine. See the Compound_Statement HELP topic for more
information.
4 – compound-use-statement
Allows you to include one SQL statement in a stored routine. See
the Compound_Statement HELP topic for more information.
If you are defining a stored function, the simple statement must
be the RETURNS clause.
5 – data-type
A valid SQL data type. Specifying an explicit data type is an
alternative to specifying a domain name. See the Data_Types HELP
topic for more information on data types.
6 – decl-local-temp-table-statement
Declares a local temporary table for the module. See the DECLARE
LOCAL_TEMPORARY_TABLE statement for more information.
7 – declare-transaction-statement
Declares a transaction for the module. Only one declare-
transaction-statement is permitted for each module. If omitted,
an implicit DECLARE TRANSACTION READ WRITE is used. See the
DECLARE TRANSACTION statement for more information.
8 – declare-variable-statement
Declares a global variable for the module. See the statement
DECLARE Variable for more information.
9 – 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.
10 – DETERMINISTIC
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
The clause controls the evaluation of an external function in the
scope of a query:
o NOT DETERMINISTIC
Specifying the NOT DETERMINISTIC clause forces evaluation
of corresponding functions (in scope of a single query)
every time the function appears. If a function can return a
different result each time it is invoked, you should use the
DETERMINISTIC clause.
o DETERMINISTIC
Specifying the DETERMINISTIC clause can result in a single
evaluation of corresponding function expressions (in scope
of a single query), and the resulting value is used in all
occurrences of the corresponding function expression. When you
use the DETERMINISTIC clause, Oracle Rdb evaluates whether or
not to invoke the function each time it is used.
For example:
SELECT * FROM T1 WHERE F1() > 0 AND F1() < 20;
If you define the F1 function as DETERMINISTIC, the function
F1() may be evaluated just once depending on the optimizer. If
you define the F1 function as NOT DETERMINISTIC, the function
F1() is evaluated twice.
DETERMINISTIC is the default.
The DETERMINISTIC or NOT DETERMINISTIC clause is not allowed on
procedure definitions.
11 – domain-name
The name of a domain created in a CREATE DOMAIN statement. For
more information about domains, see the CREATE DOMAIN statement.
12 – 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.
13 – FUNCTION function-name
A user-supplied name that you give to a function in a module. The
name you specify must be unique within the database definition.
14 – IN parameter name
Specifies the parameter modes used in the procedure. The IN
parameter names the parameter that is read into the routine,
however is never set and cannot be written to.
The IN parameter is the only mode allowed for functions.
Each parameter name must be unique within the routine.
15 – INOUT parameter name
Specifies the parameter modes used in the procedure. The INOUT
parameter names a parameter that inputs data (is read) as well as
receives data (is set). The INOUT parameter is a parameter that
is modified.
Each parameter name must be unique within the procedure.
You cannot use the INOUT parameter mode for stored functions.
16 – LANGUAGE_SQL
The LANGUAGE keyword and the SQL argument signify that the
procedures in a module are to be invoked by SQL statements, not a
host language program.
With unstored procedures, the LANGUAGE keyword specifies the name
of a host language; this identifies the host language in which
the program calling a module's procedures is written.
Beginning with Oracle Rdb Release 7.1, this clause is optional.
17 – module-name
A user-supplied name that you assign to a module.
See the User_Supplied_Names HELP topic for more information on
user-supplied names.
18 – OUT parameter name
Specifies the parameter modes used in the procedure. The OUT
parameter names the parameter into which data is being sent. The
OUT parameter is set, but never read.
Each parameter name must be unique within the procedure.
You cannot use the OUT parameter mode for stored functions.
19 – parameter-decl
Specifies the parameters and parameter modes used in a stored or
external routine.
20 – PROCEDURE procedure-name
A user-supplied name that you give to a procedure in a module.
The name you specify must be unique within the database
definition.
21 – RETURNS result data type
Specifies the data type or domain of the result of the function
invocation. This clause is only valid when defining a function.
You can only use the RETURNS clause when defining a function.
22 – routine-clause
The definition of a stored function or stored procedure created
in a module.
23 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a module
procedure or function created in a multischema database. Or
specifies a name that Oracle Rdb uses to access a procedure or
a function created in a stored module.
24 – USAGE_IS
Specifies how the function or procedure can be called:
o USAGE IS GLOBAL indicates that the function or procedure can
be called outside the current module. This is the default.
o USAGE IS LOCAL specifies that the routine is restricted to
references within the module. This clause is provided for
compatibility with CREATE MODULE but is not allowed for CREATE
FUNCTION or CREATE PROCEDURE.
25 – VARIANT
Syntax options:
VARIANT | NOT VARIANT
These clauses are synonyms for the DETERMINISTIC and NOT
DETERMINISTIC clauses. The DETERMINISTIC clause indicates that
the same inputs to the function will generate the same output.
It is the same as the NOT VARIANT clause. The NOT DETERMINISTIC
clause indicates that the output of the function does not depend
on the inputs. It is the same as the VARIANT clause.
This clause is deprecated. Use DETERMINISTIC instead.
26 – variant-clause
Syntax options:
DETERMINISTIC | NOT DETERMINISTIC
The clause controls the evaluation of an external function in the
scope of a query:
o NOT DETERMINISTIC
Specifying the NOT DETERMINISTIC clause forces evaluation
of corresponding functions (in scope of a single query)
every time the function appears. If a function can return a
different result each time it is invoked, you should use the
DETERMINISTIC clause.
o DETERMINISTIC
Specifying the DETERMINISTIC clause can result in a single
evaluation of corresponding function expressions (in scope
of a single query), and the resulting value is used in all
occurrences of the corresponding function expression. When you
use the DETERMINISTIC clause, Oracle Rdb evaluates whether or
not to invoke the function each time it is used.
For example:
SELECT * FROM T1 WHERE F1() > 0 AND F1() < 20;
If you define the F1 function as DETERMINISTIC, the function
F1() may be evaluated just once depending on the optimizer. If
you define the F1 function as NOT DETERMINISTIC, the function
F1() is evaluated twice.
DETERMINISTIC is the default.
The DETERMINISTIC or NOT DETERMINISTIC clause is not allowed on
procedure definitions.