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

桃扇骨 2024-04-17 05:50 27阅读 0赞

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

*  存储过程：
    
        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