我在做一个查询页面,但是一次查出的数据量比较大,需要分页来显示。但是在asp.net页面中调用已经编译测试通过的一个oracle分页存储过程就是不成功!郁闷啊……
分页存储过程如下,已经在PL/SQL中测试通过。
包头:create or replace package DB_Oper is
type ref_DataSet IS REF CURSOR;
function GetCount(mTableName varchar2, mTerm varchar2) return number;
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
mDateSet out ref_DataSet --记录
);
end DB_Oper;包体:create or replace package body DB_Oper is ----- 获取记录数 ------
function GetCount(mTableName varchar2, mTerm varchar2) return number is
i number;
vSql varchar2(1000);
begin
i := 0;
vSql := 'select count(*) from ' || mTableName ; if length(mTerm) > 0 then
vSql := vSql || ' where ' || mTerm;
end if; execute IMMEDIATE vSql into i; return(i);
end; ----- 数据分页 ----------
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
mDateSet out ref_DataSet --记录集
) is
begin
declare
Invalid_Input Exception;
vSql varchar2(1000);
Start_page number;
End_page number;
n_PageIndex number;
n_PageSize number;
--mTotalRecords number;
begin
mTotalRecords := GetCount(trim(mTableName), trim(mTerm));
vSql := 'select * from ' || mTableName;
if length(trim(mTerm)) > 0 then
vSql := vSql || ' where ' || trim(mTerm) ;
end if;
if length(trim(mOrderField)) > 0 then
begin
vSql := vSql || ' order by ' || trim(mOrderField);
if morderStyle > 0 then
vSql := vSql || ' desc ';
end if;
end;
end if; --
n_PageSize := mPageSize;
if mPageSize <= 0 then
n_PageSize := 10;
end if;
n_PageIndex := mPageIndex;
if mPageIndex <= 0 then
n_PageIndex := 1;
end if;
if mTotalRecords > 0 and n_PageIndex > 1 then
begin
--如果n_pageindex大于实际的页数,则取实际页数
if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then
n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5);
end if;
end;
end if;
Start_page := (n_PageIndex - 1) * n_PageSize + 1;
End_page := n_PageIndex * n_PageSize;
vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum <= ' || End_page || ' ) B WHERE r >= '|| Start_page; Open mDateSet for vSql; exception
When Invalid_Input Then
open mDateSet for select null from dual;
end;
end return_DataSet;
end DB_Oper;
调用代码:using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int num = 0;
DataSet ds = new DataSet();
ds = QuickPage("test1", "logday between '20090401' and '20090415'", 10, 1, "logday", 1,ref num);
//返回记录总数
lblnum.Text = num.ToString();
//返回结果集绑定GridView
gv.DataSource = ds.Tables["t"];
gv.DataBind(); } public DataSet QuickPage(string TableName, string Condition, int PageSize, int PageIndex, string OrderByColumn, int OrderByAscDesc, ref int RecodsCount)
{
OracleConnection con = new OracleConnection("data source=ora123;uid=test;pwd=test");
OracleCommand com = new OracleCommand(); con.Open();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "db_oper.return_dataset"; //表名称
com.Parameters.Add("TableName", OracleType.VarChar, 50);
com.Parameters["TableName"].Direction = ParameterDirection.Input;
com.Parameters["TableName"].Value = TableName; //Where子句下条件
com.Parameters.Add("Condition", OracleType.VarChar, 3000);
com.Parameters["Condition"].Direction = ParameterDirection.Input;
com.Parameters["Condition"].Value = Condition; //每页显示记录数
com.Parameters.Add("PageSize", OracleType.Number);
com.Parameters["PageSize"].Direction = ParameterDirection.Input;
com.Parameters["PageSize"].Value = PageSize; //当前页索引值
com.Parameters.Add("PageIndex", OracleType.Number);
com.Parameters["PageIndex"].Direction = ParameterDirection.Input;
com.Parameters["PageIndex"].Value = PageIndex; //排序字段
com.Parameters.Add("OrderByColumn", OracleType.VarChar, 3000);
com.Parameters["OrderByColumn"].Direction = ParameterDirection.Input;
com.Parameters["OrderByColumn"].Value = OrderByColumn; //排序方式
com.Parameters.Add("OrderByAscDesc", OracleType.Number);
com.Parameters["OrderByAscDesc"].Direction = ParameterDirection.Input;
com.Parameters["OrderByAscDesc"].Value = OrderByAscDesc; //返回总记录数
com.Parameters.Add("TotalRecords", OracleType.UInt16);
com.Parameters["TotalRecords"].Direction = ParameterDirection.InputOutput;
com.Parameters["TotalRecords"].Value = 0; //返回记录集
com.Parameters.Add("rDataSet", OracleType.Cursor);
com.Parameters["rDataSet"].Direction = ParameterDirection.ReturnValue; DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(com);
da.Fill(ds, "t"); con.Close();
RecodsCount = int.Parse(com.Parameters["TotalRecords"].Value.ToString()); return ds;
}
}
调用出现如下错误:
ORA-06550: 第 1 行, 第 20 列:
PLS-00306: 调用 'RETURN_DATASET' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored调了两天了没调好,伤心啊,请大伙帮忙看下?
分页存储过程如下,已经在PL/SQL中测试通过。
包头:create or replace package DB_Oper is
type ref_DataSet IS REF CURSOR;
function GetCount(mTableName varchar2, mTerm varchar2) return number;
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
mDateSet out ref_DataSet --记录
);
end DB_Oper;包体:create or replace package body DB_Oper is ----- 获取记录数 ------
function GetCount(mTableName varchar2, mTerm varchar2) return number is
i number;
vSql varchar2(1000);
begin
i := 0;
vSql := 'select count(*) from ' || mTableName ; if length(mTerm) > 0 then
vSql := vSql || ' where ' || mTerm;
end if; execute IMMEDIATE vSql into i; return(i);
end; ----- 数据分页 ----------
procedure return_DataSet(
mTableName in varchar2, --表名
mTerm in varchar2, --条件
mPageSize in number, --每页显示记录数
mPageIndex in number, --当前页
mOrderField in varchar2, --排序字段
mOrderStyle in number, --排序方式 0 升序 1 降序
mTotalRecords out number, --总记录数
mDateSet out ref_DataSet --记录集
) is
begin
declare
Invalid_Input Exception;
vSql varchar2(1000);
Start_page number;
End_page number;
n_PageIndex number;
n_PageSize number;
--mTotalRecords number;
begin
mTotalRecords := GetCount(trim(mTableName), trim(mTerm));
vSql := 'select * from ' || mTableName;
if length(trim(mTerm)) > 0 then
vSql := vSql || ' where ' || trim(mTerm) ;
end if;
if length(trim(mOrderField)) > 0 then
begin
vSql := vSql || ' order by ' || trim(mOrderField);
if morderStyle > 0 then
vSql := vSql || ' desc ';
end if;
end;
end if; --
n_PageSize := mPageSize;
if mPageSize <= 0 then
n_PageSize := 10;
end if;
n_PageIndex := mPageIndex;
if mPageIndex <= 0 then
n_PageIndex := 1;
end if;
if mTotalRecords > 0 and n_PageIndex > 1 then
begin
--如果n_pageindex大于实际的页数,则取实际页数
if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then
n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5);
end if;
end;
end if;
Start_page := (n_PageIndex - 1) * n_PageSize + 1;
End_page := n_PageIndex * n_PageSize;
vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum <= ' || End_page || ' ) B WHERE r >= '|| Start_page; Open mDateSet for vSql; exception
When Invalid_Input Then
open mDateSet for select null from dual;
end;
end return_DataSet;
end DB_Oper;
调用代码:using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int num = 0;
DataSet ds = new DataSet();
ds = QuickPage("test1", "logday between '20090401' and '20090415'", 10, 1, "logday", 1,ref num);
//返回记录总数
lblnum.Text = num.ToString();
//返回结果集绑定GridView
gv.DataSource = ds.Tables["t"];
gv.DataBind(); } public DataSet QuickPage(string TableName, string Condition, int PageSize, int PageIndex, string OrderByColumn, int OrderByAscDesc, ref int RecodsCount)
{
OracleConnection con = new OracleConnection("data source=ora123;uid=test;pwd=test");
OracleCommand com = new OracleCommand(); con.Open();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "db_oper.return_dataset"; //表名称
com.Parameters.Add("TableName", OracleType.VarChar, 50);
com.Parameters["TableName"].Direction = ParameterDirection.Input;
com.Parameters["TableName"].Value = TableName; //Where子句下条件
com.Parameters.Add("Condition", OracleType.VarChar, 3000);
com.Parameters["Condition"].Direction = ParameterDirection.Input;
com.Parameters["Condition"].Value = Condition; //每页显示记录数
com.Parameters.Add("PageSize", OracleType.Number);
com.Parameters["PageSize"].Direction = ParameterDirection.Input;
com.Parameters["PageSize"].Value = PageSize; //当前页索引值
com.Parameters.Add("PageIndex", OracleType.Number);
com.Parameters["PageIndex"].Direction = ParameterDirection.Input;
com.Parameters["PageIndex"].Value = PageIndex; //排序字段
com.Parameters.Add("OrderByColumn", OracleType.VarChar, 3000);
com.Parameters["OrderByColumn"].Direction = ParameterDirection.Input;
com.Parameters["OrderByColumn"].Value = OrderByColumn; //排序方式
com.Parameters.Add("OrderByAscDesc", OracleType.Number);
com.Parameters["OrderByAscDesc"].Direction = ParameterDirection.Input;
com.Parameters["OrderByAscDesc"].Value = OrderByAscDesc; //返回总记录数
com.Parameters.Add("TotalRecords", OracleType.UInt16);
com.Parameters["TotalRecords"].Direction = ParameterDirection.InputOutput;
com.Parameters["TotalRecords"].Value = 0; //返回记录集
com.Parameters.Add("rDataSet", OracleType.Cursor);
com.Parameters["rDataSet"].Direction = ParameterDirection.ReturnValue; DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(com);
da.Fill(ds, "t"); con.Close();
RecodsCount = int.Parse(com.Parameters["TotalRecords"].Value.ToString()); return ds;
}
}
调用出现如下错误:
ORA-06550: 第 1 行, 第 20 列:
PLS-00306: 调用 'RETURN_DATASET' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored调了两天了没调好,伤心啊,请大伙帮忙看下?
com.CommandText = "db_oper.return_DataSet";
参数是否缺指针:
db.AddOutParameter(command, "v_cur", DbType.Object, 0);
command.Parameters["v_cur"].OracleType = OracleType.Cursor;public DataTable GetPopulationsBy_c_Type(QueryInfo query) {
db = DatabaseFactory.CreateDatabase("OracleConnectionString");
builder = new StringBuilder();
builder.Append("SELECT b.RYBH AS \"人员编号\"");
builder.Append(",b.GMSFHM AS \"身份证\"");
builder.Append(",b.XM AS \"姓名\"");
builder.Append(",3 AS \"人员类别\"");
builder.Append(" FROM ZHXT_DBA.RY_JZXX a,ZHXT_DBA.RY_JBXX b");
builder.Append(" WHERE a.RYBH=b.RYBH");
if (query.Population.IdCard != "") {
builder.Append(" AND b.GMSFHM='" + query.Population.IdCard + "'");
}
if (query.Population.Name != "") {
builder.Append(" AND b.XM='" + query.Population.Name + "'");
}
if (query.Population.Sex != "3") {
builder.Append(" AND b.XB='" + query.Population.Sex + "'");
}
if (query.Population.Birthdate != "") {
builder.Append(" AND b.CSRQ='" + query.Population.Birthdate + "'");
}
string P_SQL = builder.ToString();
OracleCommand command = (OracleCommand)db.GetStoredProcCommand("zhxt_dba.DotNet.DotNetPagination");
db.AddInParameter(command, "P_SQL", DbType.String, P_SQL);
db.AddInParameter(command, "PageIndex", DbType.Int32, query.Pager.PageIndex);
db.AddInParameter(command, "PageSize", DbType.Int32, query.Pager.PageSize);
db.AddOutParameter(command, "PageCount", DbType.Int32, 4);
db.AddOutParameter(command, "RecordCount", DbType.Int32, 4);
db.AddOutParameter(command, "v_cur", DbType.Object, 0);
command.Parameters["v_cur"].OracleType = OracleType.Cursor;
DataTable dataTable = db.ExecuteDataSet(command).Tables[0];
PagerInfo pager = new PagerInfo(
query.Pager.PageIndex
, query.Pager.PageSize
, int.Parse(command.Parameters["PageCount"].Value.ToString())
, int.Parse(command.Parameters["RecordCount"].Value.ToString())
);
query.Pager = pager;
return dataTable;
}
//返回记录集
com.Parameters.Add("rDataSet", OracleType.Cursor);
com.Parameters["rDataSet"].Direction = ParameterDirection.ReturnValue;
我的这一段应该就是你说的意思吧
com.Parameters["mDateSet"].Direction = ParameterDirection.Output;
gaijf(变神马),太牛了,佩服!!!果然是参数名称的问题,我把所有参数都该成和存储过程中一样的,测试就OK了。顺便再请教一下,我看了一些资料说这种分页其实效率不是很好。当数量达到几十万或者百万以上,分页后页码越靠后效率越低。因为我现在的表记录大概在百万条记录左右,要经常进行筛选符合条件的记录进行显示,不知道有没有好的分页方法?或者有没有其他更好的解决方法?
hibernate分页(百万级数据查询)效率怎么样啊?有没有就基于数据库本身编程,再C#调用实现起来的啊?