oracle 存储过程分页
将分页的存储过程封装到包中,包规范
create or replace package pkg_query as type cur_query is ref cursor; PROCEDURE prc_query (p_tableName in varchar2, --表名 p_strWhere in varchar2, --查询条件 p_orderColumn in varchar2, --排序的列 p_orderStyle in varchar2, --排序方式 p_curPage in out Number, --当前页 p_pageSize in out Number, --每页显示记录条数 p_totalRecords out Number, --总记录数 p_totalPages out Number, --总页数 v_cur out pkg_query.cur_query); --返回的结果集 end pkg_query;
包体的实现如下
create or replace package body pkg_query as PROCEDURE prc_query (p_tableName in varchar2, --表名 p_strWhere in varchar2, --查询条件 p_orderColumn in varchar2, --排序的列 p_orderStyle in varchar2, --排序方式 p_curPage in out Number, --当前页 p_pageSize in out Number, --每页显示记录条数 p_totalRecords out Number, --总记录数 p_totalPages out Number, --总页数 v_cur out pkg_query.cur_query) --返回的结果集 IS v_sql VARCHAR2(1000) := ‘‘; --sql语句 v_startRecord Number(4); --开始显示的记录条数 v_endRecord Number(4); --结束显示的记录条数 BEGIN --记录中总记录条数 v_sql := ‘SELECT COUNT(1) FROM ‘ || p_tableName || ‘ WHERE 1=1 ‘; IF p_strWhere IS NOT NULL or p_strWhere <> ‘‘ THEN v_sql := v_sql ||‘ and ‘|| p_strWhere; END IF; EXECUTE IMMEDIATE v_sql INTO p_totalRecords; --验证页面记录大小 IF p_pageSize < 0 THEN p_pageSize := 0; END IF; --根据页大小计算总页数 IF MOD(p_totalRecords,p_pageSize) = 0 THEN p_totalPages := p_totalRecords / p_pageSize; ELSE p_totalPages := floor(p_totalRecords / p_pageSize) + 1; END IF; --验证页号 IF p_curPage < 1 THEN p_curPage := 1; END IF; IF p_curPage > p_totalPages THEN p_curPage := p_totalPages; END IF; --实现分页查询 v_startRecord := (p_curPage - 1) * p_pageSize + 1; v_endRecord := p_curPage * p_pageSize; v_sql := ‘SELECT * FROM (SELECT a.*, rownum rn FROM ‘ || ‘(SELECT * FROM ‘ || p_tableName; IF p_strWhere IS NOT NULL or p_strWhere <> ‘‘ THEN v_sql := v_sql || ‘ WHERE 1=1 and ‘ || p_strWhere; END IF; IF p_orderColumn IS NOT NULL or p_orderColumn <> ‘‘ THEN v_sql := v_sql || ‘ ORDER BY ‘ || p_orderColumn || ‘ ‘ || p_orderStyle; END IF; v_sql := v_sql || ‘) a WHERE rownum <= ‘ || v_endRecord || ‘) WHERE rn >= ‘ || v_startRecord; DBMS_OUTPUT.put_line(v_sql); OPEN v_cur FOR v_sql; END prc_query; end pkg_query;
c# 调用存储过程
private DataSet ExecuteProduce(string prodeceName, OracleParameter[] parms) { string connStr = ConfigurationManager. ConnectionStrings["RMS.DataAccess.Properties.Settings.ConnectionString"].ConnectionString; using (OracleConnection conn = new OracleConnection(connStr)) { if (conn.State != ConnectionState.Open) { conn.Open(); } OracleCommand cmd = new OracleCommand(prodeceName, conn); cmd.CommandType = CommandType.StoredProcedure; if (parms != null) { cmd.Parameters.AddRange(parms); } OracleDataAdapter oadp = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); oadp.Fill(ds); return ds; } } private DataSet Paging(string p_tableName, string p_strWhere, string p_orderColumn, string p_orderStyle, int p_curPage, int p_pageSize, ref int p_totalRecords, ref int p_totalPages) { string prodeceName = "pkg_query.prc_query"; OracleParameter[] parms = new OracleParameter[] { new OracleParameter("p_tableName",OracleType.VarChar), new OracleParameter("p_strWhere",OracleType.VarChar), new OracleParameter("p_orderColumn",OracleType.VarChar), new OracleParameter("p_orderStyle",OracleType.VarChar), new OracleParameter("p_curPage",OracleType.Number), new OracleParameter("p_pageSize",OracleType.Number), new OracleParameter("p_totalRecords",OracleType.Number), new OracleParameter("p_totalPages",OracleType.Number), new OracleParameter("v_cur",OracleType.Cursor) }; parms[4].Direction = ParameterDirection.InputOutput; parms[5].Direction = ParameterDirection.InputOutput; parms[6].Direction = ParameterDirection.Output; parms[7].Direction = ParameterDirection.Output; parms[8].Direction = ParameterDirection.Output; parms[0].Value = p_tableName; parms[1].Value = p_strWhere; parms[2].Value = p_orderColumn; parms[3].Value = p_orderStyle; parms[4].Value = p_curPage; parms[5].Value = p_pageSize; DataSet ds = ExecuteProduce(prodeceName, parms); p_totalRecords = Convert.ToInt32(parms[6].Value); p_totalPages = Convert.ToInt32(parms[7].Value); return ds; }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。