Friday, April 23, 2010

[Level 3] MySQL Stored Procedure Template.

This is my stored procedure template:

--
myparent_block:BEGIN
  -- declare variables
  DECLARE fetch_id INT DEFAULT NULL;
  DECLARE loop_done BOOLEAN DEFAULT FALSE;
  -- declare conditions
  DECLARE condition_not_found CONDITION FOR SQLSTATE '02000';
  -- declare cursors
  DECLARE cursor_myparent CURSOR FOR
    SELECT id FROM myparent WHERE serialnumber=sn;
  -- declare handlers
  DECLARE continue HANDLER FOR condition_not_found
    SET loop_done := TRUE;
  OPEN cursor_myparent;
  REPEAT
    FETCH cursor_myparent into fetch_id;
    DELETE FROM mychild WHERE pid=fetch_id;
  UNTIL loop_done END REPEAT;
  CLOSE cursor_device;
  DELETE FROM myparent WHERE fkid=fetchfkid;
END myparent_block;
--

Wish this helps.
regards,
Stanley Huang