MySQL | Creating stored function

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_id fname lname start_date
1 Michael Smith 2001-06-22
2 Susan Barker 2002-09-12
3 Robert Tvler 2000-02-09
4 Susan Hawthorne 2002-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_id fname lname years
1 Michael Smith 18
2 Susan Barker 17
3 Robert Tvler 19
4 Susan Hawthorne 17



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.