Home Quartz Using while loop to loop through a cursor
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Using while loop to loop through a cursor

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

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

Ask Questions?    Discuss: Using while loop to loop through a cursor  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
عبدا?عز?ز
December 23, 2011
c. program

this very important
DMCA.com