oracle分页存储过程

CREATE OR REPLACE 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(4000) := '';      --sql语句
   v_startRecord Number(10);         --开始显示的记录条数
   v_endRecord Number(10);           --结束显示的记录条数
BEGIN
   --记录中总记录条数
   v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || 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 := round(p_totalRecords / p_pageSize,5);
   ELSE
       p_totalPages := round(p_totalRecords / p_pageSize,5) + 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 r FROM ' ||
            '(SELECT * FROM ' || p_tableName;
   IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
       v_sql := v_sql || ' WHERE 1=1' || 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 || ') B WHERE r >= '
            || v_startRecord;
   DBMS_OUTPUT.put_line(v_sql);
   OPEN v_cur FOR v_sql;
END prc_query;

  /// <summary>
        /// 通用分页ADO（分页）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">检索条件</param>
        /// <param name="sort">排序的列</param>
        /// <param name="order">排序方式</param>
        /// <param name="page">当前页</param>
        /// <param name="rows">每页显示记录条数</param>
        /// <returns></returns>
        public static ArrayList GetPaging(string tableName, string strWhere, string sort, string order, int page, int rows)
        {
            OracleParameter[] parameters =
            {
                new OracleParameter("p_tableName",OracleType.NVarChar), 
                new OracleParameter("p_strWhere",OracleType.NVarChar), 
                new OracleParameter("p_orderColumn", OracleType.NVarChar),
                new OracleParameter("p_orderStyle", OracleType.NVarChar),
                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)

            };
            parameters[0].Direction = ParameterDirection.Input;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[2].Direction = ParameterDirection.Input;
            parameters[3].Direction = ParameterDirection.Input;
            parameters[4].Direction = ParameterDirection.Input;
            parameters[5].Direction = ParameterDirection.Input;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.Output;
            parameters[8].Direction = ParameterDirection.Output;

            parameters[0].Value = tableName;
            parameters[1].Value = strWhere;
            parameters[2].Value = sort;
            parameters[3].Value = order;
            parameters[4].Value = page;
            parameters[5].Value = rows;

            ArrayList arrayList = RunProcedureForPaging("prc_query", parameters);
            return arrayList;
        }

   /// <summary>
        /// 执行存储过程,返回数据集
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static ArrayList RunProcedureForPaging(string storedProcName, OracleParameter[] parameters)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                ArrayList arrayList = new ArrayList();
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, "dt");
                connection.Close();

                arrayList.Add(dataSet);
                arrayList.Add(parameters[6].Value);
                arrayList.Add(parameters[7].Value);

                return arrayList;
            }
        }

  /// <summary>
        /// 数据表分页
        /// </summary>
        /// <param name="request">输入HttpRequestBase对象</param>
        /// <param name="tableName">分页表名</param>
        /// <param name="strWhere">检索条件</param>
        /// <param name="defautlSort">默认排序字段</param>
        /// <param name="orderSort">排序方式（默认降序）</param>
        /// <returns></returns>
        public ActionResult GetIndexJson(HttpRequestBase request, string tableName, string strWhere, string defautlSort, string orderSort = "desc")
        {
            //当前页码
            var page = int.Parse((request.Form["page"] ?? "1"));
            //显示行数
            var rows = int.Parse(request.Form["rows"] ?? "10");
            //排序字段
            var sort = request.Form["sort"] ?? defautlSort;
            //排序方式
            var order = request.Form["order"] ?? orderSort;

            //临时存储
            var arrayList = OracleHelper.GetPaging(tableName, strWhere, sort, order, page, rows);
            var ds = arrayList[0] as DataSet;
            HttpContext.Current.Session["Exprotds"] = ds;

            //正常检索
            var pTotalRecords = Convert.ToInt32(arrayList[1]);
            var jsons = JsonHelper.EasyUIDataTableJson(ds.Tables[0], null, pTotalRecords).Replace("
", "\r").Replace("
", "\n").Replace("	", "\t").Replace("\", "\\");
            return new StringResult(jsons);
        }

 public class JsonHelper
    {
        /// <summary>
        ///  Easyui专用Json数据列生成器
        /// </summary>
        /// <param name="dt">输入数据源</param>
        /// <param name="getrows">生成列的长度 null表示生成全部列</param>
        /// <param name="totalRecords">总行数</param>
        /// <returns></returns>
        public static string EasyUIDataTableJson(DataTable dt, int? getrows, int totalRecords)
        {
            /*
             * 源模型
             * "{"total":30,"rows":[{"dateTB":"2014-05-27","PeopleTZ":"姜杰","PeopleTJ":"邓康","State":"1"}]}"
             */
            const decimal decimaltype = 0.01M; //定义decimal数据类型
            var stringBuilder = new StringBuilder();
            if (dt.Rows.Count > 0)
            {
                stringBuilder.Append("{"total":" + totalRecords + ","rows":[");
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    var dr = dt.Rows[i];
                    stringBuilder.Append("{");
                    if (getrows == null) getrows = dt.Columns.Count;
                    for (var j = 0; j < getrows; j++)
                    {
                        if (dr[j].GetType() == decimaltype.GetType())
                        {
                            //若为 decimal 类型，则不加单引号
                            stringBuilder.Append(""" + dt.Columns[j].ColumnName + "":" +
                                                 dr[j] + ",");
                        }
                        else
                        {
                            stringBuilder.Append(""" + dt.Columns[j].ColumnName + "":"" +
                                                 dr[j].ToString().Trim().Replace("
", "").Replace("
", "") + "",");
                        }
                    }
                    stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
                    stringBuilder.Append("},");
                }
                stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(','), 1);
                stringBuilder.Append("]}");
            }
            else
            {
                stringBuilder.Append("{"total":0,"rows":[]}");
            }
            return stringBuilder.ToString();
        }

        /// <summary>
        /// 泛型集合转DataTable
        /// </summary>
        /// <param name="arrayList"></param>
        /// <param name="rowName"></param>
        /// <returns></returns>
        public static DataTable ArrayToDataTable(ArrayList arrayList, string[] rowName)
        {
            var dt = new DataTable();
            foreach (var name in rowName)
            {
                dt.Columns.Add(name, typeof(string));
            }
            foreach (var t in arrayList)
            {
                var dr = dt.NewRow();
                var rowStrings = t as string[];
                for (var j = 0; j < rowStrings.Length; j++)
                {
                    dr[rowName[j]] = rowStrings[j];
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 树形菜单Json序列化 定制
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string CreateJsonParameters(DataSet ds)
        {
            DataTable dt = ds.Tables[0];
            StringBuilder JsonString = new StringBuilder();
            if (dt != null && dt.Rows.Count > 0)
            {
                JsonString.Append("[ ");
                for (int i = 0; i < dt.Rows.Count; i++)
                {

                    JsonString.Append("{ ");

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        int _result = 0;
                        if (j < dt.Columns.Count - 1)
                        {
                            JsonString.Append("'isParent':true,'" + dt.Columns[j].ColumnName.ToString().ToLower() + "':");
                            if (int.TryParse(dt.Rows[i][j].ToString(), out _result))
                            {
                                JsonString.Append("" + dt.Rows[i][j].ToString() + ",");
                            }
                            else
                            {
                                JsonString.Append("'" + dt.Rows[i][j].ToString() + "',");
                            }
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            JsonString.Append("'isParent':true,'" + dt.Columns[j].ColumnName.ToString().ToLower() + "':");
                            if (int.TryParse(dt.Rows[i][j].ToString(), out _result))
                            {
                                JsonString.Append("" + dt.Rows[i][j].ToString());
                            }
                            else
                            {
                                JsonString.Append("'" + dt.Rows[i][j].ToString() + "'");
                            }
                        }
                    }
                    if (i == dt.Rows.Count - 1)
                    {
                        JsonString.Append("} ");
                    }
                    else
                    {
                        JsonString.Append("}, ");
                    }
                }
                JsonString.Append("]");
                return JsonString.ToString();
            }
            else
            {
                return null;
            }
        }