Scope of Reference of SPL Variables and Exception Handlers

The BEGIN and END keywords can limit the scope of SPL variables and exception handlers. Declarations of variables and definitions of exception handlers inside a BEGIN and END statement block are local to that statement block and are not visible from outside the statement block. The following code uses a BEGIN and END statement block to delimit the scope of reference of variables:

CREATE DATABASE demo;
CREATE TABLE tracker (
   who_submitted CHAR(80), -- Show what code was running.
   value INT,              -- Show value of the variable.
   sequential_order SERIAL -- Show order of statement execution.
    );
CREATE PROCEDURE demo_local_var()
DEFINE var1, var2 INT;
   LET var1 = 1;
   LET var2 = 2;
   INSERT INTO tracker (who_submitted, value) 
    VALUES ('var1 param before sub-block', var1);
BEGIN
   DEFINE var1 INT;   -- same name as global parameter.
   LET var1 = var2;
   INSERT INTO tracker (who_submitted, value) 
   VALUES ('var1 var defined inside the "IF/BEGIN".', var1);
END
INSERT INTO tracker (who_submitted, value) 
   VALUES ('var1 param after sub-block (unchanged!)', var1);
END PROCEDURE;
EXECUTE PROCEDURE demo_local_var();
SELECT sequential_order, who_submitted, value FROM tracker 
 ORDER BY sequential_order;

This example declares three variables, two of which are named var1. (Name conflicts are created here to illustrate which variables are visible. Using the same name for different variables is generally not recommended, because conflicting names of variables can make your code more difficult to read and to maintain.)

Because of the statement block, only one var1 variable is in scope at a time.

The var1 variable that is declared inside the statement block is the only var1 variable that can be referenced from within the statement block.

The var1 variable that is declared outside the statement block is not visible within the statement block. Because it is out of scope, it is unaffected by the change in value to the var1 variable that takes place inside the statement block. After all the statements run, the outer var1 still has a value of 1.

The var2 variable is visible within the statement block because it was not superseded by a name conflict with a block-specific variable.