Oracle利用存儲過程性實現分頁

Oracle利用存儲過程性實現分頁

分頁的簡單配置 在上一次已經說過了

這邊說說怎麼在存儲過程中實現分頁

首先建立存儲過程

參考

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代表要執行的類型