User Tools

Site Tools


db:storedpf

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:storedpf [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== Stored procedures and functions in MySQL ====
 +16.11.2009
  
 +
 +The mysql server implements stored procedures and functions starting with 5.0 version.
 +
 +=== What is a procedure/​function?​ ===
 +
 +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 \\ 
 +
 +
 +==== Procedures ====
 +
 +
 +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.
 +
 +<​code>​
 +delimiter //
 +create procedure ...
 +
 +delimiter ;
 +</​code>​
 +
 +Here is a small example of a procedure (it calculates the area of a square):
 +
 +<​code>​
 +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)
 +</​code>​
 +
 +
 +=== Where can I find information about procedures? ===
 +
 +
 +Look for **information_schema** database, **routines** table.
 +
 +<​code>​
 +SELECT routine_name FROM information_schema.routines;​
 +</​code>​
 +
 +
 +==== MySQL compound statement syntax ====
 +
 +Full informations [[hhttp://​dev.mysql.com/​doc/​refman/​5.0/​en/​sql-syntax-compound-statements.html | 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.
 +
 +<​code>​
 ++---------------+-------------+------+-----+---------+----------------+
 +| 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    |                | 
 ++---------------+-------------+------+-----+---------+----------------+
 +</​code>​
 +
 +Our procedure:
 +
 +<​code>​
 +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 ;
 +</​code>​
 +
 +Example output:
 +
 +<​code>​
 +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)
 +</​code>​
db/storedpf.txt ยท Last modified: 2013/03/16 17:40 (external edit)