Explicit Cursor

A Cursor is a work area or a section of memory in which SQL statements are processed in the Oracle server. A cursor is a pointer to a private SQL area that stores information about the processing of a SELECT or Data Manipulation Language statements like INSERT, UPDATE, DELETE or MERGE. Implicit and explicit are two types of PL/SQL statement cursor available on PL/SQL. This article is about Explicit Cursor but will have a some background on Implicit Cursor.

Implicit CURSOR:
Implicit Cursors are declared automatically for all DML and SELECT statements issued within a PLSQL block. Oracle server automatically creates Implicit cursor when a SQL statement is executed. The cursor is a work area in a memory where the statement is processed and contain the result of a SQL statements. There are several cursor attributes which allow the results of an SQL statement to be checked whether the statement affect any rows and how many? Implicit cursors are used in statement that returns only one row.You can learn more on Implicit Cursor here.

Explicit CURSOR:
An explicit cursor names the unnamed work area in which the database stores processing information when it executes a multiple-row query. When you have named the work area, you can access its information, and process the rows of the query individually.

PL/SQL Cursor: Example 1

SET serveroutput ON
DECLARE
  CURSOR cur_emp
  IS
    SELECT ename, empno, deptno FROM prabin_emp;
  c_ename prabin_emp.ename%TYPE;
  c_empno prabin_emp.empno%TYPE;
  c_deptno prabin_emp.deptno%TYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO c_ename, c_empno, c_deptno;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(c_ename||' '|| c_empno||' '|| c_deptno);
  END LOOP;
  CLOSE cur_emp;
END;
/

OUTPUT:

PL/SQL procedure successfully completed.

 KING   7839 10
 BLAKE  7698 30
 CLARK  7782 10
 JONES  7566 20
 SCOTT  7788 20
 FORD   7902 20
 SMITH  7369 20
 ALLEN  7499 30
 WARD   7521 30
 MARTIN 7654 30
 TURNER 7844 30
 ADAMS  7876 20
 JAMES  7900 30
 MILLER 7934 10

PL/SQL Cursor: Example 2 alternative to example 1 and this is what I would prefer on my PL/SQL.

SET serveroutput ON
DECLARE
  CURSOR cur_emp
  IS
    SELECT ename, empno, deptno FROM prabin_emp;
  l_cur_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(l_cur_emp.ename ||' '|| l_cur_emp.empno||' '||l_cur_emp.deptno);
  END LOOP;
  CLOSE cur_emp;
END;
/ 

OUTPUT:

PL/SQL procedure successfully completed.

 KING   7839 10
 BLAKE  7698 30
 CLARK  7782 10
 JONES  7566 20
 SCOTT  7788 20
 FORD   7902 20
 SMITH  7369 20
 ALLEN  7499 30
 WARD   7521 30
 MARTIN 7654 30
 TURNER 7844 30
 ADAMS  7876 20
 JAMES  7900 30
 MILLER 7934 10 

Parameterized Cursor takes in a value like that in stored procedure.Cursor becomes more reusable with Cursor parameters.Default values can be assigned to Cursor parameters.The scope of the cursor parameters is local to the cursor. Below are some of the examples of parameter cursor.

Parameterized Cursor: Example 1

SET serveroutput ON
DECLARE
  CURSOR cur_emp(p_deptno IN NUMBER)
  IS
    SELECT ename, empno, deptno 
    FROM prabin_emp
    where deptno = p_deptno;
    
  l_cur_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp(10);
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(l_cur_emp.ename ||' '|| l_cur_emp.empno||' '||l_cur_emp.deptno);
  END LOOP;
  CLOSE cur_emp;
END;
/ 

OUTPUT:

PL/SQL procedure successfully completed.

KING   7839 10
CLARK  7782 10
MILLER 7934 10

Parameterized Cursor: Example 2

SET serveroutput ON
DECLARE
  CURSOR cur_emp(p_deptno IN NUMBER)
  IS
    SELECT ename, empno, deptno 
    FROM prabin_emp
    where deptno = p_deptno;
    
  l_cur_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp(10);
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(l_cur_emp.ename ||' '|| l_cur_emp.empno||' '||l_cur_emp.deptno);
  END LOOP;
  CLOSE cur_emp;
  OPEN cur_emp(30);
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(l_cur_emp.ename ||' '|| l_cur_emp.empno||' '||l_cur_emp.deptno);
  END LOOP;
  CLOSE cur_emp;
END;
/ 

OUTPUT:

PL/SQL procedure successfully completed.
KING   7839 10
CLARK  7782 10
MILLER 7934 10
BLAKE  7698 30
ALLEN  7499 30
WARD   7521 30
MARTIN 7654 30
TURNER 7844 30
JAMES  7900 30

Parameterized Cursor: Example with Default value:

PL/SQL procedure successfully completed.
SET serveroutput ON
DECLARE
  CURSOR cur_emp(p_deptno IN NUMBER := 10)
  IS
    SELECT ename, empno, deptno 
    FROM prabin_emp
    where deptno = p_deptno;
    
  l_cur_emp cur_emp%ROWTYPE;
BEGIN
  OPEN cur_emp;
  LOOP
    FETCH cur_emp INTO l_cur_emp;
    EXIT
  WHEN cur_emp%NOTFOUND;
    dbms_output.put_line(l_cur_emp.ename ||' '|| l_cur_emp.empno||' '||l_cur_emp.deptno);
  END LOOP;
  CLOSE cur_emp;
END;
/

OUTPUT:

PL/SQL procedure successfully completed.

KING   7839 10
CLARK  7782 10
MILLER 7934 10

When nothing is defined, it takes deptno as 10 and executes. To prevent failure, assigning default value is a way the best way to write PL/SQL cursor.