示例

把tb_policy的某些数据migration到新表中

  1. DELIMITER $$
  3. USE `hesui`$$
  4. start transaction$$
  6. DROP PROCEDURE if EXISTS proc_sender_exception_migration$$
  7. CREATE PROCEDURE proc_sender_exception_migration()
  8. BEGIN
  9.     DECLARE splitChar VARCHAR(2) DEFAULT ';';
  10.     DECLARE tmp VARCHAR(255) DEFAULT '';
  11.     DECLARE cnt INT DEFAULT 0;
  12.     DECLARE i INT DEFAULT 0;
  14.     DECLARE p_id INT DEFAULT 0;
  15.     DECLARE s_except LONGTEXT DEFAULT '';
  16.     DECLARE done INT DEFAULT 0;
  17.     DECLARE sender_except_cursor CURSOR
  18.     FOR
  19.         SELECT policy_id, sender_except 
  20.         FROM tb_policy 
  21.         WHERE sender_except IS NOT NULL 
  22.         AND sender_except != '';
  24.     DECLARE continue handler FOR NOT FOUND SET done = 1;
  26.     OPEN sender_except_cursor;
  27.     start_loop: loop
  28.         fetch sender_except_cursor INTO p_id, s_except;
  30.         if done = 1 then
  31.             leave start_loop;
  32.         END if;
  33.         SET cnt = 1+(LENGTH(s_except) - LENGTH(REPLACE(s_except,splitChar,'')));
  34.         SET i = 0;
  35.         while i < cnt do
  36.             SET i = i + 1;
  37.             SET tmp = reverse(substring_index(reverse(substring_index(s_except,splitChar,i)),splitChar,1));
  38.             if tmp IS NOT NULL OR tmp != '' then
  39.                 INSERT INTO tb_policy_target_exception(policy_id, sender_except, rcpt_except) VALUES (p_id, tmp, '*');
  40.             END if;
  41.         END while;
  42.     END loop;
  44. END$$
  45. call proc_sender_exception_migration();$$
  47. DROP PROCEDURE if EXISTS proc_rcpt_exception_migration$$
  48. CREATE PROCEDURE proc_rcpt_exception_migration()
  49. BEGIN
  50.     DECLARE splitChar VARCHAR(2) DEFAULT ';';
  51.     DECLARE tmp VARCHAR(255) DEFAULT '';
  52.     DECLARE cnt INT DEFAULT 0;
  53.     DECLARE i INT DEFAULT 0;
  55.     DECLARE p_id INT DEFAULT 0;
  56.     DECLARE r_except LONGTEXT DEFAULT '';
  57.     DECLARE done INT DEFAULT 0;
  58.     DECLARE rcpt_except_cursor CURSOR
  59.     FOR
  60.         SELECT policy_id, rcpt_except 
  61.         FROM tb_policy 
  62.         WHERE rcpt_except IS NOT NULL 
  63.         AND rcpt_except != '';
  65.     DECLARE continue handler FOR NOT FOUND SET done = 1;
  67.     OPEN rcpt_except_cursor;
  68.     start_loop: loop
  69.         fetch rcpt_except_cursor INTO p_id, r_except;
  71.         if done = 1 then
  72.             leave start_loop;
  73.         END if;
  74.         SET cnt = 1+(LENGTH(r_except) - LENGTH(REPLACE(r_except,splitChar,'')));
  75.         SET i = 0;
  76.         while i < cnt do
  77.             SET i = i + 1;
  78.             SET tmp = reverse(substring_index(reverse(substring_index(r_except,splitChar,i)),splitChar,1));
  79.             if tmp IS NOT NULL OR tmp != '' then
  80.                 INSERT INTO tb_policy_target_exception(policy_id, sender_except, rcpt_except) VALUES (p_id, '*', tmp);
  81.             END if;
  82.         END while;
  83.     END loop;
  85. END$$
  86. call proc_rcpt_exception_migration();$$
  88. drop procedure if exists group_policy_relation_migration$$
  89. CREATE procedure group_policy_relation_migration()
  90. BEGIN
  91.       declare done INT DEFAULT 0;
  92.     declare policy_id int(10);
  93.     declare gpr_id int(10);
  94.     DECLARE exception_id INT(10);
  96.     declare gpr_cursor_rcpt CURSOR
  97.     FOR
  98.         select gpr.policy_id, gpr.id 
  99.         from tb_ldap_group_policy_relation gpr, tb_policy 
  100.         where gpr.policy_id = tb_policy.policy_id 
  101.         and gpr.include = 0
  102.         and tb_policy.direction = 0;
  104.     declare gpr_cursor_sender CURSOR
  105.     FOR
  106.         select gpr.policy_id, gpr.id 
  107.         from tb_ldap_group_policy_relation gpr, tb_policy 
  108.         where gpr.policy_id = tb_policy.policy_id 
  109.         and gpr.include = 0
  110.         and tb_policy.direction = 1;
  112.     declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  114.     open gpr_cursor_rcpt;
  115.     start_loop : loop
  116.         fetch gpr_cursor_rcpt into policy_id, gpr_id;
  117.         if done=1 then
  118.             leave start_loop;
  119.         end if;
  120.         insert into tb_policy_target_exception(policy_id, sender_except) values(policy_id, '*');
  121.         select max(id) from tb_policy_target_exception into exception_id;
  122.         update tb_ldap_group_policy_relation set exception_id = exception_id where id = gpr_id;
  123.     end loop;
  125.     open gpr_cursor_sender;
  126.     start_loop : loop
  127.         fetch gpr_cursor_sender into policy_id, gpr_id;
  128.         if done=1 then
  129.             leave start_loop;
  130.         end if;
  131.         insert into tb_policy_target_exception(policy_id, rcpt_except) values(policy_id, '*');
  132.         select max(id) from tb_policy_target_exception into exception_id;
  133.         update tb_ldap_group_policy_relation set exception_id = exception_id where id = gpr_id;
  134.     end loop;
  135. END$$
  137. call group_policy_relation_migration();$$
  139. commit$$
  140. DELIMITER ;