mysql存储过程语法错误与日期

Hi I am having fun trying to get a stored procedure to parse correctly in MySQL.

嗨,我很乐意尝试在MySQL中正确解析存储过程。

My issue is with dates. I am trying to get the store procedure to create a start date that is the beginning of the current month e.g. 2009-07-01. Using this date I then use the DATA_ADD() function to add a month so that it reads 2009-08-01.

我的问题是日期。我试图让商店程序创建一个开始日期,即当月的开始日期,例如2009-07-01。使用这个日期我然后使用DATA_ADD()函数添加一个月,使其显示为2009-08-01。

However my problem is that when I try and run the procdure to create it I get the following error:

但是我的问题是,当我尝试运行procdure来创建它时,我收到以下错误:

Script line: 7  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 'DECLARE 
cur_month INT;
SET cur_month = (SELECT MONTH(CURRENT_DATE()));

The code for the store procedure looks like this:

存储过程的代码如下所示:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  DECLARE cur_month INT;
  SET cur_month = (SELECT MONTH(CURRENT_DATE()));


  DECLARE temp_date VARCHAR(10);
  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

  DECLARE start_date DATE;
  SET start_date = (SELECT CAST(temp_date AS DATE)));

  DECLARE end_date DATE;
  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));


  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);

 END; //

DELIMITER ;

I've run the queries independantly and they all return correct values and work, it only starts to fail with syntax errors when I add them into a stored procedure.

我独立运行查询并且它们都返回正确的值并且正常工作,当我将它们添加到存储过程时,它只会因语法错误而失败。

What am I missing here that is causing all my head aches?

我在这里错过了什么导致我的头疼?

Thanks...

3 个解决方案

#1

You need to DECLARE all variables at the start of the BEGIN - END block. You also have a mismatched parentheses (extra close) in your CAST line. The following should work:

您需要在BEGIN - END块的开头DECLARE所有变量。您的CAST行中也有不匹配的括号(额外收盘价)。以下应该有效:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  DECLARE cur_month INT;
  DECLARE temp_date VARCHAR(10);
  DECLARE start_date DATE;
  DECLARE end_date DATE;

  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  SET cur_month = (SELECT MONTH(CURRENT_DATE()));

  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));
  SET start_date = (SELECT CAST(temp_date AS DATE));

  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));
  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);


 END; //

DELIMITER ;

#2

I've tried your example, it works in my MySQL 5.0.32 :

我试过你的例子,它适用于我的MySQL 5.0.32:

delimiter //
CREATE PROCEDURE sp_test()
BEGIN
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));
  INSERT INTO tt (y) VALUES (cur_year);
END; //
delimiter ;

CREATE TABLE tt (y INT);
CALL sp_test();
Query OK, 1 row affected (0.00 sec)

You can try rewrite SET as

您可以尝试将SET重写为

SELECT YEAR(CURRENT_DATE()) INTO cur_year;

Will it work? What's your MySQL version?..

它会起作用吗?你的MySQL版本是什么?..

#3

You have to declare all variables before you start to assign values to them, the following code seems to work for me:

您必须在开始为它们赋值之前声明所有变量,以下代码似乎对我有用:

`DELIMITER $$

DROP PROCEDURE IF EXISTS test.sp_test$$ CREATE PROCEDURE test.sp_test () BEGIN

DROP PROCEDURE IF EXISTS test.sp_test $$ CREATE PROCEDURE test.sp_test()BEGIN

/we work out the start and end dates/ DECLARE cur_year INT; DECLARE cur_month INT; DECLARE temp_date VARCHAR(10); DECLARE start_date DATE; DECLARE end_date DATE;

/我们计算出开始和结束日期/ DECLARE cur_year INT; DECLARE cur_month INT; DECLARE temp_date VARCHAR(10); DECLARE start_date DATE; DECLARE end_date DATE;

SET cur_year = (SELECT YEAR(CURRENT_DATE()));

SET cur_year =(SELECT YEAR(CURRENT_DATE()));

SET cur_month = (SELECT MONTH(CURRENT_DATE()));

SET cur_month =(SELECT MONTH(CURRENT_DATE()));

SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

SET temp_date =(SELECT CONCAT(cur_year,' - ',cur_month,' - 01'));

SET start_date = (SELECT CAST(temp_date AS DATE));

SET start_date =(SELECT CAST(temp_date AS DATE));

SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));

SET end_date =(SELECT DATE_ADD(start_date,INTERVAL 1 MONTH));

INSERT INTO alex.my_table (startdate, enddate)VALUES(start_date, end_date);

INSERT INTO alex.my_table(startdate,enddate)VALUES(start_date,end_date);

END$$

DELIMITER ;`

#1