Creating Routines - CREATE PROCEDURE and CREATE FUNCTION

Routine Description (SQL)

routine_description ::= { procedure_description | function_description };
procedure_description ::= PROCEDURE [scheme.]name ([ formal_parameter {, formal_parameter } ]) ; body
function_description ::= FUNCTION name ([ formal_parameter {, formal_parameter }… ]) RETURNS type; body
formal_parameter ::= [ parameter_mode ] [ name ] type [ DEFAULT expression ]
parameter_mode  ::= IN | OUT | INOUT
body ::= statement | EXTERNAL NAME  external_routine_designation

Each routine has a name that is used for calling the routine. Each routine also has a list of formal parameters that are assigned real parameters when the routine is called. The routine body is executed when the routine is called in a single statement or statement block.

If the LEAVE statement is called in the routine body as part of the statement block, it is possible to use the routine name as the label.

The parameter_mode specifies the method of passing value at the beginning and end of routine execution. If the routine body does not have an EXTERNAL specification, the real parameter values with the IN (input parameter) and INOUT (input-output parameters) modes are copied to formal parameters when the routine is called. The formal parameter values with the OUT (output parameters) and INOUT modes are copied (back) into the real parameters when the routine is terminated. If the parameter mode is not specified, IN mode is used for functions. For procedure parameters that are only read are considered IN, parameters that are only assigned values are considered OUT, and parameters that are both read and assigned values are considered INOUT.

If a parameter is not specified when calling a routine, the relevant formal parameter will have the value of the expression specified by the DEFAULT keyword. If DEFAULT is not specified it will have a NULL value. If this parameter is OUT or INOUT, the formal parameter value will not be written anywhere.

The parameter name can be omitted from the formal parameter specification. You cannot refer to such parameter in the routine body. Therefore, it is acceptable to omit the parameter name for external routines.

A routine that has the EXTERNAL keyword specified in the body is implemented in an external .DLL library (.SO on Linux). The external_routine_designation is a character string in the form ‘function_name@library_name’. You can set the path in the library_name. If the path is not set, the library is searched for according to the operating system rules (the PATH variable). If the extension is missing in the library_name, .DLL is appended on Windows and .SO on Linux.

If the library is not found or the function of the specified name is missing in the library, the error sqlstate 38001 (SQ_EXT_ROUT_NOT_AVAIL) or W0224 (LIBRARY_NOT_FOUND) occurs. If the library is not in the folder where executable libraries are stored, the error W0223 (LIBRARY_ACCESS_DISABLED) occurs.

There are strict rules for passing parameters to external routines. These rules define the relation between the formal parameters in the declaration and parameters in the executed routines written in an external language. See Calling external routines and parameter passing.

602SQL Variations

Functions can have parameters with the IN, OUT and INOUT mode in 602SQL. SQL3 only allows the IN mode.

Example:

PROCEDURE CancelRes(IN lim INT);
BEGIN

  DECLARE PROCEDURE CheckResBook(code CHAR(8));  // auxiliary local procedure
  BEGIN
    DECLARE count INT;
    SELECT COUNT(co) INTO count               // how many times is the book reserved
    FROM Reservations
    WHERE Reservations.co = code;

    IF count = 1 THEN                        // if only once then remove reservation
      UPDATE Books
      SET Books.reserved = FALSE
      WHERE Books.code = code;
    END IF;
  END;

  FOR row AS temp SENSITIVE CURSOR FOR        // for all reservations after the limit do
    SELECT * 
    FROM Reservations
    WHERE CURRENT_DATE - Reservations.res_date > lim
  DO BEGIN
      CALL CheckResBook(row.co);      // change the reserved attribute in the Books table
      DELETE WHERE CURRENT OF temp;     // delete the record in the Reservations table
    END;
  END FOR;
END

See

Routines Stored on the Server

Creating Routines - CREATE PROCEDURE and CREATE FUNCTION