1. classification:
Static cursors: explicit cursors and implicit cursors are called static cursors because before they are used, the definition of cursors has been completed and can no longer be changed.
Dynamic cursor: the cursor is not set when it is declared and can be modified when it is opened. There are strong type cursors and weak type cursors.
Strongly typed dynamic cursors: use the return keyword to define the return type of cursors when declaring variables
Weakly typed dynamic cursors: do not use the return keyword to define the return type of cursors when declaring variables
General dynamic cursors include REF CURSOR, REF CURSOR RETURN, and sys [refcursor].
REF CURSOR RETURN is a strong type, REF CURSOR is a weak type, and sys [refcursor] is a system predefined (defined in the STANDARD package) weak type.
2. There are three definitions:
--Support record type type t_cur is ref cursor return emp%rowtype; v_cur t_cur; type t_weak_cur is ref cursor; v_cur2 t_weak_cur; v_cur3 sys_refcursor;
Example 1: strong and weak cursor
declare --Define strong type ,Types returned and defined v_dept Must be consistent type c_dept_ref is ref cursor return dept%rowtype; cur_dept c_dept_ref; v_dept dept%rowtype; begin open cur_dept for select * from dept; --Dynamic cursor,Out of commission for loop loop fetch cur_dept into v_dept; exit when cur_dept%notfound ; dbms_output.put_line(v_dept.dname); end loop; close cur_dept; end; / --Weak type declare type c_cursor is ref cursor; v_ref_cur c_cursor; v_emprow emp%rowtype; v_deptrow dept%rowtype; begin open v_ref_cur for select * from dept; loop fetch v_ref_cur into v_deptrow; exit when v_ref_cur%notfound; dbms_output.put_line(v_deptrow.dname); end loop; close v_ref_cur; dbms_output.put_line('-------------------------'); open v_ref_cur for select * from emp where deptno = 10; loop fetch v_ref_cur into v_emprow; exit when v_ref_cur%notfound; dbms_output.put_line(v_emprow.ename); end loop; close v_ref_cur; exception when others then dbms_output.put_line(sqlerrm); end; /
Example 2: sys "refcursor
Use oracle9i Provided SYS_REFCURSOR Weak type cursor,No need to define cursors.... --Weak type declare v_ref_cur sys_refcursor; v_emprow emp%rowtype; v_deptrow dept%rowtype; begin open v_ref_cur for select * from dept; loop fetch v_ref_cur into v_deptrow; exit when v_ref_cur%notfound; dbms_output.put_line(v_deptrow.dname); end loop; close v_ref_cur; dbms_output.put_line('-------------------------'); open v_ref_cur for select * from emp where deptno = 10; loop fetch v_ref_cur into v_emprow; exit when v_ref_cur%notfound; dbms_output.put_line(v_emprow.ename); end loop; close v_ref_cur; exception when others then dbms_output.put_line(sqlerrm); end; / //We found that in order to accept the possible V ﹐ ref types, we used the2Variables,Can we use a variable to accept directly v_ref What is the value of it?? Not for dynamic cursors of weak type,Why?? //If used directly: v_ref_cur sys_refcursor; v_ref_vlaue v_ref_cur%rowtype; //It will report errors, declare problems, and personal understanding: because the dynamic cursor return content at this time is unknown, you write the V ﹣ ref ﹣ vlaue V ﹣ ref ﹣ cur% rowtype directly; then what type of V ﹣ ref ﹣ vlaue is, accept those values, you certainly don't know, which violates the certainty of the declaration //And if it's a strong type of dynamic, it's OK, because people are alreadyreturnIt's dead type;
Example 3: it can be used as an out / in parameter
declare --Strong type,Support record type type t_cur is ref cursor return emp%rowtype; v_cur t_cur; --Custom weak type type t_weak_cur is ref cursor; v_cur2 t_weak_cur; --Weak type of system v_cur3 sys_refcursor; --Use strong type procedure c_get_info(p_emp_cursor t_cur) is v_emp_cur p_emp_cursor%rowtype; begin loop fetch p_emp_cursor into v_emp_cur; exit when p_emp_cursor%notfound; dbms_output.put_line('Strong type-'||v_emp_cur.ename); end loop; end; --Use custom weak type procedure c_get_info_weak1(p_emp_cursor t_weak_cur,choice pls_integer) is --Because it's a weak type,Out of commissionp_emp_cursor%notfound --Specific type must be specified v_emp_cur emp%rowtype; v_dept_cur dept%rowtype; begin if choice=1 then loop fetch p_emp_cursor into v_emp_cur; exit when p_emp_cursor%notfound; dbms_output.put_line('Weak type-'||v_emp_cur.ename); end loop; else loop fetch p_emp_cursor into v_dept_cur; exit when p_emp_cursor%notfound; dbms_output.put_line('Weak type-'||v_dept_cur.dname); end loop; end if; end; --Weak type of using system procedure c_get_info_weak2(p_emp_cursor sys_refcursor,choice pls_integer) is --Because it's a weak type,Out of commissionp_emp_cursor%notfound --Specific type must be specified v_emp_cur emp%rowtype; v_dept_cur dept%rowtype; begin if choice=1 then loop fetch p_emp_cursor into v_emp_cur; exit when p_emp_cursor%notfound; dbms_output.put_line('Weak type-'||v_emp_cur.ename); end loop; else loop fetch p_emp_cursor into v_dept_cur; exit when p_emp_cursor%notfound; dbms_output.put_line('Weak type-'||v_dept_cur.dname); end loop; end if; end; begin --Strong type open v_cur for select * from emp where rownum<5; c_get_info(v_cur); --stay oracle 11gR2 in,Support opening the same cursor multiple times open v_cur for select * from emp where rownum<5; c_get_info(v_cur); close v_cur; dbms_output.put_line('-------------------------'); --Weak type1 open v_cur2 for select * from emp where rownum<5; c_get_info_weak1(v_cur2,1); open v_cur2 for select * from dept where rownum<5; c_get_info_weak1(v_cur2,2); close v_cur2; dbms_output.put_line('-------------------------'); --Weak type2 open v_cur3 for select * from emp where rownum<5; c_get_info_weak2(v_cur3,1); open v_cur3 for select * from dept where rownum<5; c_get_info_weak2(v_cur3,2); close v_cur3; end; /
Example 4: batch import is supported
--Dynamic cursors and bulk collect into declare v_cur sys_refcursor; type c_dept_tb is table of dept%rowtype; v_dept_tab c_dept_tb:=c_dept_tb(); begin open v_cur for select * from dept; fetch v_cur bulk collect into v_dept_tab; for x in 1 .. v_dept_tab.count loop dbms_output.put_line(v_dept_tab(x).dname); end loop; close v_cur; end; /
O(∩_∩)O~~~