mysql ストアド - tyama-joy @ ウィキ

• ストアドファンクション

DELIMITER $$

DROP FUNCTION IF EXISTS `exchange`.`sf_sma` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `sf_sma`(in_date date,in_days int) RETURNS double
BEGIN
  DECLARE rtn_val double default 0;
  DECLARE done INT DEFAULT 0;
  declare rate varchar(6);
  declare total_rate double default 0;
  declare cnt int default 0;
  declare cur cursor for select end from rate where date < in_date order by date desc;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 

  open cur;
  REPEAT
    FETCH cur INTO rate;
    IF NOT done THEN
      set cnt = cnt + 1;
      set total_rate = total_rate + rate;
      if cnt = in_days then
        SET done = 1;
      end if;
    END IF;
  UNTIL done END REPEAT;
  close cur;

  IF cnt = in_days THEN
    set rtn_val = total_rate / in_days;
  END IF;

  return round(rtn_val,2);
END $$

DELIMITER ;