MySQL - SHOW FUNCTION CODE Statement

MySQL SHOW FUNCTION CODE Statement

A function is a block of organized, reusable code that is used to perform a single, related action. Functions provide better modularity for your application and a high degree of code reusing. You can create a stored function using the CREATE FUNCTION statement.

The SHOW FUNCTION CODE Statement displays the internal code of the specified named stored function. 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 function.

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 FUNCTION CODE statement −

SHOW FUNCTION CODE func_name;

Where func_name is the name of the function for which you need the code.

Example

Suppose we have created a table named Emp in the database using the CREATE statement as shown below −

CREATE TABLE Emp(
   Name VARCHAR(255), 
   DOB DATE, 
   Location VARCHAR(255)
);

Now, let's insert some records into the Emp table −

INSERT INTO Emp VALUES 
('Amit', DATE('1970-01-08'), 'Hyderabad'),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam'),
('Sudha', DATE('1980-11-06'), 'Vijayawada');

And, if we have created a getDob() function which accepts the name of the employee, retrieves and returns the value of DOB column.

DELIMITER //
CREATE FUNCTION getDob(emp_name VARCHAR(50))
   RETURNS DATE
   DETERMINISTIC
   BEGIN
      declare dateOfBirth DATE DEFAULT CURDATE();
      select DOB into dateOfBirth from emp where Name = emp_name;
      return dateOfBirth;
   END//
DELIMITER ;

Following query returns the code of the above created procedure as a result set −

SHOW PROCEDURE CODE test;

Output

The above query produces the following output −