java调用存储过程，并返回多个结果集

项目需要，并返回结果集和另一个状态，此前未使用过存储过程，故作以记录：
sql代码：

Sql代码代码
-- Create table
create table JYXT_HDBM
(
ID      VARCHAR2(50),
MID     VARCHAR2(50),
ORDERNO VARCHAR2(50),
HDTYPE VARCHAR2(2),
MONEY   NUMBER default 0.0,
PAYTYPE VARCHAR2(2),
TIME    DATE,
STATUS VARCHAR2(2),
REMARKS VARCHAR2(200)
)

INSERT INTO jyxt_hdbm
(ID, mid, orderno, hdtype, money, paytype, TIME, status, remarks)
VALUES
(v_id, v_mid, v_orderno, v_hdtype, v_money, v_paytype, v_time, v_status, v_remarks);

--创建包，声明游标、存储过程
create or replace package users_package is
         type search_results is ref cursor;
         PROCEDURE users_proc(results_out out search_results, hdtype IN OUT VARCHAR);
end users_package;


--创建（实例化）存储过程
create or replace package body users_package is
   procedure users_proc (results_out out search_results, hdtype IN OUT VARCHAR)
              is
              begin
               dbms_output.put_line('start....');
              if hdtype is not null then
                  open results_out for select t.orderno,t.money,t.hdtype FROM jyxt_hdbm t WHERE t.hdtype=hdtype;
                  hdtype:='单项活动';
              else
                  open results_out for select t.orderno,t.money,t.hdtype FROM jyxt_hdbm t;
                  hdtype:='所有活动';
              end if;
                dbms_output.put_line('end!!!!!');
          END users_proc;
end users_package;



---pl sql 　测试调用
declare
cur_search_results jy.users_package.search_results;
CURSOR myCur is select t.orderno,t.money,t.hdtype FROM jyxt_hdbm t;
hdbmrow myCur%ROWTYPE ; --要保证行里的字段与游标里的行字段一致
hdtype VARCHAR(10);
BEGIN
hdtype:='';
jy.users_package.users_proc(cur_search_results, hdtype);
-- OPEN cur_search_results;
dbms_output.put_line(hdtype);
LOOP
    FETCH cur_search_results into hdbmrow;     --游标中查询出来的一行into进定义的变量hdbmrow中
    EXIT WHEN cur_search_results%NOTFOUND;
    dbms_output.put_line(hdbmrow.orderno||'    ' ||hdbmrow.money||'    ' ||hdbmrow.hdtype);

END LOOP;

   CLOSE cur_search_results;
END;

jsp代码(只摘录了核心代码)：

Java代码
　try{
    String type = ToolKit.dealNull(request.getParameter("type"));
    DataSource ds = GlobalParameter.getDataSource_User();
    Connection conn = ds.getConnection();

    String sql = "{call users_package.users_proc(?, ?)}";
          CallableStatement cstmt = conn.prepareCall(sql);
          cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

          /*　第二个参数，定义了in out类型的参数，所以做以下设置*/
          cstmt.setString(2,type);
          cstmt.registerOutParameter(2, Types.VARCHAR);

          cstmt.execute();
          ResultSet rs = null;

          //获取游标结果集
          rs = ((oracle.jdbc.OracleCallableStatement)cstmt).getCursor(1);


          //获取字符弄返回参数
          String str = cstmt.getString(2);

          out.println("---------"+str+"---------</br>");

          //处理返回游标
          while(rs.next()){
        out.print("　　订单号："+rs.getString (1));
        out.print("　　金额："+rs.getDouble(2));
        out.print("　　类型："+rs.getString (3));
        out.println("</br>");
          }

}catch(Exception e){
    System.out.println("error:"+e.toString());

}

作者“jiang-huatao”

补充：软件开发 , Java ,