orcale分页存储过程分页

参考了许多例子，总算写出了这个小功能：ORACLE 存储过程如下（里面有一些值得注意的地方，比如将　两个NUMBER值整除的结果用　TRUNC　函数截取，这是为了避免　溢出错误，还有就是　在查询结果集时的一些写法）：-- 创建为分页的包CREATE OR REPLACE PACKAGE PAGPAGEAS TYPE PAGE_CURSOR IS REF CURSOR; P

rxopt

1251人浏览 · 2008-12-04 10:17:00

rxopt · 2008-12-04 10:17:00 发布

参考了许多例子，总算写出了这个小功能：

ORACLE 存储过程如下（里面有一些值得注意的地方，比如将　两个NUMBER值整除的结果用　TRUNC　函数截取，这是为了避免　溢出错误，还有就是　在查询结果集时的一些写法）：

-- 创建为分页的包
CREATE OR REPLACE PACKAGE PAGPAGE
AS
TYPE PAGE_CURSOR IS REF CURSOR;
PROCEDURE PROSeparatePage(PAGE_TABLECOLUMN IN VARCHAR2 ,--传入的字段
        PAGE_TABLENAME IN VARCHAR2 ,             --表的名字(可以是多个表)
        PAGE_WHERE IN VARCHAR2 ,                        --查询的条件
        PAGE_ORDER IN VARCHAR2 ,                        --排序语句
        PAGE_SIZE IN NUMBER ,                                 --一个页面有多少条记录
        PAGE_CURRENTINDEX IN NUMBER ,          --当前页面
        PAGE_TOTAL OUT NUMBER ,                        --输出总记录数(根据需求可用可不用)
        PAGE_COUNT OUT NUMBER ,                        --输出总页数((根据需求可用可不用)
        PAGE_RESULT OUT PAGE_CURSOR);            --输出查询结果集

END PAGPAGE;

--　包体

CREATE OR REPLACE PACKAGE BODY PAGPAGE
AS
PROCEDURE PROSeparatePage(PAGE_TABLECOLUMN IN VARCHAR2 ,
            PAGE_TABLENAME IN VARCHAR2 ,
            PAGE_WHERE IN VARCHAR2 ,
            PAGE_ORDER IN VARCHAR2 ,
            PAGE_SIZE IN NUMBER ,
            PAGE_CURRENTINDEX IN NUMBER ,
            PAGE_TOTAL OUT NUMBER ,
            PAGE_COUNT OUT NUMBER ,
            PAGE_RESULT OUT PAGE_CURSOR)
IS
V_SELECTROWCOUNT VARCHAR2(2000);
V_SELECTRESULT VARCHAR2(2000);
BEGIN
V_SELECTROWCOUNT := 'SELECT COUNT(*) FROM ' || PAGE_TABLENAME;

IF PAGE_WHERE IS NOT NULL THEN
V_SELECTROWCOUNT := V_SELECTROWCOUNT || PAGE_WHERE;
END IF;

-- 求总记录数
EXECUTE IMMEDIATE V_SELECTROWCOUNT INTO PAGE_TOTAL;

-- OUTPUT TEST
DBMS_OUTPUT.PUT_LINE('COUNT : = ' || PAGE_TOTAL);

-- 求一共有多少页
IF MOD(PAGE_TOTAL,PAGE_SIZE) = 0 THEN
 PAGE_COUNT := TRUNC(PAGE_TOTAL / PAGE_SIZE); --用TRUNC 函数截取,也可以用其他方法,能保证数据转换时- --不溢出就行
ELSE
 PAGE_COUNT := TRUNC(PAGE_TOTAL / PAGE_SIZE) + 1;
END IF;

-- 查询数据库的指定记录个数
V_SELECTRESULT := 'SELECT * FROM( SELECT ROWNUM R, A.* FROM (' ||
 'SELECT ' || PAGE_TABLECOLUMN || ' FROM ' || PAGE_TABLENAME || PAGE_WHERE ||
 PAGE_ORDER || ') A WHERE ROWNUM <= ' || PAGE_CURRENTINDEX * PAGE_SIZE || ' ) WHERE R > ' ||
 (PAGE_CURRENTINDEX - 1) * PAGE_SIZE;

-- 查询语句(为了测试)
DBMS_OUTPUT.PUT_LINE(' RESULT SQL : ' || V_SELECTRESULT);

-- 将查询结果集赋给输出CURSOR
OPEN PAGE_RESULT FOR V_SELECTRESULT;
END PROSeparatePage;
END PAGPAGE;

有一个好的数据库工具,会事半功倍,特别对存储过程进行测试时,比如PL/SQL DEVELOPER

C#代码(这些是测试时用的,还没有优化):

OracleCommand ocmd = new OracleCommand("PAGPAGE.PROSEPARATEPAGE", connection);
ocmd.CommandType = CommandType.StoredProcedure;

                OracleParameter para1 = new OracleParameter("PAGE_TABLECOLUMN", OracleType.VarChar);
                OracleParameter para2 = new OracleParameter("PAGE_TABLENAME", OracleType.VarChar);
                OracleParameter para3 = new OracleParameter("PAGE_WHERE", OracleType.VarChar);
                OracleParameter para4 = new OracleParameter("PAGE_ORDER", OracleType.VarChar);

OracleParameter para5 = new OracleParameter("PAGE_SIZE", OracleType.Number);
OracleParameter para6 = new OracleParameter("PAGE_CURRENTINDEX", OracleType.Number);

                OracleParameter para7 = new OracleParameter("PAGE_TOTAL", OracleType.Number);
                OracleParameter para8 = new OracleParameter("PAGE_COUNT", OracleType.Number);
                OracleParameter para9 = new OracleParameter("PAGE_RESULT", OracleType.Cursor);

                para1.Direction = ParameterDirection.Input;
                para2.Direction = ParameterDirection.Input;
                para3.Direction = ParameterDirection.Input;
                para4.Direction = ParameterDirection.Input;
                para5.Direction = ParameterDirection.Input;
                para6.Direction = ParameterDirection.Input;

                para7.Direction = ParameterDirection.Output;
                para8.Direction = ParameterDirection.Output;
                para9.Direction = ParameterDirection.Output;

                para1.Value = " FID,U_CNAME,MSG_TITLE,FDATE,DECODE(FREAD,0,'未读','已读') AS FREAD ";
                para2.Value = " T_MESSAGE,SYS_USER ";
                para3.Value = " WHERE FSENDER = USERID ";
                para4.Value = " ORDER BY FDATE ";
                para5.Value = this.AspNetPagerTest.PageSize;
                para6.Value = this.AspNetPagerTest.CurrentPageIndex;
                para7.Value = null;
                para8.Value = null;
                para9.Value = null;

                ocmd.Parameters.Add(para1);
                ocmd.Parameters.Add(para2);
                ocmd.Parameters.Add(para3);
                ocmd.Parameters.Add(para4);
                ocmd.Parameters.Add(para5);
                ocmd.Parameters.Add(para6);
                ocmd.Parameters.Add(para7);
                ocmd.Parameters.Add(para8);
                ocmd.Parameters.Add(para9);

                DataSet ds = new DataSet();
                OracleDataAdapter oda = new OracleDataAdapter(ocmd);
                oda.Fill(ds);
                this.GridView1.DataSource = ds.Tables[0];
                this.GridView1.DataBind();

AspNetPagerTest.CustomInfoHTML = "记录总数：" + AspNetPagerTest.RecordCount.ToString() + "";
 AspNetPagerTest.CustomInfoHTML += " 总页数：" + AspNetPagerTest.PageCount.ToString() + "";
 AspNetPagerTest.CustomInfoHTML += " 当前页：" + AspNetPagerTest.CurrentPageIndex.ToString() + "";