Skip to content
Related Articles

Related Articles

MySQL | Creating stored function
  • Last Updated : 27 Feb, 2019

The CREATE FUNCTION statement is used for creating a stored function and user-defined functions. A stored function is a set of SQL statements that perform some operation and return a single value.

Just like Mysql in-built function, it can be called from within a Mysql statement.

By default, the stored function is associated with the default database.

The CREATE FUNCTION statement require CREATE ROUTINE database privilege.

Syntax:
The syntax for CREATE FUNCTION statement in Mysql is:



CREATE FUNCTION function_name(func_parameter1, func_parameter2, ..)
          RETURN datatype [characteristics]
          func_body

Parameters used:

  1. function_name:
    It is the name by which stored function is called. The name should not be same as native(built_in) function. In order to associate routine explicitly with a specific database function name should be given as database_name.func_name.
  2. func_parameter:
    It is the argument whose value is used by the function inside its body. You can’t specify to these parameters IN, OUT, INOUT. The parameter declaration inside parenthesis is provided as func_parameter type. Here, type represents a valid Mysql datatype.
  3. datatype:
    It is datatype of value returned by function.
  4. characteristics:
    The CREATE FUNCTION statement is accepted only if at least one of the characterisitics { DETERMINISTIC, NO SQL, or READS SQL DATA } is specified in its declaration.

func_body is the set of Mysql statements that perform operation. It’s structure is as follows:

BEGIN

        Mysql Statements

        RETURN expression;
END

The function body must contain one RETURN statement.

Example:

Consider following Employee Table-

emp_idfnamelnamestart_date
1MichaelSmith2001-06-22
2SusanBarker2002-09-12
3RobertTvler2000-02-09
4SusanHawthorne2002-04-24

We have to find the number of years the employee has been in the company-

DELIMITER //

CREATE FUNCTION no_of_years(date1 date) RETURNS int DETERMINISTIC
BEGIN
 DECLARE date2 DATE;
  Select current_date()into date2;
  RETURN year(date2)-year(date1);
END 

//

DELIMITER ;

Calling of above function:

Select emp_id, fname, lname, no_of_years(start_date) as 'years' from employee;

Output:

emp_idfnamelnameyears
1MichaelSmith18
2SusanBarker17
3RobertTvler19
4SusanHawthorne17

My Personal Notes arrow_drop_up
Recommended Articles
Page :