Re: Date Operations in Stored Procedure

What happens when p_d1 is less than d1 and p_d2 is > d2? That's an overlapping reservation and would not be caught by this code?

"Binod Kumar Singh" <binodkumar.s_at_in.bosch.com> wrote in message news:3A59A064.DBE38B71_at_in.bosch.com...
> Hi
> The following procedure may help u.
>
> Create or replace procedure
> Proc_CheckReservation(p_item in number, p_d1 in date, p_d2 in date)
> as
> NoOfRows Number := 0;
> Begin
> Select count(item) into NoOfRows from t_test where item=p_item and
> (p_d1 between d1 and d2 OR
> p_d2 between d1 and d2 );
> If NoOfRows<>0 Then
> -- the slot is full
> dbms_output.put_line('already reserved');
> Else
> -- reserve the slot by inserting record.
> Insert Into t_test Values(p_item,p_d1,p_d2);
> commit;
> dbms_output.put_line('one data inserted to reserve the slot');
> End If;
> End;
>
>
> Arun Solleti wrote:
>
> > What i have ?
> >
> > I table which contains reservation schdules for some given set of Items.
> > The Reservation schdules will be in the form of Data range i.e. start
> > date and end date. So i will have 3 field in the database Items, StDate,
> > EndDate. There can be multiple reservations for a single Item.
> >
> > Problems ?
> >
> > I am trying to write a stored operations which gets three input
> > parameters Item, D1, D2 for placing reservation on Item from D1 to D2.
> > For this purpose i have to parse through existing reservation scehdules
> > and verify if the i can reserve the Item between D1 and D2 or will it
> > overlap with existing schdules.
> >
> > Result ?
> >
> > My stored procedure should return OK if there is no overlap otherwise
> > RESERVED if there is a overlap.
>
Received on Thu Jan 11 2001 - 03:08:08 CET