Home Sql Close Cursors in SQL

Related Tutorials


 
 

Share on Google+Share on Google+

Close Cursors in SQL

Advertisement
Close Cursors is used to close the Cursor temporary, when the open cursor is not required. The keyword close in cursor release the current records to close an open cursor.

Close Cursors in SQL

     

Close Cursors is used to close the Cursor temporary, when the open cursor is not required. The keyword close in cursor release the current records to close an open cursor.

Understand with Example

The Tutorial illustrate an example from Close Cursors in SQL. To understand this example, we create a table 'Stu_Table'. The create table statement construct a table 'Stu_Table' with specified field attribute and data type respectively.

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 value into 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

Now ,we use the cursor on table 'Stu_Table'. We create a procedure 'close cursor' that accept 'id' as input parameter and the data type used for variable 'id' is integer. The Declare keyword in Cursor is used to define a cur1 and help you to include the records from table 'Stu_Table' on the basis of 'stu_id' passed to it . The OPEN statement cur1 open the cur1,this make the cur1 accessible. The Fetch statement fetch the records from the cursor cur1 into variable 'name'. The select name return you the name of the record. Finally close cursor is used to close the curl1,when it is not required to access further. 

DELIMITER $$
create procedure close_cursor( id int)
  begin
	DECLARE name varchar(10);
	DECLARE cur1 CURSOR FOR SELECT Stu_Name from stu_table where Stu_Id =id;
	OPEN cur1;
	Fetch cur1 into name;
	select name;
	close cur1;
end$$
DELIMITER ;


Call Cursor

To call a close cursor we use the given below Syntax :

Call close_cursor(3);

Result

+--------+
| name   |
+--------+
| Rakesh |
+--------+
Advertisement

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: December 20, 2008

Related Tutorials

Discuss: Close Cursors in SQL  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0
DMCA.com