counting found/notfound rows in cursor loops
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: counting found/notfound rows in cursor loops

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    I think I'm going brain dead. How can a row be both "found" and "not found"? I'm obviously missing something basic here. My eventual goal is to count rows where a match is not found, but it seems every one is both found and not found. Here is the code, with results from running it after:

    CREATE OR REPLACE PROCEDURE TEST

    AS
    v_coy NUMBER(2) := 01; /* set as needed for test or prod */
    v_sql NUMBER(7);
    v_text VARCHAR2(1000);
    v_counter_1 NUMBER(7) := 0;
    v_counter_2 NUMBER(7) := 0;
    v_counter_3 NUMBER(7) := 0;
    v_spcur_sp_id NUMBER(10);
    v_spcur_premise_id NUMBER(10);
    v_spcur_name VARCHAR2(30);
    v_spcur_phone VARCHAR2(12);
    v_sacur_coy NUMBER(2);
    v_sacur_sa_id NUMBER(10);
    v_sacur_sp_id NUMBER(10);
    v_sacur_premise_id NUMBER(10);
    v_sacur_account_id NUMBER(10);

    CURSOR sp_cur
    IS
    SELECT sp_id,
    premise_id,
    std_format_name,
    ph_primary_nbr
    FROM TABLE A
    WHERE premise_id between 555555555 and 555599999;

    CURSOR sa_sp_cur
    IS
    SELECT *
    FROM TABLE B
    WHERE coy = v_coy
    AND premise_id = v_spcur_premise_id
    AND sp_id = v_spcur_sp_id;

    BEGIN

    OPEN sp_cur;

    LOOP
    BEGIN
    FETCH sp_cur INTO
    v_spcur_sp_id,
    v_spcur_premise_id,
    v_spcur_name,
    v_spcur_phone;

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM || ' Error on fetch from TABLE A');
    END;

    IF sp_cur%NOTFOUND THEN
    EXIT;
    ELSE
    v_counter_1 := v_counter_1 + 1; /* count how many rows processed in outer loop */
    END IF;

    /* for testing: */
    dbms_output.put_line('looping thru sp_cur with: prem: ' || v_spcur_premise_id
    || ' sp: ' || v_spcur_sp_id || ' ' || v_spcur_name
    || ' ' || v_spcur_phone);

    OPEN sa_sp_cur;

    LOOP
    BEGIN
    FETCH sa_sp_cur INTO
    v_sacur_coy,
    v_sacur_sa_id,
    v_sacur_sp_id,
    v_sacur_premise_id,
    v_sacur_account_id;

    EXCEPTION
    WHEN OTHERS THEN
    v_text := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(v_text || ' Error on select from TABLE B');
    END;

    IF sa_sp_cur%NOTFOUND THEN
    v_counter_2 := v_counter_2 + 1; /* increment counter where match not found */
    EXIT;
    else
    v_counter_3 := v_counter_3 + 1; /* increment counter where match found */
    END IF;

    END LOOP;
    CLOSE sa_sp_cur;

    END LOOP;
    CLOSE sp_cur;

    DBMS_OUTPUT.PUT_LINE('Total TABLE A records read: ' || v_counter_1);
    DBMS_OUTPUT.PUT_LINE('No matches found on prem# AND sp# matches from TABLE B: ' || v_counter_2);
    DBMS_OUTPUT.PUT_LINE('Matches found on prem# AND sp# matches from TABLE B: ' || v_counter_3);

    END TEST;
    /

    RESULTS WHEN RUN:
    SQL> exec gis.test;
    looping thru sp_cur with: prem: 555595204 sp: 555598235 DOE, JOHN

    Total TABLE A records read: 1
    No matches found on prem# AND sp# matches from TABLE B: 1
    Matches found on prem# AND sp# matches from TABLE B: 1

    PL/SQL procedure successfully completed.

    I know I am only reading one row in the outer loop, as a select on the table has shown. Any help is greatly appreciated.


  2. #2
    Join Date
    Apr 2001
    Posts
    118
    I think it's the following snippet of code in your inner loop:


    IF sa_sp_cur%NOTFOUND THEN
        v_counter_2 := v_counter_2 + 1; /* increment counter where match not found */
        EXIT;
    else
        v_counter_3 := v_counter_3 + 1; /* increment counter where match found */
    END IF;


    You will always increment v_counter_2 even if you do find a row. First time thorugh the inner loop, you find a row and increment v_counter_3. You will restart the loop, see the no more rows are found and then increment v_counter_2.

    I would suggest using %ROWCOUNT in tandem with %NOTFOUND to see if you should set v_counter_2.

    HTH,

    Heath

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