mysql 存储过程给表里的空字段赋值，从其他表里取数据

51 阅读 0 评论 34 点赞

我是靠谱客的博主娇气奇异果，最近开发中收集的这篇文章主要介绍mysql 存储过程给表里的空字段赋值，从其他表里取数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

假设某个表的字段内容某些行为空，我们需要从其他表获取值，更新到这个表里；这里是采用游标，把值为空的数据查出来，然后用关联值去其他表查出内容，更新表

存储过程：

    BEGIN
  	#Routine body goes here...
        DECLARE district_code_ VARCHAR(64);
        DECLARE district_code_2 VARCHAR(64);
        DECLARE district_name_ VARCHAR(60);
  
        DECLARE community_code_ VARCHAR(64);
        DECLARE community_name_ VARCHAR(60);
  
        DECLARE street_code_ VARCHAR(64);
        DECLARE street_name_ VARCHAR(60);
  
  
  DECLARE doned INT DEFAULT 0;-- 结束标志变量
  
        DECLARE num_ INT; 
  
      DECLARE  geoCursor CURSOR FOR( select distinct district,street,community from geo_t_component_copy WHERE district IS NOT NULL  AND district_name is NULL );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = 1;
  
      open geoCursor;
      f_loop:LOOP
        --      LOOP-- ??  赋值给变量
                  FETCH geoCursor into district_code_,street_code_,community_code_;
  
                IF doned=1 THEN
                 LEAVE f_loop; ##--到结尾了跳出循环
                END IF;  
                -- 其他操作 将查出来的值更新到表里
  
                 SELECT gd.name INTO district_name_ FROM geo_t_district gd LEFT JOIN geo_t_component_copy gc ON (gd.code = gc.district) WHERE gc.district=district_code_ LIMIT 1;
  
                 SELECT gd.name INTO community_name_ FROM geo_t_community gd LEFT JOIN geo_t_component_copy gc ON (gd.code = gc.community) WHERE gc.community=community_code_ LIMIT 1;
  
                 SELECT gd.name INTO street_name_ FROM geo_t_street gd LEFT JOIN geo_t_component_copy gc ON (gd.code = gc.street) WHERE gc.street=street_code_ LIMIT 1;
  
                 UPDATE geo_t_component_copy SET district_name=district_name_,community_name=community_name_,street_name=street_name_ WHERE district=district_code_ AND street=street_code_ AND community=community_code_;
  
                end loop; -- 结束游标循环;
                CLOSE geoCursor; # 关闭游标
  END