Home » SQL & PL/SQL » SQL & PL/SQL » If exists clause in oracle
If exists clause in oracle [message #656504] |
Sat, 08 October 2016 16:55 |
|
mazeem
Messages: 2 Registered: October 2016
|
Junior Member |
|
|
Hi,
I found the following statement in mysql trigger which needs to migrated in oracle ,kindly help me to resolve this problem
if exists (select 1 from stakeholder_extids where stakeh_type='RA' and
case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1
) then
insert into trrans_requests_aabbcc values(1);
end if;
Complete code for trigger is
create or replace TRIGGER stakeholder_extid_dirt_i before insert on stakeholder_extids for each row
Declare v_rec_no Number(19);
begin
gen_rec_no('I','stakeholder_extids',v_rec_no);
insert into stakeholder_extidh (action_no ,stakeholder_id ,extid_srno ,stakeh_type ,ext_acq_id ,ext_merchant_id ,ext_store_id ,ext_device_id ,last_upd_on ,geog_id ,last_upd_dtime ,extid_name ,stkhldr_ext_dbit04_flg ) values(v_rec_no ,:new.stakeholder_id ,:new.extid_srno ,:new.stakeh_type ,:new.ext_acq_id ,:new.ext_merchant_id ,:new.ext_store_id ,:new.ext_device_id ,:new.last_upd_on ,:new.geog_id ,:new.last_upd_dtime ,:new.extid_name ,:new.stkhldr_ext_dbit04_flg );
:new.rec_no:=v_rec_no;
Update central_log set rec_no = v_rec_no where action_no = v_rec_no;
if :new.stakeh_type='RA' then
if exists (select 1 from stakeholder_extids where stakeh_type='RA' and
case when coalesce(ext_acq_id,'oOo')=coalesce(:new.ext_acq_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_merchant_id,'oOo')=coalesce(:new.ext_merchant_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_store_id,'oOo')=coalesce(:new.ext_store_id,'oOo') then 1 else 0 end=1 and
case when coalesce(ext_device_id,'oOo')=coalesce(:new.ext_device_id,'oOo') then 1 else 0 end=1
) then
insert into trrans_requests_aabbcc values(1);
end if;
end if;
End;
|
|
|
Re: If exists clause in oracle [message #656505 is a reply to message #656504] |
Sat, 08 October 2016 18:37 |
|
Barbara Boehmer
Messages: 9095 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
create or replace TRIGGER stakeholder_extid_dirt_i
before insert on stakeholder_extids
for each row
Declare
v_rec_no Number(19);
begin
gen_rec_no('I','stakeholder_extids',v_rec_no);
insert into stakeholder_extidh
(action_no ,stakeholder_id ,extid_srno ,stakeh_type ,ext_acq_id ,ext_merchant_id ,
ext_store_id ,ext_device_id , last_upd_on ,geog_id ,last_upd_dtime ,extid_name ,
stkhldr_ext_dbit04_flg )
values
(v_rec_no ,:new.stakeholder_id ,:new.extid_srno ,:new.stakeh_type ,:new.ext_acq_id ,:new.ext_merchant_id ,
:new.ext_store_id ,:new.ext_device_id ,:new.last_upd_on ,:new.geog_id ,:new.last_upd_dtime ,:new.extid_name ,
:new.stkhldr_ext_dbit04_flg );
:new.rec_no:=v_rec_no;
Update central_log
set rec_no = v_rec_no
where action_no = v_rec_no;
if :new.stakeh_type='RA' then
--
for x in
(select count(*) cnt
from dual
where exists
((select 1
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
loop
if (x.cnt = 1) then
insert into trrans_requests_aabbcc values(1);
end if;
end loop;
--
end if;
End stakeholder_extid_dirt_i;
/
|
|
|
|
Re: If exists clause in oracle [message #656508 is a reply to message #656505] |
Sun, 09 October 2016 01:27 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote: for x in
(select count(*) cnt
from dual
where exists
((select 1
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
loop
if (x.cnt = 1) then
insert into trrans_requests_aabbcc values(1);
end if;
end loop;
Better is (with dummy_var to be declared in DECLARE section):
begin
select 1 into dummy_var
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
insert into trrans_requests_aabbcc values(1);
exception when no data_found then null;
end;
|
|
|
Re: If exists clause in oracle [message #656509 is a reply to message #656508] |
Sun, 09 October 2016 01:58 |
|
Barbara Boehmer
Messages: 9095 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 08 October 2016 23:27
Quote: for x in
(select count(*) cnt
from dual
where exists
((select 1
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo'))))
loop
if (x.cnt = 1) then
insert into trrans_requests_aabbcc values(1);
end if;
end loop;
Better is (with dummy_var to be declared in DECLARE section):
begin
select 1 into dummy_var
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
insert into trrans_requests_aabbcc values(1);
exception when no data_found then null;
end;
"no data_found" should be "no_data_found" and if the select statement returns more than 1 row it will raise "ORA-01422: exact fetch returns more than requested number of rows" error. Another alternative would be to select count(*) into dummy_var and check for >0.
|
|
|
Re: If exists clause in oracle [message #656510 is a reply to message #656509] |
Sun, 09 October 2016 02:53 |
|
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
So:
begin
select 1 into dummy_var
from stakeholder_extids
where stakeh_type='RA'
and nvl(ext_acq_id,'oOo')=nvl(:new.ext_acq_id,'oOo')
and nvl(ext_merchant_id,'oOo')=nvl(:new.ext_merchant_id,'oOo')
and nvl(ext_store_id,'oOo')=nvl(:new.ext_store_id,'oOo')
and nvl(ext_device_id,'oOo')=nvl(:new.ext_device_id,'oOo');
insert into trrans_requests_aabbcc values(1);
exception when no_data_found or too_many_rows then null;
end;
No need to count when the result should be 1.
Counting means execute the statement till the end to have the actual count.
Trapping the exception means stop the execution as soon as you get the second one.
|
|
|
Re: If exists clause in oracle [message #656511 is a reply to message #656504] |
Sun, 09 October 2016 07:03 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
1. No need for v_rec_no and unnecessary assignments between it and :new.rec_no.
2. No need for case statements in select from stakeholder_extids.
3. No need for inner if statement.
create or replace
trigger stakeholder_extid_dirt_i
before insert
on stakeholder_extids
for each row
begin
gen_rec_no(
'I',
'stakeholder_extids',
:new.rec_no
);
insert
into stakeholder_extidh(
action_no,
stakeholder_id,
extid_srno,
stakeh_type,
ext_acq_id,
ext_merchant_id,
ext_store_id,
ext_device_id,
last_upd_on,
geog_id,
last_upd_dtime,
extid_name,
stkhldr_ext_dbit04_flg
)
values(
:new.rec_no,
:new.stakeholder_id,
:new.extid_srno,
:new.stakeh_type,
:new.ext_acq_id,
:new.ext_merchant_id,
:new.ext_store_id,
:new.ext_device_id,
:new.last_upd_on,
:new.geog_id,
:new.last_upd_dtime,
:new.extid_name,
:new.stkhldr_ext_dbit04_flg
);
update central_log
set rec_no = :new.rec_no
where action_no = :new.rec_no;
if :new.stakeh_type='RA'
then
insert
into trrans_requests_aabbcc
select 1
from stakeholder_extids
where stakeh_type='RA'
and coalesce(ext_acq_id,'oOo') = coalesce(:new.ext_acq_id,'oOo')
and coalesce(ext_merchant_id,'oOo') = coalesce(:new.ext_merchant_id,'oOo')
and coalesce(ext_store_id,'oOo') = coalesce(:new.ext_store_id,'oOo')
and coalesce(ext_device_id,'oOo') = coalesce(:new.ext_device_id,'oOo')
and rownum = 1;
end if;
end;
/
SY.
[Updated on: Sun, 09 October 2016 07:08] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sun May 19 10:31:36 CDT 2024
|