Introduction
Continuing with our series on Stored Procedures and Functions (see part 1, part 2, or part 3), this month we focus on Stored Functions. Most of what we have covered in those earlier tutorials is relevant here, so I suggest you read those first if you haven’t already.
What’s a Stored Function
If procedural programming is new to you, you may be wondering what the difference is between a Stored Procedure and a Stored Function. Not too much really. A function always returns a result, and can be called inside an SQL statement just like ordinary SQL functions. A function parameter is the equivalent of the IN procedure parameter, as functions use the RETURN keyword to determine what is passed back. Stored functions also have slightly more limitations in what SQL statements they can run than stored procedures.
A Stored Function example
Here is an example of a stored function:
mysql> DELIMITER | mysql> CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT) RETURNS INT DETERMINISTIC BEGIN DECLARE avg INT; SET avg = (n1+n2+n3*2+n4*4)/8; RETURN avg; END| Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEIGHTED_AVERAGE(70,65,65,60)G *************************** 1. row *************************** WEIGHTED_AVERAGE(70,65,65,60): 63 1 row in set (0.00 sec)
As mentioned in the first stored procedures tutorial, we declare the “|” symbol as a delimiter, so that our function body can use ordinary “;” characters. This function returns a weighted average, as could be used to determine an overall result for a subject. The third test score is weighted twice as heavily as the first and second scores, while the fourth score counts four times as much. We also make use of the DECLARE (declaring a variable) and DETERMINISTIC (telling MySQL that, given the same input, the function will always return the same result) statements, as discussed in earlier tutorials.
Accessing tables in stored functions
Stored functions in early versions of MySQL 5.0 (< 5.0.10) could not reference tables except in a very limited capacity. That limited their usefulness to a large degree. Newer versions can now do so, but still cannot make use of statements that return a result set. So, no SELECT queries returning result sets from a table. However, you can get around this by using SELECT INTO. For the next example, we create a table allowing us to store 4 marks, and a name. Then we will define a new WEIGHTED_AVERAGE function to make use of the dynamic data from the table.
mysql> CREATE TABLE sfdata(mark1 INT,mark2 INT,mark3 INT,mark4 INT,name VARCHAR(50)) mysql> INSERT INTO sfdata VALUES(70,65,65,60,'Mark')| mysql> INSERT INTO sfdata VALUES(95,94,75,50,'Pavlov')| mysql> CREATE FUNCTION WEIGHTED_AVERAGE2 (v1 VARCHAR(50)) RETURNS INT DETERMINISTIC BEGIN DECLARE i1,i2,i3,i4,avg INT; SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sfdata WHERE name=v1; SET avg = (i1+i2+i3*2+i4*4)/8; RETURN avg; END| Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEIGHTED_AVERAGE2('Pavlov') AS Pavlov, WEIGHTED_AVERAGE2('Mark') AS MarkG *************************** 1. row *************************** Pavlov: 67 Mark: 63 1 row in set (0.00 sec)
By SELECTING the contents of the mark1 to mark4 rows INTO the variables we have just declared, there is no need to return a result set, and we can happily use the results inside of the function.
All the usual behaviors and conditions apply inside the function. Here is what happens if one of the records is missing a field.
mysql> INSERT INTO sfdata VALUES(90,NULL,70,60,'Isabelle')| Query OK, 1 row affected (0.18 sec) mysql> SELECT WEIGHTED_AVERAGE2('Isabelle') AS IsabelleG *************************** 1. row *************************** Isabelle: NULL 1 row in set (0.16 sec)
As expected, the NULL (and NULLs are always a bad idea to use) contaminates the entire result, and MySQL, not knowing what else to do, can do nothing other than return a NULL.
Here is the full syntax for stored functions:
CREATE FUNCTION sf_name ([parameter1 [...]]) RETURNS type [ LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' ] SQL statements
Manipulating tables
With the early restrictions on accessing tables inside a function lifted, you can use a function to make changes to a table as well. The next two examples are not ideal use of functions (in their current format they would more ideally be stored procedures), as we are not interested in the result being returned, and only want to manipulate the data, but they show you some of the potential power of functions. A function is best used when you want to return a result. Building upon these examples, you can create your own where complex INSERTs, SELECTs and UPDATEs are performed, with a single result being returned at the end of it all. First, we INSERT a record into the sfdata table.
mysql> CREATE FUNCTION WEIGHTED_AVERAGE3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)) RETURNS INT DETERMINISTIC BEGIN DECLARE i1,i2,i3,i4,avg INT; INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1); RETURN 1; END| Query OK, 0 rows affected (0.08 sec) mysql> SELECT WEIGHTED_AVERAGE3(50,60,60,50,'Thoko')G *************************** 1. row *************************** WEIGHTED_AVERAGE3(50,60,60,50,'Thoko'): 1 1 row in set (0.00 sec) mysql> SELECT * FROM sfdataG *************************** 1. row *************************** mark1: 70 mark2: 65 mark3: 65 mark4: 60 name: Mark *************************** 2. row *************************** mark1: 95 mark2: 94 mark3: 75 mark4: 50 name: Pavlov *************************** 3. row *************************** mark1: 90 mark2: NULL mark3: 70 mark4: 60 name: Isabelle *************************** 4. row *************************** mark1: 50 mark2: 60 mark3: 60 mark4: 50 name: Thoko 4 rows in set (0.01 sec)
Similarly, the next example UPDATEs a record based upon the parameters passed to it:
mysql> CREATE FUNCTION WEIGHTED_AVERAGE_UPDATE (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)) RETURNS INT DETERMINISTIC BEGIN DECLARE i1,i2,i3,i4,avg INT; UPDATE sfdata SET mark1=n1,mark2=n2,mark3=n3,mark4=n4 WHERE name=v1; RETURN 1; END| Query OK, 0 rows affected (0.00 sec) mysql> SELECT WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko')G *************************** 1. row *************************** WEIGHTED_AVERAGE_UPDATE(60,60,60,50,'Thoko'): 1 1 row in set (0.00 sec) mysql> SELECT * FROM sfdataG *************************** 1. row *************************** mark1: 70 mark2: 65 mark3: 65 mark4: 60 name: Mark *************************** 2. row *************************** mark1: 95 mark2: 94 mark3: 75 mark4: 50 name: Pavlov *************************** 3. row *************************** mark1: 90 mark2: NULL mark3: 70 mark4: 60 name: Isabelle *************************** 4. row *************************** mark1: 60 mark2: 60 mark3: 60 mark4: 50 name: Thoko 5 rows in set (0.01 sec)