Get resultset from oracle stored procedure

方法1：

In SQL Plus:

SQL>createprocedure myproc (prc out sys_refcursor) 
  2  is 
  3  begin 
  4     open prc forselect*from emp; 
  5  end; 
  6  / 
 
Procedure created. 
 
SQL> var rc refcursor 
SQL>execute myproc(:rc) 
 
PL/SQL procedure successfully completed. 
 
SQL>print rc 


方法二：

DECLARE 
  P_CAE_SEC_ID_N NUMBER; 
 
 P_FM_SEC_CODE_C VARCHAR2(200); 

  P_PAGE_INDEX NUMBER; 
  P_PAGE_SIZE NUMBER; 
 
 v_Return sys_refcursor; 
  
type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number); 
 
 v_rec t_row; 

 
BEGIN 
  P_CAE_SEC_ID_N :=NULL; 
 
 P_FM_SEC_CODE_C :=NULL; 
 
 P_PAGE_INDEX :=0; 
  
P_PAGE_SIZE :=25; 
 
  
CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY( 

    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N, 
  
  P_FM_SEC_CODE_C => P_FM_SEC_CODE_C, 
 
   P_PAGE_INDEX => P_PAGE_INDEX, 
  
  P_PAGE_SIZE => P_PAGE_SIZE, 
  
  P_FOF_SEC_REFCUR => v_Return 
  ); 
  -- Modify the code to output the variable 
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = '); 
 
 loop 
    
fetch v_Return into v_rec; 
   
exit when v_Return%notfound; 
  
DBMS_OUTPUT.PUT_LINE('sec_id = '|| v_rec.CAE_SEC_ID ||'sec code = '||v_rec.FM_SEC_CODE); 
 
end loop; 
 

END; 

方法三：


SQL> var r refcursor 

SQL> set autoprint on 

SQL> exec :r := function_returning_refcursor(); 
DECLARE 
  P_CAE_SEC_ID_N NUMBER; 
 
 P_FM_SEC_CODE_C VARCHAR2(200); 

  P_PAGE_INDEX NUMBER; 
  P_PAGE_SIZE NUMBER; 
 
 v_Return sys_refcursor; 
  
type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),rownum number, v_total_count number); 
 
 v_rec t_row; 

 
BEGIN 
  P_CAE_SEC_ID_N :=NULL; 
 
 P_FM_SEC_CODE_C :=NULL; 
 
 P_PAGE_INDEX :=0; 
  
P_PAGE_SIZE :=25; 
 
  
CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY( 

    P_CAE_SEC_ID_N => P_CAE_SEC_ID_N, 
  
  P_FM_SEC_CODE_C => P_FM_SEC_CODE_C, 
 
   P_PAGE_INDEX => P_PAGE_INDEX, 
  
  P_PAGE_SIZE => P_PAGE_SIZE, 
  
  P_FOF_SEC_REFCUR => v_Return 
  ); 
  -- Modify the code to output the variable 
  -- DBMS_OUTPUT.PUT_LINE('P_FOF_SEC_REFCUR = '); 
 
 loop 
    
fetch v_Return into v_rec; 
   
exit when v_Return%notfound; 
  
DBMS_OUTPUT.PUT_LINE('sec_id = '|| v_rec.CAE_SEC_ID ||'sec code = '||v_rec.FM_SEC_CODE); 
 
end loop; 
 

END; 

方法三：