高分求助mysql 存储过程报错以及如何返回受影响的行数

[seqwait]

DELIMITER $$
USE `xiehou_3`$$
DROP PROCEDURE IF EXISTS `proc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(IN iuid INT,IN icnt INT)
BEGIN
     DECLARE idate DATE;
     SELECT `date` INTO idate FROM tb_act20140602_user_value WHERE uid=iuid;
     IF idate IS NULL THEN   
        INSERT INTO tb_act20140602_user_value VALUES(iuid,icnt,NOW(),icnt,icnt,NOW());
     ELSEIF idate=CONVERT(NOW(),DATE) THEN
        UPDATE tb_act20140602_user_value SET daycnt=daycnt+icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW() WHERE uid=iuid;
     ELSE
        UPDATE tb_act20140602_user_value SET daycnt=icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW(),`date`=NOW() WHERE uid=iuid;
     END IF;
    END$$
DELIMITER;
 
以上语句执行后

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:047
Total Time     : 00:00:00:047

(0 row(s) affected)
Execution Time : 00:00:00:032
Transfer Time  : 00:00:01:201
Total Time     : 00:00:01:233

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:015
Total Time     : 00:00:00:015

错误码: 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 'DELIMITER' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000


存储过程proc创建成功


调用结果

(1 row(s) affected)
Execution Time : 00:00:00:093
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:093

我就想问为毛有一行报错

顺便问下如何返回受影响的行数

[benluobo]

错误码: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the 贴完整 定义返回变量rowCount select ROW_COUNT() into rowCount return rowCount

[WWWWA]

DELIMITER $$：前面有无空格 用ROW_COUNT()返回行数

[ACMAIN_CHM]

这段语句是在什么工具中执行的？ 请在MYSQL自带的命令行工具中进行测试。

[seqwait]

引用 1 楼 benluobobo 的回复:

错误码: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the 贴完整 定义返回变量rowCount select ROW_COUNT() into rowCount return rowCount

贴完整了,往右托

[hucuibai]

USE `xiehou_3`;
DROP PROCEDURE IF EXISTS `proc`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(IN iuid INT,IN icnt INT)
BEGIN
     DECLARE idate DATE;
     SELECT `date` INTO idate FROM tb_act20140602_user_value WHERE uid=iuid;
     IF idate IS NULL THEN   
        INSERT INTO tb_act20140602_user_value VALUES(iuid,icnt,NOW(),icnt,icnt,NOW());
     ELSEIF idate=CONVERT(NOW(),DATE) THEN
        UPDATE tb_act20140602_user_value SET daycnt=daycnt+icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW() WHERE uid=iuid;
     ELSE
        UPDATE tb_act20140602_user_value SET daycnt=icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW(),`date`=NOW() WHERE uid=iuid;
     END IF;
END
$$

[hucuibai]

引用 楼主 oZhiMing12 的回复:

DELIMITER $$
USE `xiehou_3`$$
DROP PROCEDURE IF EXISTS `proc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(IN iuid INT,IN icnt INT)
BEGIN
     DECLARE idate DATE;
     SELECT `date` INTO idate FROM tb_act20140602_user_value WHERE uid=iuid;
     IF idate IS NULL THEN   
        INSERT INTO tb_act20140602_user_value VALUES(iuid,icnt,NOW(),icnt,icnt,NOW());
     ELSEIF idate=CONVERT(NOW(),DATE) THEN
        UPDATE tb_act20140602_user_value SET daycnt=daycnt+icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW() WHERE uid=iuid;
     ELSE
        UPDATE tb_act20140602_user_value SET daycnt=icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW(),`date`=NOW() WHERE uid=iuid;
     END IF;
    END$$
DELIMITER;
 
以上语句执行后

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:047
Total Time     : 00:00:00:047

(0 row(s) affected)
Execution Time : 00:00:00:032
Transfer Time  : 00:00:01:201
Total Time     : 00:00:01:233

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:015
Total Time     : 00:00:00:015

错误码: 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 'DELIMITER' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000


存储过程proc创建成功


调用结果

(1 row(s) affected)
Execution Time : 00:00:00:093
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:093

我就想问为毛有一行报错

顺便问下如何返回受影响的行数

最后一行DELIMITER ; 中间没空格

[seqwait]

引用 2 楼 WWWWA 的回复:

DELIMITER $$：前面有无空格 用ROW_COUNT()返回行数

引用 6 楼 hucuibai 的回复:

[quote=引用 楼主 oZhiMing12 的回复:]

DELIMITER $$
USE `xiehou_3`$$
DROP PROCEDURE IF EXISTS `proc`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(IN iuid INT,IN icnt INT)
BEGIN
     DECLARE idate DATE;
     SELECT `date` INTO idate FROM tb_act20140602_user_value WHERE uid=iuid;
     IF idate IS NULL THEN   
        INSERT INTO tb_act20140602_user_value VALUES(iuid,icnt,NOW(),icnt,icnt,NOW());
     ELSEIF idate=CONVERT(NOW(),DATE) THEN
        UPDATE tb_act20140602_user_value SET daycnt=daycnt+icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW() WHERE uid=iuid;
     ELSE
        UPDATE tb_act20140602_user_value SET daycnt=icnt,maxcnt=maxcnt+icnt,`count`=`count`+icnt,lastModifyTime=NOW(),`date`=NOW() WHERE uid=iuid;
     END IF;
    END$$
DELIMITER;
 
以上语句执行后

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:047
Total Time     : 00:00:00:047

(0 row(s) affected)
Execution Time : 00:00:00:032
Transfer Time  : 00:00:01:201
Total Time     : 00:00:01:233

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:015
Total Time     : 00:00:00:015

错误码: 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 'DELIMITER' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000


存储过程proc创建成功


调用结果

(1 row(s) affected)
Execution Time : 00:00:00:093
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:093

我就想问为毛有一行报错

顺便问下如何返回受影响的行数

最后一行DELIMITER ; 中间没空格[/quote] 嗯,加上空格就对了,在问下怎么在resultset中获取受影响的行数呢??

[wwwwb]

select ROW_COUNT()　即可得到 select ROW_COUNT() into @aa　存入变量