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

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

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代码(只摘录了核心代码)：

 

 

　　　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());
			
		}