rownumber是查询的数据集之后加入一个伪列（连续的）使用它可以去制作以oracle数据库为基础的分页，语句类似于公式直接套用如下：
select * from (select rownum r,keycode from (select * from Acceptlist order by procid ))
where r between (pageindex-1)*pagesize +1 and pageindex*pagesize
其中pageindex为页码，pagesize为每页最多条数
oracle的between..and..前后都是闭区间，也就是说包含两个端的数.
多说一句的是：between and的相反的是not between..and，
not between..and..只是取指定范围的相反范围，也就是不在这个范围的记录，其用户可以用组个逻辑条件来取代。
between and 表示 >= or <=，那么not between..and..就表示< or >。
写成存储过程也可以这样
create or replace procedure proc_user_page
(
v_pagesize in number,--一页有多少条数据
v_pagenow in number,--当前页码
v_myrows out number,--总数据数
v_mypage_count out number,--总页数
p_cursor out testpackage.test_cursor--返回的结果集
)
is
v_sql varchar2(); v_begin number:=(v_pagenow-)*v_pagesize +;--开始页数
v_end number:=v_pagenow*v_pagesize;--截止页数 begin v_sql:='select * from (select rownum r,user_id from (select * from T_USER_INFO order by user_id ))
where r between '|| v_begin||' and '|| v_end;
--v_sql是执行SQL语句之后的一个结果集
--接下来需要使用游标读取结果集 open p_cursor
for v_sql ;
v_sql:='select count(*) from T_USER_INFO';
--执行v_sql，把值返回给v_rows
execute immediate v_sql into v_myrows;
if mod(v_myrows ,v_pagesize)=
then
--总页数=总记录数/一页有多少条数据，如果有余数，就+
v_mypage_count:=v_myrows/v_pagesize;
else v_mypage_count:=v_myrows/v_pagesize+;
end if;
--为什么不能关闭？因为它是隐式游标。可以自动关闭
--close p_cursor;
end;
其中游标需要创建一个包：
create or replace package testpackage
as
type test_cursor is ref cursor;
end testpackage ;
is ref cursor：相当于数据类型，不过是引用游标的数据类型。
这种变量通常用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了。 前台以及按钮的部分参考代码经过整理如下：
<asp:Label ID="lblPage" runat="server" ></asp:Label>
<asp:LinkButton ID="lbnFirst" runat="Server" Text="首页" CommandName="Page"
CommandArgument="First" onclick="lbnFirst_Click" ></asp:LinkButton>
<asp:LinkButton ID="lbnPrev" runat="server" Text="上一页" CommandName="Page"
CommandArgument="Prev" onclick="lbnPrev_Click" ></asp:LinkButton>
<asp:LinkButton ID="lbnNext" runat="Server" Text="下一页" CommandName="Page"
CommandArgument="Next" onclick="lbnNext_Click" ></asp:LinkButton>
<asp:LinkButton ID="lbnLast" runat="Server" Text="尾页" CommandName="Page"
CommandArgument="Last" onclick="lbnLast_Click" ></asp:LinkButton>
到第<asp:TextBox runat="server" ID="inPageNum" Width=""></asp:TextBox>页
<asp:Button ID="Button1" CommandName="go" runat="server" Text="go"
onclick="Button1_Click" />
<br />
protected void lbnPrev_Click(object sender, EventArgs e)
{
pagecount = Convert.ToInt32(ViewState["page"]);
pagecount--;
ViewState["page"] = pagecount;
Bind("test", , pagecount, "", "");
lblPage.Text = "当前页：第" + pagecount + "页 共:" + info[] + "页";
if (pagecount == )
{
lbnFirst.Enabled = false;
lbnPrev.Enabled = false;
lbnNext.Enabled = true;
lbnLast.Enabled = true;
}
else
{
lbnFirst.Enabled = true;
lbnPrev.Enabled = true;
lbnNext.Enabled = true;
lbnLast.Enabled = true;
}
}
protected void lbnNext_Click(object sender, EventArgs e)
{
pagecount = Convert.ToInt32(ViewState["page"]);
pagecount++;
ViewState["page"] = pagecount;
if (pagecount == Convert.ToInt32(info[]))
{
lbnFirst.Enabled = true;
lbnPrev.Enabled = true;
lbnNext.Enabled = false;
lbnLast.Enabled = false;
}
else
{
lbnFirst.Enabled = true;
lbnPrev.Enabled = true;
lbnNext.Enabled = true;
lbnLast.Enabled = true;
}
Bind("test", , pagecount, "", "");
lblPage.Text = "当前页：第" + pagecount + "页 共:" + info[] + "页";
}
protected void lbnFirst_Click(object sender, EventArgs e)
{
pagecount = ;
ViewState["page"] = pagecount;
if (pagecount == )
{
lbnFirst.Enabled = false;
lbnPrev.Enabled = false;
lbnNext.Enabled = true;
lbnLast.Enabled = true;
}
else
{
lbnFirst.Enabled = true;
lbnPrev.Enabled = true;
lbnNext.Enabled = true;
lbnLast.Enabled = true;
}
Bind("test", , , "", "");
lblPage.Text = "当前页:第1页 共:" + info[] + "页";
}
protected void lbnLast_Click(object sender, EventArgs e)
{
pagecount = Convert.ToInt32(info[]);
ViewState["page"] = pagecount;
Bind("test", , pagecount, "", "");
lblPage.Text = "当前页:第" + pagecount + "页 共:" + info[] + "页";
if (pagecount == Convert.ToInt32(info[]))
{
lbnFirst.Enabled = true;
lbnPrev.Enabled = true;
lbnNext.Enabled = false;
lbnLast.Enabled = false;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(inPageNum.Text.Trim()))
{
Regex regex = new Regex(@"^\d+$");//验证是否为数字
Match match = regex.Match(inPageNum.Text.Trim());
if (match.Success)
{
pagecount = Convert.ToInt32(info[]);
if (Convert.ToInt32(inPageNum.Text.Trim()) <= )
{
pagecount = ;
}
else if (Convert.ToInt32(inPageNum.Text.Trim()) < pagecount)
{
pagecount = Convert.ToInt32(inPageNum.Text.Trim());
}
Bind("test", , pagecount, "", "");
lblPage.Text = "当前页:第" + pagecount + "页 共:" + info[] + "页";
}
else
Response.Write("<script>alert('只能输入正整数')</script>");
}
}
核心部分：
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.GridView1.DataSource = QuickPage(, );
this.GridView1.DataBind();
}
} public DataTable QuickPage(int v_pagesize, int v_pagenow)
{
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "proc_user_page";
cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("v_pagesize", OracleType.Number, ); //一页有多少条数据
cmd.Parameters["v_pagesize"].Direction = ParameterDirection.Input;
cmd.Parameters["v_pagesize"].Value = v_pagesize; cmd.Parameters.Add("v_pagenow", OracleType.Number); //当前页码
cmd.Parameters["v_pagenow"].Direction = ParameterDirection.Input;
cmd.Parameters["v_pagenow"].Value = v_pagenow; cmd.Parameters.Add("v_myrows", OracleType.Number); //总数据数
cmd.Parameters["v_myrows"].Direction = ParameterDirection.Output; cmd.Parameters.Add("v_mypage_count", OracleType.Number); //总页数
cmd.Parameters["v_mypage_count"].Direction = ParameterDirection.Output;
cmd.Parameters["v_mypage_count"].Value = ; cmd.Parameters.Add("p_cursor", OracleType.Cursor); //返回的结果集
cmd.Parameters["p_cursor"].Direction = ParameterDirection.Output; DataSet Ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(Ds);
conn.Close(); //总记录数
// RecordCount = int.Parse(cmd.Parameters["v_myrows"].Value.ToString());
return Ds.Tables[];
}