Database

hello albert,
as mr. sandeeptiwari mentioned on your question as comment you can use merge statement and here i will share with you sample example for merge and not exists with regular insert statement
first we need to create dummy table to test our code on it :

CREATE TABLE testtab
(
 id NUMBER PRIMARY KEY
 ,last_name VARCHAR2 (200)
);

now we will using not exists clause with regular insert statement but with select like this :

INSERT INTO testtab
  SELECT 1, 'albert'
  FROM   dual
  WHERE  NOT EXISTS
           (SELECT NULL
            FROM   testtab
            WHERE  last_name = 'albert');

now the result would be :
1 row created.
then trying to execute same statement again and check the result :
0 rows created.
that because the row is already exists before

now we will trying to do the same but in this time we will using merge statement :

MERGE INTO testtab a
USING      (SELECT 2 id, 'jonny' last_name FROM dual) b
ON         (a.last_name = b.last_name)
WHEN NOT MATCHED THEN
  INSERT     (id, last_name)
  VALUES     (b.id, b.last_name);

now the result would be :
1 row merged.
then trying to execute same statement again and check the result :
0 row merged.

hope this help 🙂

SQL%ROWCOUNT :  return the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed so far
basically you can use :

BEGIN
  UPDATE xx_test_tab
  SET    test_col = 777777
  WHERE  col2 LIKE '2%';

  DBMS_OUTPUT.PUT_LINE(TO_Char(SQL%ROWCOUNT)||' rows affected.');
END;

hope this help 🙂