create or replace procedure p_olddic_newdic is
cursor commons is select t1.name codename from n_code t1, o_dictable t2
where t1.name = t2.dic_name; dic_id_temp varchar2(80);
codeid_temp varchar2(80);
dic_name_temp varchar2(200);
begin
for common in commons loop
begin
select f.dic_id into dic_id_temp from o_dictable f where f.dic_name= common.codename;
select t.id into codeid_temp from n_code t where t.name=common.codename;
dic_name_temp := common.codename;
insert into old_new_dic_compare(OLDCODE,OLDNAME,NEWID,NEWTEXT,dic_name)
select t1.OLDCODE, t1.OLDNAME, t2.NEWID, t2.NEWTEXT,t2.dicname
from
(
select e.code OLDCODE,e.name OLDNAME
from o_dic_columns e
where e.dic_id = dic_id_temp
) t1
full join
(
select e.id NEWID, e.text NEWTEXT,
(select dic_name_temp from dual) dicname
from n_codeitem e
where e.codeid = codeid_temp
) t2
on t1.OLDNAME = t2.NEWTEXT;
commit;
end;
end loop;
end;
cursor commons is select t1.name codename from n_code t1, o_dictable t2
where t1.name = t2.dic_name; dic_id_temp varchar2(80);
codeid_temp varchar2(80);
dic_name_temp varchar2(200);
begin
for common in commons loop
begin
select f.dic_id into dic_id_temp from o_dictable f where f.dic_name= common.codename;
select t.id into codeid_temp from n_code t where t.name=common.codename;
dic_name_temp := common.codename;
insert into old_new_dic_compare(OLDCODE,OLDNAME,NEWID,NEWTEXT,dic_name)
select t1.OLDCODE, t1.OLDNAME, t2.NEWID, t2.NEWTEXT,t2.dicname
from
(
select e.code OLDCODE,e.name OLDNAME
from o_dic_columns e
where e.dic_id = dic_id_temp
) t1
full join
(
select e.id NEWID, e.text NEWTEXT,
(select dic_name_temp from dual) dicname
from n_codeitem e
where e.codeid = codeid_temp
) t2
on t1.OLDNAME = t2.NEWTEXT;
commit;
end;
end loop;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货