{"id":3145,"date":"2014-02-15T08:01:03","date_gmt":"2014-02-15T16:01:03","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3145"},"modified":"2023-12-27T02:11:26","modified_gmt":"2023-12-27T09:11:26","slug":"mysql-stored-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/mysql-stored-function\/","title":{"rendered":"MySQL Stored Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a stored function using the CREATE FUNCTION<\/code> statement.<\/p>\n\n\n\n

Introduction to MySQL Stored Function<\/h2>\n\n\n\n

A stored function is a specialized type of stored program designed to return a single value. Typically, you use stored functions to encapsulate common formulas or business rules, making them reusable across SQL statements or other stored programs.<\/p>\n\n\n\n

Unlike a stored procedure<\/a>, you can use a stored function in SQL statements wherever you use an expression. This enhances the readability and maintainability of the procedural code.<\/p>\n\n\n\n

To create a stored function, you use the CREATE FUNCTION<\/code> statement. The following illustrates the basic syntax for creating a new stored function:<\/p>\n\n\n

DELIMITER $$\n\nCREATE<\/span> FUNCTION<\/span> function_name(\n    param1,\n    param2,\u2026\n)\nRETURNS<\/span> datatype\n[NOT<\/span>] DETERMINISTIC<\/span>\nBEGIN<\/span>\n -- statements<\/span>\nEND<\/span> $$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name of the stored function that you want to create after CREATE FUNCTION<\/code> keywords.<\/p>\n\n\n\n
By default, stored functions consider all parameters as IN<\/code> parameters. You cannot specify IN<\/code> , OUT<\/code> or INOUT<\/code> modifiers to parameters<\/p>\n\n\n\n
Third, specify the data type of the return value in the RETURNS<\/code> statement, which can be any valid MySQL data types<\/a>.<\/p>\n\n\n\n
Fourth, determine whether a function is deterministic or not using the DETERMINISTIC<\/code> keyword.<\/p>\n\n\n\n
A deterministic function always returns the same result for the same input parameters, while a non-deterministic function produces different results for the same input parameters.<\/p>\n\n\n\n
If you don’t use DETERMINISTIC<\/code> or NOT DETERMINISTIC<\/code>, MySQL defaults to the NOT DETERMINISTIC<\/code> option.<\/p>\n\n\n\n
Finally, write the code in the body of the stored function in the BEGIN...END<\/code> block. <\/p>\n\n\n\n
Inside the body section, you need to include at least one RETURN<\/code> statement. The RETURN<\/code> statement sends a value to the calling programs. <\/p>\n\n\n\n
Upon reaching the RETURN<\/code> statement, the stored function terminates the execution of the stored function immediately.<\/p>\n\n\n\n
MySQL CREATE FUNCTION example<\/h2>\n\n\n\n
Let’s take an example of creating a stored function. We will use the customers<\/code> table in the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following CREATE FUNCTION<\/code> statement creates a function that returns the customer level based on credit:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> FUNCTION<\/span> CustomerLevel(\n\tcredit DECIMAL<\/span>(10<\/span>,2<\/span>)\n) \nRETURNS<\/span> VARCHAR<\/span>(20<\/span>)\nDETERMINISTIC<\/span>\nBEGIN<\/span>\n    DECLARE<\/span> customerLevel VARCHAR<\/span>(20<\/span>);\n\n    IF credit > 50000 THEN\n\t\tSET<\/span> customerLevel = 'PLATINUM'<\/span>;\n    ELSEIF (credit >= 50000 AND \n\t\t\tcredit <= 10000) THEN\n        SET<\/span> customerLevel = 'GOLD'<\/span>;\n    ELSEIF credit < 10000 THEN\n        SET<\/span> customerLevel = 'SILVER'<\/span>;\n    END<\/span> IF<\/span>;\n\t-- return the customer level<\/span>\n\tRETURN (customerLevel);\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Once the function is created, you can view it in MySQL Workbench under the Functions<\/strong> section:<\/p>\n\n\n\n
\"mysql<\/figure>\n\n\n\n
Or, you can view all stored functions in the current classicmodels<\/code> database by using the SHOW FUNCTION STATUS<\/code> as follows:<\/p>\n\n\n
SHOW<\/span> FUNCTION<\/span> STATUS<\/span> WHERE<\/span> db = 'classicmodels'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mysql<\/figure>\n\n\n\n
Calling a stored function in an SQL statement<\/h3>\n\n\n\n
The following statement illustrates how to call the CustomerLevel<\/code> stored function:<\/p>\n\n\n
SELECT<\/span> \n    customerName, \n    CustomerLevel(creditLimit)\nFROM<\/span>\n    customers\nORDER<\/span> BY<\/span> \n    customerName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mysql<\/figure>\n\n\n\n
Calling a stored function in a stored procedure<\/h3>\n\n\n\n
The following statement creates a new stored procedure<\/a> that calls the CustomerLevel()<\/code> stored function:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> PROCEDURE<\/span> GetCustomerLevel(\n    IN<\/span>  customerNo INT<\/span>,  \n    OUT<\/span> customerLevel VARCHAR<\/span>(20<\/span>)\n)\nBEGIN<\/span>\n\n\tDECLARE<\/span> credit DEC<\/span>(10<\/span>,2<\/span>) DEFAULT<\/span> 0<\/span>;\n    \n    -- get credit limit of a customer<\/span>\n    SELECT<\/span> \n\t\tcreditLimit \n\tINTO<\/span> credit\n    FROM<\/span> customers\n    WHERE<\/span> \n\t\tcustomerNumber = customerNo;\n    \n    -- call the function <\/span>\n    SET<\/span> customerLevel = CustomerLevel(credit);\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following illustrates how to call the GetCustomerLevel()<\/code> stored procedure:<\/p>\n\n\n
CALL<\/span> GetCustomerLevel(131<\/span>,@customerLevel);\nSELECT<\/span> @customerLevel;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----------------+\r\n| @customerLevel |\r\n+----------------+\r\n| PLATINUM       |\r\n+----------------+\r\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It’s important to notice that if a stored function contains SQL statements that retrieve data from tables, then you should avoid using it in other SQL statements; otherwise, the stored function may slow down the query speed.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • A stored function is a reusable and encapsulated piece of code in a database that performs a specific task and returns a single value. <\/li>\n\n\n\n
  • Use the CREATE FUNCTION<\/code> statement to create a stored function.<\/li>\n\n\n\n
  • Use stored functions to enhance the modularity and efficiency of SQL statements.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, list all parameters<\/a> of the stored function inside the parentheses followed by the function name.<\/p>\n\n\n\n