guoyubo1231
guoyubo1231
采纳率0%
2019-04-03 18:44 浏览 409

oracle用游标将A表的字段和B表3个字段匹配,匹配到就将B表的三个字段和A表的CODE字段输入到一张新表里面

40

将A表的字段和B表3个字段匹配,匹配到就将B表的三个字段和A表的CODE字段输入到一张新表里面。哪位大神能帮我补充一下我的逻辑,很少写游标这样的SQL。

图片说明

create or replace procedure testProc_guo
as
cursor cur is select keywords,brand_code from TMP_GUOSHOU_dim_test;
keywords varchar(100);
brand_code varchar(100);

cursor cur2 is select vehicle_brand_name,vehicle_series_name,vehicle_model_name from tmp_guoshou_guoyubo;
vehicle_brand_name varchar(100);
vehicle_series_name varchar(100);
vehicle_model_name varchar(100);

Begin

open cur;
fetch cur into keywords;
while cur%found loop
dbms_output.put_line(keywords);
fetch cur into keywords;
end loop;

open cur2; 
fetch cur2 into vehicle_brand_name,vehicle_series_name,vehicle_model_name;

--做一个A表和B表的判断 得到对应的 brand_code

--我想把 这个brand_code 和 名称 牌子 类型 这三个字段一起放到一个新表里面这块不太会写
loop
case when vehicle_brand_name like '%'||keywords||'%' or
vehicle_series_name like '%'||keywords||'%' or
vehicle_model_name like '%'||keywords||'%'
then cur1.brand_code
end
end loop;

insert into nmlz_veh_model_custom_guo (vehicle_brand_name,vehicle_series_name,vehicle_model_name,brand_code)
values (cur2.vehicle_brand_name,cur2vehicle_series_name,cur2vehicle_model_name,cur.brand_code)

close cur;
close cur2;
Exception
When others then
Rollback;
End;

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答 默认 最新

  • devmiao devmiao 2019-04-03 19:01
  • guoyubo1231 guoyubo1231 2019-04-04 10:42

    create or replace procedure testProc_guo
    is begin
    for rec in (select keywords,brand_code from tmp_guoshou_dim_test)
    loop

    for rec2 in (select vehicle_brand_name,vehicle_series_name,vehicle_model_name from tmp_guoshou_guoyubo)
    loop
    if
    rec2.vehicle_brand_name like '%'||rec.keywords||'%' or
    rec2.vehicle_series_name like '%'||rec.keywords||'%' or
    rec2.vehicle_model_name like '%'||rec.keywords||'%' --一条一条的进行遍历,进行判断
    then
    insert into nmlz_veh_model_custom_guo(vehicle_brand_name,vehicle_series_name,vehicle_model_name,brand_code)
    values(rec2.vehicle_brand_name,rec2.vehicle_series_name,rec2.vehicle_model_name,rec.brand_code);
    end if;
    end loop;
    end loop;

    commit;
    end;

    点赞 评论 复制链接分享
  • qq_41623714 qq_41623714 2019-04-04 21:26

    create or replace procedure testPro_guo
    as
    branch_c varchar2(100);
    keywords_serch varchar2(100);
    numcount int;

    cursor cur1 is
    select a.keywords,a.branch_code from tmp_guoshou_dim_test a;
    begin
    open cur1;
    loop
    fetch cur1 into keywords_serch,branch_c;
    exit when cur1%notfound;

    select count(1)
      into numcount
      from tmp_guoshou_guoyubo b
     where b.vehicle_brand_name like '%' || keywords_serch || '%'
        or b.vehicle_series_name like '%' || keywords_serch || '%'
        or b.vehicle_model_name like '%' || keywords_serch || '%';
    
     if numcount>0
        then 
          execute immediate 'update tmp_guoshou_guoyubo c set c.branch_code=''' 
          ||branch_c ||''' where c.vehicle_brand_name like ''%' || keywords_serch 
          || '%'' or c.vehicle_series_name like ''%' || keywords_serch || '%'' 
          or c.vehicle_model_name like ''%' || keywords_serch || '%''';
          commit;
     end if;
    

    end loop;
    close cur1;
    end;

    点赞 评论 复制链接分享
  • ShanghaiMail 阿发爸 2019-04-09 11:38

    针对数据库中的数据匹配,个人不太推荐有数据记录间的逐个比对,可以利用数据库本身的性能优势,特别是数据量大的时候,利用表间的相互比对,达到匹配的效果。

    步骤1: 利用Full Join将表A和B做全量笛卡尔积
    Select A.Name, A.Code, A.Keyword, b.名称, b.牌子, b.类型,"" as FindCode into tmp_Table From Table_A, Table_B
    步骤2: 利用Instr公式,更新临时表里的FindCode字段
    Update tmp_Table Set FindCode = Instr(名称, Keyword) ;
    Update tmp_Table Set FindCode = Instr(牌子, Keyword) ;
    Update tmp_Table Set FindCode = Instr(类型, Keyword) ;
    步骤3:删除所有没有匹配到的数据
    Delete from tmp_table where FindCode=0
    步骤4:将匹配到的Code值更新会表Table B
    Update b.Code=a.FindCode from tmp_Table a, Table_B b
    where a.名称=b.名称 AND a.牌子=b.牌子 AND a.类型=b.类型

    希望这种方法有用。

    点赞 评论 复制链接分享

相关推荐