最近做项目碰到个N烦人的问题,困扰已久。表达能力不好,只能通过图片结合。跪求各位大虾帮忙,感激不尽。
下图是我制作的一些DEMO记录。上图的JH列中的元素相当于 一个主对象,DBJH1、DBJH2、DBJH3列 作为JH主对象的从属对象。
(实际JH有8列从属对象:DBJH1、DBJH2、DBJH3......DBJH8)
JH列主对象最多只有8个从对象。
以JH列的jh5情况为例,描述需求:
上图主对象JH列取值为jh3时,jh5 位于jh3的从属对象DBJH2列中。
JH列取值为jh1时,jh5 位于jh13的从属对象DBJH1列中。
JH列取值为jh6时,jh5 位于jh13的从属对象DBJH1列中。
所以jh5的从属对象中也应该包括:jh3、jh1 、jh6现在,需要将jh3、jh1 、jh6 这些jh5的从属对象,依次填充到jh5 右边(DBJH1、DBJH2、DBJH3......DBJH8)8列的空列中。
填充的顺序并没有要求。
但有几点需注意:
1、jh5的从属列已存在某元素,则不填充。如jh5的从属列中已经存在了jh3,则jh3就不需填充到jh5的从属列中。2、主对象JH列和从对象DBJH列,是通过U_XCH列 和 U_LTCH列(U_LTCH1、U_LTCH2、U_LTCH3、U_LTCH4.....U_LTCH8) 关联的。3、这里只列举了JH列的jh5作为例子,实际需要将JH列中所有的取值情况都遍历判断一边的。4、jh1、jh2、jh3..这些只是DEMO数据而已。实际情况中这些元素只是自动生成的主键标识。
下图是我制作的一些DEMO记录。上图的JH列中的元素相当于 一个主对象,DBJH1、DBJH2、DBJH3列 作为JH主对象的从属对象。
(实际JH有8列从属对象:DBJH1、DBJH2、DBJH3......DBJH8)
JH列主对象最多只有8个从对象。
以JH列的jh5情况为例,描述需求:
上图主对象JH列取值为jh3时,jh5 位于jh3的从属对象DBJH2列中。
JH列取值为jh1时,jh5 位于jh13的从属对象DBJH1列中。
JH列取值为jh6时,jh5 位于jh13的从属对象DBJH1列中。
所以jh5的从属对象中也应该包括:jh3、jh1 、jh6现在,需要将jh3、jh1 、jh6 这些jh5的从属对象,依次填充到jh5 右边(DBJH1、DBJH2、DBJH3......DBJH8)8列的空列中。
填充的顺序并没有要求。
但有几点需注意:
1、jh5的从属列已存在某元素,则不填充。如jh5的从属列中已经存在了jh3,则jh3就不需填充到jh5的从属列中。2、主对象JH列和从对象DBJH列,是通过U_XCH列 和 U_LTCH列(U_LTCH1、U_LTCH2、U_LTCH3、U_LTCH4.....U_LTCH8) 关联的。3、这里只列举了JH列的jh5作为例子,实际需要将JH列中所有的取值情况都遍历判断一边的。4、jh1、jh2、jh3..这些只是DEMO数据而已。实际情况中这些元素只是自动生成的主键标识。
step2: 查找jh=jh5的行,依次判斷DBJH1,2...8是否為null,union不為空列的值。
step3: 根據step2查出的結果過濾step1的結果
step4: update jh=jh5的行,判斷DBJH1,2...8,為null則插入step3的結果。
用一个select 语句写一大拖的case when
要不写个过程吧
每次创建一个临时表用以储存需要的现实结果:
游标遍历,遇到值为空的,要和当前记录中别的dbjh值比较,确定一个未出现的jh,然后再一一对应
如果能改表是最好的
比如增加一组四个字段
然后dbjh1里全部储存jh1,dbjh2中全部是dbjh2,以此类推
显示效果是差不多的,但就变得有规律了
貌似楼主并不只是jh=jh5的那几列为空
比如jh1和jh2之间的联系,需要在jh=jh1和jh=jh2两个地方都要填上这个关系,而现在是这两个地方只填写了一个,要把另一个空着的地方也补齐。不只是jh1和jh2,所有的都是如此,还不知道空着的是两个地方中的哪个。这么写的话会吐血的...
is
v_sql varchar2(2000) := null;
type out_cur is ref cursor;
dbjh_cur out_cur;
v_jh t.jh%type;
v_xch t.u_xch%type;
v_rowid rowid;
v_1 t.jh%type := null;
v_2 t.jh%type := null;
v_3 t.jh%type := null;
v_4 t.jh%type := null;
v_5 t.jh%type := null;
v_6 t.jh%type := null;
v_7 t.jh%type := null;
v_8 t.jh%type := null;begin -- step 1
v_sql := 'create tmp1 as
select t.jh, t.u_xch from t t
where t.dbjh1 = '||jh5||'
or t.dbjh2 = '||jh5||'
or t.dbjh3 = '||jh5||'
or t.dbjh4 = '||jh5||'
or t.dbjh5 = '||jh5||'
or t.dbjh6 = '||jh5||'
or t.dbjh7 = '||jh5||'
or t.dbjh8 = '||jh5; execute immediate v_sql; v_sql := 'create index idx1 on tmp1(jh)';
execute immediate v_sql; -- step 2
v_sql := 'create table tmp2 as
select nvl(t.dbjh1,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh2,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh3,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh4,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh5,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh6,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh7,0) jh from t t where t.jh = '||jh5||'
union all
select nvl(t.dbjh8,0) jh from t t where t.jh = '||jh5; execute immediate v_sql; -- step 3
v_sql := 'delete from tmp1 t1
where exists (select rowid from tmp2 t2 whre t2.jh = t1.jh)';
execute immediate v_sql;
v_sql := 'drop table tmp2';
execute immediate v_sql; -- step 4
v_sql := 'select t.jh, t.u_xch from tmp1 t'; open dbjh_cur for v_sql;
loop
<<nextloop>>
fetch dbjh_cur into v_jh,v_xch;
exit when dbjh_cur%notfound;
select rowid,
dbjh1,
dbjh2,
dbjh3,
dbjh4,
dbjh5,
dbjh6,
dbjh7,
dbjh8
into v_rowid,
v_1,
v_2,
v_3,
v_4,
v_5,
v_6,
v_7,
v_8
from t where t.jh = jh5; if v_1 is null then
v_sql := 'update t
set t.dbjh1 = '||v_jh||',
t.u_ltch1 = '||v_xch||
' where t.rowid = '||v_rowid;
goto nextloop;
else if v_2 is null then
v_sql := 'update t
set t.dbjh2 = '||v_jh||',
t.u_ltch2 = '||v_xch||
' where t.rowid = '||v_rowid;
goto nextloop;
else if v_3 is null then
v_sql := 'update t
set t.dbjh3 = '||v_jh||',
t.u_ltch3 = '||v_xch||
' where t.rowid = '||v_rowid;
goto nextloop;
-- 4,5,6,7,8
end if;
end loop;
close dbjh_cur;exception
when others then
raise;
end proc;
/
大致如此,不过应该只支持单行jh5更新,多行还需要精确定位rowid
v_sql := 'drop table tmp1';
execute immediate v_sql;
commit;
另外请教下,精确定位rowid 。是要在我的原表中增加一列rowid作为标识码?
那么将JH列的所有取值情况都判断一边的话,效率上会不会出问题呢。
运行会很耗时哦。。
不管性能,只要实现。实现过程是否可以理解为如下两步:
1、如果要处理JH列中所有情况,还需要给JH列加上游标,遍历其中所有取值。
2、一种取值情况,加上游标,遍历其中所有的行。
Compilation errors for PROCEDURE A2DW.XX_DAA051_DAA052_PROError: PLS-00103: ³öÏÖ·ûºÅ "LOOP"ÔÚÐèÒªÏÂÁÐ֮һʱ£º
if
Line: 135
Text: end loop;Error: PLS-00103:end-of-file
end not pragma
final instantiable order overriding static member constructor
map
Line: 141
is
v_count number;
v_dbjh varchar2(10);
v_u_ltch varchar2(10);
v_ltch varchar2(10);
v_ltlb varchar2(10);
cursor c1(v_jh in varchar2) is select * from temp where jh=v_jh for update;
begin
select count(1) into v_count from user_tables where table_name='TEMP';
if v_count<>0 then
execute immediate 'drop table temp';
end if;
execute immediate 'create table temp
as select * from XX_DAA051_DAA052';
--修改dbjh1块的值
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop --1
if cur1.dbjh1 is null then
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh1=v_dbjh,u_ltch1=v_u_ltch,ltch1=v_ltch,ltlb1=v_ltlb
where current of c1;
end loop;
end if;
end loop;
--dbjh2块
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh2 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh1,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh2=v_dbjh,u_ltch2=v_u_ltch,ltch2=v_ltch,ltlb2=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
--dbjh3块
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh3 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh1,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh3=v_dbjh,u_ltch3=v_u_ltch,ltch3=v_ltch,ltlb3=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
--dbjh4
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh4 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh1,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh4=v_dbjh,u_ltch4=v_u_ltch,ltch4=v_ltch,ltlb4=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh5 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh1,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh5=v_dbjh,u_ltch5=v_u_ltch,ltch5=v_ltch,ltlb5=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
--dbjh6
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh6 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh1,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh6=v_dbjh,u_ltch6=v_u_ltch,ltch6=v_ltch,ltlb6=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
--dbjh7
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop
if cur1.dbjh7 is null then --
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh1,'0')--2
and jh<>nvl(cur1.dbjh8,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh7=v_dbjh,u_ltch7=v_u_ltch,ltch7=v_ltch,ltlb7=v_ltlb --
where current of c1;
end loop;
end if;
end loop;
--dbjh8
for cur1 in (select distinct jh,dbjh1,dbjh2,dbjh3,dbjh4,dbjh5,dbjh6,dbjh7,dbjh8 from temp)loop --1
if cur1.dbjh8 is null then
select jh into v_dbjh from (
select jh from temp where jh<>nvl(cur1.dbjh2,'0')
and jh<>nvl(cur1.dbjh3,'0')
and jh<>nvl(cur1.dbjh4,'0')
and jh<>nvl(cur1.dbjh5,'0')
and jh<>nvl(cur1.dbjh6,'0')
and jh<>nvl(cur1.dbjh7,'0')--2
and jh<>nvl(cur1.dbjh1,'0')
and(dbjh1=cur1.jh or dbjh2=cur1.jh or dbjh3=cur1.jh or dbjh4=cur1.jh
or dbjh5=cur1.jh or dbjh6=cur1.jh or dbjh7=cur1.jh or dbjh8=cur1.jh)
and jh<>cur1.jh
union all select null from dual order by 1)where rownum=1;--and
for cur2 in c1(cur1.jh) loop
select u_xch,xch,decode(cur2.jh,dbjh1,ltlb1,dbjh2,ltlb2,dbjh3,ltlb3,dbjh4,
ltlb4,dbjh5,ltlb5,dbjh6,ltlb6,dbjh7,ltlb7,dbjh8,ltlb8) into v_u_ltch,v_ltch,v_ltlb
from(select u_xch,xch, dbjh1,ltlb1,dbjh2,ltlb2
from temp where jh=v_dbjh and (dbjh1=cur2.jh and u_ltch1=cur2.u_xch) or(dbjh2=cur2.jh and u_ltch2=cur2.u_xch)
or(dbjh3=cur2.jh and u_ltch3=cur2.u_xch)
or(dbjh4=cur2.jh and u_ltch4=cur2.u_xch)
or(dbjh5=cur2.jh and u_ltch5=cur2.u_xch)
or(dbjh6=cur2.jh and u_ltch6=cur2.u_xch)
or(dbjh7=cur2.jh and u_ltch7=cur2.u_xch)
or(dbjh8=cur2.jh and u_ltch8=cur2.u_xch) union all
select null,null,null,null,null,null from dual order by 1) where rownum=1;
update temp set dbjh8=v_dbjh,u_ltch8=v_u_ltch,ltch8=v_ltch,ltlb8=v_ltlb
where current of c1;
end loop;
end if;
end loop;commit;
end PRO_XX_DAA051_DAA052;