Re: Date Operations in Stored Procedure

From: Dirk Bellemans <Dirk.Bellemans_at_skynet.belgium>
Date: Sun, 14 Jan 2001 11:12:58 +0100
Message-ID: <93ruhp$vlg$2_at_news0.skynet.be>

I'm hesitant to do your homework, but I don't want you to follow bad examples either...

I always use cursors. For the reasons why, check out the FAQ's or the manuals, but readability alone should do for now.

At first look, there are 2 situations that indicate an overlap: (1) either of your date falls within the time period or (2) both dates fall outside the time period on the lesser (larger) side. If we'd be naive, we'd could it like that. However, Oracle will translate OR's into UNION's and we don't want that, because a UNION is not exactly what it looks like: A UNION B <=> A U (B\A) and the merge requires a sort operation! So, as a rule of thumb: NEVER user OR on large table scans (even through an index). Split the result up yourself into disjunct sets and code these with UNION ALL or in different cursors.
Now, as for the return value. We don't want to count all the overlappig periods, we just want to check the existance of ONE. Unfortunately for the typist, the message "You have a problem", will not help him/her much to solve it. You should at least report something like "From date overlaps reservation number XXAZ, which runs from dd/mm/yyyy to dd/mm/yyyy, To date not checked".

I did not include the package wrapping, you can figure that one out for yourself. I use a function that returns a number, but you still have to fill in the details itself.

FUNCTION fChehkOverlap( pi_From IN DATE, pi_To IN DATE ) RETURNS NUMBER
IS
  CURSOR c_From ( ci_From IN DATE )

             IS
   SELECT /*+ INDEX(table iFromIndex) */

                  pk
      FROM table
   WHERE StDate <= ci_From
        AND EndDate >= ci_From
              ;
  CURSOR c_To ( ci_To IN DATE )
             IS
   SELECT /*+ INDEX(table iToIndex) */
                  pk
      FROM table
   WHERE StDate <= ci_To
        AND EndDate >= ci_To
              ;
  CURSOR c_Out ( ci_From IN DATE, ci_To IN DATE )
             IS
   SELECT /*+ INDEX(table iFromIndex) INDEX(table iToIndex)*/
                  pk
      FROM table
   WHERE StDate > ci_From
        AND EndDate < ci_To
              ;

  d_dummy DATE;
-- errorvalues
BEGIN

     nReturn := NULL;
     -- From Date
     OPEN c_FROM (pi_From)
     FETCH c_From INTO d_dummy;
     IF c_FROM%FOUND THEN
       nReturn := nFromOverlap;
     END IF;
     CLOSE c_From; /* Some people would test if the cursor DID open, but I'd
check for that error in the Exception section */
     IF nReturn IS NOT NULL THEN
      RETURN nReturn;
     END IF;
     -- You can figure the others yourself

EXCEPTION
   WHEN OTHERS THEN
      RETURN nBadLuckBaby;
END; dirkske.

"Arun Solleti" <asolleti_at_grad.csee.usf.edu> wrote in message news:3A579914.9A793330_at_grad.csee.usf.edu...
> 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 Sun Jan 14 2001 - 11:12:58 CET