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.
- SQL%NOTFOUND
- SQL%FOUND
- SQL%ISOPEN
- 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