Nested Cursor Problem

[buck]

Hi,

i have a problem, with a cursor in PL/SQL. I'm trying to implementate a Car Hire Service.
SERIALNR is the identifikator for the Car.

I have 2 tables:

Table 1:

SERIALNR TYPNR FILNR Name
----------------------------
811 1 1 SMART
812 1 1 VW LUPO
813 2 3 Golf4
814 2 2 AUDI A3

Table 2:

SERIALNR Date_Begin Date_Return
----------- ----------- ------------
811 02.16.2005 02.16.2005
812 02.16.2005 02.16.2005
812 02.17.2005 02.22.2005
812 02.02.2005 05.02.2005


What the cursor should do, is
FIRST, go to Table 1, select the first number, take this number (i.e.: 811), give it to the next Cursor. Cursor #2 should take this number and compare for this number the Time Period.
For Example: It is not possible to rent the Car with the SerialNr "811" from 02.16.2005 until 02.16.2005, because we have already a reservation for this date for this car.

I've tried to fix this problem with the following solution:


CREATE OR REPLACE Function funct_seriennr1(typnr_in IN number,
filnrrueck_in IN number,
gepAusDat_in IN date,
gepRueckDat_in IN date)
RETURN number IS
v_seriennr number;
cnumber_2 number;

Cursor c1 IS
select seriennr from autoexemplar where typnr = typnr_in;

CURSOR c2(v_seriennr IN number) IS
select seriennr, resnr
from ausleihe
where ((vgepaus > gepRueckDat_in) or (vgeprueck < gepAusDat_in))
and seriennr = v_seriennr
and vgepfilrueck = filnrrueck_in;

BEGIN

open c1;

loop
fetch c1
into v_seriennr;
open c2(v_seriennr);
loop
fetch c2
into cnumber_2;
exit when c2%notfound;
end loop;
close c2;
exit when c1%notfound;
end loop;

close c1;

RETURN cnumber_2 ;
END;

The Problem is, that Cursor C1 takes the first Number (811), passes this number to Cursor 2 which works fine. But if Cursor #2 does not find a Car for the appropriate date, the Loop stopps.

What i'm looking for is a solution how i can tell Cursor C1: "If C2 does not find a Car, try it with the next number".

Any help is appreciate, if you need further information, please do not hesitate to ask!

Thanks in advance,
Stefan Hausknecht

[gandolf989]

Why do you need two cursors? It would be more efficient and easier to maintain as one query. I wrote this but it might not compile on the first or tenth try, but it should work. Also make sure that I didn't transpose any of the column names. Icht nicht spechen sie deutch ser gut.

CREATE OR REPLACE FUNCTION funct_seriennr1 (
   typnr_in         IN       autoexemplar.seriennr%TYPE,
   filnrrueck_in    IN       autoexemplar.vgepfilrueck%TYPE,
   gepausdat_in     IN       DATE,
   geprueckdat_in   IN       DATE )
   RETURN NUMBER
IS
   v_seriennr          autoexemplar.seriennr%TYPE;
BEGIN
   SELECT seriennr
     INTO v_seriennr
     FROM autoexemplar auto
    WHERE typnr = typnr_in                            AND
   EXISTS
        ( SELECT seriennr, resnr
            FROM ausleihe aus
           WHERE ( auto.geprueckdat_in NOT
         BETWEEN ( aus.vgepaus AND aus.vgeprueck )    AND
                   auto.gepausdat_in   NOT
         BETWEEN ( aus.vgepaus AND aus.vgeprueck ))   AND
                   seriennr     = auto.seriennr       AND
                   vgepfilrueck = filnrrueck_in  )    AND
         ROWNUM = 1;
   RETURN v_seriennr;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
        RETURN 0;
   WHEN OTHERS
        RAISE;
END funct_seriennr1;
/

[buck]

Hi,
thank you for your help! Your "Between" solution pushed me to the right direction.

Just for fun, here is my final function (after 1 Week :-))

