SQL Guide : solidDB® SQL statements : CREATE PROCEDURE : execute_statement
  
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
Executing prepared SQL statements
The SQL statement is executed with the statement
EXEC SQL EXECUTE cursor [opt_using] [opt_into]
where the optional opt-using specification has the syntax:
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.
The optional opt_into specification has the syntax
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.
Using transactions
The following statement is used to terminate transactions:
EXEC SQL {COMMIT | ROLLBACK} WORK
The following statement is used to control the type of transactions:
EXEC SQL SET TRANSACTION {READ ONLY | READ WRITE}
Closing and dropping cursors
When you are finished using a cursor, you can either CLOSE the cursor or CLOSE and DROP the cursor.
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.
Checking for errors
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.
The error string of latest failed SQL statement is stored into variable SQLERRSTR.
See also
CREATE PROCEDURE