Implicit and Explicit Cursors

Implicit cursor :

   It is implemented directly in a execution block. we cannot control the cursor . In PL/SQL you also use implicit cursors for DML statements and single select statements. it must return only one row
     Implicit (static) cursor is commonly refers to the good old SELECT INTO, in which Oracle implicitly opens, executes and closes the cursor for you, depositing any selected values of a single row INTO program data structures.
Example :
CREATE OR REPLACE PROCEDURE select_student_name (rollnum IN rollno.student%TYPE)
IS
   lv_stud_name student.name%TYPE;
BEGIN
   SELECT name INTO  lv_stud_name
     FROM student
    WHERE rollno = rollnum;
END;



The single select is a simple solution, but insufficient to solve the following problems.

• You may need to process more general result sets which return more or less than one row
• You may need to process rows in a specific order
• You may need to control the execution of your program depending on the result set.

To address these problems you need to use explicit cursors.

Working with explicit cursors

 Explicit cursors work the same way implicit cursors work, but you control the execution explicitly. It must declared in declaration part. it returns more than one row.

 DECLARE
  v_ename VARCHAR2(12);
  v_empno NUMBER:=7839;
--Oracle allocates memory and processes the query it is declared in declaration part
  CURSOR ename_cursor IS
    SELECT ename
      FROM emp
      WHERE empno=v_empno;
BEGIN
  OPEN ename_cursor;  -- Oracle binds variables, and executes query identifying the active set.
  --Oracle fetches a row from the active set, sets the value of v_ename, and advances the pointer to the active set.  
  FETCH ename_cursor INTO v_ename;
  CLOSE ename_cursor;  -- release the memory
END;
/

 Cursor attributes:

Cursor Attributes
Attribute	Description
cur%ISOPEN	Returns TRUE if cursor is open.
cur%NOTFOUND	Returns FALSE if the last FETCH found a row.
cur%FOUND	Returns TRUE if the last FETCH found a row..  (Logical inverse of %NOTFOUND).
cur%ROWCOUNT	Returns the number of rows modified by the DML statement.
SQL%BULK_ROWCOUNT	Returns the number of rows processed for each execution of the bulk DML operation.