Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: Help with CURSOR

    Hi all i am trying to answer the following question: Create a function called GUEST_FOUND that receives one parameter: a guest name and returns a BOOLEAN. Pass the guest name parameter to a PL/SQL cursor that fetches from the Guest table for guest names that begin with the parameter value in upper case. If the cursor returns no rows, return FALSE. If the cursor matches one row, return the TRUE. If the cursor returns more than one row, return NULL.

    Here is the code i have got:
    Code:
    CREATE OR REPLACE FUNCTION guest_found (p_guest_name VARCHAR2) RETURN BOOLEAN IS    
      CURSOR c_guest_cursor (c_guest_name VARCHAR2) IS
      SELECT guest_name FROM guest
      WHERE guest_name=p_guest_name;                                                v_guest_name guest.guest_name%TYPE;
      v_guest_found BOOLEAN;
      BEGIN
        OPEN c_guest_cursor(p_guest_name);
        LOOP
        FETCH c_guest_cursor INTO v_guest_name;
        EXIT WHEN c_guest_cursor%NOTFOUND;
          IF c_guest_cursor%FOUND THEN
              v_guest_found := TRUE;                    
          ELSIF c_guest_cursor%NOTFOUND THEN
              v_guest_found := FALSE;
          ELSE
              v_guest_found := NULL;                   
          END IF;
        END LOOP;
        CLOSE c_guest_cursor;
        RETURN v_guest_found;
      END;
      /
    
      ---------------------------------------------------------------------------------------------------------
    
      CREATE OR REPLACE PROCEDURE add_guest (p_guestname guest.guest_name%TYPE, p_guestaddress guest.guest_address%TYPE) IS     --Creates the procedure to add a guest
      BEGIN
        IF (guest_found(p_guestname) = TRUE OR NULL) THEN                                                                              
          INSERT INTO guest (guest_id,guest_name, guest_address)                                                                
          VALUES(guest_id_seq.NEXTVAL, p_guestname, p_guestaddress);                                                           
        ELSE
           RAISE_APPLICATION_ERROR(-20202, 'Guest already exisits');                                                        
        END IF;
      END;
    There is no errors with this code but it doesnt answer the question regards to checking for names with the Upper value, also doesnt return the correct results.

    Here is the code i am using to check it:

    Code:
    BEGIN
          ADD_GUEST ('Frederic', 'Barcelona, Spain');
          ADD_GUEST ('Mal',      'Holder');
          ADD_GUEST ('Shane',    'Lyneham');
          ADD_GUEST ('Dave',     'Turner');
          ADD_GUEST ('Anthony',  'Hackett');
          ADD_GUEST ('Troy',     'Chapman');
          DBMS_OUTPUT.PUT_LINE('Successfully called Add_Guest 6 times and added the following data:');
          FOR guest_cur IN (SELECT * FROM guest WHERE guest_id > v_guest_ID) LOOP
             DBMS_OUTPUT.PUT_LINE('   Guest ID:'||guest_cur.guest_id|| 
                                    ' Guest name:'||RPAD(guest_cur.guest_name,10)|| 
                                    ' Guest address:'||guest_cur.guest_address); 
          END LOOP;
       EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('Errors unexpectedly raised calling Add_Guest.  '||
                                  'Error raised: '||SQLERRM);
       END;
    
    BEGIN
          ADD_GUEST ('Ken',     'Chapman');--Expect this to fail
          DBMS_OUTPUT.PUT_LINE('INCORRECT: Add_Guest was meant to fail inserting a duplicate record.');
       EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('Add_Guest correctly failed inserting a duplicate record.  Expecting ORA-20202.  '||
                                  'Error raised: '||SQLERRM);
       END;
       ----------------------------------------------------------------------------------------
       BEGIN
          ADD_GUEST ('Mile Durkheim','91 Altruism Place, Canberra 2611'); --Expect this to fail
          DBMS_OUTPUT.PUT_LINE('INCORRECT: Add_Guest was meant to fail inserting a duplicate record.');
       EXCEPTION
          WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('Add_Guest correctly failed inserting a duplicate record.  Expecting ORA-20202.  '||
                                  'Error raised: '||SQLERRM);
       END;
    Can anyone please help with this issue. Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    You were writing this bunch of code just for test purpose, did not you? Because, this will never assure uniqueness in multi-user environment. Just call the function with the same name parameter from two sessions and then (after calling from both sessions) commit.

    The correct way of checking duplicate values in a column is creating a unique constraint on it. It is case sensitive. I do not understand your description about "Upper values"; anyway, to make duplicate detection case insensitive, you may make unique constraint on UPPER( <column_name> ).

    To your example - both 'Ken' and 'Mile Durkheim' do not seem to equal to any value from 'Frederic', 'Mal', 'Shane', 'Dave', 'Anthony' nor 'Troy', which were added before.

    [Edit: completed the first sentence in second paragraph]

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    Not sure why you would ever build logic that had boolean be anything but TRUE or FALSE. Another item is that its not very efficient with the loop mechanism. Its best to escape any loop as soon as possible. This is especially important if your table guest was large. You don't want to be there scanning the whole thing unnecessarily since you only care if its 0,1, or 2 in reality. Instead do it this way :

    Code:
    CREATE OR REPLACE FUNCTION guest_found (p_guest_name VARCHAR2) RETURN BOOLEAN IS    
      CURSOR c_guest_cursor (c_guest_name VARCHAR2) IS
      SELECT guest_name FROM guest
      WHERE guest_name=p_guest_name;                                                v_guest_name guest.guest_name%TYPE;
      v_guest_found BOOLEAN;
      v_counter PLS_INTEGER := 0;
      BEGIN
        OPEN c_guest_cursor(p_guest_name);
        LOOP
        FETCH c_guest_cursor INTO v_guest_name;
        EXIT WHEN c_guest_cursor%NOTFOUND;
          v_counter := v_counter + 1;
        EXIT WHEN v_counter = 2;
        END LOOP;
        CLOSE c_guest_cursor;
    
        IF v_counter <> 1 THEN
         v_quest_found := FALSE;
        ELSE
         v_quest_found := TRUE;
        END IF;
    
        RETURN v_guest_found;
      END;
      /

  4. #4
    Join Date
    Dec 2010
    Posts
    10
    Thanks heaps.

    Worked like a charm

Posting Permissions

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