oracle10growcount

rowcount in Oracle


I want to use the function SQL%ROWCOUNT as a way to tell me if a record is in a table or not. The code that I have is the following:

DECLARE
v_emp employee%ROWTYPE; 
CURSOR c_emp IS
SELECT * FROM employee WHERE name='chuck';
BEGIN
OPEN c_emp;

    FETCH c_emp INTO v_emp;
    IF SQL%ROWCOUNT=1 THEN
             DBMS_OUTPUT.PUT_LINE('found');
            ELSE
                     DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
        END IF;
END;

But it does not print anything at all, even though that record with that name exists in the database


Solution

  • Normally, you'd do something like

    DECLARE
      l_count PLS_INTEGER;
    BEGIN
      SELECT COUNT(*)
        INTO l_count
        FROM employee
       WHERE name = 'chuck'
         AND rownum = 1;
    
      IF( l_count = 1 )
      THEN
        dbms_output.put_line( 'found' );
      ELSE
        dbms_output.put_line( 'not found' );
      END IF;
    END;
    

    If you really want to use an explicit cursor, you would need to check the <<cursor_name>>%rowcount, not sql%rowcount to determine how many rows had been fetched. If you're going to use an explicit cursor, you also need to take care to close the cursor. Since you didn't post your table definition or the data you're using, I'll use the EMP table in the SCOTT schema as an example

    SQL> ed
    Wrote file afiedt.buf
    
      1  DECLARE
      2    v_emp emp%ROWTYPE;
      3    CURSOR c_emp IS
      4      SELECT * FROM emp WHERE ename='SMITH';
      5  BEGIN
      6    OPEN c_emp;
      7    FETCH c_emp INTO v_emp;
      8    IF c_emp%ROWCOUNT=1 THEN
      9      DBMS_OUTPUT.PUT_LINE('found');
     10    ELSE
     11      DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
     12    END IF;
     13    CLOSE c_emp;
     14* END;
    SQL> /
    found
    
    PL/SQL procedure successfully completed.
    

    And note that no matter what approach you use, if you want the output from DBMS_OUTPUT to be displayed, you need to enable output in whatever tool you are using. If you are using SQL*Plus, that would mean running

    SQL> set serveroutput on;
    

    before executing the anonymous PL/SQL block.