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
» 
Comment and Contribute
