Declare Cursors in SQL

Declare Cursors in SQL is used to define cursor and its characteristics.

Declare Cursors in SQL


Declare Cursors in SQL is used to define cursor and its characteristics.

Understand with Example

The Tutorial illustrate a example from 'Declare Cursors in SQL'. To grasp the example, we create a table 'Stu_Table' with table attribute like field name ,data type etc. The create table create a table name 'Stu_Table'. 

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

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

The given below Query create a procedure curedemo which accept id as INPUT parameter and var as OUTPUT parameter. The DECLARE cur1 is used to define the cursor and its characteristic. OPEN cur1 is used to open the cursor 'cur1'.The Fetch curl1 fetch the rows one by one into var. Finally close Cursor1 close the cur1temporary.

create procedure curdemo(id int , out var varchar(10))
	DECLARE cur1 CURSOR FOR SELECT stu_name from stu_table where stu_id =id;
	OPEN cur1;
	Fetch cur1 into var;
	close cur1;

Call Cursor

The call curdemo (1,@name) execute the procedure curdemo and return the value present in the output parameter.

call curdemo(1, @name);
select  @name;


| @name |
| Komal |
Share on Google+Share on Google+

Declare Cursors in SQL

Posted on: December 20, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles



Discuss: Declare Cursors in SQL   View All Comments

Post your Comment

Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image