Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Dec 13, 2005

MySQL Stored Functions

By Ian Gilfillan

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)


MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM