CREATE PROCEDURE add_test()
BEGIN
DECLARE a int;
DECLARE success int;
DECLARE failure int;
DECLARE stop int default 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
DECLARE cursor_name CURSOR FOR select ID FROM `sms_send_log` where is_long_sms<>1;
OPEN cursor_name;
FETCH cursor_name INTO a;
WHILE stop<>1 DO
set success = select count(B.ID) FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 1 AND C.ID=a;
set failure = select count(B.ID) FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 0 AND C.ID=a;
update sms_send_log set SMS_SUCCESS_NUM= success,SMS_FAILURE_NUM=failure where ID= a;
END WHILE;
CLOSE cursor_name;
END
call add_test();本人参考网上的教程写了一个存储过程,不知道为什么老是语法错误,目的就是把select ID FROM `sms_send_log` where is_long_sms<>1取到的id作为变量条件,对下面的语句进行更新操作,请
各位大神帮忙看看,或者写个别的,多谢大家!
解决方案 »
- mysql 建表多个字段默认值为当前时间
- 关于数据库存储图片的问题
- 有没有简单点的方法,两个表UNION 后LEFT JOIN 其他的两个表,共有四个表
- 网站镜像,主网站和镜像网站Mysql数据同步
- 散分!!收到CSDN给的上海英雄会电子门票,激动中..
- 系统ID_用户ID(唯一) 能做吗 ?
- windows下如何升级mysql的client从3.23到4.0.x?
- mysql中有没有类似php中的"list"函数?
- 如何只用一条delete语句便可以同时删除主表和子表中的数据?
- 请教mysql 中SELECT LAST_INSERT_ID() 获取的值的使用
- 关于mysql管理软件反编译问题
- mysql使用xcopy进行定时备份,bat文件中的语句该怎么写?
CREATE
PROCEDURE binxian.add_test()
BEGIN
DECLARE ps_id int;
DECLARE success int;
DECLARE failure int;
DECLARE done1 int default 0;
DECLARE cur1 cursor for SELECT ID FROM `sms_send_log` where is_long_sms<>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
open cur1;
repeat
FETCH cur1 INTO ps_id;
if not done1 then
select count(B.ID) into success FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 1 AND C.ID=ps_id;
select count(B.ID) into failure FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 0 AND C.ID=ps_id;
update sms_send_log set SMS_SUCCESS_NUM= success,SMS_FAILURE_NUM=failure where ID= ps_id;
end IF;
until done1 end
REPEAT;
CLOSE cur1;
commit;
END
DROP PROCEDURE IF EXISTS add_test$$
CREATE PROCEDURE add_test()
BEGIN
DECLARE a INT;
DECLARE success INT;
DECLARE failure INT;
DECLARE STOP INT DEFAULT 0;
DECLARE cursor_name CURSOR FOR SELECT ID FROM `sms_send_log` WHERE is_long_sms<>1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET `stop`=1;
OPEN cursor_name;
FETCH cursor_name INTO a;
WHILE STOP<>1 DO
SET success = (SELECT COUNT(B.ID) FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 1 AND C.ID=a);
SET `stop`=0;
SET failure = (SELECT COUNT(B.ID) FROM sms_send_real_log AS B,sms_send_log AS C WHERE B.ID = C.ID AND B.status = 0 AND C.ID=a);
SET `stop`=0;
UPDATE sms_send_log SET SMS_SUCCESS_NUM= success,SMS_FAILURE_NUM=failure WHERE ID= a;
SET `stop`=0;
END WHILE;
CLOSE cursor_name;
END$$
DELIMITER $$