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 characteristics { 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-

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: 

T

Tanvi_Garg

Follow

Improve

Article Tags :

• DBMS
• SQL
• mysql