LEAVE

Example

DECLARE variable_name INT;
DECLARE exit handler FOR NOT FOUND SET variable_name = 1;
START TRANSACTION;
  SELECT * FROM table_name;
  IF variable_name = 1 THEN
    ROLLBACK;
    LEAVE proc_name;
  END IF;
COMMIT;

Output

No output will be displayed if the condition is met and the LEAVE statement is executed.

Explanation

This example represents a transaction using the LEAVE statement in a stored procedure named proc_name. Initially variable_name is declared, also an exit handler is declared which sets variable_name = 1 if a NOT FOUND situation occurs.

Then, it starts a transaction and it selects data from table_name. If the variable_name = 1 condition is met (which is possible if NOT FOUND situation occurred), it rolls back the transaction and using the LEAVE statement, control is immediately transferred out of the procedure proc_name. Therefore, no output will be displayed if the LEAVE statement is executed.

Example

BEGIN TRANSACTION;
BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    -- Test XACT_STATE:
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should
    --     be rolled back.
    -- XACT_STATE = 0 means that there is no transaction and
    --     a commit or rollback operation would generate an error.

    SELECT
        CASE XACT_STATE()
            WHEN 1 THEN 'The transaction is committable.'
            WHEN -1 THEN
                'The transaction is uncommittable. Rolling back transaction.'
                ROLLBACK TRANSACTION;
            WHEN 0 THEN 'There is no transaction.'
        END;
    END CATCH;

Output

The transaction is uncommittable. Rolling back transaction.

Explanation

The provided example demonstrates the use of the T-SQL XACT_STATE function within the CATCH block of a TRY...CATCH construct. The XACT_STATE function returns a value indicating whether the current request has an active user transaction, and whether that transaction is capable of being committed. If XACT_STATE returns -1, the transaction is uncommittable and the code initiates a rollback. If XACT_STATE returns 1, the transaction may be committed, and if XACT_STATE returns 0, there is no active transaction. The output message reflects these conditions.

Example

DECLARE
  my_number NUMBER := 5;
BEGIN
  LOOP
    my_number := my_number - 1;
    IF my_number = 2 THEN
      LEAVE;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (my_number);
END;
/

Output

2

Explanation

In the above SQL block, a loop decreases the value of the my_number variable by 1 at each iteration. As dictated by the IF statement, when my_number equals to 2, the LEAVE statement will be executed, hence exiting the loop, and printing 2 to the DBMS output.