An Introduction to Stored Functions in MySQL 5.0

Functions are a new addition to MySQL 5.0 and bring much needed, enterprise level, functionality to the MySQL database. This added functionality allows it to compete with other databases such as Oracle, DB2 and MSSQL Server and make it a more attractive solution.

For programmers who have not used functions they are named database objects/stored programs that can be called by other MySQL stored programs, SQL statements or external programs - often used to carry out repetitive or complicated tasks. Functions by definition always return a value and should never update anything in the database. They should simply be used to generate an output based on a certain set of input parameters.

Creating a MySQL Function:
Before you create a FUNCTION you need to change the delimiter to $$. This is required because MySQL will stop processing code when it reads a semicolon. Changing the delimiter ensures that MySQL processes the entire block of code that defines your function. At the end of your function declaration you should change the delimiter back to ;.

The following example creates a function that calculates a transaction fee on an order based on the order amount, the order id and associated client id.

DELIMITER $$

DROP FUNCTION IF EXISTS `dbName`.`getName` $$

CREATE FUNCTION `dbName`.`getName`(in_client_id int) RETURNS varchar
BEGIN
   DECLARE client_name varchar(25);

   DECLARE c CURSOR FOR
   SELECT cl.name
      FROM clients cl
      WHERE TRIM(client_id) = TRIM(in_client_id);
   
   OPEN c;
   FETCH c INTO client_name;
   CLOSE c;
  
   RETURN(client_name);
END $$

DELIMITER ;

Calling a MySQL Function:
Since functions return a value you need to store it somewhere. You can do this by either calling a function in another stored program and storing the return value in a variable or by calling the function directly in a SQL statement.

Calling a function in another stored program:

CREATE PROCEDURE function_demo(p_client_id int)
BEGIN
   DECLARE v_client_name varchar(25);
   v_client_name = getName(p_client_id);
   {more code}
END

Calling a function in a SQL statement, you can either pass in a variable or column:

select getName(c1.client_id) from orders c1;

Updating a MySQL Function:
To update your function you have to simply make changes to your code and recompile it. That is why you should make it standard practice to add the DROP FUNCTION IF EXISTS line before you create your function as in the example above. That line will delete the function if it exists in the database and recreates it.

Deleting a MySQL Function:
Deleting a function is simple

DROP FUNCTION `dbName`.`getName`;

 

Related Articles

• MySQL Stored Programs - Cursors
• MySQL Stored Programs - Triggers

 

blog comments powered by Disqus

Lijit Search