How to get the affected rows (processed rows) from an update Query?
IMMEDIATLY after statement
In Oracle use: SQL%ROWCOUNT
In SQL Server use: @@RowCount
When doing Synchronization of tables this is very useful!!!
Synchronization PseudoCode
INSERT INTO xxx VALUES SELECT FROM yyy
EXCEPTION
WHEN OTHERS
UPDATE xxx SELECT FROM yyy
Bad Example - catch Exceptions, do something on Exception that is expected (and happens very often)
CREATE OR REPLACE
PROCEDURE sample IS
v_rows_processed integer := 0;
BEGIN
UPDATE sample
SET testno = 1;
WHERE test = 'PL/SQL';
v_rows_processed := SQL%ROWCOUNT;
IF v_rows_processed := 0
THEN
/* Insert Statement */
END IF;
END sample;.
Good example - Use processed rowcount in that synch case
thx to my co-worker PK. ps: In Sql-Server @@RowCount works even with SET NOCOUNT OFF. SET NOCOUNT ON says that SQl-Server should print the messages: "rows affected" and "rows returned"