MySQL Stored Functions - Page 2

December 13, 2005

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