帮忙看看一个mysql存储过程的错误
Query : CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `insist`.`InsertRecord`( _userId INT, _categoryId INT, _d…
Error Code : 1064
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 _STATUS int;
select `status` into _STATUS from record where userid=_us’ at line 30
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
—————————————————
-
SQL code
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `insist`.`InsertRecord`( _userId INT,
_categoryId INT,
_date DATETIME,
_value INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
IF _value=0 THEN
INSERT INTO `insist`.`record`
(`Id`,
`UserId`,
`CategoryId`,
`Date`,
`EachDay`,
`Status`)
VALUES ('Id',
_UserId ,
_CategoryId ,
_Date ,
_VALUE,
0);
END IF;
IF _VALUE=1 THEN
DECLARE _STATUS INT;
SELECT `status` INTO _STATUS FROM record WHERE userid=_userid AND categoryid=_categoryid AND TO_DAYS(_date)-TO_DAYS(`date`)=1;
IF _STATUS IS NULL THEN
_status=0;
END IF
INSERT INTO `insist`.`record`
(`Id`,
`UserId`,
`CategoryId`,
`Date`,
`EachDay`,
`Status`)
VALUES ('Id',
_UserId ,
_CategoryId ,
_Date ,
_VALUE,
_STATUS+1);
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `insist`.`InsertRecord`( _userId INT,
_categoryId INT,
_date DATETIME,
_value INT)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’*/
BEGIN
DECLARE _STATUS INT;
IF _value=0 THEN
INSERT INTO `insist`.`record`
(`Id`,
`UserId`,
`CategoryId`,
`Date`,
`EachDay`,
`Status`)
VALUES (‘Id’,
_UserId ,
_CategoryId ,
_Date ,
_VALUE,
0);
END IF;
IF _VALUE=1 THEN
SELECT `status` INTO _STATUS FROM record WHERE userid=_userid AND categoryid=_categoryid AND TO_DAYS(_date)-TO_DAYS(`date`)=1;
IF _STATUS IS NULL THEN
SET _status=0;
END IF;
INSERT INTO `insist`.`record`
(`Id`,
`UserId`,
`CategoryId`,
`Date`,
`EachDay`,
`Status`)
VALUES (‘Id’,
_UserId ,
_CategoryId ,
_Date ,
_VALUE,
_STATUS+1);
END IF;
END$$
DELIMITER ;
把 DECLARE _STATUS INT;这句放到其它语句之前。
MYSQL和C不同，不可以在中途定义声明变量。
