oracle存储过程返回多个游标实例

花生

oracle存储过程返回多个游标实例

定义两个包（package）
CREATE OR REPLACE PACKAGE pro_package_test_001 AS
TYPE Test_CURSOR01 IS REF CURSOR;
end pro_package_test_001;

CREATE OR REPLACE PACKAGE pro_package_test_002 AS
TYPE Test_CURSOR02 IS REF CURSOR;
end pro_package_test_002;
定义存储过程
CREATE OR REPLACE PROCEDURE pro_query_001
(
--参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。
in_lx  IN Varchar2,
p_cus_01 OUT pro_package_test_001.Test_CURSOR01,
p_cus_02 OUT pro_package_test_002.Test_CURSOR02
)
AS
--定义变量
vs_lx VARCHAR2(1); --变量
vs_test1_id VARCHAR2(100); --变量
vs_test1_mc VARCHAR2(100); --变量
vs_test2_id VARCHAR2(100); --变量
vs_test2_mc VARCHAR2(100); --变量
--default_c SYS_REFCURSOR;
BEGIN
--用输入参数给变量赋初值。
vs_lx:= in_lx;
--插入test1表。
OPEN p_cus_01 FOR  Select
a.id As id1,
a.mc As mc1,
b.id As id2,
b.mc As mc2
Into
vs_test1_id,
vs_test1_mc,
vs_test2_id,
vs_test2_mc
From test1 a,test2 b Where a.id = b.id And a.lx = vs_lx;

--if p_cus_01%rowcount = 0 then
-- p_cus_01:=default_c;
-- end if;

OPEN p_cus_02 FOR  Select
id As id1,
mc As mc1
Into
vs_test1_id,
vs_test1_mc
From test2  Where lx = vs_lx;
-- if p_cus_02%rowcount = 0 then
-- p_cus_02:=default_c;
  --  end if;
--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。
Exception
WHEN OTHERS Then
ROLLBACK;
Return;
End pro_query_001;

java 代码调用
   public void ProcQuery(String procString,String [] params) throws Exception {
      Session session = null;
      Connection conn = null;
      ResultSet rs1 = null;
      ResultSet rs2 = null;
         CallableStatement proc = null;
         procString = "{call pro_query_001(?,?,?)}";
         params = new String [1];
      params[0]="0";
      try {
            session = getHibernateTemplate().getSessionFactory()
         .openSession();
            conn = session.connection();
         proc = conn.prepareCall(procString);
         proc.setString(1, params[0]);    //传入的参数
         proc.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
         proc.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);
         proc.execute();
         rs1 = (ResultSet)proc.getObject(2);//返回第一个游标
         rs2 = (ResultSet)proc.getObject(3);//返回第二个游标
         while(rs1 != null && rs1.next()) {
               System.out.println(">>>"+rs1.getString("id1"));
               System.out.println(">>>"+rs1.getString("mc1"));
               System.out.println(">>>"+rs1.getString("id2"));
               System.out.println(">>>"+rs1.getString("mc2"));
         }
         while(rs2 != null && rs2.next()) {
            System.out.println(">>>"+rs1.getString("id1"));
            System.out.println(">>>"+rs1.getString("mc1"));
      }
      } catch (SQLException e) {
         e.printStackTrace();
         throw new Exception("调用存储过程的时候发生错误[sql = " + procString + "]", e);
      }  finally {
         if (proc != null)
               proc.close();
         if (rs1 != null)
               rs1.close();
         if (rs2 != null)
               rs2.close();
         if (conn != null)
         conn.close();
      }
   }