SHOW PROCEDURE CODE proc_name
This statement is a MySQL extension that is available only for
servers that have been built with debugging support. It displays
a representation of the internal implementation of the named
stored procedure. A similar statement, SHOW
FUNCTION CODE
, displays information about stored
functions (see Section 15.7.7.20, “SHOW FUNCTION CODE Statement”).
To use either statement, you must be the user named as the
routine DEFINER
, have the
SHOW_ROUTINE
privilege, or have
the SELECT
privilege at the
global level.
If the named routine is available, each statement produces a
result set. Each row in the result set corresponds to one
“instruction” in the routine. The first column is
Pos
, which is an ordinal number beginning
with 0. The second column is Instruction
,
which contains an SQL statement (usually changed from the
original source), or a directive which has meaning only to the
stored-routine handler.
mysql> DELIMITER //
mysql> CREATE PROCEDURE p1 ()
BEGIN
DECLARE fanta INT DEFAULT 55;
DROP TABLE t2;
LOOP
INSERT INTO t3 VALUES (fanta);
END LOOP;
END//
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW PROCEDURE CODE p1//
+-----+----------------------------------------+
| Pos | Instruction |
+-----+----------------------------------------+
| 0 | set fanta@0 55 |
| 1 | stmt 9 "DROP TABLE t2" |
| 2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
| 3 | jump 2 |
+-----+----------------------------------------+
4 rows in set (0.00 sec)
mysql> CREATE FUNCTION test.hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW FUNCTION CODE test.hello;
+-----+---------------------------------------+
| Pos | Instruction |
+-----+---------------------------------------+
| 0 | freturn 254 concat('Hello, ',s@0,'!') |
+-----+---------------------------------------+
1 row in set (0.00 sec)
In this example, the nonexecutable BEGIN
and
END
statements have disappeared, and for the
DECLARE
statement,
only the executable part appears (the part where the default is
assigned). For each statement that is taken from source, there
is a code word variable_name
stmt
followed by a type (9
means DROP
, 5 means
INSERT
, and so on). The final row
contains an instruction jump 2
, meaning
GOTO instruction #2
.