MySQL - Stored Functions

MySQL Stored Functions

A Stored Function is a set of SQL statements that perform a specific operation and then return a single value. Similar to built-in functions in MySQL, a stored function can be called from within any MySQL statement. The MySQL CREATE FUNCTION statement is used to create both stored functions and user-defined functions.

By default, a stored function is associated with the default database. In order to use the CREATE FUNCTION statement, the user must have the CREATE ROUTINE database privilege.

Syntax

Following is the syntax for creating a new stored function −

CREATE FUNCTION function_name(
   parameters...
)
RETURN datatype [characteristics]
func_body;

where,

• function_name: It is the name of the function that we are creating. The name must not be same as the MySQL built-in function names.

• parameters: These are the list of all parameters for the function. All the parameters are IN parameters by default. We cannot specify the IN, OUT or INOUT modifiers to the parameters.

• datatype: This is the datatype of the value returned by the function.

• characteristics: The CREATE FUNCTION statement will only be accepted if at least one of the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are specified in it's declaration.

• fun_body: This contains set of MySQL statements that defines the behaviour of the function between the BEGIN and END commands.

Example

First, let us create a table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

Here, we are inserting rows into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table is displayed as −

Creating a Function −

With the following CREATE FUNCTION query, we are creating a function that returns the year of birth of the customers based on their AGE −

DELIMITER $$
CREATE FUNCTION DATE_OF_BIRTH(AGE INT) RETURNS INT DETERMINISTIC
BEGIN
   DECLARE currentdate DATE;
   SELECT CURDATE() INTO currentdate;
   RETURN year(currentdate)-AGE;
END $$
DELIMITER ;

Now, we are calling the DATE_OF_BIRTH function using the following query −

SELECT ID, NAME, DATE_OF_BIRTH(AGE) 
AS 'YEAR_OF_BIRTH'
FROM CUSTOMERS;

Output

The output for the above query is produced as given below −

Calling Stored Function From Stored Procedure

In MySQL, we can call a stored function from a stored procedure. The following statement creates a stored procedure with the name StudentDetails() that calls the DATE_OF_BIRTH() stored function.

DELIMITER $$
CREATE PROCEDURE CustomerDetails()
BEGIN
SELECT ID, NAME, DATE_OF_BIRTH(AGE) AS 'YEAR_OF_BIRTH'
FROM CUSTOMERS;
END $$
DELIMITER ;

Here, we are calling the CustomerDetails() stored procedure using CALL keyword −

CALL CustomerDetails();

Output

The output for the above query is produced as given below −