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