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: 9090
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: 68645
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: 9090
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: 68645
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;
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: Fri Apr 26 07:38:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
