mariadb fonction, procedure error 你的 SQL 有错误 [英] mariadb fonction, procedure error You have an error in your SQL

问题描述

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 服务器版本相对应的手册，以了解在 'BEGIN 附近使用的正确语法声明 vQuarter VARCHAR(6);声明 vMonth_Name VARCHAR(20);' 在第 2 行

Error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN DECLARE vQuarter VARCHAR(6); DECLARE vMonth_Name VARCHAR(20); ' at line 2

推荐答案

您可能想要创建一个 存储过程而不是函数.

You probably want to create a stored procedure instead of a function.

此外，MariaDB 使用 DO 关键字而不是 LOOP 来标记 WHILE 循环.这是成功完成的 SQL 的固定版本:

Also, MariaDB uses the DO keyword instead of LOOP to mark the start and end of the WHILE loop. Here's a fixed version of the SQL that completes successfully:

DELIMITER //
CREATE PROCEDURE 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')
        DO
            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 + INTERVAL 1 DAY;
        END WHILE;
    END;
END; //

请注意，修改后的 SQL 使用日期算术而不是数字算术.这会正确更新日期，而原始日期会导致日期溢出.

Note that the modified SQL uses date arithmetic instead of numeric arithmetic. This correctly updates the date whereas the original caused a date overflow.

这篇关于mariadb fonction, procedure error 你的 SQL 有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！