Oracle calls stored procedures and functions return result sets

   in program development, commonly used to return the result set of stored procedures, which in MySQL and SQL Server better processing, directly return query results can be,
but in Oracle inside out, there is one more step, To the unfamiliar brothers also get a sweat:), here I briefly introduce for reference,
   1   definition Package
       The result set returned by Oracle needs to customize a variable of the nature of a CURSOR (cursor variable), which is defined in the header, so a package is built, as follows, the header Pl/sql code create or replace package  pak_rstest      is        type   retcursor     IS   REF   CURSOR;       procedure pro_read          (             outcurse  in out retcursor         );       END; -- Package spec  

create OR REPLACE PACKAGE pak_rstest is TYPE retcursor is REF CURSOR;
   PROCEDURE Pro_read (Outcurse in Out retcursor); End; --Package Spec 

     above is to create a header called Pak_rstest, which defines a cursor type, the type named Retcursor, with this definition we can use him to return the result set, such as the inside of the package pro The _read process is a process that returns a result set, and the following is his package, Pl/sql code Create or replace package body pak_rstest is       procedure pro_read        (           outcurse  in out retcursor       )       is       begin             open outcurse for              select * from tbl_test                     where rownum<6;            return;       end;       end;  

CREATE OR REPLACE PACKAGE body pak_rstest was
   PROCEDURE pro_read
   (
       outcurse in Out  retcursor
   )
   is
   begin
        OPEN outcurse for
         select * from Tbl_test
                where rownum<6;
        return;
   End;

End;

This defines a package that has a procedure for returning a result set in the package Pro_read

