Nested Cursor Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Nested Cursor Problem

  1. #1
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    Nested Cursor Problem

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.

    Code:
    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;
    /
    Last edited by gandolf989; 02-17-2005 at 10:31 AM.
    this space intentionally left blank

  3. #3
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    Solution - Thank you

    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

  5. #5
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    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

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width