create or replace type varchar2_ntt as table of varchar2(4000);
/
create or replace type number_ntt as table of number;
/
create type ty_Deal as object (Deal_ID number
,Deal_Type varchar2(10)
,Currency varchar2(10)
,Convert_Rate number
,Converted_Amnt number);
/
create type tb_Deal as table of ty_Deal;
/
create table Trade_Deal
(Deal_ID number not null
,Deal_Type varchar2(10)
,Deal_version integer
,Created timestamp default current_timestamp
,Trade_Risk integer check (Trade_Risk in (1,2,3,4,5,6,7,8,9))
,GBP_Amnt number not null
,USD_Amnt number);
create table FX_Rate
(FX_Rate_ID number not null
,FX_Rate_Date timestamp not null
,Deal_Type varchar2(10) not null
,FX_From_Cy varchar2(3) not null
,FX_To_Cy varchar2(3) not null
,FX_Rate number not null
);
insert all
into Trade_Deal values(1,'SPOT', 1,current_timestamp,1,10,null)
into Trade_Deal values(2,'FORWARD',1,current_timestamp,1,12,null)
into Trade_Deal values(3,'DEPOSIT',1,current_timestamp,1,11,null)
into Trade_Deal values(4,'SPOT', 1,current_timestamp,1,22,null)
select 1
from dual
/
insert all
into FX_Rate values (1,current_timestamp,'SPOT','GBP','USD',1.5757)
into FX_Rate values (2,current_timestamp,'SPOT','USD','GBP',0.6336)
select 1
from dual
commit;
The goal of the procedure is to update the USD_AMNT colunm
on the TRADE_DEAL table based on the latest foreign exchange
rate available in the FX_RATE table for the given DEAL_TYPE.
The output will be a collection of updated Deals, which could
then be passed further onto a separate PL/SQL procedure or Java
class as a parameter.
The benefit of using BULK COLLECT with
a FORALL is that other PL/SQL procedures or Java classes do not have
to query the database again to identify which deals were updated since
they're all in the output collection that was populated as a result
of the FORALL UPDATE.
The output collection could be passed on as a parameter.
Create a procedure to illustrate the point;
create or replace procedure Update_To_Currency
(
p_Deal_Type in Trade_Deal.Deal_Type%type
,p_To_Cy in FX_Rate.FX_To_Cy%type
,p_From_Cy in FX_Rate.FX_From_Cy%type
,p_Latest in timestamp
,p_tb_Deal out tb_Deal
) as
tb_Spot tb_Deal := tb_Deal(); -- input collection
tb_Spot_Updated tb_Deal := tb_Deal(); -- output collection
l_To_Cy FX_Rate.FX_To_Cy%type := p_To_Cy;
l_From_Cy FX_Rate.FX_To_Cy%type := p_From_Cy;
l_Deal_Type Trade_Deal.Deal_Type%type := p_Deal_Type;
l_Latest timestamp := p_Latest;
begin
select ty_Deal(td.Deal_ID,td.Deal_Type,l_To_Cy,fr.FX_Rate,0)
bulk collect into tb_Spot
from Trade_Deal td
inner join FX_Rate fr on td.Deal_Type = fr.Deal_Type
and fr.FX_From_Cy = l_From_Cy
and fr.FX_Rate_Date <= l_Latest
where td.Deal_Type = l_Deal_Type;
-- The following FORALL is combined with a BULK COLLECT
forall i in 1..tb_Spot.count
update Trade_Deal td
set td.USD_Amnt = td.GBP_Amnt*tb_Spot(i).Convert_Rate
where td.Deal_Type = tb_Spot(i).Deal_Type
and td.Deal_ID = tb_Spot(i).Deal_ID
returning ty_Deal(td.Deal_ID
,td.Deal_Type
,l_To_Cy
,tb_Spot(i).Convert_Rate
,td.USD_Amnt)
bulk collect into tb_Spot_Updated;
p_tb_Deal := tb_Spot_Updated;
end Update_To_Currency;
/
Now for a test;
SQL> set serveroutput on
SQL> declare
2 tb_Deals_Updated tb_Deal := tb_Deal();
3 begin
4
5 Update_To_Currency ('SPOT'
6 ,'USD'
7 ,'GBP'
8 ,current_timestamp
9 ,tb_Deals_Updated);
10
11 -- Verify that the Collection contains only updated Deals
12
13 for i in 1..tb_Deals_Updated.count loop
14 dbms_output.put_line(tb_Deals_Updated(i).Deal_ID
15 ||' '|| tb_Deals_Updated(i).Deal_Type
16 ||' '|| tb_Deals_Updated(i).Currency
17 ||' '|| tb_Deals_Updated(i).Convert_Rate
18 ||' '|| tb_Deals_Updated(i).Converted_Amnt);
19 end loop;
20
21 --call other procs that take tb_Deal as a parameter type
22 -- and pass tb_Deals_Updated as a parameter
23
24 end;
25 /
1 SPOT USD 1.5757 15.757
4 SPOT USD 1.5757 34.6654
PL/SQL procedure successfully completed.
Finally, verify the TRADE_DEAL table was updated as well.
SQL> select deal_id,deal_type,usd_amnt
2 from Trade_Deal
3 where Deal_type = 'SPOT';
DEAL_ID DEAL_TYPE USD_AMNT
---------- ---------- ----------
1 SPOT 15.757
4 SPOT 34.6654
