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 PL/SQL. Therefore, you have to make sure to save the value of SQL ROWCOUNT when you want it later.
- Return the number 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
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 in Oracle does not create 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, defining 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 be removed 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 in the middle of the statement.
Thank you for reading this article and hope this will help you in future programming.
