Routine (routine)

The part of the CREATE DBPROC[EDURE] statement, CREATE TRIGGER statement, CREATE SYSTEM TRIGGER statement or CREATE FUNCTION statement described as the routine is the implementation of the database procedure, trigger or the database function. It comprises optional variable declarations and statements.

Structure

Syntax Syntax

<routine> ::=
  [<local_variables>] <statement_list>;

<local_variables>> ::=
  VAR <local_variable_list>;

<local_variable_list>> ::=
  <local_variable>
| <local_variable_list>; <local_variable>

<local_variable> ::=
  <variable_name> <routine_data_type>

<variable_name> ::=
  <identifier>

<routine_data_type> ::=
  <data_type>
| CURSOR
| PREPAREDSTATEMENT

<statement_list> ::=
  <statement>
| <statement_list> ; <statement>

End of the code.

Control Structures, Statements and SQL Statements (statement)

Syntax Syntax

<statement> ::=
  BEGIN <statement_list> END
| BREAK
| CONTINUE
| CONTINUE EXECUTE 
| EXECUTE <expression>
| <if_statement>
| <while_statement>
| <assignment_statement>
| <case_statement>
| OPEN :<local_variable> FOR <select_statement>
| PREPARE <local_variable> FOR <expression>
| RETURN [<expression>]
| STOP (<expression>[,<expression>])
| TRY <statement_list>; CATCH <statement>
| <routine_sql_statement>

<if_statement> ::=
  IF <search_condition> THEN <statement>
    [ELSE <statement>]

<while_statement> ::=
  WHILE <search_condition> DO <statement>

<assignment_statement> ::=
  [SET] <variable_name> = <expression>

<case_statement> ::=
  <simple_case_statement>
| <searched_case_statement>

<routine_sql_statement> ::=
  <call_statement>
| <close_statement>
| <create_table_temp>
| <declare_cursor_statement>
| <delete_statement>
| <drop_table_temp>
| <fetch_statement>
| <insert_statement>
| <lock_statement>
| <recursive_declare_cursor_statement>
| <select_statement>
| <single_select_statement>
| <subtrans_statement>
| <unlock_statement>
| <update_statement>

<create_table_temp> ::=
<!  <create_table_statement> for creating
    temporary tables, that is,
    the table name <table_name> in the
    CREATE TABLE statement must have the
    form TEMP.<identifier>.
!>

<drop_table_temp> ::=
  <DROP TABLE> TEMP.<identifier>

End of the code.

Examples

SQL Tutorial, Database Procedures, Database Functions, Database Triggers

Explanation

The statements concerning database procedures below also apply for triggers and database functions.

Variables

Local variables of the database procedure must be declared explicitly by specifying a data type before they are used. Only the data types BOOLEAN, CHAR[ACTER] [BYTE], DATE, FIXED, FLOAT, INT[EGER], NUMBER, REAL, SMALLINT, TIME, TIMESTAMP and VARCHAR are permitted. Once they have been declared, these variables can be used in any SQL or other statements.

Every database procedure implicitly possesses the variables $RC, $ERRMSG and $COUNT.

The $RC variable returns a numeric error code after an SQL statement has been executed. The value 0 means that the SQL statement was successfully executed.

In parallel with $RC, the $ERRMSG variable returns an explanation of the error containing a maximum of 80 characters.

The number of lines processed in an SQL statement is indicated by the $COUNT variables. For a QUERY statement, this variable can have the value -1, which means that an unknown number of records, but at least one, comprises the result of the QUERY statement. For a DELETE statement, this variable can have the value -1, which means that an unknown number of records has been deleted, possibly 0 records.

Variables can be assigned a value using the assignment_statement.

EXECUTE

EXECUTE <expression> enables you to execute an SQL statement which is created only at the runtime of the database procedure. The specified expression must be either a local variable of the routine data type PREPAREDSTATEMENT or an expression of the data type CHAR(n).

If a local variable of the routine data type PREPAREDSTATEMENT is specified, this must previously have been prepared for execution by a PREPARE statement.

If an expression of the data type CHAR(n) is specified, the expression must display the SQL statement to be executed (in the form of a character string), which, however, may not contain any parameters.

Example Example

VAR test_expression CHAR(1000);

test_expression = 'SELECT * FROM DUAL'; /* the value of test_expression is a correct SQL statement */

EXECUTE test_expression; /* the SQL statement can be executed*/

End of the example.

Control Structures

The IF statement (if_statement) first evaluates the search condition. If this is met, the statement specified in the THEN branch is executed. Otherwise, the statement in the ELSE branch (if defined) is executed.

The WHILE statement (while_statement) enables statements to be repeated in response to certain conditions. The statement is executed as long as the search condition specified is met. The condition is checked, in particular, before the statement is executed for the first time. This means the statement may not be executed at all. By specifying BREAK, you can exit the loop straightaway without the condition being checked. If CONTINUE is specified in the loop, the condition is revaluated immediately and the loop is processed again, or exited, depending on the result.

The CASE statement (case_statement) allows the conditional execution of a statement, dependent on search conditions or the equality of operands. There are simple and general CASE statements.

OPEN

OPEN :<local_variable> FOR <select_statement> creates a result table with a name that is unique within the database session. The local variable specified must be of the routine data type CURSOR. The results table is identified in the FETCH or CLOSE statement with the variable name as the variable name is specified as a parameter for the results table name.

PREPARE

PREPARE <local_variable> FOR <expression> prepares a dynamic SQL statement for the execution using EXECUTE <expression>. The local variable specified must be of the routine data type PREPAREDSTATEMENT and must supply a permitted SQL statement during runtime. This SQL statement can contain parameters which refer to local variables or to call parameters of the database procedure.

When the database procedure is executed, the execution of the PREPARE statement leads to the SQL statement being checked for accuracy and type compatibility with the parameters specified, as well as to the generation of an execution plan which is identified with the PREPAREDSTATEMENT variable specified. The actual execution of the statement is carried out with the EXECUTE statement when the variable name is specified.

RETURN

CREATE DBPROC and CREATE TRIGGER statement: Specifying RETURN allows an immediate error-free exit of the surrounding database procedure.

CREATE FUNCTION statement: Specifying RETURN <expression> enables you to exit the database function and transfer the <expression> function value at the same time.

Error Handling

If an SQL error occurs in the statement list between TRY and CATCH, the system branches directly to the statement that follows CATCH. The actual troubleshooting routine can be programmed in this statement. If CONTINUE EXECUTE is executed here, the system jumps directly to the point after the statement that triggered the error.

The database procedure is interrupted immediately whenever the STOP function is invoked. The value of the first parameter of the STOP function is the return or error message received by the application as the result of the database procedure call. An error text can also be returned.

SQL Statements (routine_sql_statement)

Tables in SQL statements (routine_sql_statement) of the database procedure must always be complete. In other words, the schema must be specified. For SELECT statements, a complete specification of the table name in the FROM clause is sufficient.

Restrictions

The statement list (statement_list) cannot contain more than 255 SQL statements.

The length of an SQL statement (routine_sql_statement) must not exceed approximately 8 kB.