GMgKe586q6suSQnyqZLlGCooeWM

Pages

Search

Tuesday, December 21, 2010

MySQL Stored Procedure

This code will create a simple stored procedure:
mysql> DELIMITER //
mysql> CREATE PROCEDURE getData()
mysql> BEGIN
mysql> SELECT * FROM data;
mysql> END //
mysql> DELIMITER ;

To use it, type:
mysql> call getData();

Variable:
To declare a variable in stored procedure, the rule is:
DECLARE varName DATATYPE(size) DEFAULT defValue;
mysql> DECLARE x, y INT DEFAULT 0;

Then you assign the value to variable:
msyql> SET x = 8;

Complete example:
mysql> DECALRE total INT;
mysql> SELECT COUNT(*) INTO total FROM data;


Parameter:
There's 3 type of variable: IN, OUT, INOUT
To use parameter, use:
mysql> DELIMITER //
mysql> CREATE PROCEDURE getOneLine(IN namee CHAR(20))
mysql> BEGIN
mysql> SELECT * FROM data
mysql> WHERE name = namee;
mysql> END //
mysql> DELIMITER ;

Then call it:
mysql> call getOneLine('lady gaga');

Second example:
mysql> DELIMITER //
mysql> CREATE PROCEDURE getCity(IN nCity CHAR(20), OUT n INT)
mysql> BEGIN
mysql> SELECT COUNT(*) INTO n FROM data
mysql> WHERE city = nCity;
mysql> END //
mysql> DELIMITER ;

To call it, use:
mysql> call getCity('sydney',@total);
mysql> select @total as total_city;
Share/Bookmark

No comments:

Post a Comment