oracle高效分页存储过程

  create or replace package JT_P_page istype 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 isprocedure 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];