Oracle利用存储过程性实现分页

浏览数：38 / 时间：2015年06月12日

分页的简单配置在上一次已经说过了

这边说说怎么在存储过程中实现分页

首先建立存储过程

参考

http://www.cnblogs.com/gisdream/archive/2011/11/16/2251687.html

基本的代码如下所示

1、在oracle的sqlplus或其他工具中运行一下pl/sql块建立存储过程

------------------------------------------------------------
--分页存储过程
------------------------------------------------------------
--创建包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/ 

--创建过程
create or replace procedure fenye
(
       tableName in varchar2, --表名
       fields in varchar2,    --查询解果显示字段
       wherecase in varchar2, --查询条件
       pageSize in number,  --一页显示记录数
       pageNow in number,   --当前页
       orderField  varchar2, --排序字段，为空表示不排序
       orderFlag number,     --排序标识 0：正序 1：倒序
       myrows out number,   --总记录数
       myPageCount out number, --总分页
       p_cursor out testpackage.test_cursor     --返回的记录集
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(pageNow-1)*pagesize+1; --开始记录
v_end number:=pageNow*pageSize;         --结束记录
--排序sql
v_orderSql varchar2(100):='';  
v_wherecase varchar2(100):='';   
begin
  --执行部分
  --如果orderField不为空，则进行排序，如果orderFlag=0为升序，1为降序
  if orderField is not null then 
    if orderFlag=0 then
      v_orderSql:=' order by '||orderField;
    elsif orderFlag=1 then
      v_orderSql:=' order by '||orderField||' desc';
    else
      null;
    end if;
  end if;
  --条件判断语句
  if wherecase is not null then
    v_wherecase:=' where '||wherecase;
  end if;
  v_sql:='select * from
          (select t1.* ,rownum rn from(select '|| fields ||' from '|| tableName|| v_wherecase ||' '||v_orderSql ||') t1 where rownum<='|| v_end ||')
          where rn>='|| v_begin;
  --把游标和sql关联
  open p_cursor for v_sql;
  --计算myrows和myPageCount
  --组织一个sql
  v_sql:='select count(*) from '|| tableName || v_wherecase || ' ' ||v_orderSql;
  --执行sql,并把返回的值赋给myrows;
  execute immediate v_sql into myrows;
  --计算myPageCount
  if mod(myrows,Pagesize)=0 then
    myPageCount:=myrows/Pagesize;
  else
    myPageCount:=myrows/pagesize+1;
  end if;
  --关闭游标
  --close p_cursor;
end;
/

 2、.net中的oracle过程调用类

/// <summary>
    /// c#调用oracle的过程进行分页
    /// author:sirc_ljp
    /// date:2011.11.16
    /// </summary>
    public class Paging
    {
        private string _connectionString;
        private string _tableName;
        private string _fields = "*";
        private string _whercase="";
        private int _pageSize=10;
        private int _pageNow=1;
        private string _orderField="";
        private int _orderFlag = 0;
        private int _myRows;
        private int _myPageCount;
        private DataTable _result;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        /// <summary>
        /// 查询结果显示字段
        /// </summary>
        public string Fields
        {
            get { return _fields; }
            set { _fields = value; }
        }
        /// <summary>
        /// 查询条件
        /// </summary>
        public string Whercase
        {
            get { return _whercase; }
            set { _whercase = value; }
        }
        /// <summary>
        /// 页显示记录数
        /// </summary>
        public int PageSize
        {
            get { return _pageSize; }
            set { _pageSize = value; }
        }
        /// <summary>
        /// 当前页
        /// </summary>
        public int PageNow
        {
            get { return _pageNow; }
            set { _pageNow = value; }
        }
        /// <summary>
        /// 排序字段，为空""表示不排序
        /// </summary>
        public string OrderField
        {
            get { return _orderField; }
            set { _orderField = value; }
        }
        /// <summary>
        /// 排序标识 0：正序 1：倒序
        /// </summary>
        public int OrderFlag
        {
            get { return _orderFlag; }
            set { _orderFlag = value; }
        }
        /// <summary>
        /// 总记录数
        /// </summary>
        public int MyRows
        {
            get { return _myRows; }
        }
        /// <summary>
        /// 总分页
        /// </summary>
        public int MyPageCount
        {
            get { return _myPageCount; }
        }
        /// <summary>
        /// 返回的记录集
        /// </summary>
        public DataTable Result
        {
            get { return _result; }
        }

        public Paging()
        {
            fenye();
        }

        public Paging(string connectionString,string tableName, string fields, string wherecase, int pageSize, int pageNow, string orderField, int orderFlag, out int myRows, out int myPageCount, out DataTable result)
        {
            _connectionString = connectionString;
            _tableName = tableName;
            _fields = fields;
            _whercase = wherecase;
            _pageSize = pageSize;
            _pageNow = pageNow;
            _orderField = orderField;
            _orderFlag = orderFlag;

            fenye();
            myRows = _myRows;
            myPageCount = _myPageCount;
            result = _result;
            
        }

        private void fenye()
        {
            OracleConnection conn = new OracleConnection(_connectionString);
            conn.Open();

            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;

                //调用存储过程查询数据
                cmd.CommandText = "fenye";
                OracleParameter[] parameters = new OracleParameter[10];
                //注意这里的参数名和类型号与存储过程里面的一样
                parameters[0] = new OracleParameter("tableName", OracleType.VarChar);
                parameters[1] = new OracleParameter("fields", OracleType.VarChar);
                parameters[2] = new OracleParameter("wherecase", OracleType.VarChar);
                parameters[3] = new OracleParameter("pageSize", OracleType.Int32);
                parameters[4] = new OracleParameter("pageNow", OracleType.Int32);
                parameters[5] = new OracleParameter("orderField", OracleType.VarChar);
                parameters[6] = new OracleParameter("orderFlag", OracleType.Int32);
                parameters[7] = new OracleParameter("myrows", OracleType.Int32);
                parameters[8] = new OracleParameter("myPageCount", OracleType.Int32);
                parameters[9] = new OracleParameter("p_cursor", OracleType.Cursor);

                parameters[0].Value = _tableName;
                parameters[1].Value = _fields;
                parameters[2].Value = _whercase;
                parameters[3].Value = _pageSize;
                parameters[4].Value = _pageNow;
                parameters[5].Value = _orderField;
                parameters[6].Value = _orderFlag;
                parameters[7].Direction = ParameterDirection.Output;
                parameters[8].Direction = ParameterDirection.Output;
                parameters[9].Direction = ParameterDirection.Output;

                foreach (OracleParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                //执行命令
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                //得到查询结果表
                _result = ds.Tables[0];

                //取出总行数
                _myRows = Convert.ToInt32(parameters[7].Value);
                //取出总页数
                _myPageCount = Convert.ToInt32(parameters[8].Value.ToString());

            }
            catch (Exception ex)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }
            
        }

    }

