Explicit Cursor

The mean of generating an explicit cursor is to name the cursor in the declaring section of the PL/SQL block.

The only advantage of declaring explicit cursor over implicit cursor is that programmer have more control. The process of working with the explicit cursor consist of the following steps

Declaring – This initialize the cursor into memory

Open – Declared cursor is open and memory is allocated

Fetch – Now cursor can retrieve data

Close - Cursor must be closed to release the allocated memory.

SET SERVEROUTPUT ON
DECLARE
  --Cursor declaration
  CURSOR C_EMP is
      SELECT  INITCAP(ename), sal, NVL(comm,0)
      FROM emp;
  --Variable declaration
  v_ename   emp.ename%TYPE;
  v_sal     emp.sal%TYPE;
  v_comm    emp.comm%TYPE;
  v_earning BINARY_INTEGER;
BEGIN
  -- open cursor
  OPEN C_EMP;
  LOOP
   -- fetch cursor
    FETCH C_EMP INTO v_ename,v_sal,v_comm;
      EXIT WHEN C_EMP%NOTFOUND;
      IF C_EMP%ROWCOUNT = 1 THEN
        DBMS_OUTPUT.PUT_LINE( 'ENAME'||'---->'||'EARNING' );
      END IF;
      v_earning := v_sal + v_comm;
      DBMS_OUTPUT.PUT_LINE( v_ename||'---->'||v_earning);
  END LOOP;
  --close cursor
  CLOSE C_EMP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found exception raised');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Others Exception raised');
    IF C_EMP%ISOPEN THEN
      CLOSE C_EMP;
    END IF;
END;
/

anonymous block completed
ENAME---->EARNING
Smith---->800
Allen---->1900
Ward---->1750
Jones---->2975
Martin---->2650
Blake---->2850
Clark---->2450
Scott---->3000
King---->5000
Turner---->1500
Adams---->1100
James---->950
Ford---->3000
Miller---->1300