MySQL stored procedures

November 4, 2014 · by dbversity · in MySQL

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 DBVERSITY_WTG_AVG (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 DBVERSITY_WTG_AVG(70,65,65,60)\G
*************************** 1. row ***************************
DBVERSITY_WTG_AVG(70,65,65,60): 63
1 row in set (0.00 sec)

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 Srinis, and a name. Then we will define a new DBVERSITY_WTG_AVG function to make use of the dynamic data from the table. mysql> CREATE TABLE sfdata(Srini1 INT,Srini2 INT,Srini3 INT,Srini4 INT,name VARCHAR(50))
mysql> INSERT INTO tbl_dbversityVALUES(70,65,65,60,’Srini’)|
mysql> INSERT INTO tbl_dbversityVALUES(95,94,75,50,’dbversity’)|
mysql>
CREATE FUNCTION DBVERSITY_WTG_AVG2 (v1 VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i1,i2,i3,i4,avg INT;
SELECT Srini1,Srini2,Srini3,Srini4 INTO i1,i2,i3,i4 FROM tbl_dbversityWHERE name=v1;
SET avg = (i1+i2+i3*2+i4*4)/8;
RETURN avg;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DBVERSITY_WTG_AVG2(‘dbversity’) AS dbversity, DBVERSITY_WTG_AVG2(‘Srini’) AS Srini\G
*************************** 1. row ***************************
dbversity: 67
Srini: 63
1 row in set (0.00 sec)

By SELECTING the contents of the Srini1 to Srini4 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 tbl_dbversityVALUES(90,NULL,70,60,’University’)|
Query OK, 1 row affected (0.18 sec)
mysql> SELECT DBVERSITY_WTG_AVG2(‘University’) AS University\G
*************************** 1. row ***************************
University: 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 tbl_dbversitytable.

mysql>
CREATE FUNCTION DBVERSITY_WTG_AVG3 (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i1,i2,i3,i4,avg INT;
INSERT INTO tbl_dbversityVALUES(n1,n2,n3,n4,v1);
RETURN 1;
END|
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT DBVERSITY_WTG_AVG3(50,60,60,50,’versity’)\G
*************************** 1. row ***************************
DBVERSITY_WTG_AVG3(50,60,60,50,’versity’): 1
1 row in set (0.00 sec)
mysql> SELECT * FROM sfdata\G
*************************** 1. row ***************************
Srini1: 70
Srini2: 65
Srini3: 65
Srini4: 60
name: Srini
*************************** 2. row ***************************
Srini1: 95
Srini2: 94
Srini3: 75
Srini4: 50
name: dbversity
*************************** 3. row ***************************
Srini1: 90
Srini2: NULL
Srini3: 70
Srini4: 60
name: University
*************************** 4. row ***************************
Srini1: 50
Srini2: 60
Srini3: 60
Srini4: 50
name: versity
4 rows in set (0.01 sec)
Similarly, the next example UPDATEs a record based upon the parameters passed to it:

mysql>
CREATE FUNCTION DBVERSITY_WTG_AVG_UPDATE (n1 INT,n2 INT,n3 INT,n4 INT,v1 VARCHAR(50))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i1,i2,i3,i4,avg INT;
UPDATE tbl_dbversitySET Srini1=n1,Srini2=n2,Srini3=n3,Srini4=n4 WHERE name=v1;
RETURN 1;
END|
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DBVERSITY_WTG_AVG_UPDATE(60,60,60,50,’versity’)\G
*************************** 1. row ***************************
DBVERSITY_WTG_AVG_UPDATE(60,60,60,50,’versity’): 1
1 row in set (0.00 sec)

mysql> SELECT * FROM sfdata\G
*************************** 1. row ***************************
Srini1: 70
Srini2: 65
Srini3: 65
Srini4: 60
name: Srini
*************************** 2. row ***************************
Srini1: 95
Srini2: 94
Srini3: 75
Srini4: 50
name: dbversity
*************************** 3. row ***************************
Srini1: 90
Srini2: NULL
Srini3: 70
Srini4: 60
name: University
*************************** 4. row ***************************
Srini1: 60
Srini2: 60
Srini3: 60
Srini4: 50
name: versity
5 rows in set (0.01 sec)

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 DBVERSITY_WTG_AVG\G
*************************** 1. row ***************************
Function: DBVERSITY_WTG_AVG
sql_mode:
Create Function: CREATE FUNCTION ‘DBVERSITY_WTG_AVG'(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: DBVERSITY_WTG_AVG
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: DBVERSITY_WTG_AVG2
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: DBVERSITY_WTG_AVG3
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: DBVERSITY_WTG_AVG_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: DBVERSITY_WTG_AVG
type: FUNCTION
specific_name: DBVERSITY_WTG_AVG
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: DBVERSITY_WTG_AVG2
type: FUNCTION
specific_name: DBVERSITY_WTG_AVG2
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 Srini1,Srini2,Srini3,Srini4 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: DBVERSITY_WTG_AVG3
type: FUNCTION
specific_name: DBVERSITY_WTG_AVG3
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 tbl_dbversityVALUES(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: DBVERSITY_WTG_AVG_UPDATE
type: FUNCTION
specific_name: DBVERSITY_WTG_AVG_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 tbl_dbversitySET Srini1=n1,Srini2=n2,Srini3=n3,Srini4=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: DBVERSITY_WTG_AVG
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: DBVERSITY_WTG_AVG
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: DBVERSITY_WTG_AVG2
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: DBVERSITY_WTG_AVG2
ROUTINE_TYPE: FUNCTION
DTD_IDENTIFIER: int(11)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE i1,i2,i3,i4,avg INT;
SELECT Srini1,Srini2,Srini3,Srini4 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: DBVERSITY_WTG_AVG3
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: DBVERSITY_WTG_AVG3
ROUTINE_TYPE: FUNCTION
DTD_IDENTIFIER: int(11)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE i1,i2,i3,i4,avg INT;
INSERT INTO tbl_dbversityVALUES(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: DBVERSITY_WTG_AVG_UPDATE
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: DBVERSITY_WTG_AVG_UPDATE
ROUTINE_TYPE: FUNCTION
DTD_IDENTIFIER: int(11)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN
DECLARE i1,i2,i3,i4,avg INT;
UPDATE tbl_dbversitySET Srini1=n1,Srini2=n2,Srini3=n3,Srini4=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.

MySQL stored procedures

Leave a Reply

Categories

Categories