Difference between explicit cursor and cursor variable - CURSOR vs REF CURSOR

Explicit cursor and Cursor variable both belong to reference data type in PL/SQL. However, there are some visible difference between them.The main agenda of this post is to understand how they are different so that they can be used appropriately. The most fundamental difference between them is : a cursor variable is a pointer and its value is the address of an item, not the item itself. In other words-  an explicit cursor assign name to the private SQL area where SELECT statement and its execution information is stored where as a cursor variable is address of that private area that's why it is more flexible and multiple SELECT statement can be accommodated. Lets discuss functional differences between them as in detail.

1. Explicit cursor refers to a predefined (static) SELECT statement and cursor variable can refer to multiple select statement in entire session.Both refers to static SQL(statement is known at compile time) however result set obtained from it can be varied using cursor with parameters(Second declaration).The select statement binding with cursor variable happens at at the time of the OPEN.
i.e: OPEN cur_var FOR <dynamic_select_statements>

Explicit cursor vs Cursor variable in PL/SQL 

2. Declaration of explicit cursor is different from cursor variable.Following blocks show difference between them and how cursor variable is used to bind different select statement in same PL/SQL program. Notice the declaration differences with keyword REF CURSOR and CURSOR.
Explicit cursor declaration:-

DECLARE 
  --a cursor with parameters. 
  CURSOR employee_cur2 (dept_id_in IN NUMBER) 
  IS SELECT FIRST_NAME FROM HR.EMPLOYEES where 
  department_id = dept_id_in;
BEGIN
  Open employee_cur2;
 LOOP 
   FETCH employee_cur2 .....
 END LOOP;
END;

Cursor variable declaration :-

DECLARE 
  -- Declare a type of cursor variable, notice REF CURSOR in syntax
  TYPE empcurtyp IS REF CURSOR; 
  emp_cv  empcurtyp; 
BEGIN
  -- Open cursor for a SELECT statement querying last_name and salary  
  OPEN emp_cv FOR
  SELECT last_name, salary FROM employees
  WHERE DEPARTMENT_ID = 90;
  LOOP
 FETCH ....
  END LOOP; 
 -- Open cursor for another SELECT statement querying additionally employee_id 
  OPEN emp_cv FOR
  SELECT first_name, salary, employee_id FROM employees
  WHERE DEPARTMENT_ID = 90;
  LOOP
 FETCH ....
  END LOOP;

 END;

3. A cursor variable can be assigned to another cursor variable, however it is not acceptable in explicit cursor. NULL cannot be assigned to a cursor variable. Refer following code blocks, If source_cursor_variable is open and after the assignment to target_cursor_variable is also open and both cursor variables point to the same SQL work area.

--valid syntax 
target_cursor_variable := source_cursor_variable; 
--invalid syntax 
source_cursor_variable := NULL
--Invalid syntax 
explict_cursor := another_Explicit_cursor

4. Scope of cursor variable is wider than explicit cursor, we can use cursor variables to pass query result sets between subprograms or pass a cursor variable as an argument to a procedure or function.
However, explicit cursor scope is limited to that PL/SQL block only. Following example shows how does REF CURSOR can be  used to call sub-program.We have two procedures-
get_employees_name(v_deptId_in NUMBER, v_cur OUT SYS_REFCURSOR ) and emp_detail_client_procedure(), emp_detail_client_procedure calling procedure get_employees_name and retrieving resultset from it and display using DBMS_OUTPUT.PUT_LINE.
get_employees_name (v_deptId_in NUMBER, v_cur OUT SYS_REFCURSOR ):-

create or replace procedure get_employees_name(
v_deptId_in NUMBER,
v_cur OUT SYS_REFCURSOR ) 
IS
begin
  --open cursor v_cur, pointing to employees records/result set   
  open v_cur for select * from  employees where DEPARTMENT_ID = v_deptId_in;
end get_employees_name;

procedure emp_detail_client_procedure();-

create or replace procedure emp_detail_client_procedure as 
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; 
v_emp_cur empcurtyp;
v_emp_rec EMPLOYEES%ROWTYPE;

begin
  /*call to another subprogram and passing cursor variable */
  HR.get_employees_name(90,v_emp_cur);
  dbms_output.put_line('FIRST_NAME' || ' ' || 'LAST_NAME');
  dbms_output.put_line('---------------------------------');
  LOOP
        FETCH v_emp_cur INTO v_emp_rec;
        EXIT WHEN v_emp_cur%NOTFOUND;
        dbms_output.put_line(v_emp_rec.FIRST_NAME|| '   ' || v_emp_rec.LAST_NAME);
    END LOOP;
    CLOSE v_emp_cur;
end emp_detail_client_procedure;

From emp_detail_client_procedure(), procedure get_employees_name is being called with cursor variable as parameter.Once this procedure is executed, cursor variable passed is open for employees record set and that result set we are able to fetch from client procedure and display the employees names.

==========End of post===========