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>
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:-
Cursor variable declaration :-
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.
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;
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===========