MySQL嵌套游标（Nested Cursor）批量更新

batchUpdateMatchedAddress.sql

CREATE DEFINER = 'geocodingdb'@'%'
PROCEDURE geocodingdb.updateMatchResult()
BEGIN 
  DECLARE done1 int default false;  
  DECLARE bizId nvarchar(64);
  # 批量更新计数器
  DECLARE cachSize_matchedAddress int DEFAULT 0;

  # 定义光标  where  t1.source_address_id IN ('001A866D0F5647B5A74530EF8CF8AAFA','000032E78F5E4F65A2C75DAEAF53B62E') 
  DECLARE cursor_bizId CURSOR FOR
          select t1.source_address_id from geocodingdb.MatchedAddressGroupbySkeleton t1  
            left  join  地名地址整合数据源表 t2 on  t1.source_address_id=t2.业务ID  
            where t2.匹配ID  is null  group  by  t1.source_address_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;  

  CREATE TEMPORARY TABLE IF NOT EXISTS  temp_matchedAddress_updateRows AS SELECT 匹配ID,匹配精度,匹配地址,地址经度,地址纬度,业务ID FROM 地名地址整合数据源表 WHERE 1=0;  
  /* 打开光标 */
  OPEN cursor_bizId;
  # 循环获取业务ID  
  loop_getBizId:LOOP
     FETCH cursor_bizId INTO bizId; 
      IF done1 THEN
        LEAVE loop_getBizId;
        /*关闭光标*/
        CLOSE cursor_bizId;
      END IF; 

    # 计时器cachSize_matchedAddress到限时执行关联批量更新
    IF cachSize_matchedAddress=10000 THEN
      SELECT cachSize_matchedAddress;
        -- update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
      UPDATE 地名地址整合数据源表 t1,temp_matchedAddress_updateRows t2 
            SET t1.匹配ID=t2.匹配ID,t1.匹配精度=t2.匹配精度,t1.匹配地址=t2.匹配地址,t1.地址经度=t2.地址经度,t1.地址纬度=t2.地址纬度 WHERE t1.业务ID=t2.业务ID;
      SET cachSize_matchedAddress=0;
      DROP TEMPORARY TABLE IF EXISTS temp_matchedAddress_updateRows;
      CREATE TEMPORARY TABLE IF NOT EXISTS  temp_matchedAddress_updateRows AS SELECT 匹配ID,匹配精度,匹配地址,地址经度,地址纬度,业务ID FROM 地名地址整合数据源表 WHERE 1=0;  
    End IF; 

    # 根据业务ID获取匹配地址
    block_matchedAddress:BEGIN 
        DECLARE _rownum int;
        DECLARE _matchID nvarchar(64);
        DECLARE _matchedType nvarchar(8);
        DECLARE _matchedAddress nvarchar(3000);
        DECLARE _x,_y decimal(11, 6); 
      
        DECLARE done2 int default false; 
        #cursor:获取匹配地址（由于是动态select语句，故从视图中间接查询）
        DECLARE cursor_matchedAddress CURSOR FOR SELECT * FROM temp_matchedAddress; 
        DECLARE continue handler for not found set done2 = true;  
 
        DROP TEMPORARY TABLE IF EXISTS temp_matchedAddress;
        
        #新建临时表temp_maxAddressNodeOrder存储 匹配地址（多个）
        SET @temp_rowNumber=0;
        SET @sql_query_matchedAddress= concat("SELECT  getRowNum() AS _rownum, matched_address_id AS _matchID, matched_address_type AS _matchedType, matched_address AS _matchedAddress, longitude AS _x, latitude AS _y 
          FROM MatchedAddressGroupbySkeleton t3 WHERE t3.source_address_id='",bizId  COLLATE utf8_unicode_ci ,"' AND t3.matched_address_type = (SELECT  matched_address_type  FROM 
          (SELECT  MAX(o1.AddressNodeOrder) AS deepSize, matched_address_type FROM MatchedAddressGroupbySkeleton t1 
          LEFT JOIN AddressNodeOrder o1   ON t1.matched_address_type = o1.addressNode WHERE t1.source_address_id='",bizId  COLLATE utf8_unicode_ci ,"') AS t2)"); 
        SET @query_matchedAddress = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS  temp_matchedAddress as ', @sql_query_matchedAddress);  
        PREPARE stmt from @query_matchedAddress; 
        EXECUTE stmt; 
        DEALLOCATE PREPARE stmt;   

        OPEN cursor_matchedAddress;  
        # 循环获取匹配地址
        loop_getMatchedAddress:LOOP  
            #XXX 每次生成的视图数据是正确的），但 FETCH Into的结果总是父级Cursor(cursor_bizId)的第一个bizId的查询结果！why?
            FETCH cursor_matchedAddress INTO _rownum, _matchID, _matchedType, _matchedAddress, _x, _y;
            IF done2 THEN
               LEAVE loop_getMatchedAddress;
               CLOSE cursor_matchedAddress;
            END IF;
            # SELECT _rownum, _matchID, _matchedType, _matchedAddress, _x, _y;
            # 根据当前循环的rownum，更新指定候选匹配地址字段
          
             # rownum=1时，更新4个匹配结果字段；rownumber=n时，更新字段<候选匹配地址n> 
            IF _rownum = 1 THEN 
              # 插入临时缓存表
              INSERT INTO temp_matchedAddress_updateRows(匹配ID,匹配精度,匹配地址,地址经度,地址纬度,业务ID) VALUES (_matchID,_matchedType,_matchedAddress,_x,_y,bizId   COLLATE utf8_unicode_ci);
              SET cachSize_matchedAddress=cachSize_matchedAddress+1;
             # update 地名地址整合数据源表 set 匹配ID=_matchID,匹配精度=_matchedType,匹配地址=_matchedAddress,地址经度=_x,地址纬度=_y where 业务ID=bizId   COLLATE utf8_unicode_ci;
            
            ELSE 
               SET @matchedAddressField = CONCAT("候选匹配地址", _rownum - 1);
               #MySQL5.7.8支持JSON序列化，暂用版本5.5，手动序列化
               # SET @matchedAddress=JSON_ARRAY(匹配精度,匹配地址,地址经度,地址纬度,匹配ID);
               SET @matchedAddress = CONCAT_WS(" | ", _matchedType, _matchedAddress,_x,_y, _matchID); 
               # XXX 以临时表缓存批量更新：待处动态更新field和value问题
               # INSERT INTO temp_matchedCandidateAddress_updateRows(候选匹配地址_field,候选匹配地址_value,业务ID) VALUES (@matchedAddressField, @matchedAddress,bizId   COLLATE utf8_unicode_ci);

               SET @sql = CONCAT("update 地名地址整合数据源表 SET ", @matchedAddressField, "='", @matchedAddress, "' where 业务ID='", bizId COLLATE utf8_unicode_ci,"'");  
               PREPARE stmt from  @sql;
               EXECUTE stmt;
               DEALLOCATE PREPARE stmt;  
            END IF;
  
        END LOOP loop_getMatchedAddress;
  
      END block_matchedAddress;
  
  END LOOP loop_getBizId;


END