最近公司上马了一个新的项目，用到的数据库是Oracle10g的
今天用到C#里调用Oralce包里的SP返回分页的记录集，在网上查了一下，都用的是MS提供的Oracle.Cient，而在Oracle.DataAccess.Client 里调用时（Ms的Oracle.Client 有问题，所以用的是Oracle官方提供的Oracle.DataAccess.Client ），输出的参数也要赋值后调用才能返回，否则返回的记录集是空的。即DbNull.Value要先赋值到参数上。本人用Oracle还不到三个星期，因此肯定有不对的地方，还望各位大虾们多多指教。
今天发现Oracle的Package确实不错，是我用的这几天里的我目前感觉到的一大亮点（可以重载，可以把相关的操作放到一个包里）。不知MSSQL2008里有没有。
闲话少说，上代码：
C#里调用的代码如下：
C#
[Test]
public void Page1Test()
{
using(Database db = DBFactory.Create())
{
const string sql = " select * from test_temp where ID < 1000 order by id asc ";
OracleCommand cmd = new OracleCommand("PAGESPLIT_PKG.PAGESPLIT_SP",db.Connection);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("pagesize", OracleDbType.Int32);
p1.Direction = ParameterDirection.Input;
p1.Value = 20;
OracleParameter p2 = new OracleParameter("pageindex", OracleDbType.Int32);
p2.Direction = ParameterDirection.Input;
p2.Value = 2;
OracleParameter p3 = new OracleParameter("sqltext", OracleDbType.Varchar2);
p3.Direction = ParameterDirection.Input;
p3.Value = sql;
OracleParameter p4 = new OracleParameter("Records_out", OracleDbType.Int32,DBNull. Value,ParameterDirection.Output); //就算是空值也要写，否则是会出错的
OracleParameter p5 = new OracleParameter("table_out", OracleDbType.RefCursor,DBNull.Value, ParameterDirection.Output);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p5);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Console.WriteLine(ds.Tables.Count);
Console.WriteLine(ds.Tables[0].TableName);
Console.WriteLine(ds.Tables[0].Rows.Count);
Console.WriteLine("结果:" + cmd.Parameters["Records_out"].Value.ToString());
foreach(DataRow row in ds.Tables[0].Rows)
{
Console.WriteLine(row[1]);
}
}
}Oracle里分页包代码如下：
sql
create or replace package PageSplit_PKG as
-----------------------------------------------------------------------
-- 分页模块PageSplitPackage4Oracle V0.01
-- Author : zhouyu Eamil:[email protected]
-- 2009-11-4
-----------------------------------------------------------------------/
type DataTable is REF CURSOR; --游标类型定义，用于返回数据集
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Table_out out DataTable); --申明包里的SP1
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Pages_out out int,Table_out out DataTable); --申明包里的SP2
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,SqlTextCount string,Records_out out int,Table_out out DataTable); --申明包里的SP3
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Table_out out DataTable); --申明包里的SP3
end;
create or replace package body PageSplit_PKG as
-- 以下方法来自于网络修改
--------------------------------------------------------------------------------------------
--功能描述: 大数据量分页通用存储过程,超过100W数据的表，尽量加上索引上的条件
--创建时间: 2009-11-4
--------------------------------------------------------------------------------------------/
procedure PageSplit_SP
(
PageSize int, --每页记录数
PageIndex int, --当前页码,从 1 开始
SqlText string, --查询语句,含排序部分
Records_out out int,--返回总记录数
Table_out out DataTable ----返回当前页数据记录
) as --对包中定义的SP1的实现
v_sql varchar2(8000); -- 不要超过32767个字符,不可以使用nvarchar2型,下面用的是to_char()
v_count int;
v_maxRownum int;
v_minRownum int;
begin
--取记录总数
v_sql := 'select count(*) from (' || SqlText || ')'; --拼接统计Sql
execute immediate v_sql into v_count; --执行统计
Records_out := v_count;
--行位置判断
v_maxRownum := PageIndex * PageSize;
v_minRownum := v_maxRownum - PageSize + 1;
--拼接查询语句
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| SqlText ||') A
WHERE rownum <= '|| to_char(v_maxRownum) || '
) B
WHERE rn >= ' || to_char(v_minRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
open Table_out for v_sql;
end;
/*****************************************************************
*功能描述: 大数据量分页通用存储过程(重载1,直接返回分页数量)
*创建时间: 2009-11-3
*****************************************************************/
procedure PageSplit_SP
(
PageSize int, --每页记录数
PageIndex int, --当前页码,从 1 开始
SqlText string, --查询语句,含排序部分
Records_out out int,--返回总记录数
Pages_out out int, --返回分出的页数
Table_out out DataTable ----返回当前页数据记录
)
as
v_sql varchar2(8000);
v_count int;
v_maxRownum int;
v_minRownum int;
begin
----取记录总数
v_sql := 'select count(*) from (' || SqlText || ')'; --生成统计字符串
execute immediate v_sql into v_count; --执行统计
Records_out := v_count;
--行位置判断
v_maxRownum := PageIndex * PageSize;
v_minRownum := v_maxRownum - PageSize + 1;
--拼接查询语句
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| SqlText ||') A
WHERE rownum <= '|| to_char(v_maxRownum) || '
) B
WHERE rn >= ' || to_char(v_minRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN Table_out FOR v_sql;
end;
/*****************************************************************
*功能描述: 大数据量分页通用存储过程(重载2，自定义统计查询，推荐使用)
*创建时间: 2009-11-3
*****************************************************************/
procedure PageSplit_SP
(
PageSize int, --每页记录数
PageIndex int, --当前页码,从 1 开始
SqlText string, --查询语句,含排序部分
SqlTextCount string, --获取记录总数的查询语句
Records_out out int, --返回总记录数
Table_out out DataTable)
as
v_sql varchar2(8000);
v_count int;
v_maxRownum int;
v_minRownum int;
begin
----取记录总数
execute immediate SqlTextCount into v_count;
Records_out := v_count;
----执行分页查询
v_maxRownum := PageIndex * PageSize;
v_minRownum := v_maxRownum - PageSize + 1;
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| SqlText ||') A
WHERE rownum <= '|| to_char(v_maxRownum) || '
) B
WHERE rn >= ' || to_char(v_minRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN Table_out FOR v_sql;
end ;
/*****************************************************************
*功能描述: 大数据量分页通用存储过程(重载3，不输出总记录数，适用于外部分页计算，内部直选记录集，DB计算压力最小)
*创建时间: 2009-11-3
*****************************************************************/
procedure PageSplit_SP
(
PageSize int, --每页记录数
PageIndex int, --当前页码,从 1 开始
SqlText string, --查询语句,含排序部分
Table_out out DataTable
)
as
v_sql varchar2(8000);
v_maxRownum int;
v_minRownum int;
begin
----执行分页查询
v_maxRownum := PageIndex * PageSize;
v_minRownum := v_maxRownum - PageSize + 1;
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| SqlText ||') A
WHERE rownum <= '|| to_char(v_maxRownum) || '
) B
WHERE rn >= ' || to_char(v_minRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN Table_out FOR v_sql;
end;
end;这儿有个怪问题，就是在Toad里编译能通过，PLSQL Developer里则有问题。不知啥原因！
转换自: https://www.cnblogs.com/atwind/archive/2009/11/04/1596202.html