帮忙看看一个mysql存储过程的异常

帮忙看看一个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不同，不可以在中途定义声明变量。