Re: Date Operations in Stored Procedure

Hi
[Quoted] 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');

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 Mon Jan 08 2001 - 12:11:32 CET