Mysql dynamic nested cursor _ MySQL

Mysql dynamic nested cursor bitsCN.com

Prerequisites:

Table 1. ddm_demand

System_iddemand_idquotate_end_team11team1, team5, team212tea3, team1, team, 4

Pk: quotate_end_team, demand_id;

Table 2. mc_team

Team_namview_orderteam11team22

Solution:

Sort the quotate_end_team field in table 2 by view_order.

Method:

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;

-- Define cursor 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;

-- Call cursor 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;

-- Define cursor 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;

-- Update table
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