Oracle与C#存储过程分页
--分页
select * from(select t1.ename ,rownum rn from (select * from emp) t1 where rownum<=10) where rn>=6;
--开发一个包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;
--分页存储过程
create or replace procedure sp_fengye
(tableName in varchar2,--参数1 表名字
pagesize in number,
pageNow in number,
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out tespackage.test_cursor --返回记录集
)is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pagesize+1; --从哪里开始取
v_end number:=pageNow*pagesize;
begin
--执行部分
v_sql:='select * from(select t1.ename ,rownum rn from
(select * from '||tableName||' order by sal) 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;
--执行sql，并把返回值赋给myrows；
execute immediate v_sql into myrows;
--计算myPagweCount
if mod(myrows,pagesize)=0 then
myPageCount:=myrows/pagesize;
else
myPageCount:=myrows/pagesize+1;
end if;
--关闭游标
close p_cursor;
end;
直接调用Oracle中的存储过程完成对表的分页,对代码封闭程度高,执行高效.以下为C#分页类代码,包括Oracle数据库端存储过程PLSQL代码.
#endregion
#endregion
using System;
using System.Collections.Generic;
using System.Web;
using System.Data.OracleClient;
using System.Data;
/// <summary>
///调用Oracle存储过程对表进行分页
/// </summary>
public class Pagination
{
string m_procedureName;//要调用的存储过程名称
OracleConnection m_oracleConnection; //Oracle连接对象
/// <summary>
/// 构造函数,传入存储过程名称与连接对象
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="orclConnection">初始化后连接字符串的Oracle连接对象</param>
public Pagination(string procedureName,OracleConnection orclConnection)
{
m_procedureName = procedureName;
m_oracleConnection = orclConnection;
}
/// <summary>
/// 执行分页
/// </summary>
/// <param name="tableName">需分页表名</param>
/// <param name="paeSize">每页记录数</param>
/// <param name="indexNowPage">当前页码</param>
/// <param name="totalRows">引用参数,总记录数</param>
/// <param name="totalPages">引用参数,总页数</param>
/// <returns>分页结果集</returns>
public DataTable Paging(string tableName, int paeSize, int indexNowPage, ref int totalRows, ref int totalPages)
{
try
{
//打开连接
OpenOracleConnection();
//定义OracleCommand对象,设置命令类型为存储过程
OracleCommand pOracleCMD = new OracleCommand(m_procedureName, m_oracleConnection);
pOracleCMD.CommandType = CommandType.StoredProcedure;
//根据存储过程的参数个数及类型生成参数对象
OracleParameter p1 = new OracleParameter("rowCountPerPage", OracleType.Number, 10);
OracleParameter p2 = new OracleParameter("indexNowPage", OracleType.Number, 10);
OracleParameter p3 = new OracleParameter("tabName", OracleType.VarChar, 50);
OracleParameter p4 = new OracleParameter("totalRows", OracleType.Number, 10);
OracleParameter p5 = new OracleParameter("totalPages", OracleType.Int16, 10);
OracleParameter p6 = new OracleParameter("p_cursor", OracleType.Cursor);
//设置参数的输入输出类型,默认为输入
p1.Direction = ParameterDirection.Input;
p2.Direction = ParameterDirection.Input;
p3.Direction = ParameterDirection.Input;
p4.Direction = ParameterDirection.Output;
p5.Direction = ParameterDirection.Output;
p6.Direction = ParameterDirection.Output;
//对输入参数定义初值,输出参数不必赋值.
p1.Value = paeSize;
p2.Value = indexNowPage;
p3.Value = tableName;
//按照存储过程参数顺序把参数依次加入到OracleCommand对象参数集合中
pOracleCMD.Parameters.Add(p1);
pOracleCMD.Parameters.Add(p2);
pOracleCMD.Parameters.Add(p3);
pOracleCMD.Parameters.Add(p4);
pOracleCMD.Parameters.Add(p5);
pOracleCMD.Parameters.Add(p6);
//执行,把分页结果集填入datatable中
OracleDataAdapter pOracleDataAdapter = new OracleDataAdapter(pOracleCMD);
DataTable datatable = new DataTable();
pOracleDataAdapter.Fill(datatable);
//在执行结束后,从存储过程输出参数中取得相应的值放入引用参数中以供程序调用
totalRows = int.Parse(p4.Value.ToString());
totalPages = int.Parse(p5.Value.ToString());
//关闭连接
CloseOracleConnection();
return datatable;
}
catch(Exception ex)
{
return null;
}
}
/// <summary>
/// 关闭连接
/// </summary>
private void CloseOracleConnection()
{
if (m_oracleConnection.State == ConnectionState.Open)
{
m_oracleConnection.Close();
}
}
/// <summary>
/// 打开连接
/// </summary>
private void OpenOracleConnection()
{
if (m_oracleConnection.State== ConnectionState.Closed)
{
m_oracleConnection.Open();
}
}
}