在数据库建立好存储过程之后

在C#控制台创建类包 Paging

建立好了之后输出这边给出一个示例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            /*
            string connString = "Data Source=server1;Persist Security Info=True;User ID=s227;Password=123;Unicode=True";
            string queryString = "SELECT * FROM PRODUCT ";
            OracleConnection conn = new OracleConnection(connString);
            conn.Open();
            OracleCommand command = new OracleCommand(queryString,conn);
            OracleDataReader reader = command.ExecuteReader();
            reader.Read();
            Console.WriteLine("1 "+reader["MAKER"]+" 2 "+reader["MODEL"]+" 3 "+reader["TYPE"]);
            reader.Close();
            conn.Close();
            */
            /*
             * //学习的代码块
            string connectionString;
            string queryString;

            connectionString = "Data Source=202.200.136.125/orcl;User ID=openlab;PassWord=open123";

            queryString = "SELECT * FROM T_USER";

            OracleConnection myConnection = new OracleConnection(connectionString);

            OracleCommand myORACCommand = myConnection.CreateCommand();

            myORACCommand.CommandText = queryString;

            myConnection.Open();

            OracleDataReader myDataReader = myORACCommand.ExecuteReader();

            myDataReader.Read();

            Console.WriteLine("email: " + myDataReader["EMAIL"]);

            myDataReader.Close();

            myConnection.Close();
            */
            string connString = "Data Source=server1;Persist Security Info=True;User ID=s227;Password=123;Unicode=True";
            string queryString = "SELECT * FROM PRODUCT ";
            string _connectionString="Data Source=server1;Persist Security Info=True;User ID=s227;Password=123;Unicode=True";
            string _tableName="SPJ";
            string _fields = "*";
            string _whercase = "";
            int _pageSize = 10;
            int _pageNow = 1;
            string _orderField = "";
            int _orderFlag = 0;
            int _myRows;
            int _myPageCount;
            DataTable result2;
            Paging newpage = new Paging();
            newpage.ConnectionString = connString;
            newpage.TableName = _tableName;
            newpage.fenye();
            //Paging newpage = new Paging(_connectionString, _tableName, _fields, _whercase, _pageSize, _pageNow, _orderField, _orderFlag, _myRows, _myPageCount, result2);
            for (int i = 0; i < newpage.Result.Rows.Count; i++)
            {
                for (int j = 0; j < newpage.Result.Columns.Count; j++)
                {
                    Console.Write(newpage.Result.Rows[i][j].ToString()+"  ");
                }
                Console.WriteLine(" ");
            }
            newpage.PageNow = newpage.PageNow + 1;
            Console.WriteLine(" ");
            newpage.fenye();
            for (int i = 0; i < newpage.Result.Rows.Count; i++)
            {
                for (int j = 0; j < newpage.Result.Columns.Count; j++)
                {
                    Console.Write(newpage.Result.Rows[i][j].ToString() + "  ");
                }
                Console.WriteLine(" ");
            }

        }
    }
    /// <summary>
    /// c#调用oracle的过程进行分页
    /// author:sirc_ljp
    /// date:2011.11.16
    /// </summary>
    public class Paging
    {
        private string _connectionString;
        private string _tableName;
        private string _fields = "*";
        private string _whercase = "";
        private int _pageSize = 10;
        private int _pageNow = 1;
        private string _orderField = "";
        private int _orderFlag = 0;
        private int _myRows;
        private int _myPageCount;
        private DataTable _result;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        /// <summary>
        /// 查询结果显示字段
        /// </summary>
        public string Fields
        {
            get { return _fields; }
            set { _fields = value; }
        }
        /// <summary>
        /// 查询条件
        /// </summary>
        public string Whercase
        {
            get { return _whercase; }
            set { _whercase = value; }
        }
        /// <summary>
        /// 页显示记录数
        /// </summary>
        public int PageSize
        {
            get { return _pageSize; }
            set { _pageSize = value; }
        }
        /// <summary>
        /// 当前页
        /// </summary>
        public int PageNow
        {
            get { return _pageNow; }
            set { _pageNow = value; }
        }
        /// <summary>
        /// 排序字段，为空""表示不排序
        /// </summary>
        public string OrderField
        {
            get { return _orderField; }
            set { _orderField = value; }
        }
        /// <summary>
        /// 排序标识 0：正序 1：倒序
        /// </summary>
        public int OrderFlag
        {
            get { return _orderFlag; }
            set { _orderFlag = value; }
        }
        /// <summary>
        /// 总记录数
        /// </summary>
        public int MyRows
        {
            get { return _myRows; }
        }
        /// <summary>
        /// 总分页
        /// </summary>
        public int MyPageCount
        {
            get { return _myPageCount; }
        }
        /// <summary>
        /// 返回的记录集
        /// </summary>
        public DataTable Result
        {
            get { return _result; }
        }

        public Paging()
        {
           // fenye();
        }

        public Paging(string connectionString, string tableName, string fields, string wherecase, int pageSize, int pageNow, string orderField, int orderFlag, out int myRows, out int myPageCount, out DataTable result)
        {
            _connectionString = connectionString;
            _tableName = tableName;
            _fields = fields;
            _whercase = wherecase;
            _pageSize = pageSize;
            _pageNow = pageNow;
            _orderField = orderField;
            _orderFlag = orderFlag;

            //fenye();
            myRows = _myRows;
            myPageCount = _myPageCount;
            result = _result;

        }

        public void fenye()
        {
            OracleConnection conn = new OracleConnection(_connectionString);
            conn.Open();

            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                //CommandType.Text代表执行的是SQL语句CommandType.StoreProcedure代表执行的是存储过程CommandType代表要执行的类型
                cmd.CommandType = CommandType.StoredProcedure;

                //调用存储过程查询数据
                cmd.CommandText = "fenye";
                OracleParameter[] parameters = new OracleParameter[10];
                //注意这里的参数名和类型号与存储过程里面的一样
                parameters[0] = new OracleParameter("tableName", OracleType.VarChar);
                parameters[1] = new OracleParameter("fields", OracleType.VarChar);
                parameters[2] = new OracleParameter("wherecase", OracleType.VarChar);
                parameters[3] = new OracleParameter("pageSize", OracleType.Int32);
                parameters[4] = new OracleParameter("pageNow", OracleType.Int32);
                parameters[5] = new OracleParameter("orderField", OracleType.VarChar);
                parameters[6] = new OracleParameter("orderFlag", OracleType.Int32);
                parameters[7] = new OracleParameter("myrows", OracleType.Int32);
                parameters[8] = new OracleParameter("myPageCount", OracleType.Int32);
                parameters[9] = new OracleParameter("p_cursor", OracleType.Cursor);

                parameters[0].Value = _tableName;
                parameters[1].Value = _fields;
                parameters[2].Value = _whercase;
                parameters[3].Value = _pageSize;
                parameters[4].Value = _pageNow;
                parameters[5].Value = _orderField;
                parameters[6].Value = _orderFlag;
                parameters[7].Direction = ParameterDirection.Output;
                parameters[8].Direction = ParameterDirection.Output;
                parameters[9].Direction = ParameterDirection.Output;

                foreach (OracleParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
                //执行命令
                OracleDataAdapter oda = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                //得到查询结果表
                _result = ds.Tables[0];

                //取出总行数
                _myRows = Convert.ToInt32(parameters[7].Value);
                //取出总页数
                _myPageCount = Convert.ToInt32(parameters[8].Value.ToString());

            }
            catch (Exception ex)
            {

                throw;
            }
            finally
            {
                conn.Close();
            }

        }

    }


}

