oracle学习 八 分页(使用rownumber)（持续更）

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(1000);

v_begin number:=(v_pagenow-1)*v_pagesize +1;--开始页数
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)=0
  then
    --总页数=总记录数/一页有多少条数据，如果有余数，就+1
  v_mypage_count:=v_myrows/v_pagesize;
  else

  v_mypage_count:=v_myrows/v_pagesize+1;
  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="25"></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", 20, pagecount, "", "");
        lblPage.Text = "当前页：第" + pagecount + "页  共:" + info[0] + "页";
        if (pagecount == 1)
        {
            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[0]))
        {
            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", 20, pagecount, "", "");
        lblPage.Text = "当前页：第" + pagecount + "页 共:" + info[0] + "页";
    }
    protected void lbnFirst_Click(object sender, EventArgs e)
    {
        pagecount = 1;
        ViewState["page"] = pagecount;
        if (pagecount == 1)
        {
            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", 20, 1, "", "");
        lblPage.Text = "当前页:第1页 共:" + info[0] + "页";
    }
    protected void lbnLast_Click(object sender, EventArgs e)
    {
        pagecount = Convert.ToInt32(info[0]);
        ViewState["page"] = pagecount;
        Bind("test", 20, pagecount, "", "");
        lblPage.Text = "当前页:第" + pagecount + "页 共:" + info[0] + "页";
        if (pagecount == Convert.ToInt32(info[0]))
        {
            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[0]);
                if (Convert.ToInt32(inPageNum.Text.Trim()) <= 0)
                {
                    pagecount = 1;
                }
                else if (Convert.ToInt32(inPageNum.Text.Trim()) < pagecount)
                {
                    pagecount = Convert.ToInt32(inPageNum.Text.Trim());
                }
                Bind("test", 20, pagecount, "", "");
                lblPage.Text = "当前页:第" + pagecount + "页 共:" + info[0] + "页";
            }
            else
                Response.Write("<script>alert('只能输入正整数')</script>");
        }
    }

核心部分：

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) 
            {
                this.GridView1.DataSource = QuickPage(2, 2);
                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, 50);    //一页有多少条数据
            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 = 0;

            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[0];
        }