mysql 存储过程 游标嵌套

Joshua_zhong 2015-10-09 06:27:07
代码需求：业务更改状态时写通知表，通知用户。但是报错。各位大侠帮忙看下呗？？



BEGIN

	declare main_id,business_id,people_count, msg_id,launch_user_id int;

	DECLARE business_type,msg_type CHAR(2);

	declare _DONE TINYINT(1) default 0;     /*是否达到记录的末尾控制变量*/

	DECLARE regists CURSOR FOR select r.main_id, '02' as business_type, '02' as msg_type, r.id as business_id , r.launch_user_id, (SELECT count(DISTINCT la.wz_regist_user.user_id) FROM wz_regist_options JOIN wz_regist_user ON la.wz_regist_options.id = la.wz_regist_user.option_id WHERE la.wz_regist_options.regist_id = r.id) AS people_count   FROM  wz_regist r  where r.cutoff_datetime<=now() and r.status='01';

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DONE = 1;

	OPEN regists;

	LOOP_OUTER:LOOP

		FETCH regists INTO main_id, business_type, msg_type, business_id, launch_user_id, people_count;

		IF _DONE =1 THEN

			LEAVE LOOP_OUTER;

		END IF;

		BEGIN

					update wz_regist r set r.status='03' where r.id = business_id;

					insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type)  values(main_id, '系统通知', concat('您发起的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);

					select LAST_INSERT_ID() into msg_id;

					IF launch_user_id is not NULL  THEN

							INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, launch_user_id, now(), '0');

					end IF;

					IF people_count >0 THEN

								insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type)  values(main_id, '系统通知', concat('您参与的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);

								DECLARE msg_id_INNER int;

								select LAST_INSERT_ID() into msg_id_INNER;

								declare _inner tinyint(1) default 0; 

								DECLARE joiner_id int(11);

								declare continue handler for not found set _inner = 1;  

								DECLARE regist_joiners CURSOR FOR SELECT DISTINCT wz_regist_user.user_id as joiner_id FROM wz_regist_options JOIN wz_regist_user ON wz_regist_options.id = wz_regist_user.option_id WHERE wz_regist_options.regist_id = business_id; 



								OPEN regist_joiners;

								LOOP_INNER:LOOP

											FETCH regist_joiners INTO joiner_id;

											if _inner=1 then  

													leave LOOP_INNER;  

											end if;

											INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, joiner_id, now(), '0');	

								END LOOP;

								COMMIT;

					END IF;

		END;

	END LOOP;

END
...全文
152 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 2015-10-10
  • 打赏
  • 举报
 回复
写的好复杂啊。。。
Joshua_zhong 2015-10-10
  • 打赏
  • 举报
 回复
游标循环体中不能再定义其他游标么？我把inner_loop中的游标放在外面定义就行了！
BEGIN
    declare main_id,business_id,people_count, msg_id,launch_user_id int;
    DECLARE business_type,msg_type CHAR(2);
		DECLARE msg_id_INNER int;
		DECLARE joiner_id int(11);
    declare _DONE TINYINT(1) default 0;     /*是否达到记录的末尾控制变量*/
    DECLARE regists CURSOR FOR select r.main_id, '02' as business_type, '02' as msg_type, r.id as business_id , r.launch_user_id, (SELECT count(DISTINCT la.wz_regist_user.user_id) FROM wz_regist_options JOIN wz_regist_user ON la.wz_regist_options.id = la.wz_regist_user.option_id WHERE la.wz_regist_options.regist_id = r.id) AS people_count   FROM  wz_regist r  where r.cutoff_datetime<=now() and r.status='01';
		DECLARE regist_joiners CURSOR FOR SELECT DISTINCT wz_regist_user.user_id as joiner_id FROM wz_regist_options JOIN wz_regist_user ON wz_regist_options.id = wz_regist_user.option_id WHERE wz_regist_options.regist_id = business_id; 
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DONE = 1;
    OPEN regists;
    LOOP_OUTER:LOOP
        FETCH regists INTO main_id, business_type, msg_type, business_id, launch_user_id, people_count;
        IF _DONE =1 THEN
            LEAVE LOOP_OUTER;
        END IF;
        BEGIN
                    update wz_regist r set r.status='03' where r.id = business_id;
                    insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type)  values(main_id, '系统通知', concat('您发起的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
                    select LAST_INSERT_ID() into msg_id;
                    IF launch_user_id is not NULL  THEN
                            INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, launch_user_id, now(), '0');
                    end IF;
                    IF people_count >0 THEN
                                insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type)  values(main_id, '系统通知', concat('您参与的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
                                select LAST_INSERT_ID() into msg_id_INNER;
                                OPEN regist_joiners;
                                LOOP_INNER:LOOP
                                            FETCH regist_joiners INTO joiner_id;
                                            if _DONE=1 then  
                                                    leave LOOP_INNER;  
                                            end if;
                                            INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, joiner_id, now(), '0');   
                                END LOOP;
                    END IF;
        END;
    END LOOP;
END
Joshua_zhong 2015-10-10
  • 打赏
  • 举报
 回复
引用 2 楼 ACMAIN_CHM 的回复:
注意红字部分。

BEGIN
declare main_id,business_id,people_count, msg_id,launch_user_id int;
DECLARE business_type,msg_type CHAR(2);
declare _DONE TINYINT(1) default 0; /*是否达到记录的末尾控制变量*/
declare _DONE_O TINYINT;
DECLARE regists CURSOR FOR select r.main_id, '02' as business_type, '02' as msg_type, r.id as business_id , r.launch_user_id, (SELECT count(DISTINCT la.wz_regist_user.user_id) FROM wz_regist_options JOIN wz_regist_user ON la.wz_regist_options.id = la.wz_regist_user.option_id WHERE la.wz_regist_options.regist_id = r.id) AS people_count FROM wz_regist r where r.cutoff_datetime<=now() and r.status='01';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DONE = 1;
OPEN regists;
LOOP_OUTER:LOOP
FETCH regists INTO main_id, business_type, msg_type, business_id, launch_user_id, people_count;
IF _DONE =1 THEN
LEAVE LOOP_OUTER;
END IF;
BEGIN
set _DONE_O=_DONE;
update wz_regist r set r.status='03' where r.id = business_id;
insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您发起的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
select LAST_INSERT_ID() into msg_id;
IF launch_user_id is not NULL THEN
INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, launch_user_id, now(), '0');
end IF;
IF people_count >0 THEN
insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您参与的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
DECLARE msg_id_INNER int;
select LAST_INSERT_ID() into msg_id_INNER;
declare _inner tinyint(1) default 0;
DECLARE joiner_id int(11);
declare continue handler for not found set _inner = 1;
DECLARE regist_joiners CURSOR FOR SELECT DISTINCT wz_regist_user.user_id as joiner_id FROM wz_regist_options JOIN wz_regist_user ON wz_regist_options.id = wz_regist_user.option_id WHERE wz_regist_options.regist_id = business_id;

OPEN regist_joiners;
LOOP_INNER:LOOP
FETCH regist_joiners INTO joiner_id;
if _inner=1 then
leave LOOP_INNER;
end if;
INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, joiner_id, now(), '0');
END LOOP;
COMMIT;
END IF;
set _DONE=_DONE_O;
END;
END LOOP;
END
Joshua_zhong 2015-10-10
  • 打赏
  • 举报
 回复
引用 2 楼 ACMAIN_CHM 的回复:
注意红字部分。

BEGIN
declare main_id,business_id,people_count, msg_id,launch_user_id int;
DECLARE business_type,msg_type CHAR(2);
declare _DONE TINYINT(1) default 0; /*是否达到记录的末尾控制变量*/
declare _DONE_O TINYINT;
DECLARE regists CURSOR FOR select r.main_id, '02' as business_type, '02' as msg_type, r.id as business_id , r.launch_user_id, (SELECT count(DISTINCT la.wz_regist_user.user_id) FROM wz_regist_options JOIN wz_regist_user ON la.wz_regist_options.id = la.wz_regist_user.option_id WHERE la.wz_regist_options.regist_id = r.id) AS people_count FROM wz_regist r where r.cutoff_datetime<=now() and r.status='01';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DONE = 1;
OPEN regists;
LOOP_OUTER:LOOP
FETCH regists INTO main_id, business_type, msg_type, business_id, launch_user_id, people_count;
IF _DONE =1 THEN
LEAVE LOOP_OUTER;
END IF;
BEGIN
set _DONE_O=_DONE;
update wz_regist r set r.status='03' where r.id = business_id;
insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您发起的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
select LAST_INSERT_ID() into msg_id;
IF launch_user_id is not NULL THEN
INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, launch_user_id, now(), '0');
end IF;
IF people_count >0 THEN
insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您参与的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type);
DECLARE msg_id_INNER int;
select LAST_INSERT_ID() into msg_id_INNER;
declare _inner tinyint(1) default 0;
DECLARE joiner_id int(11);
declare continue handler for not found set _inner = 1;
DECLARE regist_joiners CURSOR FOR SELECT DISTINCT wz_regist_user.user_id as joiner_id FROM wz_regist_options JOIN wz_regist_user ON wz_regist_options.id = wz_regist_user.option_id WHERE wz_regist_options.regist_id = business_id;

OPEN regist_joiners;
LOOP_INNER:LOOP
FETCH regist_joiners INTO joiner_id;
if _inner=1 then
leave LOOP_INNER;
end if;
INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, joiner_id, now(), '0');
END LOOP;
COMMIT;
END IF;
set _DONE=_DONE_O;
END;
END LOOP;
END



现在是有语法错误，保存都保存不了？
ACMAIN_CHM 2015-10-09
  • 打赏
  • 举报
 回复
注意红字部分。 BEGIN declare main_id,business_id,people_count, msg_id,launch_user_id int; DECLARE business_type,msg_type CHAR(2); declare _DONE TINYINT(1) default 0; /*是否达到记录的末尾控制变量*/ declare _DONE_O TINYINT; DECLARE regists CURSOR FOR select r.main_id, '02' as business_type, '02' as msg_type, r.id as business_id , r.launch_user_id, (SELECT count(DISTINCT la.wz_regist_user.user_id) FROM wz_regist_options JOIN wz_regist_user ON la.wz_regist_options.id = la.wz_regist_user.option_id WHERE la.wz_regist_options.regist_id = r.id) AS people_count FROM wz_regist r where r.cutoff_datetime<=now() and r.status='01'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET _DONE = 1; OPEN regists; LOOP_OUTER:LOOP FETCH regists INTO main_id, business_type, msg_type, business_id, launch_user_id, people_count; IF _DONE =1 THEN LEAVE LOOP_OUTER; END IF; BEGIN set _DONE_O=_DONE; update wz_regist r set r.status='03' where r.id = business_id; insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您发起的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type); select LAST_INSERT_ID() into msg_id; IF launch_user_id is not NULL THEN INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, launch_user_id, now(), '0'); end IF; IF people_count >0 THEN insert into wz_user_message(main_id, title, content, msg_type, value_datetime, business_id, business_type) values(main_id, '系统通知', concat('您参与的报名已截止，共有',people_count,'人参与报名。'), msg_type, now(), business_id, business_type); DECLARE msg_id_INNER int; select LAST_INSERT_ID() into msg_id_INNER; declare _inner tinyint(1) default 0; DECLARE joiner_id int(11); declare continue handler for not found set _inner = 1; DECLARE regist_joiners CURSOR FOR SELECT DISTINCT wz_regist_user.user_id as joiner_id FROM wz_regist_options JOIN wz_regist_user ON wz_regist_options.id = wz_regist_user.option_id WHERE wz_regist_options.regist_id = business_id; OPEN regist_joiners; LOOP_INNER:LOOP FETCH regist_joiners INTO joiner_id; if _inner=1 then leave LOOP_INNER; end if; INSERT INTO wz_user_message_receive(message_id, wz_user_id, receive_datetime, is_read) VALUES (msg_id, joiner_id, now(), '0'); END LOOP; COMMIT; END IF; set _DONE=_DONE_O; END; END LOOP; END
rick-he 2015-10-09
  • 打赏
  • 举报
 回复
把错误也贴下。
Mysql存储过程循环内嵌套使用游标示例代码
本节主要介绍了Mysql存储过程循环内如何嵌套使用游标，详细实现如下，需要的朋友不要错过
mysql存储过程双层嵌套
loop 游标双层嵌套循环 创建临时表， 游标
15天入门MySQL和高性能调优视频教程
15天玩转MySQL和高性能调优视频培训课程是一套入门MySQL数据库和高性能调优的课程，通过本课程的学习，可以掌握MySQL的使用，包括了解MySQL数据库、数据类型、常用操作、查询、函数和存储过程、视图、游标和触发器；...
mysql 多个游标依次执行
mysql存储过程 多个游标循环（依次执行，非嵌套循环）REPEAT循环。有需要的可自行下载。
mysql存储过程 游标 循环使用介绍
今天分享下自己对于Mysql存储过程的认识与了解,这里主要说说大家常用的游标加循环的嵌套使用
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章