Mysql动态嵌套游标

页面导航:首页 > 数据库 > Mysql > Mysql动态嵌套游标

Mysql动态嵌套游标

来源: 作者: 时间:2016-02-02 08:56 【

前提:表一、 ddm_demandsystem_iddemand_idquotate_end_team11team1,team5,team212tea3,team1,team,4 pk:quotate_end_team、demand_id;表二、 mc_teamteam_namview_or

前提:

表一、 ddm_demand

system_id demand_id quotate_end_team
1 1 team1,team5,team2
1 2 tea3,team1,team,4

pk: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;

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<