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

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