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

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

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

首先建立存储过程

参考

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

基本的代码如下所示

1、在oracle的sqlplus或其他工具中运行一下pl/sql块建立存储过程--------------------------------------------------------------分页存储过程--------------------------------------------------------------创建包create or replace package testpackage astype 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;         --结束记录--排序sqlv_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代表要执行的类型