PL/SQL Implicit Cursor Attribute

When a PL/SQL block contain select into clause or pure DML statement, then oracle server automatically created a memory area.

This memory area is also called as ‘SQL Area’ or Context Area or Implicit cursor, this memory area return single record when PL/SQL block contain select into clause, this memory area also return multiple records when PL/SQL block contain DML statement., But these multiple records processed at a time by using SQL engine.

Along with this memory area oracle server automatically created four memory locations, these memory locations also behaves like a variables, these memory locations are identified by implicit cursor attribute.

Implicit cursor having following four attributes.

  1. SQL%NOTFOUND
  2. SQL%FOUND
  3. SQL%ISOPEN
  4. SQL%ROWCOUNT

SQL%ISOPEN returns false and also SQL%NOTFOUND, SQL%FOUND Attributes returns Boolean values either true or false, whereas SQL%ROWCOUNT returns number datatype

BEGIN

Delete from emp where ename = ‘&ename’;

If SQL%FOUND THEN

Dbms_output.put_line(‘Your record does not exist’);

END IF;

END;

Enter Value For Ename: ORA

O/P => Your Record Does Not Exist

Enter Value for Ename: KING

O/P => Your Friend Has Deleted

Below some more example

BEGIN

UPDATE emp SET sal = sal +100 where job = ‘ANALYST’;

Dbms_output.put_line(‘Effected Number of Records’||’ ‘||SQL%ROWCOUNT);

END;

O/P => Effected Number of Records: 2

Note: Always group function returns single value