Stored Functions

Stored Functions

A stored function is a kind of a stored program that can only return one value. It can be used in SQL statements in place of an expression. Common formulas or expressions that are used over and over again can be written in stored functions to make the code reusable. However, if a stored function that queries data from tables is used in a SQL statement, then it may slow down the speed of the query.

There are a number of differences when we compare stored functions to stored procedures. Stored procedures can call stored functions but the opposite is not possible. Stored functions can be used in SQL statements but stored procedures can only be called with the CALL keyword. That is why stored procedures are stored in compiled form where as stored functions are parsed and compiled at runtime. Return value is optional in stored procedures but a must in stored functions. Moreover, stored functions can only return one value but there is no such restriction on the number of return values in stored procedures. Stored functions only support IN parameter type while stored procedures can have IN, OUT and INOUT parameters. Error handling is not possible in stored functions.

The CREATE FUNCTION statement is used to create a stored function. The parameter list contains all the parameters of the function. Unlike stored procedures where the parameters could be IN, OUT or INOUT type, a stored function only takes IN parameters so there is no need to specify the type of parameters in the parameter list. Since the stored function can return only one value, the data type of the return value is specified after the RETURN keyword.

A stored function can be deterministic or non deterministic meaning that for the same input parameters, the result will either be the same or different. This can be specified by using the keywords DETERMINISTIC or NOT DETERMINISTIC. If this keyword is not specified, the type is set to NOT DETERMINISTIC by default. The function body must have at least one RETURN statement. When control reaches it, the stored function exits.

To view all functions in a database, SHOW FUNCTION STATUS statement is used. This results returned can be narrowed down based on the LIKE operator or any other condition specified in the optional WHERE clause. To delete a stored function, DROP FUNCTION keywords with the optional IF EXISTS clause is used.

Syntax

DELIMITER **

CREATE FUNCTION function_name(parameter_list)

RETURNS datatype

[NOT] DETERMINISTIC

BEGIN

function body

END **

DELIMITER ;

SHOW FUNCTION STATUS [LIKE ‘pattern’ | WHERE condition];

DROP FUNCTION [IF EXISTS] function_name;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command ./DataJek/Lessons/59lesson.sh and wait for the mysql prompt to start-up.