Close Cursors in SQL

Close Cursor is used to close the cursor temporary when it is not required. A cursor in a SQL can be closed with close statement. When a close statement is executed ,it leaves the cursor structure for reopening. 

Understand with Example

The Tutorial illustrates an example 'Close Cursor in SQL'. To grasp this example, we create a table 'Stu_Table'. The create table Stu_Table is used to create a table 'Stu_Table' with table attribute specified in the Query. 

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_Table

Create Cursor

The given SQL Query create a procedure curedemo which accept a input parameter name 'id'. To define a cursor and its characteristics, we use Declare Cursor. The Open Cursor is used to open the cursor name curl1,curl2 respectively. Fetch Cursor is used to fetch the  values successively one by one from the cursor into 'name' and 'class'. The Close Cursor is used to de allocate the memory occupied by the cursor. 

DELIMITER $$
create procedure curdemo(id int)
  begin
	
	DECLARE name varchar(10);
	DECLARE class varchar(10);
	DECLARE cur1 CURSOR FOR SELECT stu_name from stu_table where Stu_Id =id;
	DECLARE cur2 CURSOR FOR SELECT stu_class from stu_table where Stu_Id =id;
	OPEN cur1;
	OPEN cur2;
	Fetch cur1 into name;
	Fetch cur2 into class;
	select name ,class;
	close cur1;
	close cur2;
end$$
DELIMITER ;

Call Cursor

The Call Cursor execute the cursor value, where id is '2' as input parameter.

Call close_cursor(2);

Result

+------+-------+
| name | class |
+------+-------+
| Ajay | 10    |
+------+-------+