16.7.1.1 Local Variables

Values of local variables in an SQL Stored Program are stored within the sp_rcontext. When the code enters a new scope, the sp_instr contains explicit statements to initialize the local variable DEFAULT value, if any. Since initialization of values is done in the code, and since no logic needs to be executed when an SQL variable goes out of scope, space allocation to represent the data does not need to follow the nesting of BEGIN/END blocks during runtime.

Another important point regarding the representation of local SQL variables is that, conceptually, a local variable can be considered to be an SQL table with a single column (of the variable type), with a single row (to represent the value).

As a result, all the local variables of a Stored Program are represented by a row in a table internally. For example, consider the following SQL code:

CREATE PROCEDURE proc_7(x int)
BEGIN
  DECLARE v1 INT;
  DECLARE v2 VARCHAR(10);
  DECLARE v3 TEXT;

  IF (x > 0) THEN
    BEGIN
      DECLARE v4 BLOB;
      DECLARE v5 VARCHAR(20);
    END;
  ELSE
    BEGIN
      DECLARE v6 DECIMAL(10, 2);
      DECLARE v7 BIGINT;
    END;
  END IF;
END$$

Internally, a temporary table is created, with the following structure:

CREATE TEMPORARY TABLE `proc_7_vars` (
  `v1` int(11) DEFAULT NULL,
  `v2` varchar(10) DEFAULT NULL,
  `v3` text,
  `v4` blob,
  `v5` varchar(20) DEFAULT NULL,
  `v6` decimal(10,2) DEFAULT NULL,
  `v7` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The real name of the table and the columns are purely internal, and the table is not accessible to regular statements for DDL or DML operations: proc_7_vars and v1 ... v7 are just a notation used in this example. The TABLE handle that implements all the local variable storage is the member sp_rcontext::m_var_table

Inside a statement, local variables in a Stored Program are represented by the dedicated C++ class Item_splocal. Item_splocal really is a proxy exposing the interface needed to suport Item, which delegates to the underlying sp_rcontext for reading or writing local variable values. The coupling between Item_splocal and sp_rcontext is based on Item_splocal::m_var_idx, which is the variable index in the symbol table computed by the parser, and maintained in sp_pcontext.


User Comments
Sign Up Login You must be logged in to post a comment.