Stored Function Overview

A Stored Function is a defined function that is called from within an SQL statement like a regular function and returns a single value.

Creating Stored Functions

Here's a skeleton example to see a stored function in action:

First, the delimiter is changed, since the function definition will contain the regular semicolon delimiter. See Delimiters in the mariadb client for more. Then the function is named FortyTwo and defined to return a tinyin. The DETERMINISTIC keyword is not necessary in all cases (although if binary logging is on, leaving it out will throw an error), and is to help the query optimizer choose a query plan. A deterministic function is one that, given the same arguments, will always return the same result.

Next, the function body is placed between statements. It declares a tinyint, X, which is simply set to 42, and this is the result returned.

Of course, a function that doesn't take any arguments is of little use. Here's a more complex example:

This function takes an argument, price which is defined as a DECIMAL, and returns an INT.

Take a look at the page for more details.

It is also possible to create .

Stored Function Listings and Definitions

To find which stored functions are running on the server, use :

Alternatively, query the in the INFORMATION_SCHEMA database directly:

To find out what the stored function does, use :

Dropping and Updating Stored Functions

To drop a stored function, use the statement.

To change the characteristics of a stored function, use . Note that you cannot change the parameters or body of a stored function using this statement; to make such changes, you must drop and re-create the function using DROP FUNCTION and CREATE FUNCTION.

Permissions in Stored Functions

See the article .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}