----Oracle创建存储过程

create or replace procedure Pro_fenye(
tablename in varchar2 , --表名
pageNow in number ,--当前页数
pageSize in number ,--每页的显示数目
pageCount out number ,---总页数
total out number,---输出总条数
pageResultSet out sys_refcursor  )--返回结果集
--未加过滤条件，排序条件，有待完善

is
  totalCount number; 
  startrow number;
  endrow number; 
  pageSql varchar2(200);---为游标而写
  countSql varchar2(100);-----求总条数的字符串,
 --filterSql varchar2(100);

 -----带完善
 
begin
  
   countSql:='select count(*) from '||tablename;--求总条数的字符串,
  
   execute immediate(countSql) into totalCount;
  
   total:=totalCount;
 
   pageCount:=ceil(totalCount/pageSize);--总页数
  
   startrow:=(pageNow-1)*pageSize+1;--开始
   endrow:=pageNow*pageSize;--第几条数据结束
  
   if endrow>totalCount then
   endrow:=totalCount;--判断是否超过
   end if;--结束符号
  
 pageSql:=
 ---分页，在修改动态的参数
  'select *  from
    (
     select * from
     (
        select t.* ,rownum rn from
           ( select * from '||tablename||' ) t
     ) where rn <='||endrow||'
    )where rn>='||startrow ;
   
   open pageResultSet for pageSql;
  
end Pro_fenye;

--------java调用存储过程

package com.shopping.test;
import ....................

public class Procedure {
 
 public static void main(String[] args) throws SQLException {
  

  Configuration con = new Configuration().configure();
  SessionFactory sf = con.buildSessionFactory();
  Session s = sf.openSession();
  org.hibernate.Transaction tc = s.getTransaction();
  tc.begin();
  
  Connection conn = s.connection();
  
        ResultSet rs = null;  
        CallableStatement cstmt = null;  
        try {  
            cstmt = conn.prepareCall("{call Pro_fenye(?,?,?,?,?,?)}");  
            cstmt.setString(1, "duser");  
            cstmt.setInt(2, 1);  
            cstmt.setInt(3, 5);  
            cstmt.registerOutParameter(4, OracleTypes.NUMBER);  
            cstmt.registerOutParameter(5, OracleTypes.NUMBER);  
            cstmt.registerOutParameter(6, OracleTypes.CURSOR);  
           
            cstmt.executeQuery();  
           
            System.out.println("共" + cstmt.getObject(4) + "页");  
            System.out.println("共" + cstmt.getObject(5) + "条");  
           
            rs = (ResultSet) cstmt.getObject(6);  
            while (rs.next()) {  
                System.out.println(
                      rs.getString("duid") + " -- "+
                      rs.getString("uname") + " -- " +
                         rs.getString("upassword"));  
            }  
        } catch (Exception e){
            e.printStackTrace();  
        } finally {  
            rs.close();  
            cstmt.close();  
            conn.close();  
        }  
     tc.commit();
  s.close();
 }
}

------------------------------------------

QQ:727030664

欢迎大家讨论java,oracle 一起进步

本资源根据网络资源而修改