利用Console.write输出之后也可以把它利用网页的控件链接

输出结果如下所示最后一行为ID 可调

S1 P1 J1 200 1
S1 P1 J3 100 2
S1 P1 J4 700 3
S1 P2 J2 100 4
S2 P3 J1 400 5
S2 P3 J2 200 6
S2 P3 J4 500 7
S2 P3 J5 400 8
S2 P5 J1 400 9
S2 P5 J2 100 10

S3 P1 J1 200 11
S3 P3 J1 200 12
S4 P5 J1 100 13
S4 P6 J3 300 14
S4 P6 J4 200 15
S5 P2 J4 100 16
S5 P3 J1 200 17
S5 P6 J2 200 18
S5 P6 J4 500 19
请按任意键继续. . .

最后补充几个常识

1 dataTable的遍历问题

            for (int i = 0; i < Table.Rows.Count; i++)
            {
                for (int j = 0; j < TableColumns.Count; j++)
                {
                    Console.Write(Table.Rows[i][j].ToString() + "  ");
                }
                Console.WriteLine(" ");
            }

2数据类型的问题

1.varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；
2.VARCHAR2把空串等同于null处理，而varchar仍按照空串处理；
3.VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，
大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

3 Oracle基本数据类型

Character 数据类型
　　Character 数据类型用来存储字母数字型数据。当你在oracle 中定义一个character 数据时，通常需要制定字段的长度，它是该字段的最大长度。ORACLE提供以下几种character 数据类型：
　　
　　CHAR() CHAR数据类型是一种有固定长度和最大长度的字符串。存储在数据类型为CHAR字段中的数据将以空格的形式补到最大长度。长度定义在1——2000字节之间。
　　
　　当你创建一个CHAR型字段，数据库将保证在这个字段中的所有数据是定义长度，如果某个数据比定义长度短，那么将用空格在数据的右边补到定义长度。如果长度大于定义长度将会触发错误信息。
　　
　　VARCHAR() varchar型数据是varchar2型数据的快照。
　　
　　VARCHAR2() varchar2数据类型是一种可变长度的、有最大长度的字母数字型数据。Varchar2类型的字段长度可以达到4000字节，Varchar2类型的变量长度可以达到32676字节。
　　
　　一个空的varchar2(2000)字段和一个空的varchar2(2)字段所占用的空间是一样的。
　　
　　NCHAR() 和 NVARCHAR2()　NCHAR() 和 NVARCHAR2()数据类型分别与CHAR() 和 VARCHAR2()类型是相同的，只不过它们用来存储NLS（National Language Support）数据。
　　
　　LONG　LONG 数据类型是一个遗留下来的而且在将来不会被支持的数据类型。它将被LOB(Large Object)数据类型所代替。
　　
　　比较规则 Varchar2和char数据类型根据尾部的空格有不同的比较规则。对Char型数据，尾部的空格将被忽略掉，对于Varchar2型数据尾部带空格的数据排序比没有空格的要大些。比如：
　　
　　Char 型数据：　　　　　　　‘YO’=‘YO　　 ’
　　
　　Varchar2型数据：　　　　　 ‘YO’<’YO　　 ’
　　
　　Numberic 数据类型
　　
　　Numberic 数据类型用来存储负的和正的整数、分数和浮点型数据，范围在-1*10-103 和9.999…99*10125之间，有38位的精确度。标识一个数据超出这个范围时就会出错。
　　
　　Number(

,) Number数据类型存储一个有p位精确度的s位等级的数据。
　　
　　DATE 数据类型
　　DATE 数据类型用来存储日期和时间格式的数据。这种格式可以转换为其他格式的数据去浏览，而且它有专门的函数和属性用来控制和计算。以下的几种信息都包含在DATE数据类型中：
　　
　　Century
　　
　　Year
　　
　　Month
　　
　　Day
　　
　　Hour
　　
　　Minute
　　
　　Second
　　
　　LOB 数据类型
　　LOB(Large Object) 数据类型存储非结构化数据，比如二进制文件，图形文件，或其他外部文件。LOB 可以存储到4G字节大小。数据可以存储到数据库中也可以存储到外部数据文件中。LOB数据的控制通过DBMS_LOB 包实现。BLOB, NCLOB, 和CLOB 数据可以存储到不同的表空间中，BFILE存储在服务器上的外部文件中。LOB数据类型有以下几种：
　　
　　BLOB: 二进制数据
　　
　　CLOB: 字符型数据
　　
　　BFILE: 二进制文件
　　
　　其他数据类型
　　
　　ROWID ROWID 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。

4CommandType的属性类型

CommandType.Text代表执行的是SQL语句
CommandType.StoreProcedure代表执行的是存储过程
CommandType代表要执行的类型

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Oracle利用存储过程性实现分页

标签：控制台 oracle c# 存储过程控制台 oracle c# 存储过程

Oracle利用存储过程性实现分页

相关文章

随机文章

您可能还喜欢

您可能还喜欢

最新图文

您可能还喜欢

您可能还喜欢

文摘排行

文章排行

推荐文章

图文排行

推荐图文

Oracle利用存储过程性 实现分页

相关文章

随机文章

您可能还喜欢

您可能还喜欢

最新图文

您可能还喜欢

您可能还喜欢

文摘排行

文章排行

推荐文章

图文排行

推荐图文

Oracle利用存储过程性实现分页