MySQL Functions

MySQL Stored function is a set of SQL statements that perform some operation and return some value. Many built-in string , numeric, data, grouping and some advanced functions available in MySQL.

MySQL Functions Syntax

DELIMITER $$
CREATE FUNCTION function_name(
parameter1 datatype,
parameter2 datatype,....
)
    RETURNS datatype
     [NOT] DETERMINISTIC
    BEGIN
    statements;
    END$$
DELIMITER ;

After CREATE need to follow FUNCTION with function_nameex:sum_of_num .

Write all parameters of a function inside of parentheses followed by function_name. In MySQL Functions all parameters are in parameters by default.

Enter Function return value datatype after RETURN statement.

Need to specify Function deterministic or not deterministic. If Function returns the same value for the same input parameters, in that case, it uses deterministic. If Function returns a different value for the same input parameters then define the function as not deterministic. In MySQL, bY default Function defines as not deterministic.

In between BEGIN, END block you will write your own logic.After your code must specify at least one return statement.

MySQL Function Example

Below function do sum of two numbers. In that function, I will pass n1,n2 as an integer number. The function will return sum as output.

DELIMITER $$
CREATE FUNCTION `sum_of_num`(n1 int,n2 int)
    RETURNS int
    [NOT] DETERMINISTIC
    BEGIN
    declare sum int;
    set sum=n1+n2;
return sum;
    END$$
DELIMITER ;

After creating the function you can use it by below SQL statement.

SELECT sum_of_num(10,20);

SmartMySQL Workbench has advance options for writing functions. After creating the function you can use it by simply drag function_name into query editor. It will create select function_name();drag sum_of_num function in SmartMySQL Workbench query editor.

SELECT sum_of_num;

After drag function_name simply add parameter values inside the parentheses.

The SmartMySQL Workbench is free and you can download. If any problem contacts support@smartmysql.com.