sql/recursive.sql

   1
   2
   3 You can create recursive procedures following the same guidelines. First create the procedure with a simple body that throws an exception. You need to specify the SPECIFIC name of the procedure:
   4
   5 CREATE PROCEDURE updateFolderTotals(IN p_id VARCHAR(32), IN p_size BIGINT, IN p_files   INT, IN p_folders INT)
   6   SPECIFIC updateFolderTotals_1 MODIFIES SQL DATA
   7   SIGNAL SQLSTATE '45000'
   8
   9 Then alter the created procedure with the full body:
  10
  11 ALTER SPECIFIC ROUTINE updateFolderTotals_1
  12   BEGIN ATOMIC
  13   DECLARE l_parentid VARCHAR(32);
  14   UPDATE folders
  15   SET tot_files = tot_files + p_files,
  16     tot_size = tot_size + p_size ,
  17     tot_folders = tot_folders + p_folders
  18   WHERE id = p_id;
  19
  20   SELECT parentid INTO l_parentid FROM folders WHERE id = p_id;
  21
  22   IF (l_parentid IS NOT NULL) THEN
  23     CALL updateFolderTotals(l_parentid,p_size,p_files,p_folders);
  24   END IF;
  25 END;
  26
  27