Latest Tutorials| Questions and Answers|Ask Questions?|Site Map

Home Quartz Using while loop to loop through a cursor

Related Tutorials


Share on Google+Share on Google+

Using while loop to loop through a cursor

using while loop to loop through cursor

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_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.

create procedure curdemo()
    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 ;
 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;

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();


| 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)

If you enjoyed this post then why not add us on Google+? Add us to your Circles

Liked it!  Share this Tutorial

Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: April 18, 2011

Related Tutorials

Discuss: Using while loop to loop through a cursor  

Post your Comment

Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
December 23, 2011
c. program

this very important