MySQL Internals Manual  /  ...  /  Instructions

15.3.1 Instructions

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 construct.

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, is:

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 locations:

  • 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 BEGIN/END block in the SQL logic:

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 hpush_jump / 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".


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