MySQL - SHOW PROCEDURE CODE Statement

MySQL SHOW PROCEDURE CODE Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. All the applications that can access Relational databases (Java, Python, PHP etc.), can access stored procedures.

Stored procedures contain IN and OUT parameters or both. They may return result sets in case you use SELECT statements. Stored procedures can return multiple result sets.

The SHOW PROCEDURE CODE Statement displays the internal code of the specified named stored procedure. To execute this statement, we need to have SHOW ROUTENE or the SELECT (global) privileges.

This statement returns the code in the form of a result set where, each row in it represents an instruction in the procedure.

This statement is available only for the debugging servers. To execute this, make sure you have built your MySQL with '--with-debug'option otherwise an error will be generated as follows −

ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is 
disabled; 
you need MySQL built with '--with-debug' to have it working

Syntax

Following is the syntax the SHOW PROCEDURE CODE statement −

SHOW PROCEDURE CODE proc_name

Where proc_name is the name of the procedure for which you need the code.

Example

Assume we have created a table named Marks in the database as shown below −

CREATE TABLE Marks(
   ID INT, 
   Mathematics INT, 
   Science INT, 
   Languages INT
);

Let us create a stored procedure myProcedure which accepts inserts a record into the above created table. −

DELIMITER //
CREATE PROCEDURE myProcedure()
   BEGIN
      DECLARE varID INT DEFAULT 1;
      DECLARE varMath INT DEFAULT 55;
      DECLARE varSci INT DEFAULT 75;
      DECLARE varLan INT DEFAULT 80;
      INSERT INTO Marks VALUES (varID, varMath, varSci, varLan);
   END//
DELIMITER ;

Let us create a stored procedure myProcedure which accepts inserts a record into the above created table. −

SHOW PROCEDURE CODE myProcedure;

Output

Once the query is executed, it will produce the following output −