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

将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;

qq_41623714
qq_41623714 有问题找我,我就想混点赏金。
10 个月之前 回复

4个回答

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 有问题可以找我哦,我就向混点赏金。
10 个月之前 回复
guoyubo1231
guoyubo1231 这样就可以了
10 个月之前 回复

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;

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

步骤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.类型

希望这种方法有用。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问