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:-
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;
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;
--valid syntax target_cursor_variable := source_cursor_variable; --invalid syntax source_cursor_variable := NULL --Invalid syntax explict_cursor := another_Explicit_cursor
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===========
0 comments:
Post a Comment