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.

OPEN cursor_name;

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.

CLOSE cursor_name;

You have to use REPEAT LOOP or LOOP to iterate through Cursor.

REPEAT LOOP Syntax:

[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]

LOOP Syntax:

[begin_label:] LOOP
statement_list
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.

Using 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;

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.

Thanks
A Rahim Khan

Advertisements
    • amit
    • May 3rd, 2011

    Good job
    Really its too good i m new with Cursur but ur explanation teach me many things

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: