首页 / MYSQL / Mysql动态嵌套游标_MySQL
Mysql动态嵌套游标_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql动态嵌套游标_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2111字,纯文字阅读大概需要4分钟。
内容图文
![Mysql动态嵌套游标_MySQL](/upload/InfoBanner/zyjiaocheng/577/b1e83d2fdf224b69b77493a0681886a5.jpg)
前提:
表一、 ddm_demand
system_id demand_id quotate_end_team 1 1 team1,team5,team2 1 2 tea3,team1,team,4pk:quotate_end_team、demand_id;
表二、 mc_team
team_nam view_order team1 1 team2 2解决问题:
将表一中的quotate_end_team字段按照表二的view_order重新排序。
方法:
DELIMITER $$
DROP PROCEDURE IF EXISTS order_team_name $$
CREATE PROCEDURE order_team_name()
BEGIN
declare order_before_teams text ;
declare temp_team_nam text;
declare systemId decimal(10,0) unsigned;
declare demandId char(6);
declare done int;
-- 定义游标1
DECLARE rs_cursor CURSOR FOR SELECT system_id,demand_id,quotate_end_team FROM ddm_demand;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open rs_cursor;
cursor_loop:loop
FETCH rs_cursor into systemId,demandId,order_before_teams;
if done=1 then
leave cursor_loop;
end if;
if order_before_teams is not null and order_before_teams <> '' then
SET @sqlstr = concat("where team_nam in ('",replace(order_before_teams,",","','"),"');");
SET @sqlstr = concat("CREATE VIEW temporary_team_view as SELECT team_nam,view_order FROM mc_team ",@sqlstr);
-- DROP VIEW IF EXISTS temporary_team_view;
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
-- 调用游标2
CALL update_ddm_demand(systemId,demandId);
drop view temporary_team_view;
end if;
end loop cursor_loop;
close rs_cursor;
END$$
DROP PROCEDURE IF EXISTS update_ddm_demand $$
CREATE PROCEDURE update_ddm_demand(systemId decimal(10,0) unsigned,demandId char(6))
BEGIN
declare temp_team_nam text;
declare order_after_teams text;
declare done int;
-- 定义游标2
DECLARE rs_cursor CURSOR FOR SELECT team_nam FROM temporary_team_view order by view_order;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET order_after_teams = "";
open rs_cursor;
cursor_loop:loop
FETCH rs_cursor into temp_team_nam;
if done=1 then
leave cursor_loop;
end if;
-- 更新表
SET order_after_teams = concat(order_after_teams,",",temp_team_nam);
end loop cursor_loop;
update ddm_demand set quotate_end_team = subString(order_after_teams,2) where system_id = systemId and demand_id = demandId;
close rs_cursor;
END$$
DELIMITER ;
call order_team_name;
bitsCN.com内容总结
以上是互联网集市为您收集整理的Mysql动态嵌套游标_MySQL全部内容,希望文章能够帮你解决Mysql动态嵌套游标_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。