希望大家能提供一个.net+oralce的分页存储过程

[yutu520]

【急】希望能提供一个.net + oracle的分页存储过程

之前都是用.net + mssql的，目前项目需要用到oracle的。
希望最好能结合AspNetPager1的分页控件

[云水千寻]

帮顶

[alan0128]

http://blog.csdn.net/jinru2560/archive/2007/11/13/1882123.aspx

[「已注销」]

http://www.cnblogs.com/Files/liubr/WebPager.rar

[zzxap]

[code=SQL]
存储过程：

create or replace package body PageTest
is
procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_SqlSelect in varchar2, --查询语句,含排序部分
p_OutRecordCount out number,--返回总记录数
p_OutCursor out varchar2

)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
v_sql := ' select count(rownum) as rn from (' || p_SqlSelect || ') ';
execute immediate v_sql into v_count;


IF MOD(v_count, p_PageSize) = 0 THEN

p_OutRecordCount := v_count/p_PageSize;
ELSE
p_OutRecordCount := floor(v_count/p_PageSize) + 1;
END IF;

----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;

v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
--OPEN p_OutCursor FOR v_sql;
p_OutCursor := v_sql;

end Per_QuickPage;
end PageTest;


java调用方法：

/**
* 分页查询SQL方法
* @param sql 需要执行的SQL语句
* @param currentPage 需要查询的页数
* @return ResultSet 返回对应的记录集
*/
public static ResultSet getListBySQL(String sql, int currentPage) {
ResultSet rs = null;
try {
JDBCManager jdbc;
jdbc = JDBCManager.getInstance();
conn = jdbc.getConn();
CallableStatement upperProc = conn
.prepareCall("{ call pagetest.Per_QuickPage( ? ,?,?,?,?) }");
upperProc.setInt(1, 15);
upperProc.setInt(2, currentPage);
upperProc.setString(3, sql);
upperProc.registerOutParameter(4, Types.INTEGER);
upperProc.registerOutParameter(5, Types.VARCHAR);
upperProc.execute();
String upperCased = upperProc.getString(5);
maxPage=upperProc.getInt(4);
PreparedStatement pstm = conn.prepareStatement(upperCased);

rs = pstm.executeQuery();
upperProc.close();
} catch (Exception ex) {
ex.printStackTrace();
}
return rs;
}
/**
* 获取该表以getListBySQL(String sql, int currentPage)查询方法所得总页数
* @return int 总页数
*/
public static int getMaxPage() {
return maxPage;
}
/**
* 从页面到数据库格式化日期
* @return Date 日期
*/
public static java.sql.Date getDate(String d){
SimpleDateFormat t_format1 = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date d1=null;
try {
d1 = t_format1.parse(d);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
java.sql.Date dd= new java.sql.Date(d1.getTime());

return dd;
}
[/CODE]

[yutu520]

以下是我们从网络上找的，但是显示会有问题：
代码如下：
private void InitData()
{
int row = 0;
DataTable mytable = QuickPage("t_zd_mz", "*", "", "udfcode", AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1), AspNetPager1.PageSize, ref row);
AspNetPager1.RecordCount = row;

GridView1.DataSource = mytable;
GridView1.DataBind();
AspNetPager1.CustomInfoHTML = "<font>" + AspNetPager1.RecordCount.ToString() + "</font><font>" + AspNetPager1.CurrentPageIndex.ToString() + "";
AspNetPager1.CustomInfoHTML += " /" + AspNetPager1.PageCount.ToString() + "</font>";
this.lblAll.Text = AspNetPager1.RecordCount.ToString();
}


/// <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["ConnectionString3"].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];
}


存储过程：

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, --排序集
MinRowNum in number, --分页小值
MaxRowNum in number, --分页大值
TotalCount out number, --总记录数
Cur_ReturnCur out T_CURSOR
);
end MaterialManage;

包主体：
create or replace package body MaterialManage AS
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;

[红街咖啡]

里面有.

[yutu520]

显示的页面错误为：

“/Web”应用程序中的服务器错误。
--------------------------------------------------------------------------------

ORA-00936: missing expression
ORA-06512: at "FRMIS.MATERIALMANAGE", line 26
ORA-06512: at line 1

[yutu520]

[Quote=引用 6 楼 zengfanxing 的回复:]
里面有.
[/Quote]

如果是多表关联的sql语句 传进去 会有问题

[红街咖啡]

。。。。

[红街咖啡]

ASPNETPAGE自带有支持分组 再分组 很强大》》

[misssdf]

学习下，

[YnSky]

Up

[红街咖啡]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[proCicPagination_new]
(
  @TableName nvarchar(200)
 ,@OrderBy nvarchar(200) = ' id '
 ,@FieldList nvarchar(200) = '*'
 ,@FieldList_ret nvarchar(200)
 ,@Filter nvarchar(500)
 ,@StartIndex int = 1
 ,@PageSize int = 1
 )
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @ExecSql nvarchar(max)         -- 要执行的ql
  DECLARE @where varchar(max)
  DECLARE @EndIndex int
  DECLARE @VFieldList_ret VARCHAR(1000)
  IF @FieldList_ret = ''
	set @VFieldList_ret = @FieldList
  ELSE
	set @VFieldList_ret = @FieldList_ret
  SET @EndIndex = @startIndex+@pageSize-1
     if len(@Filter) = 0
     set @where = ''
     else 
     set @where  =' WHERE '+ @Filter
	SET @ExecSql = 'WITH RowList AS ( '
				+'SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderBy+' )AS Row, '+ @FieldList
				+' from  '+@TableName + ' ' + @where +' ) '
				+' SELECT ' + @VFieldList_ret + ' FROM RowList WHERE Row between '+convert(varchar,@startIndex)+' and '+convert(varchar,@EndIndex)
	print @ExecSql
    EXEC (@ExecSql)                 -- 返回结果集
  END

[yutu520]

能否提供一个比较详细的例子。万分谢谢

[yutu520]

Oracle 的

[yutu520]

help~~，在线等~