Implicit cursors are also called as SQL cursors.

A cursor that is constructed and managed by PL/SQL is an implicit cursor. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement within a PL/SQL block.

Implicit cursor features:

• You cannot open/close/iterate an implicit cursor, but you can get information from its attributes.
• An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs.
• Cursor attributes for implicit cursor are prefixed with SQL. e.g. SQL%FOUND, SQL%ROWCOUNT
• Cursors attributes return values only after an implicit/explicit cursor.

Implicit cursor attributes

SQL%ISOPEN attribute

SQL%ISOPEN always returns FALSE, because an implicit cursor always closes after its associated statement runs.

SQL%FOUND Attribute

SQL%FOUND returns:

• NULL if no SELECT or DML statement has run
• TRUE if a SELECT statement returned one or more rows or a DML statement affected one or more rows
• FALSE otherwise

SQL%NOTFOUND Attribute

SQL%NOTFOUND (the logical opposite of SQL%FOUND) returns:

• NULL if no SELECT or DML statement has run
• FALSE if a SELECT statement returned one or more rows or a DML statement affected one or more rows
• TRUE otherwise

Q : What does SQL%NOTFOUND return with SELECT INTO statement?

Ans: The SQL%NOTFOUND attribute is not useful with the PL/SQL SELECT INTO statement, because:

• If the SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, before you can check SQL%NOTFOUND.
• A SELECT INTO statement that invokes a SQL aggregate function always returns a value (possibly NULL). After such a statement, the SQL%NOTFOUND attribute is always FALSE, so checking it is unnecessary.

SQL%ROWCOUNT Attribute

SQL%ROWCOUNT returns:

• NULL if no SELECT or DML statement has run
• Otherwise, the number of rows returned by a SELECT statement or affected by a DML statement (an INTEGER)

Cursors attributes values before and after implicit cursor.

DECLARE
BEGIN
  IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Above- ISOPEN');
  END IF;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Above- FOUND');
  END IF;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Above- NOT FOUND');
  END IF;
  DBMS_OUTPUT.PUT_LINE('Above ROWCOUNT '||SQL%ROWCOUNT||' row created.');

  INSERT INTO Dept
  VALUES(50, 'SHIPPING', 'CHENNAI');

  IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Below- ISOPEN');
  END IF;
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Below- FOUND');
  END IF;
  IF SQL%NOTFOUND THEN
    DBMS_OUTPUT.PUT_LINE('Below- NOT FOUND');
  END IF;
  DBMS_OUTPUT.PUT_LINE('Below ROWCOUNT '||SQL%ROWCOUNT||' row created.');
END;
/

CREATE TABLE Dept_temp
(
  Deptno INTEGER,
  Dname  VARCHAR2(20),
  Loc    VARCHAR2(20)
);

INSERT INTO Dept_temp
VALUES(50, 'HR', 'CHENNAI');

INSERT INTO Dept_temp
VALUES(60, 'OPS', 'DELHI');

INSERT INTO Dept_temp
VALUES(70, 'PRD', 'DALLAS');

BEGIN
  INSERT INTO Dept
  SELECT * FROM Dept_temp;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Rows Inserted.');

  UPDATE Dept
     SET Dname = 'EXPORT'
   WHERE Deptno = 50;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Rows Updated.');

  DELETE FROM Dept
   WHERE Deptno > 50;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||' Rows Deleted.');
END;
/