MySQL プロシージャサンプル

Function@MySQL

CREATE PROCEDURE `p.resource-update`(
     OUT _ret         VARCHAR(255),
     IN  resource      INT,
     IN  resource_name VARCHAR(255),
     IN  resource_note TEXT       
)
BEGIN

   DECLARE val BOOLEAN;
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
     ROLLBACK;
   END;
 
   SET _ret = 'ERROR00';
 
   START TRANSACTION;
 
   main_proc: BEGIN

      /* resource の存在チェック */
      IF NOT `baihu`.`f.resource-p`(resource) THEN
         SET _ret = 'ERROR02';
         LEAVE main_proc;
      END IF;

      /* update */
      UPDATE `baihu`.`rs_resource`
         SET `update-datetime` = now()
           , `name`            = resource_name
           , `note`            = resource_note
       WHERE `resource-id`     = resource;

      SET _ret = NULL; 

   END main_proc;
 
   IF _ret IS NULL THEN
      COMMIT;
   ELSE
      ROLLBACK;
   END IF;

END