MySQL Stored Functions

Tuesday Dec 13th 2005 by Ian Gilfillan
Share:

Continuing with our series on Stored Procedures and Functions, this month we focus on Stored Functions.

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 Mark\G
*************************** 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 Isabelle\G
*************************** 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 sfdata\G
*************************** 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 sfdata\G
*************************** 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)

Information about existing stored functions

As with stored procedures, there are various ways to get to the metadata about existing stored functions. There is SHOW CREATE FUNCTION, and SHOW FUNCTION STATUS. The former returns the CREATE statement applied to the supplied function, while the latter returns metadata about all existing functions, as follows.

mysql> SHOW CREATE FUNCTION WEIGHTED_AVERAGE\G
*************************** 1. row ***************************
       Function: WEIGHTED_AVERAGE
       sql_mode:
Create Function: CREATE FUNCTION 'WEIGHTED_AVERAGE'(n1 INT, n2 INT, n3 INT, n4 INT) 
 RETURNS int(11)
    DETERMINISTIC
BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
1 row in set (0.01 sec)
mysql> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 13:21:37
      Created: 2005-12-07 13:21:37
Security_type: DEFINER
      Comment:
*************************** 2. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE2
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 13:41:07
      Created: 2005-12-07 13:41:07
Security_type: DEFINER
      Comment:
*************************** 3. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE3
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 15:51:16
      Created: 2005-12-07 15:51:16
Security_type: DEFINER
      Comment:
*************************** 4. row ***************************
           Db: test
         Name: WEIGHTED_AVERAGE_UPDATE
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2005-12-07 16:03:26
      Created: 2005-12-07 16:03:26
Security_type: DEFINER
      Comment:
4 rows in set (0.00 sec)

Another way to get the same information is to query the mysql.proc table. As you may know, the mysql database stores all sorts of data about permissions, and you can UPDATE the user or db table to change the MySQL privileges. Since MySQL 5, the mysql.proc table also contains metadata about stored procedures and functions.

mysql> SELECT * FROM mysql.proc\G
*************************** 1. row ***************************
              db: test
            name: WEIGHTED_AVERAGE
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT, n2 INT, n3 INT, n4 INT
         returns: int(11)
            body: BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
         definer: root@localhost
         created: 2005-12-07 13:21:37
        modified: 2005-12-07 13:21:37
        sql_mode:
         comment:
*************************** 2. row ***************************
              db: test
            name: WEIGHTED_AVERAGE2
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE2
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: v1 VARCHAR(50)
         returns: int(11)
            body: BEGIN     
                    DECLARE i1,i2,i3,i4,avg INT;    
                    SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    
                    SET avg = (i1+i2+i3*2+i4*4)/8;    
                    RETURN avg;   
                  END
         definer: root@localhost
         created: 2005-12-07 13:41:07
        modified: 2005-12-07 13:41:07
        sql_mode:
         comment:
*************************** 3. row ***************************
              db: test
            name: WEIGHTED_AVERAGE3
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE3
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)
         returns: int(11)
            body: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
   RETURN 1;
  END
         definer: root@localhost
         created: 2005-12-07 15:51:16
        modified: 2005-12-07 15:51:16
        sql_mode:
         comment:
*************************** 4. row ***************************
              db: test
            name: WEIGHTED_AVERAGE_UPDATE
            type: FUNCTION
   specific_name: WEIGHTED_AVERAGE_UPDATE
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: YES
   security_type: DEFINER
      param_list: n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50)
         returns: int(11)
            body: 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
         definer: root@localhost
         created: 2005-12-07 16:03:26
        modified: 2005-12-07 16:03:26
        sql_mode:
         comment:
4 rows in set (0.00 sec)

Note that querying the mysql.proc table returns more complete data than either of the first two methods, effectively returning the sum of both of those methods.

However, people coming from other DBMS', familiar with the ANSI standard, may be uncomfortable with these MySQL-specific methods. The standard way is to query the INFORMATION_SCHEMA. It is a highly flexible way of getting what you want, but can be a bit of an overkill, hence MySQL's provision of the more simple SHOW methods. I will leave a more complete explanation of INFORMATION_SCHEMA for another day, as it extends well beyond stored procedures and functions. For now, suffice to say that you can query INFORMATION_SCHEMA.ROUTINES to get similar metadata as the above, as follows:

mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
   DECLARE avg INT;
   SET avg = (n1+n2+n3*2+n4*4)/8;
   RETURN avg;
  END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 13:21:37
      LAST_ALTERED: 2005-12-07 13:21:37
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 2. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE2
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE2
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
                      DECLARE i1,i2,i3,i4,avg INT;    
                      SELECT mark1,mark2,mark3,mark4 INTO i1,i2,i3,i4 FROM sf1_data WHERE name=v1;    
                      SET avg = (i1+i2+i3*2+i4*4)/8;    
                      RETURN avg;   
                    END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 13:41:07
      LAST_ALTERED: 2005-12-07 13:41:07
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 3. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE3
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE3
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
   DECLARE i1,i2,i3,i4,avg INT;
   INSERT INTO sfdata VALUES(n1,n2,n3,n4,v1);
   RETURN 1;
  END
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 15:51:16
      LAST_ALTERED: 2005-12-07 15:51:16
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
*************************** 4. row ***************************
     SPECIFIC_NAME: WEIGHTED_AVERAGE_UPDATE
   ROUTINE_CATALOG: NULL
    ROUTINE_SCHEMA: test
      ROUTINE_NAME: WEIGHTED_AVERAGE_UPDATE
      ROUTINE_TYPE: FUNCTION
    DTD_IDENTIFIER: int(11)
      ROUTINE_BODY: SQL
ROUTINE_DEFINITION: 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
     EXTERNAL_NAME: NULL
 EXTERNAL_LANGUAGE: NULL
   PARAMETER_STYLE: SQL
  IS_DETERMINISTIC: YES
   SQL_DATA_ACCESS: CONTAINS SQL
          SQL_PATH: NULL
     SECURITY_TYPE: DEFINER
           CREATED: 2005-12-07 16:03:26
      LAST_ALTERED: 2005-12-07 16:03:26
          SQL_MODE:
   ROUTINE_COMMENT:
           DEFINER: root@localhost
4 rows in set (0.00 sec)

The MySQL documentation gives a complete overview about the INFORMATION_SCHEMA structure, and some MySQL oddities, if you want to pursue that further.

Conclusion

Stored procedures and stored functions open a whole new world to MySQL developers, and mean that MySQL is starting to attract attention from developers of entirely new types of applications. While the implementation in MySQL 5.0 is still raw (MySQL 5.1 is out in alpha now, and develops things further, as will MySQL 6.0), most of what is needed is already there. Not all of the my applications are running on MySQL 5 yet, but the itch to move everything is starting to get stronger every time I do further development, and have to make do with more unwanted logic in the application. I came across a query recently from one poor developer bemoaning the lack of features in MySQL 3.23. Upgrading legacy systems is not fun, but for those of you lucky enough to start with a clean slate, enjoy the new world of MySQL 5, stored procedures and stored functions!

» See All Articles by Columnist Ian Gilfillan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved