Mysql: Cursor Example

Simple example for looping every row in a table

Data taken from
table: users
fields: username, password

Data to be placed on
table: views
fields: username, password

Hope this will help you guyz

First - Delete the existing stored procedure
DELIMITER
DROP PROCEDURE IF EXISTS proc_CURSOR;

Second
DELIMITER //
CREATE PROCEDURE `proc_CURSOR` ()
BEGIN
	DECLARE _username CHAR(255);
	DECLARE _password CHAR(255);

	DECLARE dataFound INT;
	DECLARE cur1 CURSOR FOR SELECT username, password FROM users;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET dataFound = false;
	OPEN cur1;
		SET dataFound = true;
		WHILE dataFound DO
			FETCH cur1 INTO _username,_password ;
			IF dataFound THEN
				INSERT INTO views (username, password) VALUES (_username,_password);
			end if;
		END WHILE;
	CLOSE cur1;
END //

Third - execute the created procedure
CALL proc_CURSOR();

Tags:

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

You must be logged in to post a comment.