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

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          }

数据操作层

 

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

标签：

查看更多关于Oracle数据分页，并传出数据集的详细内容...