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.
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.
- Return No of Row count if statement completed successfully.
- Return NULL when there is no statement run.
- Returns TOO_MANY_ROWS exception when SELECT INTO without BULK COLLECT.
Example on SQL%ROWCOUNT
-- 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
-- BEGIN UPDATE Students SET CLASS_ID = 9 WHERE AGE < 12; DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows.'); END; / --Updated 12 rows.
-- 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
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.