2 is called inside the program,
Here's what if you call it in the program, here's a brief introduction to Java as an example,
Let's say you already have a connection conn object connected to the database (how to connect to the database I don't have to elaborate here),
     calls the procedure with the following code, Pl/sql code if (conn !=null) {       String  sqlstr =  "{Call  pak_sms2_route. Mo_issue (?)} ";         callablestatement cstmt = conn.preparecall (SQLSTR);         cstmt.registeroutparameter (1, oracle.jdbc.oracletypes.cursor);  //outcurse         cstmt.executeupdate ();         ResultSet rs =  (ResultSet)  cstmt.getobject (1);  //  Here the information has been read into the RS result set, and the rest of you are familiar with it.       while  (Rs.next ())  {            system.out.println (rs.getstring ("s_date1"));  //tbl_ The field name in the test  table or the column name of the result set         system.out.println (rs.getstring ("S_date2") );          }                 conn.close ();             conn = null;            }   

if (conn!=null) {
    String sqlstr = ' {call  pak_sms2_route. Mo_issue (?)} ";
     CallableStatement cstmt = Conn.preparecall (sqlstr);
     Cstmt.registeroutparameter (1, Oracle.jdbc.OracleTypes.CURSOR); Outcurse
     cstmt.executeupdate ();
     ResultSet rs = (ResultSet) cstmt.getobject (1); Here it is. The information has been read into the RS result set, and the rest of us are familiar with it. While
   (Rs.next ()) {
        System.out.println (rs.getstring ("s_date1"));//tbl_test The field name in the table or the column name of the result set
     System.out.println (rs.getstring ("S_date2"));    
         Conn.close ();
         conn = null;        
} 

OK, here we can see the result set of the returned content, is not relatively simple ah,:

   Oracle Stored procedure return result set: Procedure return recordset code Create or replace package pkg_test    as        TYPE myrctype IS REF CURSOR;              PROCEDURE get  (p_id number, p_ Rc out myrctype);    end pkg_test;             create or replace package body pkg_ Test    as        PROCEDURE get  (P_ID&NBSP;NUMBER,&NBSP;P_RC  out myrctype)        is            sqlstr   VARCHAR2  (500);        begin           if p_id  = 0 then    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;OPEN&NBSP;P_RC  for  &nbsP              SELECT ID, NAME,  Sex, address, postcode, birthday                    FROM student;           else               sqlstr :=                   ' Select id,name,sex,address,postcode,birthday                 from student where id=:w_id ';              open p_rc for sqlstr  USING p_id;           END IF;        END get;    end pkg_test;  

CREATE OR REPLACE PACKAGE pkg_test
as
    TYPE Myrctype is REF CURSOR;
  
    PROCEDURE Get (p_id number, p_rc out myrctype);
End Pkg_test;

  
CREATE OR REPLACE PACKAGE body pkg_test
    as PROCEDURE get (p_id number, p_rc out Myrctype)
    is
       sqlstr   V ARCHAR2 ();
    BEGIN
       IF p_id = 0 THEN
          OPEN p_rc for
             SELECT ID, NAME, sex, address, postcode, birthday from
               student;
  else
          sqlstr: =
             ' Select Id,name,sex,address,postcode,birthday from
            student where id=:w_id ';
          OPEN P_RC for Sqlstr USING p_id;
       End IF;
    End get;
End Pkg_test;

function returns the recordset:
Create packages and packages and functions with REF CURSOR definitions:

function return recordset code CREATE or REPLACE package pkg_test AS/* Define REF CURSOR type without return type, weak type, allow dynamic SQL query, otherwise strong type, cannot    use dynamic SQL queries;             */type Myrctype is REF CURSOR;    --function affirms functions get (IntID number) return myrctype; End Pkg_test;

CREATE or REPLACE
package pkg_test AS/
* Define REF CURSOR type
    without return type, weak type, allow dynamic SQL query,
    otherwise strong type, Unable to use dynamic SQL query;
*

   /Type myrctype is REF CURSOR;  
   
--function affirms functions get
   (IntID number) return Myrctype
; End Pkg_test;

  Package Body Code create or replace    package body pkg_test as   --function body        function get (Intid number)  return myrctype is          rc myrctype;  --define ref cursor variable    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SQLSTR&NBSP;VARCHAR2 (500);        begin          if intID=0  Then             --static test, directly returns results with SELECT statement             open rc for select id,name,sex, Address,postcode,birthday from    student;          else              --Dynamic SQL assignment, using: w_id to declare that the variable is obtained externally    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;SQlstr :=  ' Select id,name,sex,address,postcode,birthday from    student where  id=:w_id ';             --dynamic test, return results with sqlstr string, pass parameters with using keyword              open rc for sqlstr using  IntID          end if;                 return rc;        end get;           end pkg_test;  

create OR REPLACE Package Body pkg_test AS--function bodies functions get (IntID number) return Myrctype is R  c Myrctype;
    --Define REF CURSOR variable SQLSTR VARCHAR2 (500); Begin if Intid=0 then-static test, directly returns results with SELECT statement Open RC for select Id,name,sex,address,postcode,bir
      Thday from student; else--Dynamic SQL assignment, using: w_id to declare that the variable obtains from the external sqlstr: = ' Select Id,name,sex,address,postcode,birthday from student WH
         Ere id=:w_id ';
      -Dynamic testing, using SQLSTR string to return the result, using the Using keyword to pass the parameter open RC for sqlstr using IntID;
   
      End If;
    return RC;
   
End get;
End Pkg_test; 

  Java invoke Oracle function return cursor processing code callablestatement cstmt = null;    resultset rs = null;    try {   string callsql =  "{ = call aaaaa (?)}";    Cstmt = conn.preparecall (Callsql);            cstmt.setstring (2,  "UserName");    Cstmt.registeroutparameter (1, oracle.jdbc.oracletypes.cursor);    Cstmt.execute ();    rs =  (ResultSet)  cstmt.getobject (1);    if  (rs != null)  {           System.out.print ("Usercd");           system.out.print ("UserName");                             system.out.println ("EMAIL");    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBsp; while  (Rs.next ())  {                 system.out.print (rs.getstring (1) + "   ");        system.out.print (rs.getstring (2) + "   ");                          system.out.println (Rs.getstring (3));           }       }