C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标，含Oralce里的分页包代码
2009-11-04 20:56 咒语 阅读(3118) 评论(8) 编辑 收藏 举报
最近公司上马了一个新的项目，用到的数据库是Oracle10g的
今天用到C#里调用Oralce包里的SP返回分页的记录集，在网上查了一下，都用的是MS提供的Oracle.Cient，而在Oracle.DataAccess.Client 里调用时（Ms的Oracle.Client 有问题，所以用的是Oracle官方提供的Oracle.DataAccess.Client ），输出的参数也要赋值后调用才能返回，否则返回的记录集是空的。即DbNull.Value要先赋值到参数上。本人用Oracle还不到三个星期，因此肯定有不对的地方，还望各位大虾们多多指教。
今天发现Oracle的Package确实不错，是我用的这几天里的我目前感觉到的一大亮点（可以重载，可以把相关的操作放到一个包里）。不知MSSQL2008里有没有。
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
闲话少说，上代码：
C#里调用的代码如下：
Oracle里分页包代码如下：
这儿有个怪问题，就是在Toad里编译能通过，PLSQL Developer里则有问题。不知啥原因！
今天用到C#里调用Oralce包里的SP返回分页的记录集，在网上查了一下，都用的是MS提供的Oracle.Cient，而在Oracle.DataAccess.Client 里调用时（Ms的Oracle.Client 有问题，所以用的是Oracle官方提供的Oracle.DataAccess.Client ），输出的参数也要赋值后调用才能返回，否则返回的记录集是空的。即DbNull.Value要先赋值到参数上。本人用Oracle还不到三个星期，因此肯定有不对的地方，还望各位大虾们多多指教。
今天发现Oracle的Package确实不错，是我用的这几天里的我目前感觉到的一大亮点（可以重载，可以把相关的操作放到一个包里）。不知MSSQL2008里有没有。
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
闲话少说，上代码：
C#里调用的代码如下：
1
[Test]
2 public void Page1Test()
3
{
4
using(Database db = DBFactory.Create())
5
{
6 const string sql = " select * from test_temp where ID < 1000 order by id asc ";
7
8 OracleCommand cmd = new OracleCommand("PAGESPLIT_PKG.PAGESPLIT_SP",db.Connection);
9 cmd.CommandType = CommandType.StoredProcedure;
10 OracleParameter p1 = new OracleParameter("pagesize", OracleDbType.Int32);
11 p1.Direction = ParameterDirection.Input;
12 p1.Value = 20;
13 OracleParameter p2 = new OracleParameter("pageindex", OracleDbType.Int32);
14 p2.Direction = ParameterDirection.Input;
15 p2.Value = 2;
16 OracleParameter p3 = new OracleParameter("sqltext", OracleDbType.Varchar2);
17 p3.Direction = ParameterDirection.Input;
18 p3.Value = sql;
19
20 OracleParameter p4 = new OracleParameter("Records_out", OracleDbType.Int32,DBNull.Value,ParameterDirection.Output); //就算是空值也要写，否则是会出错的
21 OracleParameter p5 = new OracleParameter("table_out", OracleDbType.RefCursor,DBNull.Value, ParameterDirection.Output);
22
23 cmd.Parameters.Add(p1);
24 cmd.Parameters.Add(p2);
25 cmd.Parameters.Add(p3);
26 cmd.Parameters.Add(p4);
27 cmd.Parameters.Add(p5);
28
29 OracleDataAdapter da = new OracleDataAdapter(cmd);
30
31
32 DataSet ds = new DataSet();
33 da.Fill(ds);
34
35 Console.WriteLine(ds.Tables.Count);
36 Console.WriteLine(ds.Tables[0].TableName);
37 Console.WriteLine(ds.Tables[0].Rows.Count);
38 Console.WriteLine("结果:" + cmd.Parameters["Records_out"].Value.ToString());
39
40 foreach(DataRow row in ds.Tables[0].Rows)
41 {
42 Console.WriteLine(row[1]);
43
}
44 }
45
}
46
[Test] 2
public void Page1Test() 3
{ 
4
using(Database db = DBFactory.Create()) 5
{ 
6
const string sql = " select * from test_temp where ID < 1000 order by id asc "; 7
8
OracleCommand cmd = new OracleCommand("PAGESPLIT_PKG.PAGESPLIT_SP",db.Connection); 9
cmd.CommandType = CommandType.StoredProcedure; 10
OracleParameter p1 = new OracleParameter("pagesize", OracleDbType.Int32); 11
p1.Direction = ParameterDirection.Input; 12
p1.Value = 20; 13
OracleParameter p2 = new OracleParameter("pageindex", OracleDbType.Int32); 14
p2.Direction = ParameterDirection.Input; 15
p2.Value = 2; 16
OracleParameter p3 = new OracleParameter("sqltext", OracleDbType.Varchar2); 17
p3.Direction = ParameterDirection.Input; 18
p3.Value = sql; 19
20
OracleParameter p4 = new OracleParameter("Records_out", OracleDbType.Int32,DBNull.Value,ParameterDirection.Output); //就算是空值也要写，否则是会出错的 21
OracleParameter p5 = new OracleParameter("table_out", OracleDbType.RefCursor,DBNull.Value, ParameterDirection.Output); 22
23
cmd.Parameters.Add(p1); 24
cmd.Parameters.Add(p2); 25
cmd.Parameters.Add(p3); 26
cmd.Parameters.Add(p4); 27
cmd.Parameters.Add(p5); 28
29
OracleDataAdapter da = new OracleDataAdapter(cmd); 30
31
32
DataSet ds = new DataSet(); 33
da.Fill(ds); 34
35
Console.WriteLine(ds.Tables.Count); 36
Console.WriteLine(ds.Tables[0].TableName); 37
Console.WriteLine(ds.Tables[0].Rows.Count); 38
Console.WriteLine("结果:" + cmd.Parameters["Records_out"].Value.ToString()); 39
40
foreach(DataRow row in ds.Tables[0].Rows) 41
{ 42
Console.WriteLine(row[1]); 43
} 44
} 45
} 46
Oracle里分页包代码如下：
1create or replace package PageSplit_PKG as
2-----------------------------------------------------------------------
3-- 分页模块PageSplitPackage4Oracle V0.01
4-- Author : zhouyu Eamil:atwind@cszi.com
5-- 2009-11-4
6-----------------------------------------------------------------------/
7
8 type DataTable is REF CURSOR; --游标类型定义，用于返回数据集
9
10 procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Table_out out DataTable); --申明包里的SP1
11
12 procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Pages_out out int,Table_out out DataTable); --申明包里的SP2
13
14 procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,SqlTextCount string,Records_out out int,Table_out out DataTable); --申明包里的SP3
15
16 procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Table_out out DataTable); --申明包里的SP3
17
18end;
19
20create or replace package body PageSplit_PKG as
21
22-- 以下方法来自于网络修改
23--------------------------------------------------------------------------------------------
24--功能描述: 大数据量分页通用存储过程,超过100W数据的表，尽量加上索引上的条件
25--创建时间: 2009-11-4
26--------------------------------------------------------------------------------------------/
27 procedure PageSplit_SP
28 (
29 PageSize int, --每页记录数
30 PageIndex int, --当前页码,从 1 开始
31 SqlText string, --查询语句,含排序部分
32 Records_out out int,--返回总记录数
33 Table_out out DataTable ----返回当前页数据记录
34 ) as --对包中定义的SP1的实现
35 v_sql varchar2(8000); -- 不要超过32767个字符,不可以使用nvarchar2型,下面用的是to_char()
36 v_count int;
37 v_maxRownum int;
38 v_minRownum int;
39 begin
40 --取记录总数
41 v_sql := 'select count(*) from (' || SqlText || ')'; --拼接统计Sql
42 execute immediate v_sql into v_count; --执行统计
43 Records_out := v_count;
44
45 --行位置判断
46 v_maxRownum := PageIndex * PageSize;
47 v_minRownum := v_maxRownum - PageSize + 1;
48
49 --拼接查询语句
50 v_sql := 'SELECT *
51 FROM (
52 SELECT A.*, rownum rn
53 FROM ('|| SqlText ||') A
54 WHERE rownum <= '|| to_char(v_maxRownum) || '
55 ) B
56 WHERE rn >= ' || to_char(v_minRownum) ;
57 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
58
59 open Table_out for v_sql;
60
61 end;
62
63
64/*****************************************************************
65 *功能描述: 大数据量分页通用存储过程(重载1,直接返回分页数量)
66 *创建时间: 2009-11-3
67 *****************************************************************/
68 procedure PageSplit_SP
69 (
70 PageSize int, --每页记录数
71 PageIndex int, --当前页码,从 1 开始
72 SqlText string, --查询语句,含排序部分
73 Records_out out int,--返回总记录数
74 Pages_out out int, --返回分出的页数
75 Table_out out DataTable ----返回当前页数据记录
76 )
77 as
78 v_sql varchar2(8000);
79 v_count int;
80 v_maxRownum int;
81 v_minRownum int;
82 begin
83 ----取记录总数
84 v_sql := 'select count(*) from (' || SqlText || ')'; --生成统计字符串
85 execute immediate v_sql into v_count; --执行统计
86 Records_out := v_count;
87
88 --行位置判断
89 v_maxRownum := PageIndex * PageSize;
90 v_minRownum := v_maxRownum - PageSize + 1;
91
92 --拼接查询语句
93 v_sql := 'SELECT *
94 FROM (
95 SELECT A.*, rownum rn
96 FROM ('|| SqlText ||') A
97 WHERE rownum <= '|| to_char(v_maxRownum) || '
98 ) B
99 WHERE rn >= ' || to_char(v_minRownum) ;
100 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
101
102 OPEN Table_out FOR v_sql;
103
104 end;
105
106
107/*****************************************************************
108 *功能描述: 大数据量分页通用存储过程(重载2，自定义统计查询，推荐使用)
109 *创建时间: 2009-11-3
110 *****************************************************************/
111 procedure PageSplit_SP
112 (
113 PageSize int, --每页记录数
114 PageIndex int, --当前页码,从 1 开始
115 SqlText string, --查询语句,含排序部分
116 SqlTextCount string, --获取记录总数的查询语句
117 Records_out out int, --返回总记录数
118 Table_out out DataTable)
119 as
120 v_sql varchar2(8000);
121 v_count int;
122 v_maxRownum int;
123 v_minRownum int;
124 begin
125 ----取记录总数
126 execute immediate SqlTextCount into v_count;
127 Records_out := v_count;
128 ----执行分页查询
129 v_maxRownum := PageIndex * PageSize;
130 v_minRownum := v_maxRownum - PageSize + 1;
131
132 v_sql := 'SELECT *
133 FROM (
134 SELECT A.*, rownum rn
135 FROM ('|| SqlText ||') A
136 WHERE rownum <= '|| to_char(v_maxRownum) || '
137 ) B
138 WHERE rn >= ' || to_char(v_minRownum) ;
139 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
140
141 OPEN Table_out FOR v_sql;
142
143 end ;
144
145/*****************************************************************
146 *功能描述: 大数据量分页通用存储过程(重载3，不输出总记录数，适用于外部分页计算，内部直选记录集，DB计算压力最小)
147 *创建时间: 2009-11-3
148 *****************************************************************/
149 procedure PageSplit_SP
150 (
151 PageSize int, --每页记录数
152 PageIndex int, --当前页码,从 1 开始
153 SqlText string, --查询语句,含排序部分
154 Table_out out DataTable
155 )
156 as
157 v_sql varchar2(8000);
158 v_maxRownum int;
159 v_minRownum int;
160 begin
161
162 ----执行分页查询
163 v_maxRownum := PageIndex * PageSize;
164 v_minRownum := v_maxRownum - PageSize + 1;
165
166 v_sql := 'SELECT *
167 FROM (
168 SELECT A.*, rownum rn
169 FROM ('|| SqlText ||') A
170 WHERE rownum <= '|| to_char(v_maxRownum) || '
171 ) B
172 WHERE rn >= ' || to_char(v_minRownum) ;
173 --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
174
175 OPEN Table_out FOR v_sql;
176
177 end;
178
179
180end;
181
create or replace package PageSplit_PKG as 2
----------------------------------------------------------------------- 3
-- 分页模块PageSplitPackage4Oracle V0.01 4
-- Author : zhouyu Eamil:atwind@cszi.com 5
-- 2009-11-4 6
-----------------------------------------------------------------------/ 7
8
type DataTable is REF CURSOR; --游标类型定义，用于返回数据集 9
10
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Table_out out DataTable); --申明包里的SP1 11
12
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Records_out out int,Pages_out out int,Table_out out DataTable); --申明包里的SP2 13
14
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,SqlTextCount string,Records_out out int,Table_out out DataTable); --申明包里的SP3 15
16
procedure PageSplit_SP(PageSize int,PageIndex int,SqlText string,Table_out out DataTable); --申明包里的SP3 17
18
end; 19
20
create or replace package body PageSplit_PKG as 21
22
-- 以下方法来自于网络修改 23
-------------------------------------------------------------------------------------------- 24
--功能描述: 大数据量分页通用存储过程,超过100W数据的表，尽量加上索引上的条件 25
--创建时间: 2009-11-4 26
--------------------------------------------------------------------------------------------/ 27
procedure PageSplit_SP 28
( 29
PageSize int, --每页记录数 30
PageIndex int, --当前页码,从 1 开始 31
SqlText string, --查询语句,含排序部分 32
Records_out out int,--返回总记录数 33
Table_out out DataTable ----返回当前页数据记录 34
) as --对包中定义的SP1的实现 35
v_sql varchar2(8000); -- 不要超过32767个字符,不可以使用nvarchar2型,下面用的是to_char() 36
v_count int; 37
v_maxRownum int; 38
v_minRownum int; 39
begin 40
--取记录总数 41
v_sql := 'select count(*) from (' || SqlText || ')'; --拼接统计Sql 42
execute immediate v_sql into v_count; --执行统计 43
Records_out := v_count; 44
45
--行位置判断 46
v_maxRownum := PageIndex * PageSize; 47
v_minRownum := v_maxRownum - PageSize + 1; 48
49
--拼接查询语句 50
v_sql := 'SELECT * 51
FROM ( 52
SELECT A.*, rownum rn 53
FROM ('|| SqlText ||') A 54
WHERE rownum <= '|| to_char(v_maxRownum) || ' 55
) B 56
WHERE rn >= ' || to_char(v_minRownum) ; 57
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 58
59
open Table_out for v_sql; 60
61
end; 62
63
64
/***************************************************************** 65
*功能描述: 大数据量分页通用存储过程(重载1,直接返回分页数量) 66
*创建时间: 2009-11-3 67
*****************************************************************/ 68
procedure PageSplit_SP 69
( 70
PageSize int, --每页记录数 71
PageIndex int, --当前页码,从 1 开始 72
SqlText string, --查询语句,含排序部分 73
Records_out out int,--返回总记录数 74
Pages_out out int, --返回分出的页数 75
Table_out out DataTable ----返回当前页数据记录 76
) 77
as 78
v_sql varchar2(8000); 79
v_count int; 80
v_maxRownum int; 81
v_minRownum int; 82
begin 83
----取记录总数 84
v_sql := 'select count(*) from (' || SqlText || ')'; --生成统计字符串 85
execute immediate v_sql into v_count; --执行统计 86
Records_out := v_count; 87
88
--行位置判断 89
v_maxRownum := PageIndex * PageSize; 90
v_minRownum := v_maxRownum - PageSize + 1; 91
92
--拼接查询语句 93
v_sql := 'SELECT * 94
FROM ( 95
SELECT A.*, rownum rn 96
FROM ('|| SqlText ||') A 97
WHERE rownum <= '|| to_char(v_maxRownum) || ' 98
) B 99
WHERE rn >= ' || to_char(v_minRownum) ; 100
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 101
102
OPEN Table_out FOR v_sql; 103
104
end; 105
106
107
/***************************************************************** 108
*功能描述: 大数据量分页通用存储过程(重载2，自定义统计查询，推荐使用) 109
*创建时间: 2009-11-3 110
*****************************************************************/ 111
procedure PageSplit_SP 112
( 113
PageSize int, --每页记录数 114
PageIndex int, --当前页码,从 1 开始 115
SqlText string, --查询语句,含排序部分 116
SqlTextCount string, --获取记录总数的查询语句 117
Records_out out int, --返回总记录数 118
Table_out out DataTable) 119
as 120
v_sql varchar2(8000); 121
v_count int; 122
v_maxRownum int; 123
v_minRownum int; 124
begin 125
----取记录总数 126
execute immediate SqlTextCount into v_count; 127
Records_out := v_count; 128
----执行分页查询 129
v_maxRownum := PageIndex * PageSize; 130
v_minRownum := v_maxRownum - PageSize + 1; 131
132
v_sql := 'SELECT * 133
FROM ( 134
SELECT A.*, rownum rn 135
FROM ('|| SqlText ||') A 136
WHERE rownum <= '|| to_char(v_maxRownum) || ' 137
) B 138
WHERE rn >= ' || to_char(v_minRownum) ; 139
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 140
141
OPEN Table_out FOR v_sql; 142
143
end ; 144
145
/***************************************************************** 146
*功能描述: 大数据量分页通用存储过程(重载3，不输出总记录数，适用于外部分页计算，内部直选记录集，DB计算压力最小) 147
*创建时间: 2009-11-3 148
*****************************************************************/ 149
procedure PageSplit_SP 150
( 151
PageSize int, --每页记录数 152
PageIndex int, --当前页码,从 1 开始 153
SqlText string, --查询语句,含排序部分 154
Table_out out DataTable 155
) 156
as 157
v_sql varchar2(8000); 158
v_maxRownum int; 159
v_minRownum int; 160
begin 161
162
----执行分页查询 163
v_maxRownum := PageIndex * PageSize; 164
v_minRownum := v_maxRownum - PageSize + 1; 165
166
v_sql := 'SELECT * 167
FROM ( 168
SELECT A.*, rownum rn 169
FROM ('|| SqlText ||') A 170
WHERE rownum <= '|| to_char(v_maxRownum) || ' 171
) B 172
WHERE rn >= ' || to_char(v_minRownum) ; 173
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn 174
175
OPEN Table_out FOR v_sql; 176
177
end; 178
179
180
end; 181
这儿有个怪问题，就是在Toad里编译能通过，PLSQL Developer里则有问题。不知啥原因！
