mariadb功能,过程错误您的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; /
错误:ERROR 1064(42000):您的SQL语法有错误;检查与您的MariaDB服务器版本相对应的手册,以获取在'BEGIN附近使用的正确语法 声明vQuarter VARCHAR(6); DECLARE 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.
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功能,过程错误您的SQL错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!