Cursor with Stored Procedure in MySQL Server
Let’s start with Cursor directly. If you are not familiar with MySQL Stored Procedure, have a look at this posting first. Cursor is used to navigate thorough Result Set returned by a Query.
First of all, Cursor has to be declared. Cursor declaration is very simple.
DECLARE cursor_name CURSOR FOR select_statement;
Now, you need to use OPEN Command to start using this Cursor.
Use FETCH Command to retrieve data from Cursor.
FETCH cursor_name INTO var_name;
Finally, you must close Cursor using CLOSE Command when you are done.
You have to use REPEAT LOOP or LOOP to iterate through Cursor.
REPEAT LOOP Syntax:
END REPEAT [end_label]
END LOOP [end_label]
Prior to start iterating through Cursor, you have to make a Continue Handler Declaration to maintain data fetching. It must be declared just after Cursor declaration.
DECLARE details_id_loop INT DEFAULT 0; DECLARE record_not_found INT DEFAULT 0; DECLARE cursor_details_id CURSOR FOR select_statement; DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1; OPEN cursor_details_id; all_details_id:LOOP FETCH cursor_details_id INTO details_id_loop; IF record_not_found THEN LEAVE all_details_id; END IF; statement_list; END LOOP all_details_id;
Using REPEAT LOOP:
DECLARE details_id_loop INT DEFAULT 0; DECLARE record_not_found INT DEFAULT 0; DECLARE cursor_details_id CURSOR FOR select_statement; DECLARE CONTINUE HANDLER FOR NOT FOUND SET record_not_found = 1; OPEN cursor_details_id; REPEAT FETCH cursor_details_id INTO details_id_loop; statement_list; UNTIL record_not_found END REPEAT;
Download the SQL Script . Here, I iterate through Cursor using LOOP to write a Stored Procedure.
A Rahim Khan