MYSQL : CREATE PROCEDURE

Source :
http://www.java2s.com/Tutorial/MySQL/0240__Cursor/Usingwhilelooptoloopthroughacursor.htm

mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc (in_customer_id INT)
-> BEGIN
->
-> DECLARE l_last_row_fetched int;
-> DECLARE l_id int;
-> DECLARE l_first_name VARCHAR(30);
-> DECLARE l_last_name VARCHAR(30);
->
->
-> DECLARE c1 CURSOR FOR
-> SELECT id,first_name, last_name
-> FROM employee
-> WHERE id=in_customer_id;
->
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;
->
-> SET l_last_row_fetched=0;
->
->
-> OPEN c1;
-> cursor_loop:LOOP
-> FETCH c1 INTO l_id,l_first_name,l_last_name;
-> IF l_last_row_fetched=1 THEN
-> LEAVE cursor_loop;
-> END IF;
-> /*Do something with the row fetched*/
-> END LOOP cursor_loop;
-> CLOSE c1;
-> SET l_last_row_fetched=0;
->
->
->
-> END$$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql>
mysql> call myProc(1);
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

http://dev.mysql.com/doc/refman/5.0/en/cursors.html


CREATE PROCEDURE curdemo()
BEGIN
 DECLARE done INT DEFAULT 0;
 DECLARE a CHAR(16);
 DECLARE b,c INT;
 DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
 DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 OPEN cur1;
 OPEN cur2;

 REPEAT
   FETCH cur1 INTO a, b;
   FETCH cur2 INTO c;
   IF NOT done THEN
      IF b < c THEN
         INSERT INTO test.t3 VALUES (a,b);
      ELSE
         INSERT INTO test.t3 VALUES (a,c);
      END IF;
   END IF;
 UNTIL done END REPEAT;

 CLOSE cur1;
 CLOSE cur2;
END