MySQL - Stored Functions

Table of content

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 −

ID NAME AGE ADDRESS SALARY
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

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 −

ID NAME YEAR_OF_BIRTH
1 Ramesh 1991
2 Khilan 1998
3 Kaushik 2000
4 Chaitali 1998
5 Hardik 1996
6 Komal 2001
7 Muffy 1999

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 −

ID NAME YEAR_OF_BIRTH
1 Ramesh 1991
2 Khilan 1998
3 Kaushik 2000
4 Chaitali 1998
5 Hardik 1996
6 Komal 2001
7 Muffy 1999
Advertisements