|
|
The CREATE PROCEDURE statement defines a procedure for storage in a
DBEnvironment. A procedure may subsequently be executed through the firing of a
rule by an INSERT, UPDATE, or DELETE statement, or by using the
EXECUTE PROCEDURE statement or a procedure cursor.
ISQL or Application Programs
CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName]
[(ParameterDeclaration [,ParameterDeclaration][...])]
[WITH RESULT ResultDeclaration [,ResultDeclaration ][...]]
AS BEGIN [ProcedureStatement][...] END [IN DBEFileSetName]
- [Owner.]ProcedureName
specifies the owner and the name of the procedure. If an owner name is
not specified, the owner is the current user's DBEUserID or the schema's
authorization name, or the ISQL SET OWNER value. You can specify the
owner of the new procedure if you have DBA authority. If you do not have
DBA authority, you can specify as owner the name of any group of which
you are a member. Two procedures cannot have the same owner and procedure
name.
- ProcLangName
is the name of the default language used within the procedure for
parameters and local variables. This language may be either the language
of the DBEnvironment or n-computer. The default is the language of the
DBEnvironment.
- ParameterDeclaration
specifies the attributes of parameter data to be passed to or from the
procedure. The syntax of ParameterDeclaration is
presented separately below.
- ResultDeclaration
specifies the attributes of a result column in a multiple row result
set or sets returned from a procedure to an application or ISQL. The
syntax of ResultDeclaration is presented separately
below.
- ProcedureStatement
Specifies a statement in the procedure body. The statement may be any
one of the following:
Local variable declaration (see
DECLAREVariable).
Parameter or local variable assignment (see Assignment).
Compound statement. A compound statement has the following syntax:
BEGIN [Statement;] [...] END;
Control flow and status statements
IF...THEN...ELSEIF...ELSE...ENDIF
WHILE...DO...ENDWHILE
Jump statement (GOTO, GO TO, or RETURN)
PRINT
Any SQL statement allowed in an application except
the following:
ADVANCE
BEGIN DECLARE SECTION
BULK statements
CLOSE (when the USING clause is specified)
COMMIT WORK RELEASE
CONNECT
CREATE PROCEDURE (including inside CREATE SCHEMA)
DECLARE CURSOR (when declaring a cursor for an
EXECUTE PROCEDURE statement)
DESCRIBE
DISCONNECT
END DECLARE SECTION
EXECUTE
EXECUTE IMMEDIATE
EXECUTE PROCEDURE
GENPLAN
INCLUDE
OPEN CURSOR USING DESCRIPTOR
OPEN CURSOR USING HostVariableList
PREPARE
RELEASE
ROLLBACK WORK RELEASE
SET CONNECTION
SET DML ATOMICITY
SET MULTITRANSACTION
SET SESSION
SET TRANSACTION
SQLEXPLAIN
START DBE
STOP DBE
A ProcedureStatement must be terminated by a
semicolon.
- DBEFileSetName
identifies the DBEFileSet in which ALLBASE/SQL is to store sections
associated with the procedure. If not specified, the SECTIONSPACE
DBEFileSet is used.
ParameterName ParameterType [LANG = ParameterLanguage]
[DEFAULT DefaultValue][NOT NULL][OUTPUT [ONLY]]
- ParameterName
is the name assigned to a parameter in the procedure. No two
parameters in the procedure can be given the same name. You can define no
more than 1023 parameters in a procedure.
- ParameterType
indicates what type of data the parameter will contain. The
ParameterType cannot be a LONG data type. For a list of
data types, refer to the "Data Types" chapter.
- ParameterLanguage
specifies the language for the parameter. A LANG may only be specified
for a parameter with a character data type. This language may be either
the language of the procedure or n-computer. The default is the language
of the procedure.
- DefaultValue
specifies the default value for the parameter. The default can be a
constant, NULL, or a date/time current function. The data type of the
default value must be compatible with the data type of the
column.
- NOT NULL
means that the parameter cannot contain null values. If NOT NULL is
specified, any statement that attempts to place a null value in the
parameter is rejected.
- OUTPUT
specifies that the parameter can be used for procedure output as well
as input (the default). If OUTPUT is not specified, the
parameter can only be used for input to the procedure.
If procedure output is required, OUTPUT must also be specified for any
corresponding parameter in the EXECUTE PROCEDURE
statement.
- ONLY
specifies that the parameter can be used for procedure output only.
ONLY should be used, when applicable, to avoid unnecessary initialization
of procedure parameters.
You must also specify OUTPUT for any corresponding parameter in the
EXECUTE PROCEDURE statement.
The DEFAULT option cannot be specified for OUTPUT ONLY
parameters.
ResultType [LANG = ResultLanguage][NOT NULL]
- ResultType
indicates the data type of a result column in a query result for a
query or queries in the procedure. The "Data Types" chapter describes the
data types available in ALLBASE/SQL.
- ResultLanguage
specifies the language of the result column. A LANG may only be
specified for a result column with a character data type. This language
may be either the language of the procedure or n-computer. The default is
the language of the procedure.
- NOT NULL
indicates that the result column cannot contain null values.
A procedure may be created through ISQL or through an application
program.
A procedure result set is the set of rows returned by a
procedure SELECT, FETCH, or REFETCH
statement.
A select cursor (one declared for a SELECT
statement) opened in an application program (i.e, outside the
procedure) cannot be accessed within the procedure. However, a
procedure can open and access its own select cursors.
A procedure cursor (one declared for an EXECUTE
PROCEDURE statement) must be opened and accessed outside of the
specified procedure, in an application program. An application can
open more than one procedure cursor.
A procedure with multiple row result sets is a procedure
containing one or more SELECT statements with no INTO
clause. In order to retrieve one or more multiple row result sets
from a procedure, you must execute the procedure using a procedure
cursor. The application can then either process data from a result
(by issuing the FETCH statement within the application) or
advance past the result set (by issuing the ADVANCE or the
CLOSE statement within the application).
If you execute a procedure without using a procedure cursor in the
above case, a warning is returned to the application, no result set
data is returned, and any return status and output parameters are
returned as usual.
Transaction statements (COMMIT WORK, ROLLBACK WORK, WHENEVER
.. STOP) executed have the usual effect on non-KEEP cursors,
i.e. such cursors are closed.
A procedure executing transaction statements can close a cursor
defined on itself. Therefore, transaction statements must be used
with care in procedures containing statements returning multiple row
result sets.
Procedures may reference the following set of built-in variables
in non-SQL statements only:
::sqlcode
::sqlerrd2
::sqlwarn0
::sqlwarn1
::sqlwarn2
::sqlwarn6
::activexact
The first six of these have the same meaning that they have as fields
in the SQLCA in application programs. Note that in procedures,
sqlerrd2 returns the number of rows processed for all host languages.
However, in application programs, sqlerrd(3) is used in COBOL and
Fortran, sqlerrd[3] is used in Pascal, and sqlerr[2] is used in C.
::activexact indicates whether a transaction is in progress or not.
For additional information, refer to the application programming
guides and to the chapter "Constraints, Procedures, and
Rules."
Built-in variables cannot be referenced in any SQL statement.
They may be referenced in ASSIGNMENT, IF, WHILE, RETURN, and
PRINT statements. Refer to the section "Using Procedures" in
the chapter "Constraints, Procedures, and Rules" for more explanation
of built-in variables.
Control flow and status statements, local variable declarations,
parameter or local variable assignments, and labeled statements are
allowed only within procedures.
Each ProcedureStatement must be terminated with a
semicolon.
A label may appear only at the start of a
ProcedureStatement that is not a compound statement,
a local variable declaration, or a WHENEVER directive.
Host variables cannot be accessed within a procedure.
No more than 1024 result columns can be defined
in a procedure result set.
Within a procedure, any SELECT, FETCH, or
REFETCH statement with an INTO clause specifying parameters
and/or local variables returns at most a one row result.
A procedure with single format multiple row result sets is
a procedure having one or more multiple row result sets, whose result
format is defined in the WITH RESULT clause. Each SELECT
statement with no INTO clause must return rows of a format compatible
with this defined result format. When using the WITH RESULT clause,
all such result sets in the procedure must return the same number of
columns. The corresponding result columns of each result set must be
compatible in data type, language and nullability. The corresponding
result columns of each result set must be no longer than defined in
the WITH RESULT clause. (For more information about data type
compatibility, refer to chapter 7, "Data Types.")
The WITH RESULT clause is used to describe the data format of a
procedure's multiple row result sets. Since, by definition, all
single format multiple row result sets have the same format, there is
no distinction made between result sets. There is no need to issue
any ADVANCE statement in the application. Use the WITH RESULT
clause only when you do not need to know the boundary between result
sets.
ALLBASE/SQL attempts to verify compatibility of each result set
format with the format defined in the WITH RESULT clause at the time
the procedure is created. In addition, since verification is not
always possible at procedure creation time (sections may be created
as invalid), compatibility is also verified at procedure execution
time for each procedure result set. If incompatibility is detected
during procedure creation, the create statement return a warning. If
incompatibility is detected during procedure execution, the execution
of the procedure result set statement fails with an error, and no
more data is returned (For an ADVANCE or CLOSE,
procedure execution continues with the next statement).
An attempt to execute a CREATE PROCEDURE statement
containing a WITH RESULT clause but no multiple row result set causes
an error and the procedure is not created.
When a procedure with single format multiple row result sets is
created using the WITH RESULT clause, the format specified in this
clause is stored in the system catalog PROCRESULT table. This format
information can be returned after defining a cursor on a procedure
(at procedure execution time) with a DESCRIBE RESULT statement
before (opening and fetching) from the
cursor.
Indicator variables are not allowed or needed inside procedures.
However, you can include an indicator variable with a host variable
in supplying a value to a parameter in EXECUTE PROCEDURE, DECLARE
CURSOR, OPEN, or CLOSE statements.
Indicator variables specified for output host variables in CLOSE,
DECLARE CURSOR, or EXECUTE PROCEDURE statements are set
by ALLBASE/SQL.
Syntactic errors are returned along with an indication of the
location of the error inside the CREATE PROCEDURE
statement.
Statements that support dynamic processing are not allowed within
a procedure.
Within a procedure, a single row SELECT statement (one
having an INTO clause) that returns multiple rows will assign the
first row to output host variables or procedure parameters, and a
warning is issued. In an application, this case would generate an
error.
If the IN DBEFileSetName clause is specified, but
the procedure owner does not have SECTIONSPACE authority for the
specified DBEFileSet, a warning is issued and the default
SECTIONSPACE DBEFileSet is used instead.
You must have RESOURCE or DBA authority to create a procedure.
If you do not have all appropriate authorities on the objects referenced
by the procedure when you create the procedure, warnings are returned.
If you do not have the appropriate authorities at execution time,
errors are returned but (except in a rule) the execution of the
rest of the procedure does not stop. The procedure owner becomes
the owner of any object created by the procedure with no owner explicitly
specified. A user granted authority to execute a procedure need
not have any direct authority on the objects accessed by the procedure.
To specify a DBEFileSetName, the procedure owner must have
SECTIONSPACE authority on the referenced DBEFileSet.
DELETE
CREATE PROCEDURE ManufDB.RemoveBatchStamp
(BatchStamp DATETIME NOT NULL)
AS
BEGIN
DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp;
IF ::sqlcode < > 0 THEN
PRINT 'Delete failed.';
ENDIF;
END;
INSERT
CREATE PROCEDURE PurchDB.ReportMonitor
(Name CHAR(20) NOT NULL,
Owner CHAR(20) NOT NULL, Type CHAR(10) NOT NULL)
AS
BEGIN
INSERT INTO PurchDB.ReportMonitor
VALUES (:Type, CURRENT_DATETIME,
USER, :Name, :Owner);
RETURN ::sqlcode;
IN PurchFS;
END
SELECT (multiple row and single row)
CREATE PROCEDURE ReportOrder
(OrderNumber INTEGER,
TotalPrice DECIMAL (10,2) OUTPUT)
AS
BEGIN
Multiple row result set is returned to the application for
processing using a procedure cursor.
SELECT ItemNumber, OrderQty, PurchasePrice
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
Single row result set value is returned to the application via an OUTPUT
parameter.
SELECT SUM (OrderQty * PurchasePrice)
INTO :TotalPrice
FROM PurchDB.OrderItems
WHERE OrderNumber = :OrderNumber;
END;
|