ALLBASE/SQL Reference Manual (36216-90216)

342 Chapter10
SQL Statements A - D
CREATE PROCEDURE
Description
• A procedure may be created through ISQL or through an application program.
•Aprocedure result set is the set of rows returned by a procedure SELECT, FETCH,or
REFETCH statement.
•Aselect 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.
•Aprocedure 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.
•Aprocedure 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."