1、在oracle的sqlplus或其他工具中运行一下pl/sql块建立存储过程
--创建包
create or replace package testpackage astype test_cursor is ref cursor;end testpackage;/--创建过程
create or replace procedure fenye( tableName in varchar2, --表名 fields in varchar2, --查询解果显示字段 wherecase in varchar2, --查询条件 pageSize in number, --一页显示记录数 pageNow in number, --当前页 orderField varchar2, --排序字段，为空表示不排序 orderFlag number, --排序标识 0：正序 1：倒序 myrows out number, --总记录数 myPageCount out number, --总分页 p_cursor out testpackage.test_cursor --返回的记录集) is--定义部分--定义sql语句字符串v_sql varchar2(1000);--定义两个整数v_begin number:=(pageNow-1)*pagesize+1; --开始记录v_end number:=pageNow*pageSize; --结束记录--排序sqlv_orderSql varchar2(100):=''; v_wherecase varchar2(100):=''; begin --执行部分 --如果orderField不为空，则进行排序，如果orderFlag=0为升序，1为降序 if orderField is not null then if orderFlag=0 then v_orderSql:=' order by '||orderField; elsif orderFlag=1 then v_orderSql:=' order by '||orderField||' desc'; else null; end if; end if; --条件判断语句 if wherecase is not null then v_wherecase:=' where '||wherecase; end if; v_sql:='select * from (select t1.* ,rownum rn from(select '|| fields ||' from '|| tableName|| v_wherecase ||' '||v_orderSql ||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin; --把游标和sql关联 open p_cursor for v_sql; --计算myrows和myPageCount --组织一个sql v_sql:='select count(*) from '|| tableName || v_wherecase || ' ' ||v_orderSql; --执行sql,并把返回的值赋给myrows; execute immediate v_sql into myrows; --计算myPageCount if mod(myrows,Pagesize)=0 then myPageCount:=myrows/Pagesize; else myPageCount:=myrows/pagesize+1; end if; --关闭游标 --close p_cursor;end;/2、.net中的oracle过程调用类
/// <summary>
/// c#调用oracle的过程进行分页 /// author:sirc_ljp /// date:2011.11.16 /// </summary> public class Paging { private string _connectionString; private string _tableName; private string _fields = "*"; private string _whercase=""; private int _pageSize=10; private int _pageNow=1; private string _orderField=""; private int _orderFlag = 0; private int _myRows; private int _myPageCount; private DataTable _result;/// <summary>
/// 数据库连接字符串 /// </summary> public string ConnectionString { get { return _connectionString; } set { _connectionString = value; } }/// <summary>
/// 表名 /// </summary> public string TableName { get { return _tableName; } set { _tableName = value; } } /// <summary> /// 查询结果显示字段 /// </summary> public string Fields { get { return _fields; } set { _fields = value; } } /// <summary> /// 查询条件 /// </summary> public string Whercase { get { return _whercase; } set { _whercase = value; } } /// <summary> /// 页显示记录数 /// </summary> public int PageSize { get { return _pageSize; } set { _pageSize = value; } } /// <summary> /// 当前页 /// </summary> public int PageNow { get { return _pageNow; } set { _pageNow = value; } } /// <summary> /// 排序字段，为空""表示不排序 /// </summary> public string OrderField { get { return _orderField; } set { _orderField = value; } } /// <summary> /// 排序标识 0：正序 1：倒序 /// </summary> public int OrderFlag { get { return _orderFlag; } set { _orderFlag = value; } } /// <summary> /// 总记录数 /// </summary> public int MyRows { get { return _myRows; } } /// <summary> /// 总分页 /// </summary> public int MyPageCount { get { return _myPageCount; } } /// <summary> /// 返回的记录集 /// </summary> public DataTable Result { get { return _result; } }public Paging()
{ fenye(); }public Paging(string connectionString,string tableName, string fields, string wherecase, int pageSize, int pageNow, string orderField, int orderFlag, out int myRows, out int myPageCount, out DataTable result)
{ _connectionString = connectionString; _tableName = tableName; _fields = fields; _whercase = wherecase; _pageSize = pageSize; _pageNow = pageNow; _orderField = orderField; _orderFlag = orderFlag;fenye();
myRows = _myRows; myPageCount = _myPageCount; result = _result; }private void fenye()
{ OracleConnection conn = new OracleConnection(_connectionString); conn.Open();try
{ OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure;//调用存储过程查询数据
cmd.CommandText = "fenye"; OracleParameter[] parameters = new OracleParameter[10]; //注意这里的参数名和类型号与存储过程里面的一样 parameters[0] = new OracleParameter("tableName", OracleType.VarChar); parameters[1] = new OracleParameter("fields", OracleType.VarChar); parameters[2] = new OracleParameter("wherecase", OracleType.VarChar); parameters[3] = new OracleParameter("pageSize", OracleType.Int32); parameters[4] = new OracleParameter("pageNow", OracleType.Int32); parameters[5] = new OracleParameter("orderField", OracleType.VarChar); parameters[6] = new OracleParameter("orderFlag", OracleType.Int32); parameters[7] = new OracleParameter("myrows", OracleType.Int32); parameters[8] = new OracleParameter("myPageCount", OracleType.Int32); parameters[9] = new OracleParameter("p_cursor", OracleType.Cursor);parameters[0].Value = _tableName;
parameters[1].Value = _fields; parameters[2].Value = _whercase; parameters[3].Value = _pageSize; parameters[4].Value = _pageNow; parameters[5].Value = _orderField; parameters[6].Value = _orderFlag; parameters[7].Direction = ParameterDirection.Output; parameters[8].Direction = ParameterDirection.Output; parameters[9].Direction = ParameterDirection.Output;foreach (OracleParameter parameter in parameters)
{ cmd.Parameters.Add(parameter); } //执行命令 OracleDataAdapter oda = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); oda.Fill(ds); //得到查询结果表 _result = ds.Tables[0];//取出总行数
_myRows = Convert.ToInt32(parameters[7].Value); //取出总页数 _myPageCount = Convert.ToInt32(parameters[8].Value.ToString());}
catch (Exception ex) {throw;
} finally { conn.Close(); } }}