1、oracle数据库分页存储过程
CREATE
OR
REPLACE
PACKAGE DotNet
is
TYPE type_cur IS REF CURSOR ; -- 定义游标变量用于返回记录集
PROCEDURE DotNetPagination
(
Pindex in number , -- 分页索引
Psql in varchar2 , -- 产生dataset的sql语句
Psize in number , -- 页面大小
Pcount out number , -- 返回分页总数
v_cur out type_cur -- 返回当前页数据记录
);
procedure DotNetPageRecordsCount
(
Psqlcount in varchar2 , -- 产生dataset的sql语句
Prcount out number -- 返回记录总数
);
end DotNet;
CREATE OR REPLACE PACKAGE BODY DotNet" is
-- ***************************************************************************************
PROCEDURE DotNetPagination
(
Pindex in number ,
Psql in varchar2 ,
Psize in number ,
Pcount out number ,
v_cur out type_cur
)
AS
v_sql VARCHAR2 ( 1000 );
v_count number ;
v_Plow number ;
v_Phei number ;
Begin
-- ----------------------------------------------------------取分页总数
v_sql : = ' select count(*) from ( ' || Psql || ' ) ' ;
execute immediate v_sql into v_count;
Pcount : = ceil(v_count / Psize);
-- ----------------------------------------------------------显示任意页内容
v_Phei : = Pindex * Psize + Psize;
v_Plow : = v_Phei - Psize + 1 ;
-- Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
v_sql : = ' select * from ( ' || Psql || ' ) where rn between ' || v_Plow || ' and ' || v_Phei ;
open v_cur for v_sql;
End DotNetPagination;
-- **************************************************************************************
procedure DotNetPageRecordsCount
(
Psqlcount in varchar2 ,
Prcount out number
)
as
v_sql varchar2 ( 1000 );
v_prcount number ;
begin
v_sql : = ' select count(*) from ( ' || Psqlcount || ' ) ' ;
execute immediate v_sql into v_prcount;
Prcount : = v_prcount; -- 返回记录总数
end DotNetPageRecordsCount;
-- **************************************************************************************
end DotNet;
TYPE type_cur IS REF CURSOR ; -- 定义游标变量用于返回记录集
PROCEDURE DotNetPagination
(
Pindex in number , -- 分页索引
Psql in varchar2 , -- 产生dataset的sql语句
Psize in number , -- 页面大小
Pcount out number , -- 返回分页总数
v_cur out type_cur -- 返回当前页数据记录
);
procedure DotNetPageRecordsCount
(
Psqlcount in varchar2 , -- 产生dataset的sql语句
Prcount out number -- 返回记录总数
);
end DotNet;
CREATE OR REPLACE PACKAGE BODY DotNet" is
-- ***************************************************************************************
PROCEDURE DotNetPagination
(
Pindex in number ,
Psql in varchar2 ,
Psize in number ,
Pcount out number ,
v_cur out type_cur
)
AS
v_sql VARCHAR2 ( 1000 );
v_count number ;
v_Plow number ;
v_Phei number ;
Begin
-- ----------------------------------------------------------取分页总数
v_sql : = ' select count(*) from ( ' || Psql || ' ) ' ;
execute immediate v_sql into v_count;
Pcount : = ceil(v_count / Psize);
-- ----------------------------------------------------------显示任意页内容
v_Phei : = Pindex * Psize + Psize;
v_Plow : = v_Phei - Psize + 1 ;
-- Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
v_sql : = ' select * from ( ' || Psql || ' ) where rn between ' || v_Plow || ' and ' || v_Phei ;
open v_cur for v_sql;
End DotNetPagination;
-- **************************************************************************************
procedure DotNetPageRecordsCount
(
Psqlcount in varchar2 ,
Prcount out number
)
as
v_sql varchar2 ( 1000 );
v_prcount number ;
begin
v_sql : = ' select count(*) from ( ' || Psqlcount || ' ) ' ;
execute immediate v_sql into v_prcount;
Prcount : = v_prcount; -- 返回记录总数
end DotNetPageRecordsCount;
-- **************************************************************************************
end DotNet;
2、使用示例
///
<summary>
/// 填充dataSet数据集-Oracle库
/// </summary>
/// <param name="pindex"> 当前页 </param>
/// <param name="psql"> 执行查询的SQL语句 </param>
/// <param name="psize"> 每页显示的记录数 </param>
/// <returns></returns>
private bool gridbind( int pindex, string psql, int psize)
{
OracleConnection conn = new OracleConnection();
OracleCommand cmd = new OracleCommand();
OracleDataAdapter dr = new OracleDataAdapter();
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandText = " DotNet.DotNetPageRecordsCount " ;
cmd.Parameters.Add( " psqlcount " , OracleType.VarChar).Value = psql;
cmd.Parameters.Add( " prcount " , OracleType.Number).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string PCount = cmd.Parameters[ " prcount " ].Value.ToString();
cmd.Parameters.Clear();
cmd.CommandText = " DotNet.DotNetPagination " ;
if (pindex != 0 )
{
cmd.Parameters.Add( " pindex " , OracleType.Number).Value = pindex - 1 ;
}
else
{
cmd.Parameters.Add( " pindex " , OracleType.Number).Value = pindex;
}
cmd.Parameters.Add( " psql " , OracleType.VarChar).Value = psql;
cmd.Parameters.Add( " psize " , OracleType.Number).Value = psize;
cmd.Parameters.Add( " v_cur " , OracleType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add( " pcount " , OracleType.Number).Direction = ParameterDirection.Output;
dr.SelectCommand = cmd;
try
{
ds = new DataSet();
dr.Fill(ds);
// 显示页码条的状态
showStatus(Convert.ToInt32(cmd.Parameters[ " pindex " ].Value) + 1 ,
Convert.ToInt32(cmd.Parameters[ " pcount " ].Value),
Convert.ToInt32(PCount));
for ( int i = 0 ; i < ds.Tables.Count; i ++ )
{ // 把数据行为零的表删除
if (ds.Tables[i].Rows.Count == 0 )
ds.Tables.Remove(ds.Tables[i].TableName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false ;
}
conn.Close();
return true ;
}
/// 填充dataSet数据集-Oracle库
/// </summary>
/// <param name="pindex"> 当前页 </param>
/// <param name="psql"> 执行查询的SQL语句 </param>
/// <param name="psize"> 每页显示的记录数 </param>
/// <returns></returns>
private bool gridbind( int pindex, string psql, int psize)
{
OracleConnection conn = new OracleConnection();
OracleCommand cmd = new OracleCommand();
OracleDataAdapter dr = new OracleDataAdapter();
conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[ " ConnectionString " ].ConnectionString;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandText = " DotNet.DotNetPageRecordsCount " ;
cmd.Parameters.Add( " psqlcount " , OracleType.VarChar).Value = psql;
cmd.Parameters.Add( " prcount " , OracleType.Number).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string PCount = cmd.Parameters[ " prcount " ].Value.ToString();
cmd.Parameters.Clear();
cmd.CommandText = " DotNet.DotNetPagination " ;
if (pindex != 0 )
{
cmd.Parameters.Add( " pindex " , OracleType.Number).Value = pindex - 1 ;
}
else
{
cmd.Parameters.Add( " pindex " , OracleType.Number).Value = pindex;
}
cmd.Parameters.Add( " psql " , OracleType.VarChar).Value = psql;
cmd.Parameters.Add( " psize " , OracleType.Number).Value = psize;
cmd.Parameters.Add( " v_cur " , OracleType.Cursor).Direction = ParameterDirection.Output;
cmd.Parameters.Add( " pcount " , OracleType.Number).Direction = ParameterDirection.Output;
dr.SelectCommand = cmd;
try
{
ds = new DataSet();
dr.Fill(ds);
// 显示页码条的状态
showStatus(Convert.ToInt32(cmd.Parameters[ " pindex " ].Value) + 1 ,
Convert.ToInt32(cmd.Parameters[ " pcount " ].Value),
Convert.ToInt32(PCount));
for ( int i = 0 ; i < ds.Tables.Count; i ++ )
{ // 把数据行为零的表删除
if (ds.Tables[i].Rows.Count == 0 )
ds.Tables.Remove(ds.Tables[i].TableName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false ;
}
conn.Close();
return true ;
}