Utilize stored functions in MariaDB Server. This section details creating, using, and managing user-defined functions to extend SQL capabilities and streamline data manipulation.
The DROP FUNCTION statement removes a stored function from the database, deleting its definition and associated privileges.
The DROP FUNCTION statement is used to drop a or a user-defined function (UDF). That is, the specified routine is removed from the server, along with all privileges specific to the function. You must have the ALTER ROUTINE for the routine in order to drop it. If the server system variable is set, both the ALTER ROUTINE and EXECUTE privileges are granted automatically to the routine creator - see .
The IF EXISTS clause is a MySQL/MariaDB extension. It prevents an error from occurring if the function does not exist. ANOTE is produced that can be viewed with .
For dropping a (UDF), see .
This page is licensed: GPLv2, originally from
Stored Aggregate Functions allow users to create custom aggregate functions that process a sequence of rows and return a single summary result.
Aggregate functions are functions that are computed over a sequence of rows and return one result for the sequence of rows.
Creating a custom aggregate function is done using the CREATE FUNCTION statement with two main differences:
The addition of the AGGREGATE keyword, so CREATE AGGREGATE FUNCTION
The FETCH GROUP NEXT ROW instruction inside the loop
Oracle PL/SQL compatibility using SQL/PL is provided
Stored aggregate functions were a project by Varun Gupta.
First a simplified example:
A non-trivial example that cannot easily be rewritten using existing functions:
This uses the same marks table as created above.
This page is licensed: CC BY-SA / Gnu FDL
This page explains the privileges required to create, alter, execute, and drop stored routines, including the automatic grants for creators.
It's important to give careful thought to the privileges associated with stored functions and stored procedures. The following is an explanation of how they work.
To create a stored routine, the CREATE ROUTINE privilege is needed. The SUPER privilege is required if a DEFINER is declared that's not the creator's account (see below). The SUPER privilege is also required if statement-based binary logging is used. See for more details.
To make changes to, or drop, a stored routine, the privilege is needed. The creator of a routine is temporarily granted this privilege if they attempt to change or drop a routine they created, unless the variable is set to 0 (it defaults to 1).
The SUPER privilege is also required if statement-based binary logging is used. See for more details.
To run a stored routine, the privilege is needed. This is also temporarily granted to the creator if they attempt to run their routine unless the variable is set to 0.
The (by default DEFINER) specifies what privileges are used when a routine is called. If SQL SECURITY is INVOKER, the function body are evaluated using the privileges of the user calling the function. If SQL SECURITY is DEFINER, the function body is always evaluated using the privileges of the definer account.
If left out, the DEFINER is treated as the account that created the stored routine or view. If the account creating the routine has the SUPER privilege, another account can be specified as the DEFINER.
This clause specifies the context the stored routine or view will run as. It can take two values - DEFINER or INVOKER. DEFINER is the account specified as the DEFINER when the stored routine or view was created (see the section above). INVOKER is the account invoking the routine or view.
As an example, let's assume a routine, created by a superuser who's specified as the DEFINER, deletes all records from a table. If SQL SECURITY=DEFINER, anyone running the routine, regardless of whether they have delete privileges, are able to delete the records. If SQL SECURITY = INVOKER, the routine will only delete the records if the account invoking the routine has permission to do so.
INVOKER is usually less risky, as a user cannot perform any operations they're normally unable to. However, it's not uncommon for accounts to have relatively limited permissions, but be specifically granted access to routines, which are then invoked in the DEFINER context.
All privileges that are specific to a stored routine are dropped when a or DROP ROUTINE is run. However, if a or is used to drop and replace and the routine, any privileges specific to that routine will not be dropped.
- maria.com post on what to do after you've dropped a user, and now want to change the DEFINER on all database objects that currently have it set to this dropped user.
This page is licensed: CC BY-SA / Gnu FDL
This page details the restrictions on stored functions, such as the inability to return result sets or use transaction control statements.
The following restrictions apply to stored functions.
All of the restrictions listed in Stored Routine Limitations.
Any statements that return a result set are not permitted. For example, a regular SELECTs is not permitted, but a SELECT INTO is. A cursor and FETCH statement is permitted.
FLUSH statements are not permitted.
Statements that perform explicit or implicit commits or rollbacks are not permitted.
Cannot be used recursively.
Cannot make changes to a table that is already in use (reading or writing) by the statement invoking the stored function.
Cannot refer to a temporary table multiple times under different aliases, even in different statements.
ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT statement which are in a stored function cannot refer to a savepoint which has been defined out of the current function.
Prepared statements (, , ) cannot be used, and therefore nor can statements be constructed as strings and then executed.
This page is licensed: CC BY-SA / Gnu FDL
A Stored Function is a set of SQL statements that can be called by name, accepts parameters, and returns a single value, enhancing SQL with custom logic.
A Stored Function is a defined function that is called from within an SQL statement like a regular function and returns a single value.
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 .
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 :
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.
See the article .
This page is licensed: CC BY-SA / Gnu FDL
DROP FUNCTION [IF EXISTS] f_nameDELIMITER //
CREATE FUNCTION FortyTwo() RETURNS TINYINT DETERMINISTIC
BEGIN
DECLARE x TINYINT;
SET x = 42;
RETURN x;
END
//
DELIMITER ;DEFINERThe creator of a routine is the account that ran the CREATE FUNCTION or CREATE PROCEDURE statement, regardless of whether a DEFINER is provided. The definer is by default the creator unless otherwise specified.
The server automatically changes the privileges in the mysql.proc table as required, but will not look out for manual changes.
DROP FUNCTION hello;
Query OK, 0 rows affected (0.042 sec)
DROP FUNCTION hello;
ERROR 1305 (42000): FUNCTION test.hello does not exist
DROP FUNCTION IF EXISTS hello;
Query OK, 0 rows affected, 1 warning (0.000 sec)
SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1305 | FUNCTION test.hello does not exist |
+-------+------+------------------------------------+CREATE AGGREGATE FUNCTION function_name (parameters) RETURNS return_type
BEGIN
ALL types of declarations
DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN return_val;
LOOP
FETCH GROUP NEXT ROW; // fetches next row FROM TABLE
other instructions
END LOOP;
ENDSET sql_mode=Oracle;
DELIMITER //
CREATE AGGREGATE FUNCTION function_name (parameters) RETURN return_type
declarations
BEGIN
LOOP
FETCH GROUP NEXT ROW; -- fetches next row from table
-- other instructions
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN return_val;
END //
DELIMITER ;CREATE TABLE marks(stud_id INT, grade_count INT);
INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8);
SELECT * FROM marks;
+---------+-------------+
| stud_id | grade_count |
+---------+-------------+
| 1 | 6 |
| 2 | 4 |
| 3 | 7 |
| 4 | 5 |
| 5 | 8 |
+---------+-------------+
DELIMITER //
CREATE AGGREGATE FUNCTION IF NOT EXISTS aggregate_count(x INT) RETURNS INT
BEGIN
DECLARE count_students INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND
RETURN count_students;
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students = count_students+1;
END IF;
END LOOP;
END //
DELIMITER ;DELIMITER //
CREATE AGGREGATE FUNCTION medi_int(x INT) RETURNS DOUBLE
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
DECLARE res DOUBLE;
DECLARE cnt INT DEFAULT (SELECT COUNT(*) FROM tt);
DECLARE lim INT DEFAULT (cnt-1) DIV 2;
IF cnt % 2 = 0 THEN
SET res = (SELECT AVG(a) FROM (SELECT a FROM tt ORDER BY a LIMIT lim,2) ttt);
ELSE
SET res = (SELECT a FROM tt ORDER BY a LIMIT lim,1);
END IF;
DROP TEMPORARY TABLE tt;
RETURN res;
END;
CREATE TEMPORARY TABLE tt (a INT);
LOOP
FETCH GROUP NEXT ROW;
INSERT INTO tt VALUES (x);
END LOOP;
END //
DELIMITER ;SET sql_mode=Oracle;
DELIMITER //
CREATE AGGREGATE FUNCTION aggregate_count(x INT) RETURN INT AS count_students INT DEFAULT 0;
BEGIN
LOOP
FETCH GROUP NEXT ROW;
IF x THEN
SET count_students := count_students+1;
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN count_students;
END aggregate_count //
DELIMITER ;
SELECT aggregate_count(stud_id) FROM marks;SELECT FortyTwo();
+------------+
| FortyTwo() |
+------------+
| 42 |
+------------+DELIMITER //
CREATE FUNCTION VatCents(price DECIMAL(10,2)) RETURNS INT DETERMINISTIC
BEGIN
DECLARE x INT;
SET x = price * 114;
RETURN x;
END //
Query OK, 0 rows affected (0.04 sec)
DELIMITER ;SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
Db: test
Name: VatCents
Type: FUNCTION
Definer: root@localhost
Modified: 2013-06-01 12:40:31
Created: 2013-06-01 12:40:31
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE
ROUTINE_TYPE='FUNCTION';
+--------------+
| ROUTINE_NAME |
+--------------+
| VatCents |
+--------------+SHOW CREATE FUNCTION VatCents\G
*************************** 1. row ***************************
Function: VatCents
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `VatCents`(price DECIMAL(10,2)) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE x INT;
SET x = price * 114;
RETURN x;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciDROP FUNCTION FortyTwo;