Oracle数据分页,并传出数据集

2016-12-29 09:55:13来源:oschina作者:hujiapeng人点击

第七城市

1、创建Package

create or replace package forPaged is type my_csr is ref cursor; procedure getPaged(tableName in varchar2,objectid in number,fields in varchar2,wherecase in varchar2,orderFieldStrvarchar2,pageSize in number,pageIndex in number,expression varchar2,totalCount out number,csr out forPaged.my_csr); end forPaged;

2、创建存储过程;里面用到了父子级联查询;SQL中如果用到了单引号,需要再用一个单引号转义

create or replace procedure getPaged(tableName in varchar2,objectid in number,fields in varchar2,wherecase in varchar2,orderFieldStr in varchar2,pageSize in number,pageIndex in number,expression varchar2,totalCount out number,csr out forPaged.my_csr) is v_sql varchar2(1000); v_begin number:=(pageIndex-1)*pageSize+1; --开始记录
v_end number:=pageIndex*pageSize;--结束记录
begin v_sql:='select count(*) from (select entitycode from '|| tableName ||' where '|| wherecase||' and Exists(select distinct companycode from company where '||tableName||'.entitycode=company.companycode start with object_id='||objectid||' connect by prior object_id=parent_id)) t'; execute immediate v_sql into totalCount; if expression is not null then v_sql:='select '|| fields ||','||expression||' HeJi from (select t.* ,rownum rn from (select '|| fields ||' from '|| tableName|| ' where '|| wherecase ||' and Exists(select distinct companycode from company where '||tableName||'.entitycode=company.companycode start with object_id='||objectid||' connect by prior object_id=parent_id) order by '|| orderFieldStr ||' ) t) where rn>='|| v_begin||' and rn<='|| v_end; else v_sql:='select '|| fields ||' from (select t.* ,rownum rn from (select '|| fields ||' from '|| tableName|| ' where '|| wherecase ||' and Exists(select distinct companycode from company where '||tableName||'.entitycode=company.companycode start with object_id='||objectid||' connect by prior object_id=parent_id) order by '|| orderFieldStr ||' ) t) where rn>='|| v_begin||' and rn<='|| v_end; end if; open csr for v_sql; --close csr;关闭后则不会传出数据集
end getPaged;

3、Asp.Net中使用


1///
2/// 使用存储过程实现的分页查询3///

4/// 数据表
5/// 实体表主键
6/// 表中字段拼接的字符串
7/// 查询条件
8/// 每页显示数据行数
9/// 当前页
10/// 排序字段拼接的字符串
11/// 符合条件总记录数
12/// 计算表达式
13///
14public DataTable getReportDataByPage(M_SelectData model, out int totalCount) 15{ 16IList dataParameters = new List(); 17dataParameters.Add(new OracleParameter("tableName", model.tableName)); 18dataParameters.Add(new OracleParameter("objectid", model.companyId)); 19dataParameters.Add(new OracleParameter("fields", model.fieldsStr)); 20dataParameters.Add(new OracleParameter("wherecase", model.whereStr)); 21dataParameters.Add(new OracleParameter("pageSize", model.pageSize)); 22dataParameters.Add(new OracleParameter("pageIndex", model.pageIndex)); 23dataParameters.Add(new OracleParameter("orderFieldStr", model.orderFieldStr)); 24dataParameters.Add(new OracleParameter("expression", model.expression)); 25dataParameters.Add(new OracleParameter("totalCount", OracleType.Int32)); 26dataParameters.Add(new OracleParameter("csr", OracleType.Cursor)); 27dataParameters[dataParameters.Count - 1].Direction = ParameterDirection.Output; 28dataParameters[dataParameters.Count - 2].Direction = ParameterDirection.Output; 29DataTable dataTable = OracleSqlHelper.ExcuteProcedure("getpaged", dataParameters.ToArray()); 30totalCount = int.Parse(dataParameters[dataParameters.Count - 2].Value.ToString()); 31return dataTable; 32}业务逻辑层

1 ///
2/// 分页存储过程3///

4/// 存储过程名字
5/// tableName表名,fields查询字段字符串,wherecase查询条件,orderFieldStr排序字段字符串,pageSize每页显示数据行数,pageIndex页码,totalCount总记录数,传出,csr游标,传出
6///
7public static DataTable ExcuteProcedure(string proName, OracleParameter[] parameters)8{9DataTable table = new DataTable(); 10using (OracleConnection conn = new OracleConnection(connStr)) 11{ 12 using (OracleCommand comm = new OracleCommand(proName, conn)) 13{ 14 if (parameters.Length > 0) 15{ 16comm.Parameters.AddRange(parameters); 17} 18 comm.CommandType = CommandType.StoredProcedure; 19conn.Open(); 20 OracleDataAdapter oda = new OracleDataAdapter(comm); 21 DataSet ds = new DataSet(); 22oda.Fill(ds); 23 //得到查询结果表
24 table = ds.Tables[0]; 25} 26} 27return table; 28}数据操作层
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台