16.11.2009

The mysql server implements stored procedures and functions starting with 5.0 version.

A procedure - a group of statements (code) who runs and execute something
A function - a group of statements (code) who runs, execute something and returns some results

The procedures cannot return directly results; but they can receive variables that can be modified.

• in - the variable can only be used as an input
• out - the variable can only be used as an output
• inout - this is both an input to, and an output from, the procedure

Because semicolons are part of a procedure, but in the same time they get interpreted by mysql, it's important to change temporary the delimiter to something else.

delimiter //
create procedure ...

delimiter ;

Here is a small example of a procedure (it calculates the area of a square):

mysql> use test;
Database changed
mysql> delimiter //
mysql> create procedure square_area(in base double, out area double)
    -> begin
    -> set area = base * base;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call square_area(120, @a);
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+-------+
| @a    |
+-------+
| 14400 | 
+-------+
1 row in set (0.00 sec)

Look for information_schema database, routines table.

SELECT routine_name FROM information_schema.routines;

Full informations here.

The next code is trying to exemplify some mysql compound statements code. We assume that we already have a table called products with the following structure and some entries.

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| product_id    | int(11)     | NO   | PRI | NULL    | auto_increment | 
| product_name  | varchar(50) | NO   |     | NULL    |                | 
| product_price | int(11)     | NO   |     | NULL    |                | 
+---------------+-------------+------+-----+---------+----------------+

Our procedure:

delimiter //
CREATE PROCEDURE test_procedure(IN param1 INT, IN param2 INT, OUT output VARCHAR(1000))
BEGIN
	-- this is a small comment
	-- param1 is the product id, param2 not used
	DECLARE pprice INT DEFAULT 0; 
	DECLARE finalprice DOUBLE DEFAULT 0;
	SET @vat = 19;
	SET pprice = (SELECT product_price FROM test.products WHERE product_id = param1);
	SET finalprice = pprice + (pprice * @vat/100);
	IF finalprice > 20 
		THEN SET output = CONCAT("Price ", finalprice, " is higher than 20");
		ELSE SET output = CONCAT("Price  ", finalprice, " is lower than 20");
	END IF;
END
//
delimiter ;

Example output:

mysql> call test_procedure(2,0,@output); SELECT @output;
Query OK, 0 rows affected (0.00 sec)

+-------------------------------+
| @output                       |
+-------------------------------+
| Price 27.37 is higher than 20 | 
+-------------------------------+
1 row in set (0.00 sec)