Table of Contents

Related Articles

How to use SQL ROWCOUNT in Oracle.

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print
SQL ROWCOUNT in Oracle

SQL%ROWCOUNT is an attribute of SQL which is returns an INTEGER value for the implicit cursor in PLSQL. In this article, I’m aiming to explain how to use SQL ROWCOUNT in Oracle and its different uses when programming. Suppose you are running a DML or SELECT statement that returns the number of affected rows followed by the last operation. Implicit cursors are not allowed to control by programmers who are created by Oracle when executing the SQL statements.

Once you execute a DML operation implicit cursor creates with the referring statement. If you are running an INSERT statement then the associated data will hold by the cursor. According to that UPDATE and DELETE operations holds affected rows as information related to the implicit cursor.

All the explicit cursor and cursor variables in these four attributes %FOUND%ISOPEN %NOTFOUND, and %ROWCOUNT. Other that this SQL the cursor has another two attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, use with the FORALL statement

Once after complete the operation the information applicable to access throughout the SQL%ATTRIBUTE ‘s. SQL%ROWCOUNT is one of the attributes that return the affected row count once finishing the operation.

How Many Rows Were Affected?

SQL ROWCOUNT in Oracle is not related to the state of the transaction. Therefore once finish the transaction it returns the affected row count BUT if you ROLLBACK the transaction ROWCOUNT in oracle SQL does not revert to the latest SAVEPOINT. transaction ROWCOUNT in Oracle SQL is still the same But not restored.

As I mentioned earlier, the implicit cursor opens every time you run a SELECT or DML statement. The implicit cursor is a session cursor that manages by Oracle PLSQL. Therefore you have to make sure to save the value of SQL ROWCOUNT when you want it later.

  1. Return No of Row count if statement completed successfully.
  2. Return NULL when there is no statement run.
  3. Returns  TOO_MANY_ROWS exception when SELECT INTO without BULK COLLECT.

Example on SQL%ROWCOUNT

Insert

--
BEGIN 
 INSERT INTO Students  
 (ID, NAME, AGE, CLASS_ID) 
 VALUES  
 (16, 'TOM ROOTS', 12, 8); 
 DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' records.'); 
END; 
/

--Inserted 1 records

Update

--
BEGIN 
 UPDATE  
 Students
 SET  
  CLASS_ID = 9  
 WHERE  
  AGE < 12; 
  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 

SQL ROWCOUNT in for loop Oracle

SQL ROWCOUNT is not creating an explicit cursor because it is related to the session therefore you can’t fetch values later. You have to fetch the value of ROWCOUNT inside the loop. If you want to count the total of affected rows inside the loop you have to add a small calculation.  Let’s see an example of loop how to use the oracle sql ROWCOUNT attribute.

--
DECLARE 
  local_cnt number := 0;
BEGIN 
  loop
   --Update statement doing some update according to loop value ...
    local_cnt:= local_cnt  + sql%rowcount;
  end loop;
  dbms_output.put_line('Number of updated rows = ' || local_cnt);
END;
/
--

<<cursor_name>>%rowcount Explicit Cursor

If you are willing to use the cursor by the programmer define the sql%rowcount will not return correct information for your output. It is because this creates an explicit cursor and you have to access information by using <<cursor_name>>%rowcount.

Explicit cursors are allowed access by the programmer at any time. Once finish the execution of the statement then you can access the affected ROWCOUNT by <<cursor_name>>%rowcount BUT keep in mind to use it before closing the cursor. Let’s see a simple example of <<cursor_name>>%rowcount.

--
DECLARE
  v_ student Students %ROWTYPE;
  CURSOR c_students IS
   SELECT class_id FROM Students WHERE name=’TOM’;
BEGIN
 OPEN c_students;
 FETCH c_students INTO v_ student;
  IF c_students %ROWCOUNT>1 THEN
   DBMS_OUTPUT.PUT_LINE('found');
  ELSE
   DBMS_OUTPUT.PUT_LINE(TO_CHAR('not found'));
  END IF;
 CLOSE c_students;
END;
/
--

Set SQL ROWCOUNT in Oracle

As a bonus, I here added how to set ROWCOUNT return by SELECT statement in the current session. This will apply to all the statements executed in the current session and will expire or remove once you change the ROWCOUNT or the session terminates.

--Syntax
--SET ROWCOUNT = number

SET ROWCOUNT = 10;
SELECT * FROM Students;

-- returns 10 rows

Summary

ROWCOUNT in Oracle SQL is a feature to return the number of affected rows related to SQL statements. This can be an implicit or explicit cursor and it will return an integer according to the use of ROWCOUNT in Oracle. SQL%ROWCOUNT returns the affected row count for SELECT or any DML statement. This is relevant to the current session and will not restore the latest savepoint values if rollback happens middle of the statement.

Thank you for reading this article and hope this will help you in future programming.

Reference

Share on facebook
Share on twitter
Share on linkedin
Share on email
Share on whatsapp
Share on pinterest
Share on print

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Posts

You May Like
Subscribe to our Newsletter