this is harder than it looks -- both from a "how to approach" to "how to do concurrently" (i find the second part more interesting actually :)


Ok, so we start with:

ops$tkyte@ORA10G> select * from product;

PROD_CODE SDATE EDATE PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 31-JAN-05 20

ops$tkyte@ORA10G> select * from product_booking;

PROD_CODE SDATE EDATE BKD_PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 15-JAN-05 10
PXP 13-JAN-05 25-JAN-05 5



Now, I need a query that shows me for a range of dates -- the "truly" available amount.

So, we need a set of rows that cover the dates in question...

8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')


will do that for a range of dates from :x to :y

We need to join that to the "booked amounts" for a given (sole) product. In fact, we want to outer join -- so as to "have a value for each day and make sure was have SOME availability on that day"


7 from product_booking pb,
8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
12 ) x
13 where x.dt between pb.sdate(+) and pb.edate(+)
14 and pb.prod_code(+) = 'PXP'


so, that'll give us all of the booked pieces by day in that range, we need to get the "initially available" and subtract:

ops$tkyte@ORA10G> variable x varchar2(20)
ops$tkyte@ORA10G> variable y varchar2(20)
ops$tkyte@ORA10G> exec :x := '01-jan-2005'; :y := '15-jan-2005';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select x.dt,
2 (select pieces
3 from product
4 where sdate <= x.dt
5 and edate >= x.dt )
6 - nvl(sum(pb.bkd_pieces),0) really_available
7 from product_booking pb,
8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
12 ) x
13 where x.dt between pb.sdate(+) and pb.edate(+)
14 and pb.prod_code(+) = 'PXP'
15 group by x.dt
16 /

DT REALLY_AVAILABLE
--------- ----------------
01-JAN-05 10
02-JAN-05 10
03-JAN-05 10
04-JAN-05 10
05-JAN-05 10
06-JAN-05 10
07-JAN-05 10
08-JAN-05 10
09-JAN-05 10
10-JAN-05 10
11-JAN-05 10
12-JAN-05 10
13-JAN-05 5
14-JAN-05 5
15-JAN-05 5

15 rows selected.


So, now we have a query that tells us, by day -- for a product -- what the real availability is.....

We are ready to code our transaction:

ops$tkyte@ORA10G> create or replace
2 procedure book_a_product( p_name in varchar2,
3 p_sdate in date,
4 p_edate in date,
5 p_amt in number )
6 as
7 l_cnt number;
8 l_row product%rowtype;
9 begin
10 update product
11 set prod_code = p_name
12 where prod_code = p_name;
13
14 select count(*) into l_cnt
15 from dual
16 where exists
17 ( select null
18 from ( select x.dt,
19 (select pieces
20 from product
21 where sdate <= x.dt
22 and edate >= x.dt )
23 - nvl(sum(pb.bkd_pieces),0) really_available
24 from product_booking pb,
25 (select p_sdate+rownum-1 dt
26 from dual
27 connect by 1=1
28 and rownum <= p_edate-p_sdate
29 ) x
30 where x.dt between pb.sdate(+) and pb.edate(+)
31 and pb.prod_code(+) = p_name
32 group by x.dt
33 )
34 where really_available < p_amt
35 );
36
37 if ( l_cnt = 0 )
38 then
39 insert into product_booking
40 ( prod_code, sdate, edate, bkd_pieces )
41 values ( p_name, p_sdate, p_edate, p_amt );
42 else
43 raise_application_error(-20001, 'Insufficient pieces available' );
44 end if;
45 end;
46 /

Procedure created.


You might ask "why the update?"

The update is there for concurrency control reasons (i would suggest you revoke insert on product_booking, ONLY let them modify it via the stored procedure which correctly does the job -- no one else will do this correctly for you!). You must make sure one person at a time inserts into product booking for a given product when doing this overlap detection.


And now you have:


ops$tkyte@ORA10G> exec book_a_product( 'PXP', to_date('01-jan-2005'), to_date('07-jan-2005'), 7 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select * from product_booking;

PROD_CODE SDATE EDATE BKD_PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 15-JAN-05 10
PXP 13-JAN-05 25-JAN-05 5
PXP 01-JAN-05 07-JAN-05 7

ops$tkyte@ORA10G> rollback;

Rollback complete.

ops$tkyte@ORA10G> exec book_a_product( 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 );
BEGIN book_a_product( 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 ); END;

*
ERROR at line 1:
ORA-20001: Insufficient pieces available
ORA-06512: at "OPS$TKYTE.BOOK_A_PRODUCT", line 42
ORA-06512: at line 1



I'm pretty sure I got the bases covered on this one -- read committed and serializable transactions should be "safe" with this method (using update).