查询3d信息（组3、组6、机球号）---存储过程的使用方法---

1、存储过程代码：pr_get_3dinfo------------------

 

create or replace procedure pr_get_3dinfo(
                             iz3    in  integer,      --------组三 为选默认0
                             iz6    in  integer,     --------组六
                             ijq       in  integer,     --------机球好
                             izjq     in  integer,        -----最近期数
                             iyear    in varchar2,      --------年份
                             ocursor out pk_public.zhcw_cursor   -------------游标集
                            )

  is                 ---------在is 和begin 直接用来定义本地的临时变量
    v_where varchar2(1000);    --------定义临时变量（作为动态的sql串中的条件变量串）
    v_sql varchar2(4000);          --------定义临时变量（作为动态的sql串）

    
    /*
   功能：
     参数：未选 默认0，
         iz3     1表示选中
         iz6    1表示选中
         ijq       1 1机1球 ，2 1机2球 ，3 2机1球， 4 2机2球
         izjq      ----最近期数
         iyear    --------年份
         作者：李成
         时间：2010-7-17
  */
  begin----------------------- （begin） 与（end 存储过程名称）之间为业务处理阶段
   --- return;
  
   v_where := ' and t.cz_id=2';
  if(ijq=1) then
    v_where := ' and m.j_num=1 and m.q_num=1'||v_where;
  elsif(ijq=2) then
    v_where := ' and m.j_num=1 and m.q_num=2'||v_where;  
  elsif(ijq=3) then
    v_where := ' and m.j_num=2 and m.q_num=1'||v_where;  
  elsif(ijq=4) then
    v_where := ' and m.j_num=2 and m.q_num=2'||v_where;   
  end if;
 
  if(iyear >0) then
   v_where := ' and substr(t.kj_date,1,4)='''||iyear||''''||v_where; 
  end if;
 
  if(iz3>0 and iz6=0) then
      v_where := ' and  ((substr(t.kj_z_num,1,1)=substr(t.kj_z_num,3,1) and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,5,1))
        or (substr(t.kj_z_num,1,1)=substr(t.kj_z_num,5,1) and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1))
        or (substr(t.kj_z_num,5,1)=substr(t.kj_z_num,3,1)  and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1)))'||v_where; 
  elsif(iz3=0 and iz6>0) then
      v_where := ' and (substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1) and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,5,1))'||v_where;
  elsif(iz3>0 and iz6>0) then
      v_where := ' and ((substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1)   and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,5,1))
                 or ((substr(t.kj_z_num,1,1)=substr(t.kj_z_num,3,1) and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,5,1))
                     or (substr(t.kj_z_num,1,1)=substr(t.kj_z_num,5,1) and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1))
                     or (substr(t.kj_z_num,5,1)=substr(t.kj_z_num,3,1)  and substr(t.kj_z_num,1,1)!=substr(t.kj_z_num,3,1))))'||v_where;
     
       
  end if;
 
 if(izjq=0) then
    v_sql := '
          select t.kj_issue,t.kj_z_num,t.kj_date,m.j_num,m.q_num,m.sj_num from tab_kaijiang_info t
                  left join tab_cz_zj_info m on t.cz_id=m.cz_id  and t.kj_issue=m.kj_issue
                   where 1=1 '  ||v_where||
          ' order by t.kj_issue desc';
 else
    v_sql := ' select t.kj_issue,t.kj_z_num,t.kj_date,m.j_num,m.q_num,m.sj_num from
               (select b.* from ( select * from  tab_kaijiang_info r where r.cz_id=2 order by r.kj_issue desc) b where rownum<='||izjq||')
                t  left join tab_cz_zj_info m on t.cz_id=m.cz_id  and t.kj_issue=m.kj_issue
                   where 1=1 '  ||v_where||
                   ' order by t.kj_issue desc' ;
  end if;    

 

    
   open ocursor for v_sql;   -------打开游标的连接
  end pr_get_3dinfo;          -------存储过程结束标识

 

 

二、创建返回的游标对象类型pk_public.zhcw_cursor ：

      create or replace package pk_public is

               type zhcw_cursor is  ref CURSOR;

      end pk_public;

      其中pk_public为包名，zhcw_cursor为游标类型。

 

三、java 调用存储过程。

 

/*
  *
  * 查询3d信息，调用存储过程
  *
  */
 public List find_3d_infor() throws Exception{
  

//创建一个连接对象
  Connection conn = getSession().connection();  
  //定义需要返回的List对象
  List list = new ArrayList();
  //定义阿帕奇提供的一个数据集对象
  RowSetDynaClass result = null;

//定义结果集对象

 ResultSet rs = null;

//定义数据连接控制对象
  CallableStatement proc = null;
 

  proc = conn.prepareCall("{ call pr_get_3dinfo(?,?,?,?,?,?) }");
  //设置整形和字符串型的数据
  proc.setInt(1, 0);
  proc.setInt(2, 0);
  proc.setInt(3, 0);
  proc.setInt(4, 30);
  proc.setString(5, "0");

  //定义需要返回的游标参数
  proc.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
  //执行数据
  proc.execute();

  //得到要返回的游标数据
  rs = (ResultSet) proc.getObject(6);

  //将结果集转换成需要的数据集对象
  result = new RowSetDynaClass(rs);
  //将记过集合对象转换成list对象
  list=result.getRows();
  //关闭结果集
  rs.close();

  //关闭控制器
  proc.close();

  //关闭连接对象

  conn.close();
  
  return list;
 }

}