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

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,orderFieldStr  varchar2,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  /// <summary>
 2         /// 使用存储过程实现的分页查询
 3         /// </summary>
 4         /// <param name="dataTableName">数据表</param>
 5         /// <param name="objectid">实体表主键</param>
 6         /// <param name="fields">表中字段拼接的字符串</param>
 7         /// <param name="wherecase">查询条件</param>
 8         /// <param name="pagesize">每页显示数据行数</param>
 9         /// <param name="pageIndex">当前页</param>
10         /// <param name="orderFieldStr">排序字段拼接的字符串</param>
11         /// <param name="totalCount">符合条件总记录数</param>
12         /// <param name="expression">计算表达式</param>
13         /// <returns></returns>
14         public DataTable getReportDataByPage(M_SelectData model, out int totalCount)
15         {
16             IList<OracleParameter> dataParameters = new List<OracleParameter>();
17             dataParameters.Add(new OracleParameter("tableName", model.tableName));
18             dataParameters.Add(new OracleParameter("objectid", model.companyId));
19             dataParameters.Add(new OracleParameter("fields", model.fieldsStr));
20             dataParameters.Add(new OracleParameter("wherecase", model.whereStr));
21             dataParameters.Add(new OracleParameter("pageSize", model.pageSize));
22             dataParameters.Add(new OracleParameter("pageIndex", model.pageIndex));
23             dataParameters.Add(new OracleParameter("orderFieldStr", model.orderFieldStr));
24             dataParameters.Add(new OracleParameter("expression", model.expression));
25             dataParameters.Add(new OracleParameter("totalCount", OracleType.Int32));
26             dataParameters.Add(new OracleParameter("csr", OracleType.Cursor));
27             dataParameters[dataParameters.Count - 1].Direction = ParameterDirection.Output;
28             dataParameters[dataParameters.Count - 2].Direction = ParameterDirection.Output;
29             DataTable dataTable = OracleSqlHelper.ExcuteProcedure("getpaged", dataParameters.ToArray());
30             totalCount = int.Parse(dataParameters[dataParameters.Count - 2].Value.ToString());
31             return dataTable;
32         }
业务逻辑层
技术分享
 1   /// <summary>
 2         /// 分页存储过程
 3         /// </summary>
 4         /// <param name="proName">存储过程名字</param>
 5         /// <param name="parameters">tableName表名,fields查询字段字符串,wherecase查询条件,orderFieldStr排序字段字符串,pageSize每页显示数据行数,pageIndex页码,totalCount总记录数,传出,csr游标,传出</param>
 6         /// <returns></returns>
 7         public static DataTable ExcuteProcedure(string proName, OracleParameter[] parameters)
 8         {
 9             DataTable table = new DataTable();
10             using (OracleConnection conn = new OracleConnection(connStr))
11             {
12                 using (OracleCommand comm = new OracleCommand(proName, conn))
13                 {
14                     if (parameters.Length > 0)
15                     {
16                         comm.Parameters.AddRange(parameters);
17                     }
18                     comm.CommandType = CommandType.StoredProcedure;
19                     conn.Open();
20                     OracleDataAdapter oda = new OracleDataAdapter(comm);
21                     DataSet ds = new DataSet();
22                     oda.Fill(ds);
23                     //得到查询结果表
24                     table = ds.Tables[0];
25                 }
26             }
27             return table;
28         }
数据操作层

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。