MariaDB pl/sql来填充一个从“1990-01-01”到“2000-12-31”的日期表

Question

我尝试创建一个过程或函数来填充MariaDB中Time_Dim的表名。

DELIMITER /
CREATE FUNCTION filltimeDim ()
BEGIN
    DECLARE vQuarter VARCHAR(6);
    DECLARE vMonth_Name VARCHAR(20);
    DECLARE vdate_id date;
    DECLARE vyear_id CHAR(4);
    DECLARE vweekly_Name VARCHAR(20);
    DECLARE vMonth_Num TINYINT(10);
    DECLARE vweekday_Num TINYINT(10);
    BEGIN
        SET vdate_id =  CONVERT('1998-01-01', DATE);
        WHILE (CONVERT('vdate_id' USING utf8) <=  '2002-12-31')
        LOOP
            SET vyear_id =  YEAR(vdate_id);
            SET vQuarter =  QUARTER(vdate_id);
            SET vMonth_Name =  MONTHNAME(vdate_id);
            SET vweekly_Name =  DAYOFWEEK(vdate_id);
            SET vMonth_Num =  MONTH(vdate_id);
            SET vweekday_Num =  WEEKDAY(vdate_id);
            INSERT INTO Time_Dim VALUES 
            (vdate_id, vyear_id, vquarter, vMonth_Name,vweekly_Name,vMonth_Num,vweekday_Num);
            SET vdate_id = vdate_id + 1;
        END LOOP;   
    END;
END; /

错误1064 (42000)：您的SQL语法有错误；检查与您的MariaDB服务器版本对应的手册，在第13行的“LOOP SETvyear_id=YEAR（vdate_id）； SET vQuarter=QUA”附近使用正确的语法

Answer 1

您可以使用序列存储引擎：

-- SELECT
  -- DATEDIFF('2002-12-31', '1990-01-01'); -- 4747

INSERT INTO `Time_Dim` (
  `vdate_id`,
  `vyear_id`,
  `vquarter`,
  `vMonth_Name`,
  `vweekly_Name`,
  `vMonth_Num`,
  `vweekday_Num`
)
SELECT
  `der`.`day`,
  YEAR(`der`.`day`),
  QUARTER(`der`.`day`),
  MONTHNAME(`der`.`day`),
  DAYOFWEEK(`der`.`day`),
  MONTH(`der`.`day`),
  WEEKDAY(`der`.`day`)
FROM (
  SELECT
    '1990-01-01' + INTERVAL (`seq`) DAY `day`
  FROM
    `seq_0_to_4747`
) `der`;

看看小提琴。

Answer 2

MariaDB中while的语法为：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

https://mariadb.com/kb/en/library/while/

您不应该使用“循环”关键字