Knowledge Walls
John Peter
Pune, Maharashtra, India
Multiple cursors in mysql stored procedure with example
26754 Views
How to create multiple blocks of statement in MySQL 
For Each Block of statement in mysql is seperated by BEGIN AND END. Like java is seperated block of statement with curly braces {}.

BEGIN
     -- First Block of statement
END
BEGIN
     -- Second Block of statement
END
Example
DROP PROCEDURE IF EXISTS `multipleCursorsAtOne`;
DELIMITER $$
CREATE PROCEDURE `multipleCursorsAtOne`()
BEGIN
    DROP TABLE IF EXISTS userNames;
    CREATE TEMPORARY TABLE userNames
    (userName varchar(200) NOT NULL);
    
    BEGIN
        DECLARE done BOOLEAN DEFAULT false;
        DECLARE p_first_name VARCHAR(200);
        DECLARE cursor_a CURSOR FOR SELECT user_name FROM user_info LIMIT 1,3;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
         
        OPEN cursor_a;

            cursor_a_loop: LOOP
                 FETCH cursor_a INTO p_first_name;
                 IF done THEN
                        LEAVE cursor_a_loop;
                 END IF;     
                 -- cursor loop statements
                    
                 IF p_first_name IS NOT NULL AND p_first_name <> "" THEN
                    INSERT INTO userNames(userName) VALUES(p_first_name);
                 END IF;
            END LOOP;
         
        CLOSE cursor_a;
    END;
    BEGIN
        DECLARE done BOOLEAN DEFAULT false;
        DECLARE p_first_name VARCHAR(200);
        DECLARE cursor_a CURSOR FOR SELECT user_name FROM user_info LIMIT 4,3;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
         
        OPEN cursor_a;

            cursor_a_loop: LOOP
                 FETCH cursor_a INTO p_first_name;
                 IF done THEN
                        LEAVE cursor_a_loop;
                 END IF;     
                 -- cursor loop statements
             
                 IF p_first_name IS NOT NULL AND p_first_name <> "" THEN
                    INSERT INTO userNames(userName) VALUES(p_first_name);
                 END IF;
            END LOOP;
         
        CLOSE cursor_a;
    END;
    
    SELECT * FROM userNames;
END
$$
Output 
Next Topics
Next lessons of current book.
INFORMATION_SCHEMA Db of MySQL
INFORMATION_SCHEMA Db of MySQL
INFORMATION_SCHEMA Db of MySQL
Best Lessons of "MySQL"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details