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存储在server上的外部文件里。LOB数据类型有下面几种：
BLOB: 二进制数据
CLOB: 字符型数据
BFILE: 二进制文件
其它数据类型
ROWID ROWID 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。
4CommandType的属性类型
CommandType.Text代表运行的是SQL语句 CommandType.StoreProcedure代表运行的是存储过程 CommandType代表要运行的类型
