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)

Using while loop to loop through a cursor

Using while loop to loop through a cursor

Using while loop to loop through a cursor

Tutorials