oracle高效分页存储过程

  create or replace package JT_P_page is
type type_cur is ref cursor;                    --定义游标变量用于返回记录集
procedure Pagination (Pindex in number,        --要显示的页数索引，从0开始
                       Psql in varchar2,        --产生分页数据的查询语句
                       Psize in number,         --每页显示记录数
                       Pcount out number,       --返回的分页数
                       Prowcount out number,    --返回的记录数
                       v_cur out type_cur      --返回分页数据的游标
                       );
end JT_P_page;
commit;
--定义包主体
create or replace package body JT_P_page is
procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
                       Psql in varchar2, --产生分页数据的查询语句
                       Psize in number, --每页显示记录数
                       Pcount out number, --返回的分页数
                       Prowcount out number, --返回的记录数
                       v_cur out type_cur --返回分页数据的游标
                       ) AS
    v_sql VARCHAR2(1000);
    v_Pbegin number;
    v_Pend number;
begin
    v_sql := 'select count(*) from (' || Psql || ')';
    execute immediate v_sql into Prowcount; --计算记录总数
    Pcount := ceil(Prowcount / Psize); --计算分页总数
    --显示任意页内容
    v_Pend := Pindex * Psize + Psize;
    v_Pbegin := v_Pend - Psize + 1;    
    v_sql := 'select * from (select rownum rn,t.* from (' || Psql || ')t) where rn between ' || v_Pbegin || ' and ' || v_Pend;       
    open v_cur for v_sql;
end Pagination;
end JT_P_page;
commit;

*************************************************************
 OracleParameter[] param = new OracleParameter[] { new OracleParameter("Pindex", OracleType.Number), new OracleParameter("Psql", OracleType.VarChar), new OracleParameter("Psize", OracleType.Number), new OracleParameter("Pcount", OracleType.Number), new OracleParameter("Prowcount", OracleType.Number), new OracleParameter("v_cur", OracleType.Cursor) };
                param[0].Value = index;
                param[1].Value = sql;
                param[2].Value = pageSize;

                param[0].Direction = ParameterDirection.Input;
                param[1].Direction = ParameterDirection.Input;
                param[2].Direction = ParameterDirection.Input;
                param[3].Direction = ParameterDirection.Output;
                param[4].Direction = ParameterDirection.Output;
                param[5].Direction = ParameterDirection.Output;

                DataSet ds = OracleHelper.ExecuteDataset(Form1.OracleConnString, CommandType.StoredProcedure, "JT_P_page.Pagination", param);


                int pcount = int.Parse(param[3].Value.ToString());
                int record = int.Parse(param[4].Value.ToString());

                lb_pcount.Text = pcount.ToString();

                dataGridView1.DataSource = ds.Tables[0];