mysql - 在mysql中使用存储过程插入日期维度日期时出错

Question

我正在使用以下存储过程来填充日期维度。并且出现以下错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM Date_Dim SET v_full_date = p_start_date WHILE v_full_date < p_end_d' at line 6

CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)

BEGIN

DECLARE v_full_date DATE
DELETE FROM Date_Dim
SET v_full_date = p_start_date

WHILE v_full_date < p_end_date 
DO
 INSERT INTO Date_Dim 
(
            Full_Date ,
            DayOfMonth ,
            DayOfYear ,
            DayOfWeek ,
            Day_Name ,
            Month_Number,
            Month_Name,
            Year,
            Quarter
  )
 VALUES 
(
            v_full_date,
            DAYOFMONTH(v_full_date),
            DAYOFYEAR(v_full_date),
            DAYOFWEEK(v_full_date),
            DAYNAME(v_full_date),
            MONTH(v_full_date),
            MONTHNAME(v_full_date),
            YEAR(v_full_date),
            QUARTER(v_full_date)
   );

SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY)

END WHILE
END

Answer 1

您必须分隔语句。试试这个:

DELIMITER $$

CREATE PROCEDURE datedimbuild (p_start_date DATE, p_end_date DATE)

BEGIN

DECLARE v_full_date DATE;
DELETE FROM Date_Dim;
SET v_full_date = p_start_date;

WHILE v_full_date < p_end_date 
DO
 INSERT INTO Date_Dim 
(
            Full_Date ,
            DayOfMonth ,
            DayOfYear ,
            DayOfWeek ,
            Day_Name ,
            Month_Number,
            Month_Name,
            Year,
            Quarter
  )
 VALUES 
(
            v_full_date,
            DAYOFMONTH(v_full_date),
            DAYOFYEAR(v_full_date),
            DAYOFWEEK(v_full_date),
            DAYNAME(v_full_date),
            MONTH(v_full_date),
            MONTHNAME(v_full_date),
            YEAR(v_full_date),
            QUARTER(v_full_date)
   );

SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);

END WHILE;
END $$

DELIMITER ;