Examples Of

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

Examples of `EXECUTE STATEMENT` with parameters

With named parameters:

...
DECLARE license_num VARCHAR(15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license '
  'FROM cars '
  'WHERE driver = :driver AND location = :loc';
BEGIN
  -- ...
  EXECUTE STATEMENT (stmt)
    (driver := current_driver,
     loc := current_location)
  ON EXTERNAL connect_string
  INTO license_num;

The same code with positional parameters:

DECLARE license_num VARCHAR (15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license '
  'FROM cars '
  'WHERE driver = ? AND location = ?';
BEGIN
  -- ...
  EXECUTE STATEMENT (stmt)
    (current_driver, current_location)
  ON EXTERNAL connect_string
  INTO license_num;

Use of EXCESS to allow named parameters to be unused (note: this is a FOR EXECUTE STATEMENT):

CREATE PROCEDURE P_EXCESS (A_ID INT, A_TRAN INT = NULL, A_CONN INT = NULL)
  RETURNS (ID INT, TRAN INT, CONN INT)
AS
DECLARE S VARCHAR(255) = 'SELECT * FROM TTT WHERE ID = :ID';
DECLARE W VARCHAR(255) = '';
BEGIN
  IF (A_TRAN IS NOT NULL)
  THEN W = W || ' AND TRAN = :a';

  IF (A_CONN IS NOT NULL)
  THEN W = W || ' AND CONN = :b';

  IF (W <> '')
  THEN S = S || W;

  -- could raise error if TRAN or CONN is null
  -- FOR EXECUTE STATEMENT (:S) (a := :A_TRAN, b := A_CONN, id := A_ID)

  -- OK in all cases
  FOR EXECUTE STATEMENT (:S) (EXCESS a := :A_TRAN, EXCESS b := A_CONN, id := A_ID)
    INTO :ID, :TRAN, :CONN
      DO SUSPEND;
END

`WITH {AUTONOMOUS | COMMON} TRANSACTION`

By default, the executed SQL statement runs within the current transaction.Using WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction.This separate transaction will be committed when the statement was executed without errors and rolled back otherwise.

The clause WITH COMMON TRANSACTION uses the current transaction whenever possible;this is the default behaviour.If the statement must run in a separate connection, an already started transaction within that connection is used, if available.Otherwise, a new transaction is started with the same parameters as the current transaction.Any new transactions started under the “COMMON” regime are committed or rolled back with the current transaction.

Examples Of

Examples of EXECUTE STATEMENT with parameters

WITH {AUTONOMOUS | COMMON} TRANSACTION

Examples of `EXECUTE STATEMENT` with parameters

`WITH {AUTONOMOUS | COMMON} TRANSACTION`