oracle dynamic (Reference) cursor

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~~~

Tags: Oracle

Posted on Fri, 31 Jan 2020 15:38:32 -0500 by scrubbicus