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