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);
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 ;
To call a Cursor we use the given syntax below:
+-------+ | 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)
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.