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;

MySQL Stored Procedure