create table a ( aid varchar2(100), aname varchar2(100), aadd varchar2(100) ); insert into a values ('a1','aname1','ssss'); insert into a values ('a2',null,'ssss2'); insert into a values ('a3','aname2','ssss3'); insert into a values ('a4',null,'ssss4'); insert into a values ('a5','aname3','ssss5'); insert into a values ('a6','aname4',null); insert into a values ('a7',null,null); insert into a values ('a8',null,null); insert into a values ('a9','aname5','sssss6'); insert into a values ('a9',null,null); create table b ( bid varchar2(100), bname varchar2(100), badd varchar2(100) ); insert into b values ('b1','bname1','dddd'); insert into b values ('b2',null,'dddd2'); insert into b values ('b3','bname2',null); insert into b values ('b4',null,'ssss4'); insert into b values ('b5','bname3','ddddd3'); insert into b values ('b6','bname4',null); insert into b values ('b7',null,null); insert into b values ('b8',null,null); insert into b values ('b9','bname5','dddd6'); insert into b values ('b9',null,null); create table c ( cid varchar2(100), cname varchar2(100), cadd varchar2(100) ); insert into c values ('c1','cname1','eeeee'); insert into c values ('c2',null,'eeee2'); insert into c values ('c3','cname2','eeee3'); insert into c values ('c4',null,'ssss4'); insert into c values ('c5','cname3','eeee5'); insert into c values ('c6','cname4',null); insert into c values ('c7',null,null); insert into c values ('c8',null,null); insert into c values ('c9','cname5','eeee6'); insert into c values ('c9',null,null);
create table a ( aid varchar2(100), aname varchar2(100), aadd varchar2(100) ); insert into a values ('a1','aname1','ssss'); insert into a values ('a2',null,'ssss2'); insert into a values ('a3','aname2','ssss3'); insert into a values ('a4',null,'ssss4'); insert into a values ('a5','aname3','ssss5'); insert into a values ('a6','aname4',null); insert into a values ('a7',null,null); insert into a values ('a8',null,null); insert into a values ('a9','aname5','sssss6'); insert into a values ('a9',null,null); create table b ( bid varchar2(100), bname varchar2(100), badd varchar2(100) ); insert into b values ('b1','bname1','dddd'); insert into b values ('b2',null,'dddd2'); insert into b values ('b3','bname2',null); insert into b values ('b4',null,'ssss4'); insert into b values ('b5','bname3','ddddd3'); insert into b values ('b6','bname4',null); insert into b values ('b7',null,null); insert into b values ('b8',null,null); insert into b values ('b9','bname5','dddd6'); insert into b values ('b9',null,null); create table c ( cid varchar2(100), cname varchar2(100), cadd varchar2(100) ); insert into c values ('c1','cname1','eeeee'); insert into c values ('c2',null,'eeee2'); insert into c values ('c3','cname2','eeee3'); insert into c values ('c4',null,'ssss4'); insert into c values ('c5','cname3','eeee5'); insert into c values ('c6','cname4',null); insert into c values ('c7',null,null); insert into c values ('c8',null,null); insert into c values ('c9','cname5','eeee6'); insert into c values ('c9',null,null);
create table d ( did varchar2(100), dname varchar2(100), dadd varchar2(100) ); insert into d values('d1','aname1','ssss1'); insert into d values('d2','aname2','ssss3'); insert into d values('d3','aname3','ssss5'); insert into d values('d4','aname4','ssss6'); insert into d values('d5','aname5','ssss1'); insert into d values('d6','aname6','ssss3'); insert into d values('d7','aname7','ssss5'); insert into d values('d8','aname10','ssss6'); insert into d values('d9','bname1','dddd1'); insert into d values('d10','bname2','eeee3'); insert into d values('d11','bname3','eeee5'); insert into d values('d12','bname4','eeee6'); insert into d values('d13','bname5','dddd1'); insert into d values('d14','bname6','eeee3'); insert into d values('d15','bname7','eeee5'); insert into d values('d16','bname8','eeee6'); insert into d values('d17','cname1','eeeee1'); insert into d values('d18','cname2','eeee3'); insert into d values('d19','cname3','eeee5'); insert into d values('d20','cname4','eeee6'); insert into d values('d21','cname5','eeeee1'); insert into d values('d22','cname6','eeee3'); insert into d values('d23','cname7','eeee5'); insert into d values('d24','cname8','eeee6');select * from a; select * from b; select * from c;这三张都是临时表,现在想把临时表的数据转换到另外三张正式表, 转换规则: 1、表中空缺的地方补全,例如:表A中的ANAME字段的值, 如果上面值为aname1,那么下面的空缺的值就补为aname1, 再到下面碰到aname2, 那么下面的空缺的值就补为aname2,以此类推; 2、表A中的ANAME中必须要在表D中的DNAME中存在,如果不存在就不能转到正式表中; 3、如果表A中的ANAME的值能够匹配到表D中的DNAME,则按照ANAME把表D中的dadd的值带出来, 如果表A中的aadd中的值与表D中的dadd中的值不一致的话,则以表D中的值为准;
--给你一个A表转换的例子: SELECT aid,Nvl(aname,Lag(aname)over(ORDER BY aid)) aname ,aadd FROM a WHERE EXISTS(SELECT 1 FROM d WHERE a.aname=d.dname) ;
--上面未看到条件3,修正如下: SELECT a.aid, Nvl(a.aname,Lag(a.aname)over(ORDER BY a.aid)) aname , a.aadd, Decode(a.aadd,d.dadd,a.aadd,d.dadd) dadd FROM a,d WHERE a.aname=d.dname;
create table a_bak as select aid,nvl(aname,lag(aname)over(order by aid)) aname ,d.dadd from a where exists(select 1 from d where a.aname=d.dname) ; --其他表类似
create or replace procedure p_test is l_aid a.aid%type; l_aname a.aname%type; l_aadd a.aadd%type; l_varname a.aname%type; l_varadd a.aadd%type; cursor v is select * from a; begin for v_a in v loop l_aid := v_a.aid; if v_a.aname is null then l_aname :=l_varname; elsif v_a.aname is not null then l_varname := v_a.aname; l_aname :=l_varname; end if; if v_a.aadd is not null then l_varadd := v_a.aadd; l_aadd := l_varadd; elsif v_a.aadd is null then l_aadd := l_varadd; end if; dbms_output.put(l_aid||'=='); dbms_output.put(l_aname||'--'); dbms_output.put_line(l_aadd||'--'); end loop; end; SQL> begin 2 p_test; 3 end ; 4 / a1==aname1--ssss-- a2==aname1--ssss2-- a3==aname2--ssss3-- a4==aname2--ssss4-- a5==aname3--ssss5-- a6==aname4--ssss5-- a7==aname4--ssss5-- a8==aname4--ssss5-- a9==aname5--sssss6-- a9==aname5--sssss6-- PL/SQL procedure successfully completed 这样结果是对的,但我不知道能否有更好更优化的游标方法
(
aid varchar2(100),
aname varchar2(100),
aadd varchar2(100)
);
insert into a values ('a1','aname1','ssss');
insert into a values ('a2',null,'ssss2');
insert into a values ('a3','aname2','ssss3');
insert into a values ('a4',null,'ssss4');
insert into a values ('a5','aname3','ssss5');
insert into a values ('a6','aname4',null);
insert into a values ('a7',null,null);
insert into a values ('a8',null,null);
insert into a values ('a9','aname5','sssss6');
insert into a values ('a9',null,null);
create table b
(
bid varchar2(100),
bname varchar2(100),
badd varchar2(100)
);
insert into b values ('b1','bname1','dddd');
insert into b values ('b2',null,'dddd2');
insert into b values ('b3','bname2',null);
insert into b values ('b4',null,'ssss4');
insert into b values ('b5','bname3','ddddd3');
insert into b values ('b6','bname4',null);
insert into b values ('b7',null,null);
insert into b values ('b8',null,null);
insert into b values ('b9','bname5','dddd6');
insert into b values ('b9',null,null);
create table c
(
cid varchar2(100),
cname varchar2(100),
cadd varchar2(100)
);
insert into c values ('c1','cname1','eeeee');
insert into c values ('c2',null,'eeee2');
insert into c values ('c3','cname2','eeee3');
insert into c values ('c4',null,'ssss4');
insert into c values ('c5','cname3','eeee5');
insert into c values ('c6','cname4',null);
insert into c values ('c7',null,null);
insert into c values ('c8',null,null);
insert into c values ('c9','cname5','eeee6');
insert into c values ('c9',null,null);
create table a
(
aid varchar2(100),
aname varchar2(100),
aadd varchar2(100)
);
insert into a values ('a1','aname1','ssss');
insert into a values ('a2',null,'ssss2');
insert into a values ('a3','aname2','ssss3');
insert into a values ('a4',null,'ssss4');
insert into a values ('a5','aname3','ssss5');
insert into a values ('a6','aname4',null);
insert into a values ('a7',null,null);
insert into a values ('a8',null,null);
insert into a values ('a9','aname5','sssss6');
insert into a values ('a9',null,null);
create table b
(
bid varchar2(100),
bname varchar2(100),
badd varchar2(100)
);
insert into b values ('b1','bname1','dddd');
insert into b values ('b2',null,'dddd2');
insert into b values ('b3','bname2',null);
insert into b values ('b4',null,'ssss4');
insert into b values ('b5','bname3','ddddd3');
insert into b values ('b6','bname4',null);
insert into b values ('b7',null,null);
insert into b values ('b8',null,null);
insert into b values ('b9','bname5','dddd6');
insert into b values ('b9',null,null);
create table c
(
cid varchar2(100),
cname varchar2(100),
cadd varchar2(100)
);
insert into c values ('c1','cname1','eeeee');
insert into c values ('c2',null,'eeee2');
insert into c values ('c3','cname2','eeee3');
insert into c values ('c4',null,'ssss4');
insert into c values ('c5','cname3','eeee5');
insert into c values ('c6','cname4',null);
insert into c values ('c7',null,null);
insert into c values ('c8',null,null);
insert into c values ('c9','cname5','eeee6');
insert into c values ('c9',null,null);
(
did varchar2(100),
dname varchar2(100),
dadd varchar2(100)
);
insert into d values('d1','aname1','ssss1');
insert into d values('d2','aname2','ssss3');
insert into d values('d3','aname3','ssss5');
insert into d values('d4','aname4','ssss6');
insert into d values('d5','aname5','ssss1');
insert into d values('d6','aname6','ssss3');
insert into d values('d7','aname7','ssss5');
insert into d values('d8','aname10','ssss6');
insert into d values('d9','bname1','dddd1');
insert into d values('d10','bname2','eeee3');
insert into d values('d11','bname3','eeee5');
insert into d values('d12','bname4','eeee6');
insert into d values('d13','bname5','dddd1');
insert into d values('d14','bname6','eeee3');
insert into d values('d15','bname7','eeee5');
insert into d values('d16','bname8','eeee6');
insert into d values('d17','cname1','eeeee1');
insert into d values('d18','cname2','eeee3');
insert into d values('d19','cname3','eeee5');
insert into d values('d20','cname4','eeee6');
insert into d values('d21','cname5','eeeee1');
insert into d values('d22','cname6','eeee3');
insert into d values('d23','cname7','eeee5');
insert into d values('d24','cname8','eeee6');select * from a;
select * from b;
select * from c;这三张都是临时表,现在想把临时表的数据转换到另外三张正式表,
转换规则: 1、表中空缺的地方补全,例如:表A中的ANAME字段的值,
如果上面值为aname1,那么下面的空缺的值就补为aname1,
再到下面碰到aname2, 那么下面的空缺的值就补为aname2,以此类推;
2、表A中的ANAME中必须要在表D中的DNAME中存在,如果不存在就不能转到正式表中;
3、如果表A中的ANAME的值能够匹配到表D中的DNAME,则按照ANAME把表D中的dadd的值带出来,
如果表A中的aadd中的值与表D中的dadd中的值不一致的话,则以表D中的值为准;
SELECT aid,Nvl(aname,Lag(aname)over(ORDER BY aid)) aname ,aadd
FROM a WHERE EXISTS(SELECT 1 FROM d WHERE a.aname=d.dname) ;
--上面未看到条件3,修正如下:
SELECT a.aid,
Nvl(a.aname,Lag(a.aname)over(ORDER BY a.aid)) aname ,
a.aadd,
Decode(a.aadd,d.dadd,a.aadd,d.dadd) dadd
FROM a,d
WHERE a.aname=d.dname;
create table a_bak as
select aid,nvl(aname,lag(aname)over(order by aid)) aname ,d.dadd
from a where exists(select 1 from d where a.aname=d.dname) ;
--其他表类似
这样不行啊,你把我说的第一个条件给漏了。
没有转换表A之前的查询结构应该是1 a1 aname1 ssss
2 a2 ssss2
3 a3 aname2 ssss3
4 a4 ssss4
5 a5 aname3 ssss5
6 a6 aname4
7 a7
8 a8
9 a9 aname5 sssss6
10 a9
根据第一个条件把空缺的补全后的结果应该是这样的
1 a1 aname1 ssss
2 a2 aname1 ssss2
3 a3 aname2 ssss3
4 a4 aname2 ssss4
5 a5 aname3 ssss5
6 a6 aname4 ssss5
7 a7 aname4 ssss5
8 a8 aname4 ssss5
9 a9 aname5 sssss6
10 a9 aname5 sssss6
红色的为补全的数据,然后再根据这些数据利用条件2,3 进行过滤。
2 a2 空缺 ssss2
3 a3 aname2 ssss3
4 a4 空缺 ssss4
5 a5 aname3 ssss5
6 a6 aname4 空缺
7 a7 空缺 空缺
8 a8 空缺 空缺
9 a9 aname5 sssss6
10 a9 空缺 空缺
--虽然lag函数能取对应前面不为空的aname,但是你a表中aname连续为空的个数不定,
--针对你数据来说,下面lag移位取对应不为空的aname,下面只是能满足你测试数据要求,
--如果要更具体的话,肯定要动态来确定lag的移位位置SELECT a.aid,a.aname,a.aadd,Decode(a.aadd,d.dadd,a.aadd,d.dadd) dadd
FROM d,
(SELECT aid,
Nvl(Nvl(aname,Lag(aname)over(ORDER BY aid)),Lag(aname,2)over(ORDER BY aid)) aname,
Nvl(Nvl(Nvl(aadd,Lag(aadd)over(ORDER BY aid)),Lag(aadd,2)over(ORDER BY aid)), Lag(aadd,3)over(ORDER BY aid)) aadd
FROM a) a
WHERE a.aname=d.dname;结果:
AID ANAME AADD DADD
-------------------------------
a2 aname1 ssss2 ssss1
a1 aname1 ssss ssss1
a4 aname2 ssss4 ssss3
a3 aname2 ssss3 ssss3
a5 aname3 ssss5 ssss5
a8 aname4 ssss5 ssss6
a7 aname4 ssss5 ssss6
a6 aname4 ssss5 ssss6
a9 aname5 sssss6 ssss1
a9 aname5 sssss6 ssss1
create or replace procedure p_test is
l_aid a.aid%type;
l_aname a.aname%type;
l_aadd a.aadd%type;
l_varname a.aname%type;
l_varadd a.aadd%type;
cursor v is select * from a;
begin
for v_a in v loop
l_aid := v_a.aid;
if v_a.aname is null then
l_aname :=l_varname;
elsif v_a.aname is not null then
l_varname := v_a.aname;
l_aname :=l_varname;
end if;
if v_a.aadd is not null then
l_varadd := v_a.aadd;
l_aadd := l_varadd;
elsif v_a.aadd is null then
l_aadd := l_varadd;
end if;
dbms_output.put(l_aid||'==');
dbms_output.put(l_aname||'--');
dbms_output.put_line(l_aadd||'--');
end loop;
end;
SQL> begin
2 p_test;
3 end ;
4 /
a1==aname1--ssss--
a2==aname1--ssss2--
a3==aname2--ssss3--
a4==aname2--ssss4--
a5==aname3--ssss5--
a6==aname4--ssss5--
a7==aname4--ssss5--
a8==aname4--ssss5--
a9==aname5--sssss6--
a9==aname5--sssss6--
PL/SQL procedure successfully completed
这样结果是对的,但我不知道能否有更好更优化的游标方法