mysql动态标签可以嵌套吗_Mysql动态嵌套游标_MySQL

bitsCN.com

前提：

表一、 ddm_demandsystem_iddemand_idquotate_end_team11team1，team5，team212tea3，team1，team，4

pk：quotate_end_team、demand_id；

表二、 mc_teamteam_namview_orderteam11team22

解决问题：

将表一中的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

相关标签：动态

本文原创发布php中文网，转载请注明出处，感谢您的尊重！