Show TOC

Background documentationCREATE DBPROC[EDURE] Statement (create_dbproc_statement)

 

The CREATE DBPROC[EDURE] statement (create_dbproc_statement) defines a database procedure.

Structure

Syntax Syntax

  1. <create_dbproc_statement> ::=
  CREATE DBPROC[EDURE] <dbproc_name> [(<formal_parameter>,...)]
    [RETURNS CURSOR] AS <routine>

<formal_parameter> ::=
  IN <argument> <data_type>
| OUT <argument> <data_type>
| INOUT <argument> <data_type>

<argument> ::=
  <identifier>
End of the code.
Examples

SQL Tutorial, Database Procedures

Explanation

If no schema is specified in the database procedure name, the current schema is assumed implicitly.

The database procedure is assigned to the schema that has been determined implicitly or specified explicitly. The current database user must have the CREATEIN privilege for this schema. The procedure name must be different from the names of the database procedures already existing in the schema.

The current user is the owner of a database procedure. He or she has the EXECUTE privilege to execute the database procedure.

Parameters

When an application calls the database procedure with the CALL statement, it exchanges data using parameters defined by means of formal parameters (formal_parameter). A formal parameter of the database procedure usually corresponds to a variable in the application.

IN | OUT | INOUT

The parameter mode (IN | OUT | INOUT) specifies the direction in which data is transferred when the procedure is called.

  • IN: IN defines an input parameter, that is, the value of the variable transferred to the database procedure when the procedure is called.

  • OUT: OUT defines an output parameter. This means the value of the formal parameter is transferred from the database procedure to the variable after the procedure has been terminated.

  • INOUT: INOUT defines an input/output parameter that combines the IN and OUT functions.

Argument

By specifying an argument (argument) you assign a name to a formal parameter of the database procedure. This parameter name can then be used as a variable in expressions and assignments in the database procedure.

Data type

Only the data types BOOLEAN, CHAR[ACTER], DATE, FIXED, FLOAT, INT[EGER], NUMBER, REAL, SMALLINT, TIME, TIMESTAMP and VARCHAR are permitted as a data type (data_type) of the formal parameter of a database procedure.

RETURNS CURSOR

If RETURNS CURSOR is specified, a database procedure is defined that returns a results table when called.

The name of this table is defined using the system variable $CURSOR. A database procedure must therefore contain a statement that generates a results table with the result set name $CURSOR.

The value of $CURSOR is already assigned by most programming language embeddings, but can also be explicitly assigned in the database procedure.

Example Example

CREATE DBPROC hotels_of_town (IN zip CHAR(5))

RETURNS CURSOR AS $CURSOR = 'HOTEL_CURSOR';

DECLARE :$CURSOR CURSOR FOR

SELECT * FROM hotel.hotel WHERE zip = :zip;

End of the example.

More Information

CREATE SYSTEM TRIGGER Statement

CREATE TRIGGER Statement

CREATE FUNCTION Statement