execute_statement

execute_statement ::= EXEC SQL EXECUTE cursor_name
  [USING (variable [, variable ...])]
  [INTO (variable [, variable ...])] |
  EXEC SQL CLOSE cursor_name |
  EXEC SQL DROP cursor_name |
  EXEC SQL {COMMIT | ROLLBACK} WORK |
  EXEC SQL SET TRANSACTION {READ ONLY | READ WRITE} |
  EXEC SQL WHENEVER SQLERROR {ABORT | ROLLBACK [WORK], ABORT}
  EXEC SEQUENCE sequence_name.CURRENT INTO variable |
  EXEC SEQUENCE sequence_name.NEXT INTO variable |
  EXEC SEQUENCE sequence_name SET VALUE USING variable

cursor_name ::= literal

EXEC SQL EXECUTE cursor [opt_using] [opt_into]

USING (variable_list)

where variable_list contains a list of procedure variables or parameters separated by a comma. These variables are input parameters for the SQL statement. The SQL input parameters are marked with the standard question mark syntax in the prepare statement. If the SQL statement has no input parameters, the USING specification is ignored.

INTO (variable_list)

where variable_list contains the variables that the column values of the SQL SELECT statement are stored into. The INTO specification is effective only for SQL SELECT statements.

After the execution of UPDATE, INSERT and DELETE statements an additional variable is available to check the result of the statement. Variable SQLROWCOUNT contains the number of rows affected by the last statement.

EXEC SQL {COMMIT | ROLLBACK} WORK

EXEC SQL SET TRANSACTION {READ ONLY | READ WRITE}

If you are likely to reuse the cursor and want to improve performance, you should only CLOSE the cursor. When you close a cursor, all the memory allocated during the execute phase is released but the cursor is kept in prepared state.

Dropping a cursor frees all allocated resources. The next time you use the dropped cursor, it needs to prepared.

The result of each EXEC SQL statement executed inside a procedure body is stored into the variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, a value one is stored into SQLSUCCESS. After a failed SQL statement, a value zero is stored into SQLSUCCESS.

EXEC SQL WHENEVER SQLERROR {ABORT | [ROLLBACK [WORK], ABORT}

is used to decrease the need for IF NOT SQLSUCCESS THEN tests after every executed SQL statement in a procedure. When this statement is included in a stored procedure all return values of executed statements are checked for errors. If statement execution returns an error, the procedure is automatically aborted. Optionally the transaction can be rolled back.