Using while loop to loop through a cursor

Cursor in SQL is used to define a set of data rows and perform complex logic on each row by row basis.

Understand with Example

The Tutorial grasp you an example on 'Using while loop to loop through a cursor'. The Example create a table Stu_Table'. The create table construct a table 'Stu_Table' with table attribute like field name,data type etc.

Create Table Stu_Table

create table Stu_Table(Stu_Id integer(2), Stu_Name varchar(15), Stu_Class varchar(10))

Insert data into Stu_Table

Once your table is created, The insert into add the records or rows to the table 'Stu_Table'.

insert into Stu_Table values(1,'Komal',10);

insert into Stu_Table values(2,'Ajay',10);

insert into Stu_Table values(3,'Rakesh',10);

insert into Stu_Table values(4,'Bhanu',10);

insert into Stu_Table values(5,'Santosh',10);

insert into Stu_Table values(6,'Tanuj',10);

Stu_Table

Stu_Id Stu_Name Stu_Class

1 Komal 10

2 Ajay 10

3 Rakesh 10

4 Bhanu 10

5 Santosh 10

6 Tanuj 10

Create Cursor


To create a Cursor, the following step are to be carried out :

1)Declare :Declare a cursor that is used to define a result set.

2)Open : This is used to open the cursor that establish the result set. The While loop check a condition and executes the loop for as long as the condition remains true.

3)Fetch : This is used in cursor to fetch the data into local variable from the cursor, one row at a time.

4)Close :This is used to close the cursor.


DELIMITER $

create procedure curdemo()

begin

DECLARE done INT DEFAULT 0;

DECLARE id int;

DECLARE name varchar(15);

DECLARE class varchar(15);

DECLARE cur1 CURSOR FOR SELECT stu_id,stu_name,stu_class from stu_table ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

cur1_loop:WHILE(done=0) DO

FETCH cur1 INTO id,name,class;

IF done=1 THEN

LEAVE cur1_loop;

END IF;

select id,name,class;

END WHILE cur1_loop;

CLOSE cur1;

END$

DELIMITER ;

Call Cursor

The call cursorname return the rows one by one from the table. The outcome result can be viewed as a pointer to one row in a set of rows,but can move to other rows of the result as needed.

call curdemo();

Result

+------+-------+-------+

| id | name | class |

+------+-------+-------+

| 1 | Komal | 10 |

+------+-------+-------+

1 row in set (0.02 sec)

+------+------+-------+

| id | name | class |

+------+------+-------+

| 2 | Ajay | 10 |

+------+------+-------+

1 row in set (0.05 sec)

+------+--------+-------+

| id | name | class |

+------+--------+-------+

| 3 | Rakesh | 10 |

+------+--------+-------+

1 row in set (0.08 sec)

+------+-------+-------+

| id | name | class |

+------+-------+-------+

| 4 | Bhanu | 10 |

+------+-------+-------+

1 row in set (0.11 sec)

+------+---------+-------+

| id | name | class |

+------+---------+-------+

| 5 | Santosh | 10 |

+------+---------+-------+

1 row in set (0.13 sec)

+------+-------+-------+

| id | name | class |

+------+-------+-------+

| 6 | Tanuj | 10 |

+------+-------+-------+

1 row in set (0.14 sec)

Query OK, 0 rows affected (0.16 sec)