HP 3000 Manuals HP 3000 Manuals
CREATE PROCEDURE
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.
Scope
ISQL or Application Programs
SQL Syntax
CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName]
[( ParameterDeclaration [, ParameterDeclaration] [...] )]
[WITH RESULT ResultDeclaration [, ResultDeclaration] [...]] AS BEGIN
[ProcedureStatement] [...] END [IN DBEFileSetName]
Parameters
[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 NATIVE-3000. 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.
SQL Syntax--ParameterDeclaration
ParameterName ParameterType [LANG = ParameterLanguage]
[DEFAULT DefaultValue] [NOT NULL] [OUTPUT [ONLY]]
Parameters--ParameterDeclaration
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 NATIVE-3000.
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.
SQL Syntax--ResultDeclaration
ResultType [LANG = ResultLanguage] [NOT NULL]
Parameters--ResultDeclaration
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
NATIVE-3000. The default is the language of the
procedure.
NOT NULL indicates that the result column cannot contain
null values.
Description
* 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.[REV BEG]
(For more information about data type compatibility, refer to
chapter 7, "Data Types.")[REV END]
* 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
returns 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.
Authorization
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.
Examples
1. 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;
2. 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
3. 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;