存储过程MySQL 5.5.16

So I have in my database a table called "weeks" where I store every weeks of the years like so:

所以我在我的数据库中有一个名为“周”的表，我在这里每隔几周存储一次，如下所示：

table weeks(id, year, num_week, date_min, date_max)

表周（id，year，num_week，date_min，date_max）

So for this week, the line look like this :

所以本周，这条线看起来像这样：

Note : My weeks starts on thursday and ends on Wenesday.

注意：我的周从周四开始，到周三结束。

Since it is a pain inserting each weeks line by line, I want to create a stored procedure for this, here's what I came up with :

由于每周都会逐行插入一个痛苦，我想为此创建一个存储过程，这就是我想到的：

DELIMITER |
CREATE PROCEDURE proc_insert_weeks()

BEGIN

SELECT year, num_week, date_min, date_max INTO @year, @num_week, @date_min, @date_max
FROM weeks
ORDER BY date_min DESC LIMIT 1;

SET @date_min = DATE_ADD(@date_max INTERVAL 1 DAY);
SET @date_max = DATE_ADD(@date_min INTERVAL 6 DAY);
SET @year= YEAR(@date_min);

IF @num_week < 52 THEN SET @num_week = @num_week + 1;
ELSE SET @num_week = 1;
END IF;

INSERT INTO weeks (year, num_week, date_min, date_max)
VALUES (@year, @num_week, @date_min, @date_max);

END |

DELIMITER ;

So the idea was to take the last record of the table and add 1 week to the dates, but I can't even make it paste the creation of the procedure.

因此，我们的想法是获取表格的最后一条记录，并在日期中添加1周，但我甚至无法粘贴程序的创建。

I get an error right after the SELECT query, can someone help me figure out what I am doing wrong ?

我在SELECT查询后立即收到错误，有人可以帮我弄清楚我做错了什么吗？

1 个解决方案

#1

drop PROCEDURE proc_insert_weeks;
DELIMITER |
CREATE PROCEDURE proc_insert_weeks()

BEGIN

SELECT year, num_week, date_min, date_max INTO @year, @num_week, @date_min, @date_max
FROM weeks
ORDER BY date_min DESC LIMIT 1;

SET @date_min = DATE_ADD(@date_max, INTERVAL 1 DAY);
SET @date_max = DATE_ADD(@date_min, INTERVAL 6 DAY);
SET @year= YEAR(@date_min);

IF @num_week < 52 THEN SET @num_week = @num_week + 1;
ELSE SET @num_week = 1;
END IF;

INSERT INTO weeks (year, num_week, date_min, date_max)
VALUES (@year, @num_week, @date_min, @date_max);

END |

DELIMITER ;

You can try above code.

你可以试试上面的代码。

You made mistake in DATE_ADD function. You missed , in it.

你在DATE_ADD函数中犯了错误。你想念它。