Oracle分页存储过程以及C#调用

最近在做项目的时候需要对数据进行分页查询，这个在Web端属于很常见的，但是在桌面端，貌似大家都不这么干，不过我在项目中要操作系统的日志，觉得必须要分页才行了，因为系统的日志会随着系统的运行而越来越大了，所以必须分页来处理，以前在MS Sql Server 中用过分页，然后直接把SQL语句贴过来，发现不能用，于是百度谷歌了半天，找了好几个网上的例子都没有成功，最后结合了网上的两个例子，自己做了小部分的修改，终于OK了，这里我把SQL语句贴出来，并且把C#调用Oracle存储过程的代码页贴出来，如果有不好的地方，大家指正，勿喷！

1、分页存储过程：

首先需要新建一个包，至于为什么要这么做，我没有深究，如有童鞋知道的话，只会一声哈

--创建包

Create or Replace package PCK_System is
type T_Cursor is ref Cursor;
end PCK_System;

--创建存储过程

CREATE OR REPLACE PROCEDURE PaggingProcedure
(p_tableName in varchar2, --表名
p_strWhere in varchar2, --查询条件
p_orderColumn in varchar2, --排序的列
p_orderStyle in varchar2, --排序方式
p_curPage in Number, --当前页
p_pageSize in Number, --每页显示记录条数
p_fields in varchar2, --要查询的列
p_totalRecords out Number, --总记录数
p_totalPages out Number, --总页数
v_cur out PCK_System.T_Cursor) --返回的结果集
IS
v_sql VARCHAR2(2000) := ''; --sql语句
v_startRecord Number(4); --开始显示的记录条数
v_endRecord Number(4); --结束显示的记录条数
BEGIN
--记录中总记录条数

v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' where ';
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || p_strWhere;
END IF;
EXECUTE IMMEDIATE v_sql INTO p_totalRecords;

--根据页大小计算总页数
IF MOD(p_totalRecords,p_pageSize) = 0 THEN

p_totalPages:=trunc(p_totalRecords/p_pageSize,0);
ELSE

p_totalPages:=trunc(p_totalRecords/p_pageSize,0)+1;
END IF;

--实现分页查询
v_startRecord := (p_curPage - 1) * p_pageSize + 1;
v_endRecord := p_curPage * p_pageSize;
v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
'(SELECT '|| p_fields ||' FROM ' || p_tableName;
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || ' WHERE ' || p_strWhere;
END IF;
IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
END IF;
v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '
|| v_startRecord;
--DBMS_OUTPUT.put_line(v_sql);
OPEN v_cur FOR v_sql;

END PaggingProcedure;

2、用C#调用存储过程

/// <summary>
/// 分页数据
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="RetureFields">查询返回字段(如果返回所有的则传入*)</param>
/// <param name="strWhere">查询条件</param>
/// <param name="PageSize">每页显示的记录数</param>
/// <param name="CurPage">当前页数</param>
/// <param name="RowCount">所有记录数</param>
/// <param name="totalPage">总页数</param>
/// <param name="sortField">排序字段</param>
/// <param name="sortType">排序类型:1表示降序，0表示升序</param>
/// <returns></returns>
public static DataTable GetPageList(string TableName, string RetureFields, string strWhere, int PageSize, int CurPage, out int RowCount,out int totalPage, string sortField,int sortType)
{

OracleParameter[] parameters = {
new OracleParameter("p_tableName", OracleType.NVarChar),
new OracleParameter("p_strWhere", OracleType.NVarChar),
new OracleParameter("p_orderColumn", OracleType.NVarChar),
new OracleParameter("p_orderStyle", OracleType.NVarChar),
new OracleParameter("p_curPage", OracleType.Number),
new OracleParameter("p_pageSize", OracleType.Number),
new OracleParameter("p_fields", OracleType.NVarChar),
new OracleParameter("p_totalRecords", OracleType.Number),
new OracleParameter("p_totalPages", OracleType.Number),
new OracleParameter("v_cur",OracleType.Cursor)};
parameters[0].Direction = ParameterDirection.Input;
parameters[1].Direction = ParameterDirection.Input;
parameters[2].Direction = ParameterDirection.Input;
parameters[3].Direction = ParameterDirection.Input;
parameters[4].Direction = ParameterDirection.Input;
parameters[5].Direction = ParameterDirection.Input;
parameters[6].Direction = ParameterDirection.Input;
parameters[7].Direction = ParameterDirection.Output;
parameters[8].Direction = ParameterDirection.Output;
parameters[9].Direction = ParameterDirection.Output;

parameters[0].Value = TableName;
parameters[1].Value = strWhere;
parameters[2].Value = sortField;
if (sortType == 0)
{
parameters[3].Value = "asc";
}
else
{
parameters[3].Value = "desc";
}
parameters[4].Value = CurPage;
parameters[5].Value = PageSize;
parameters[6].Value = RetureFields;

OracleCommand cmd = new OracleCommand("PaggingProcedure");//存储过程名称
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parameters);
DataTable dt = RunProcedureCmd(cmd);
RowCount = Convert.ToInt32(cmd.Parameters[7].Value.ToString());//返回总记录数
totalPage = Convert.ToInt32(cmd.Parameters[8].Value.ToString());
return dt;
}

/// <summary>
/// 执行存储过程 返回DataTable
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
public static DataTable RunProcedureCmd(OracleCommand cmd)
{
DataTable result = new DataTable();
OracleConnection conn = new OracleConnection(connectionString);//你自己的链接字符串
try
{
if ((conn.State == ConnectionState.Closed))
{
conn.Open();
}
cmd.Connection = conn;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(result);
da.Dispose();
conn.Close();
conn.Dispose();

return result;
}
catch (Exception ex)
{
conn.Close();
conn.Dispose();
throw ex;
}
}

上述代码经过本人多次测试，都是可以运行的，大家可以放心大胆的用，如有好的建议或者意见，记得留言哦