CREATE OR REPLACE Function funct_seriennr6(typnr_in IN number,
filnrrueck_in IN number,
gepAusDat_in IN date,
gepRueckDat_in IN date)
RETURN number IS
cnumber_2 number;
cnumber_autoex number;


cursor c1 is
select seriennr
from autoexemplar
where seriennr not in (select seriennr from ausleihe)
and typnr = typnr_in;

CURSOR c2 IS
select seriennr
from ausleihe
WHERE (((vgepaus not between gepausdat_in and geprueckdat_in) AND
(vgeprueck not BETWEEN gepausdat_in AND geprueckdat_in)) or

((gepausdat_in not between vgepaus and vgeprueck) AND
(geprueckdat_in not between vgepaus and vgeprueck)))
and seriennr in
(select seriennr from autoexemplar where typnr = typnr_in)
and seriennr not in
(select seriennr
from ausleihe

WHERE (((vgepaus between gepausdat_in and geprueckdat_in) AND
(vgeprueck BETWEEN gepausdat_in AND geprueckdat_in))) or

((gepausdat_in between vgepaus and vgeprueck) AND
(geprueckdat_in between vgepaus and vgeprueck)))

and vgepfilrueck = filnrrueck_in;
BEGIN
open c1;
loop
fetch c1
into cnumber_autoex;
exit when c1%notfound;
end loop;
close c1;

open c2;
loop
fetch c2
into cnumber_2;
exit when c2%notfound;
end loop;
close c2;

if cnumber_autoex is null then
RETURN cnumber_2;
else
return cnumber_autoex;
end if;
END;

Greetings from Germany,
Stefan Hausknecht

[gandolf989]

Buck, do you understand that the code you wrote is not very efficient? Even if it does work consistantly, which it might not work consistantly. You should avoid using explicit cursors. If you can get something done with one query, why would you use two?

Is this a homework assignment? It seems that someone is trying to get you to use a nested cursor, but not because it is needed.

[buck]

Hi,

yes it is a homework assignment. But as far as i know, i do need a cursor, if i'd like to catch more than one row, and that's what happens here. This Function is just a part of the total constraint to ensure that the customer will get a warning, if he would like to rent a type of car, if that car is not available.

Also we have a table with types, i didn't post that one. So that means: One Type does have many real Cars, and each Car is assigned to exactly one type. That was the starting point for the whole problem, that one customer doesn't make a reservation for a specific Car, he will make a reservation for one TYPE!
Also, a Customer does have the possibility to rent a Car in Munich and return the Car in Berlin. But that does work also.

I'm sure that this code isn't the most efficient code that is possible to solve the problem, but i've tested it up to now for different Date and Type combinations and it worked so far.

greetings,
Stefan Hausknecht

[gandolf989]

Originally posted by buck
Hi,

yes it is a homework assignment. But as far as i know, i do need a cursor, if i'd like to catch more than one row, and that's what happens here. This Function is just a part of the total constraint to ensure that the customer will get a warning, if he would like to rent a type of car, if that car is not available.

Also we have a table with types, i didn't post that one. So that means: One Type does have many real Cars, and each Car is assigned to exactly one type. That was the starting point for the whole problem, that one customer doesn't make a reservation for a specific Car, he will make a reservation for one TYPE!
Also, a Customer does have the possibility to rent a Car in Munich and return the Car in Berlin. But that does work also.

I'm sure that this code isn't the most efficient code that is possible to solve the problem, but i've tested it up to now for different Date and Type combinations and it worked so far.

greetings,
Stefan Hausknecht

But you do want to return one car. The user should be able to type in a search criteria, and this function should be called which will return one and only one car. Isn't there an assumption that each persone only wants to rent one car, and if they wanted more than one they could handle it with more than one transaction. Which ROWNUM = 1 will cause it to only return one car. And if no cars are available, the user will get a 0 for the car id. You could also raise an error instead of returning 0.