rowcount in Oracle

Question

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

Answer 1

1. Return No of Row count for a successful statement
2. Return NULL when getting an error once run the plsql statement
3. Returns TOO_MANY_ROWS if you run the SELECT INTO without BULK COLLECT. You can find more examples here

Example On SQL%ROWCOUNT

Insert
        --
        BEGIN 
        INSERT INTO Students_table 
        (ID, NAME, AGE, CLASS_ID) 
        VALUES  
        (16, 'TOM ', 12, 9); 
        DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' records.'); 
        END; 

        --Inserted 1 records
        Update
        --
        BEGIN 
        UPDATE  
        Students
        SET  
        CLASS_ID = 13  
        WHERE  
        AGE < 15; 
        DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows.'); 
        END; 


        --Updated 12 rows.
        Delete
        --
        DECLARE 
        LAST_CLASS_ID NUMBER(3) := 12; 
        BEGIN 
        DELETE FROM  
        Students
        WHERE  
        CLASS_ID = LAST_CLASS_ID; 
        DBMS_OUTPUT.PUT_LINE('Number of students deleted: ' || TO_CHAR(SQL%ROWCOUNT)); 
        END;

        --Number of students deleted: 10 
        @DbViewRowLevelSecurity CustomImplemented
        VIEW Shop_Order_Prod_Part_Lov IS
        Prompt = 'Manufactured Part'
        Order_No.Flags = 'PMI-L'
        Release_No.Flags = 'PMI-L'

Answer 2

Solution without SQL%ROWCOUNT and local variables:

begin
  for entry in ( select case when count(*) = 0 then 'found'
                             else 'not found'
                        end as is_found_txt
                   from dual
                  where exists(select null
                                 from employee
                                where name = 'chuck'))
  loop
      dbms_output.put_line( entry.is_found_txt );
  end loop;
end;

Answer 3

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.