IDC资讯中心
《使用 ado.net 访问 oracle 9i 存储过程》见下面的介绍(如果对这个不怎么懂的，还是先看看下面文章)http://www.microsoft.com/china/msdn/library/data/dataaccess/dmsdnorsps.mspx
包定义：
create or replace package materialmanage is
type t_cursor is ref cursor;
procedure per_quickpage
(
tbname in varchar2, –表名
fieldstr in varchar2, –字段集
rowfilter in varchar2, –过滤条件
sortstr in varchar2, –排序集
rownumfieldstr in varchar2, –分页条件
totalcount out number, –总记录数
cur_returncur out t_cursor –返回的游标
);
end materialmanage;
包主体：
create or replace package body materialmanage is
procedure per_quickpage
(
tbname in varchar2, –表 名
fieldstr in varchar2, –字段集
rowfilter in varchar2, –过滤条件
sortstr in varchar2, –排序集
minrownum in number, –分页小值
maxrownum in number, –分页大值
totalcount out number, –总记录数
cur_returncur out t_cursor
)
is
v_sourcetb1 varchar2(3000); –动态表名1
v_sourcetb2 varchar2(3000); –动态表名2
v_sourcetb3 varchar2(3000); –动态表名3
v_sourcetb4 varchar2(3000); –动态表名4
v_totalcount varchar2(50); –总记录数
v_sql varchar2(3000); –动态sql
begin
v_sourcetb1 := (select || fieldstr || from || tbname ||) sourcetb1;
v_sourcetb2 := (select * from || v_sourcetb1 || where || rowfilter || || sortstr ||) sourcetb2;
v_sourcetb3 := (select rownum as rowindex,sourcetb2.* from || v_sourcetb2 || where rownum<=|| maxrownum ||) sourcetb3;
v_sourcetb4 := (select * from || v_sourcetb1 || where || rowfilter ||) sourcetb4;
v_sql := select count(*) as totalcount from || v_sourcetb4;
execute immediate v_sql into v_totalcount;
totalcount := v_totalcount;
v_sql := select * from || v_sourcetb3 || where rowindex >=||minrownum;
open cur_returncur for v_sql;
end per_quickpage;
end materialmanage;
由于oracle有个rownum特性，所以分页的时候就是利用rownum来实现。如果大家还有什么更好的办法记得告诉我一声，多谢了，因为我测试了上面的分页方法效率并不是很高。
存储过程返回了两个参数：totalcount ：当前条件下的总记录数 cur_returncur ：游标类型，就是所要读取的记录的集合
下面是asp.net中调用的代码：
/// <summary>
/// 调用存储过程实现快速分页
/// </summary>
/// <param name=”tbname”>表名称</param>
/// <param name=”fieldstr”>字段名称</param>
/// <param name=”rowfilter”>过滤条件</param>
/// <param name=”sortstr”>排序字段</param>
/// <param name=”minpagenum”>分页小值</param>
/// <param name=”maxpagenum”>分页大值</param>
/// <param name=”totalcount”>总记录(需要返回)</param>
/// <returns>datatable</returns>
public datatable quickpage(string tbname,string fieldstr,string rowfilter,string sortstr,int minrownum,int maxrownum,ref int recordcount)
{
oracleconnection conn = new oracleconnection(configurationsettings.appsettings[“oracleconnstr”].tostring());
oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = “materialmanage.per_quickpage”;
cmd.commandtype = commandtype.storedprocedure;
cmd.parameters.add(“tbname”,oracletype.varchar,50); //表 名
cmd.parameters[“tbname”].direction = parameterdirection.input;
cmd.parameters[“tbname”].value = tbname;
cmd.parameters.add(“fieldstr”,oracletype.varchar,3000); //字段集
cmd.parameters[“fieldstr”].direction = parameterdirection.input;
cmd.parameters[“fieldstr”].value = fieldstr;
cmd.parameters.add(“rowfilter”,oracletype.varchar,3000); //过滤条件
cmd.parameters[“rowfilter”].direction = parameterdirection.input;
cmd.parameters[“rowfilter”].value = rowfilter;
cmd.parameters.add(“sortstr”,oracletype.varchar,3000); //排序字段
cmd.parameters[“sortstr”].direction = parameterdirection.input;
cmd.parameters[“sortstr”].value = sortstr;
cmd.parameters.add(“minrownum”,oracletype.number); //分页小值
cmd.parameters[“minrownum”].direction = parameterdirection.input;
cmd.parameters[“minrownum”].value = minrownum;
cmd.parameters.add(“maxrownum”,oracletype.number); //分页大值
cmd.parameters[“maxrownum”].direction = parameterdirection.input;
cmd.parameters[“maxrownum”].value = maxrownum;
cmd.parameters.add(“totalcount”,oracletype.number); //页总记录数
cmd.parameters[“totalcount”].direction = parameterdirection.output;
cmd.parameters[“totalcount”].value = 0;
cmd.parameters.add(“cur_returncur”,oracletype.cursor); //返回的游标
cmd.parameters[“cur_returncur”].direction = parameterdirection.output;
dataset ds = new dataset();
oracledataadapter adapter= new oracledataadapter(cmd);
adapter.fill(ds);
conn.close();
//总记录数
recordcount = int.parse(cmd.parameters[“totalcount”].value.tostring());
return ds.tables[0];
}
好了，代码都罗列到上面了，至于用，大家应该知道了吧，
