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_Table

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.

DELIMITER $$
create procedure curdemo(id int , out var varchar(10))
  begin
	

	DECLARE cur1 CURSOR FOR SELECT stu_name from stu_table where stu_id =id;

	OPEN cur1;

	Fetch cur1 into var;
	close cur1;

end$$
DELIMITER ;

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;

Result

+-------+
| @name |
+-------+
| Komal |
+-------+