Use HANDLER Statement in Cursors

Use HANDLER Statement in Cursor is used to define a set of data rows where we perform operation on  a row by row basis. The Cursor help you to return a result set and return directly to the caller of the client or SQL procedure.

Understand with Example

The Tutorial illustrate an example from 'Use HANDLER Statement in Cursors'. To elaborate this, we create a table 'Stu_Table'. The create table statement create a table 'Stu_Table' with table attribute like field name and respective data type. 

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 rows or records 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 Cursor for the above table 'Stu_Table'. Begin with create procedure create a procedure curdemo. The Query begins with a begin keyword that include declare statement used to define the variable and its data type. Each begin keyword end with a end statement pair. The OPEN Cursor open the cur1 which built a result set. The repeat statement in SQL include a set of statements to be executed until a condition evaluated at the end of the REPEAT statement hold true. The fetch curl1 is used to obtain  the records into variable 'name'. The select name return you all the row which satisfy the condition. The close cur1 close the cursor cur1, when cur1 is no longer to be used.

DELIMITER $$
create procedure curdemo()
    begin
DECLARE done INT DEFAULT 0;
DECLARE name varchar(15);

DECLARE cur1 CURSOR FOR SELECT stu_name from stu_table ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

REPEAT
Fetch cur1 into name;
select name;

UNTIL done END REPEAT;
close cur1;

   end$$
DELIMITER ;

Call Cursor

To call a Cursor we use the given syntax  below:

call curdemo();

Result

+-------+
| name  |
+-------+
| Komal |
+-------+
1 row in set (0.01 sec)

+------+
| name |
+------+
| Ajay |
+------+
1 row in set (0.06 sec)

+--------+
| name   |
+--------+
| Rakesh |
+--------+
1 row in set (0.13 sec)

+-------+
| name  |
+-------+
| Bhanu |
+-------+
1 row in set (0.14 sec)

+---------+
| name    |
+---------+
| Santosh |
+---------+
1 row in set (0.14 sec)

+-------+
| name  |
+-------+
| Tanuj |
+-------+
1 row in set (0.14 sec)

Query OK, 0 rows affected (0.22 sec)