markomedia - web development

• Author

  Marko Tomic

  Category

  MySQL

  Tags

  cursors, MySQL, StoredProcedures

  Comments

  0

  I’ve wanted to write a few stored procedures in MySQL for a while now, but I found it very fiddly and was unable to come up with one very quickly. Tonight I was determined to read up on MySQL documentation and get one going.

  For my reference only, this stored procedure will loop through a recordset and update a single row in a table upon a single row lookup in another table.

  DELIMITER // 
   
  DROP PROCEDURE IF EXISTS sp_test //
   
  CREATE PROCEDURE sp_test ()
  BEGIN
  	DECLARE done INT DEFAULT FALSE;
  	DECLARE myid INT;
  	DECLARE cur1 CURSOR FOR SELECT id from table1 WHERE firstname IS NULL;
  	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
  	OPEN cur1;
   
  	read_loop: LOOP
  		IF done THEN
  			LEAVE read_loop;
  		END IF;
   
  		FETCH cur1 INTO myid;
  		UPDATE table1 SET firstname = (SELECT firstname from table2 WHERE id = myid)
  		WHERE id = myid;
  	END LOOP;
   
  	close cur1;
  END;
  //

  Once I got the procedure to compile, executing it was as simple as:

  call sp_test

  Marko

• Previous post
• Next post

Related posts

• Compile PHP pcntl module on OS X Lion
• IE6, IE7, IE8, & IE9 on OS X in Virtual Machine
• opendiff and FileMerge on OS X
• Bandwidth throttling on OS X
• bash script useful tips

Share this post

Author Marko Tomic

No comments yet.