
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
What are MySQL stored functions and how can we create them?
MySQL stored function is basically a special kind of stored program that returns a single value. We can use stored functions in MySQL to encapsulate mainly simple formulas or business rules that are reusable among SQL statements or stored programs. Other than that stored functions are used in SQL statements whenever an expression is used.
This feature of stored functions is different from stored procedures. Actually, a stored function parameter is equivalent of the IN parameter of the stored procedure as the functions use RETURN keyword to determine what is passed back. Its syntax can be as follows −
Syntax
CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body func_parameter: param_name type type: Any valid MySQL data type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement
Functions have only input parameters and return a value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller.
- Related Articles
- What are MySQL Temporary Tables? How can we create them?
- What is stored procedure and how can we create MySQL stored procedures?
- How can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
- How can we see the list of stored procedures and stored functions in a particular MySQL database?
- How can we create MySQL stored procedure to calculate the factorial?
- What are weeds? How can we control them?
- How can we invoke MySQL stored procedure?
- How can we see only the list of stored functions in a particular MySQL database?
- How can we combine functions in MySQL?
- How can we create recursive functions in Python?
- How can we alter a MySQL stored procedure?
- How can we drop a MySQL stored procedure?
- How can we alter a MySQL stored function?
- How can we distinguish between MySQL IFNULL() and NULLIF() functions?
- How MySQL stored GENERATED COLUMNS can work with built-in functions?
