Cursor In My SQL Stored Procedure

Banaglore: Check out what is cursor and Cursor in MSSQL Server here.
Cursor in SQL Server
Here we are going top create cursor in MySql query for the same case described in MSSQL server
Let see the difference in Syntax, nothing much small declaration changes only

/* Your code starts Here  */
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `mysqlcursortest`()
BEGIN
declare ids int;  /*Declare ids as a int variable */
declare var1,totalrow int default 1;


 DECLARE cur1 CURSOR FOR SELECT id FROM employee_master;
/*Declaring cursor variable  */
 open cur1; /*opening cursor variable */
  set totalrow=Found_Rows(); /*Checking number of rows in cursor result set */
  while var1<=totalrow do  /*If its not empty enter into while loop */
   fetch cur1 into ids;   /*Fetching the current value into ids variable */
 /*doing Cursor operation here */
   insert into temp(incometax) values(ids);

   set var1=var1+1;  /*Increment row count */
 end while;
close cur1;  /*closing Cursor after its usage */
END

/* Your code ends Here  */

0 comments: