今天用到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![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzAvNC83LzIvMzEvMjRhOTI0YTU3YmE2YjNmMmI1MWZjOWVkYjdlYTQxODYuanBl.jpe?w=700)
{
4
using(Database db = DBFactory.Create())
5![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzQvNC82LzIvNzYvNzE1ZjJkMDU1MDNiOTlkNDFmM2I2YmEyY2RjY2M4NGQuanBl.jpe?w=700)
{
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![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzQvNC82LzIvNzYvNzE1ZjJkMDU1MDNiOTlkNDFmM2I2YmEyY2RjY2M4NGQuanBl.jpe?w=700)
{
42
Console.WriteLine(row[1]);
43
}
44
}
45
}
46
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Oracle里分页包代码如下:
1
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![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzAvNC83LzIvMzEvMjRhOTI0YTU3YmE2YjNmMmI1MWZjOWVkYjdlYTQxODYuanBl.jpe?w=700)
/*****************************************************************
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![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzAvNC83LzIvMzEvMjRhOTI0YTU3YmE2YjNmMmI1MWZjOWVkYjdlYTQxODYuanBl.jpe?w=700)
/*****************************************************************
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![C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码 C#里使用Oracle提供的Oracle.DataAccess 返回包里的记录集游标,含Oralce里的分页包代码](https://image.shishitao.com:8440/aHR0cHM6Ly93d3cuaXRkYWFuLmNvbS9pbWdzLzAvNC83LzIvMzEvMjRhOTI0YTU3YmE2YjNmMmI1MWZjOWVkYjdlYTQxODYuanBl.jpe?w=700)
/*****************************************************************
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
这儿有个怪问题,就是在Toad里编译能通过,PLSQL Developer里则有问题。不知啥原因!