MySQL - SHOW CREATE FUNCTION Statement

MySQL SHOW CREATE FUNCTION Statement

MySQL provides a set of built-in function which performs particular tasks for example the CURDATE() function returns the current date.

The SHOW CREATE FUNCTION statement displays the (string) query used to create the specified function.

Syntax

Following is the syntax of the SHOW CREATE FUNCTION statement −

SHOW CREATE FUNCTION functionName;

Where, functionName is the name of the function for which you need the create statement.

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 us 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');

Assume we have created a function named getDob() which accepts the name of the employee, retrieves and returns the value of DOB column −

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

Following query retrieves the CREATE statement used to create the above function −

SHOW CREATE FUNCTION getDob\G;

Output

Following is the output of the above query −

*********** 1. row ***********
       Function: getDob
       sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION 
                 `getDob`(emp_name VARCHAR(50)) RETURNS date
DETERMINISTIC
BEGIN
   declare dateOfBirth DATE;
   select DOB into dateOfBirth from test.emp where Name = emp_name;
   return dateOfBirth;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)