A Stored Program is represented in memory by two major parts:
The code of the stored program, including SQL statements and
control flow logic (
A symbol table that describes all the local variables, cursors, labels, conditions ... declared in the code.
Individual instructions of various kind are implemented by all the
C++ classes that inherit from class
The symbol table ('symbol table' is a term used in conjunction
with compilers or interpreters, in
term 'Parsing Context' is used instead) is implemented by the C++
sp_pcontext. A Stored Program as a whole
is represented by the C++ class
contains the instructions (array
the root parsing context (member
sp_head contains concepts from
different areas: it represents both what a stored program
is, which is the topic of this section, and
how a stored program logic is used during
runtime interpretation, which is the subject of other sections.
Data Definition Language and Data Manipulation Language SQL
statements are represented as-is, by a single instruction. For
flow control statements and exception handlers, several
instructions are used to implement in the low level
sp_instr language the semantic of the SQL
Let's see an example with a stored procedure:
delimiter $$ CREATE PROCEDURE proc_1(x int) BEGIN IF x < 0 THEN INSERT INTO t1 VALUES ("negative"); ELSEIF x = 0 THEN INSERT INTO t1 VALUES ("zero"); ELSE INSERT INTO t1 VALUES ("positive"); END IF; END$$
The resulting code, displayed by
SHOW PROCEDURE CODE proc_1; Pos Instruction 0 jump_if_not 3(7) (x@0 < 0) 1 stmt 5 "INSERT INTO t1 VALUES ("negative")" 2 jump 7 3 jump_if_not 6(7) (x@0 = 0) 4 stmt 5 "INSERT INTO t1 VALUES ("zero")" 5 jump 7 6 stmt 5 "INSERT INTO t1 VALUES ("positive")"
Instructions are numbered sequentially. Position 0 is the start of the code. The position 7 that is one past the last instruction in this example represents the end of the code.
Note that the instruction
jump_if_not 3(7) at
position 0 can actually jump to three
When the evaluation of the condition "x < 0" is true, the next instruction will be position 1 (the "then" branch),
When the evaluation of the condition "x < 0" is false, the next instruction will be position 3 (the "else" branch),
When the evaluation of the condition "x < 0" results in an error, and when a continue handler exists for the error, the next instruction will be position 7, known as the "continuation" destination.
Now, let's see how exception handlers are represented. The
following code contains just a very basic handler, protecting a
END block in the SQL
CREATE PROCEDURE proc_2(x int) BEGIN SELECT "Start"; INSERT INTO t1 VALUES (1); BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SELECT "Oops"; END; INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (2); END; INSERT INTO t1 VALUES (3); SELECT "Finish"; END$$
The internal instructions for this stored procedure are:
SHOW PROCEDURE CODE proc_2; Pos Instruction 0 stmt 0 "SELECT "Start"" 1 stmt 5 "INSERT INTO t1 VALUES (1)" 2 hpush_jump 5 1 CONTINUE 3 stmt 0 "SELECT "Oops"" 4 hreturn 1 5 stmt 5 "INSERT INTO t1 VALUES (2)" 6 stmt 5 "INSERT INTO t1 VALUES (2)" 7 hpop 1 8 stmt 5 "INSERT INTO t1 VALUES (3)" 9 stmt 0 "SELECT "Finish""
Note the flow of control in the code: there is not a single if.
The couple of
hpop represent the installation and the
removal of the exception handler. The body of the exception
handler starts at position 3, whereas the code protected by the
handler starts at position 5.
hpush_jump 5 1
means: add a handler for "1" condition
sqlexception), where "1" stands for the
index of declared conditions in the parsing context, and execute
the code starting at position "5".
A parsing context is a tree of nodes, where each node contains symbols (variables, cursors, labels, ...) declared locally in the same name visibility scope.
For example, with the following SQL code:
CREATE PROCEDURE proc_3(x int, y int) BEGIN -- This is the root parsing context DECLARE v1 INT; DECLARE v2 INT; DECLARE v3 INT; IF (x > 0) THEN BEGIN -- This is the child context A DECLARE v1 INT; DECLARE v4 INT DEFAULT 100; set v4:= 1; set v1:= x; END; ELSE BEGIN -- This is the child context B DECLARE v2 INT; DECLARE v4 INT DEFAULT 200; set v4:= 2; set v2:= y; set v3:= 3; END; END IF; set v1 := 4; END$$
The parsing contexts match exactly the nesting of
The root parsing context contains parameters
y, and local
END block in
THEN part defines a child parsing
context (let's call it 'A'), that contains local variables
ELSE block defines a
parsing context (let's call it 'B') which is a child of the
root, and contains local variables
The total number of symbols is 9: 5 for the root + 2 for A + 2 for B. All the symbols are numbered internally (starting at offset 0), by walking the parsing context tree in a depth first manner, resulting in the following:
Root:x --> 0, Root:y --> 1, Root:v1 --> 2, Root:v2 --> 3, Root:v3 --> 4,
A:v1 --> 5, A:v4 --> 6,
B:v2 --> 7, B:v4 --> 8,
There is no tool to dump the parsing context tree explicitly. However, the internal numbering of symbols is apparent when printing the code:
SHOW PROCEDURE CODE proc_3; Pos Instruction 0 set v1@2 NULL 1 set v2@3 NULL 2 set v3@4 NULL 3 jump_if_not 9(14) (x@0 > 0) 4 set v1@5 NULL 5 set v4@6 100 6 set v4@6 1 7 set v1@5 x@0 8 jump 14 9 set v2@7 NULL 10 set v4@8 200 11 set v4@8 2 12 set v2@7 y@1 13 set v3@4 3 14 set v1@2 4
The points of interest are that:
There are two variables named
v1, where the variable
v1 from block A (represented as
v1@5) eclipses the variable
v1 from the root block (represented as
There are two variables named
v4, which are independent. The variable
v4 from block A is represented as
v4@6, whereas the variable
v4 from block B is represented as
The parsing context C++ class,
contains much more information related to each symbol, notably
data types of variables (unfortunately not
SHOW PROCEDURE CODE).