DML : CURSOR ATTRIBUTES

We can execute any DML statements from within PL/SQL, however,
we cannot execute DDL statements in PL/sQL unless we run them as dynamic SQL.

DML Operations:
INSERT / UPDATE / DELETE / SELECT

Cursor Attributes for DML Operations:

Implicit Cursor attributes return information about the execution of the most recent DML,regardless
of the block in which the implicit cursor is executed.

Start of the Session: All implicit cursor attributes yield NULL

1. SQL%FOUND ( TRUE, if one or more rows were modified )

2. SQL%NOTFOUND ( TRUE, if none of the rows were modified )

3. SQL%ROWCOUNT ( Returns Number of rows modified )

4. SQL%ISOPEN (FALSE)


Out of the above 4, we mostly use SQL%FOUND & SQL%ROWCOUNT.

SQL%FOUND : Determine if the DML statement affected any rows.

SQL%ROWCOUNT : Determine exactly how many rows were affected.


Example:

Begin

Update Scott.Emp Set Sal=(Sal*1.1) where Deptno=10;

dbms_output.put_line('Did any of the employees got the pay hike (Yes/NO):');

IF ( SQL%FOUND ) THEN
dbms_output.put_line('YES');
ELSE
dbms_output.put_line('NO');
END IF;

dbms_output.put_line('Number of employees got the pay hike:');
dbms_output.put_line(SQL%ROWCOUNT);

commit;
End;
/