哦先谢谢关怀 问题是 select br1 ,br2 from b where .... group by br1) B, (select cr1, cr2 from c where .... group by cr1) C 两个查询都有结果 我想如果最后的 select dr1,dr2 from d where .... group by dr1) D 无结果的情况下能否返回0 或默认值 或其他判断方法让insert into 能够成功把前 B C 的值插入成功 而 D 的值取默认或0 再次感谢!
try: ... begin insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某' from (select br1 ,br2 from b where .... group by br1) B, (select cr1, cr2 from c where .... group by cr1) C, (select dr1,dr2 from d where .... group by dr1) D where B.br1 = C.cr1 and C.cr1 = d.dr1 ; exception when others then null; end;
try: ... begin insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某' from (select br1 ,br2 from b where .... group by br1) B, (select cr1, cr2 from c where .... group by cr1) C, (select dr1,dr2 from d where .... group by dr1) D where B.br1 = C.cr1 and C.cr1 = d.dr1 ; exception when others then null; end;
--try:and C.cr1 = d.dr1 (+)
try: ... begin insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某' from (select br1 ,br2 from b where .... group by br1) B, (select cr1, cr2 from c where .... group by cr1) C, (select dr1,dr2 from d where .... group by dr1) D where B.br1 = C.cr1 and C.cr1 = d.dr1 ; exception when others then null; end; 的方法我试了不过好像exception 并不能捕获这个错误,直接执行过去了,结果还是被视为未执行 ;<外联的方法我试了 当一种条件为空时是可以的,不过我想实现的是当任意 B C D 不同时为空时能够取得不为空的 insert 记录 难道就没有办法了吗? 好苦哟!
不过我想实现的是当任意 B C D 不同时为空时能够取得不为空的 insert 记录不太理解是什么意思放些数据来说明一下
最差的方法用 insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某' from (select br1 ,br2 from b where .... group by br1 union all select 0,0 from dual where not exists(select 1 from b where... ) B, (select cr1, cr2 from c where .... group by cr1 union all select 0,0 from dual where not exists(select 1 from c where... ) C, (select dr1,dr2 from d where .... group by dr1 union all select 0,0 from dual where not exists(select 1 from d where... ) D where B.br1 = C.cr1 and C.cr1 = d.dr1 ;还是没能了解到你的确切意图。试试上面的吧
TRY: insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' || B.br1 + C.cr1 - D.dr1 ||'某某' from (select NVL(br1, 0)br1 ,br2 from b where .... group by br1) B, (select NVL(cr1,0) cr1, cr2 from c where .... group by cr1) C, (select NVL(dr1,0) dr1,dr2 from d where .... group by dr1) Dwhere B.br1 = C.cr1 and C.cr1 = d.dr1 ;
select br1 ,br2 from b where ....
group by br1) B,
(select cr1, cr2 from c where ....
group by cr1) C
两个查询都有结果 我想如果最后的
select dr1,dr2 from d where ....
group by dr1) D
无结果的情况下能否返回0 或默认值 或其他判断方法让insert into 能够成功把前 B C 的值插入成功 而 D 的值取默认或0 再次感谢!
...
begin
insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某'
from (select br1 ,br2 from b where ....
group by br1) B,
(select cr1, cr2 from c where ....
group by cr1) C,
(select dr1,dr2 from d where ....
group by dr1) D
where B.br1 = C.cr1 and C.cr1 = d.dr1 ;
exception when others then
null;
end;
...
begin
insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某'
from (select br1 ,br2 from b where ....
group by br1) B,
(select cr1, cr2 from c where ....
group by cr1) C,
(select dr1,dr2 from d where ....
group by dr1) D
where B.br1 = C.cr1 and C.cr1 = d.dr1 ;
exception when others then
null;
end;
...
begin
insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某'
from (select br1 ,br2 from b where ....
group by br1) B,
(select cr1, cr2 from c where ....
group by cr1) C,
(select dr1,dr2 from d where ....
group by dr1) D
where B.br1 = C.cr1 and C.cr1 = d.dr1 ;
exception when others then
null;
end;
的方法我试了不过好像exception 并不能捕获这个错误,直接执行过去了,结果还是被视为未执行 ;<外联的方法我试了 当一种条件为空时是可以的,不过我想实现的是当任意 B C D 不同时为空时能够取得不为空的 insert 记录 难道就没有办法了吗? 好苦哟!
我的意思是说当 B, C, D 的查询结果当其中一条查询结果有结果时,其他未查询出结果的语句的结果都能变成0 这样insert 的条件就能够成立 插入一条记录. 我这样说能表达清楚吗?嘿嘿
再次感谢!
insert into a(r1,r2,r3,rn ) select br1,br2,br3, '某某' ||B+C-D ||'某某'
from (select br1 ,br2 from b where ....
group by br1
union all select 0,0 from dual where not exists(select 1 from b where...
) B,
(select cr1, cr2 from c where ....
group by cr1
union all select 0,0 from dual where not exists(select 1 from c where...
) C,
(select dr1,dr2 from d where ....
group by dr1
union all select 0,0 from dual where not exists(select 1 from d where...
) D
where B.br1 = C.cr1 and C.cr1 = d.dr1 ;还是没能了解到你的确切意图。试试上面的吧
insert into a(r1,r2,r3,rn )
select br1,br2,br3, '某某' || B.br1 + C.cr1 - D.dr1 ||'某某'
from (select NVL(br1, 0)br1 ,br2 from b where ....
group by br1) B, (select NVL(cr1,0) cr1, cr2 from c where ....
group by cr1) C, (select NVL(dr1,0) dr1,dr2 from d where ....
group by dr1) Dwhere B.br1 = C.cr1 and C.cr1 = d.dr1 ;