Friday, August 28, 2009

[Level 2] Declare and Handlers in MySQL Stored Procedure.

In MySQL stored procedure, we use use "DECLARE" statement to define some variables, like: Local Variable, Conditions and handlers and Cursors.
Something you should to take care is about the sequence of these variables declaration. The correct sequence is:
1. Variables
2. Conditions
3. Cursors
4. Handlers

IF the sequence is not correct, you will get a error message while building stored procedure.
The example for these variables declaration as below:
1. Variables:
mysql> DECLARE no int;
2. Conditions
mysql> DECLARE co CONDITION FOR SQLSTATE '23000';
3. Cursors
mysql> DECLARE cu CURSOR FOR SELECT * FROM MYSQL.USER;
4. Handlers
mysql> DECLARE CONTINUE HANDLER FOR co BEGIN END; -- the empty between BEGIN and END means to ignore the handler, do nothing.

Wish this helps.

regards,
Stanley Huang