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;

    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[].Direction = ParameterDirection.InputOutput;

        parms[].Direction = ParameterDirection.InputOutput;

        parms[].Direction = ParameterDirection.Output;

        parms[].Direction = ParameterDirection.Output;

        parms[].Direction = ParameterDirection.Output;

        parms[].Value = p_tableName;

        parms[].Value = p_strWhere;

        parms[].Value = p_orderColumn;

        parms[].Value = p_orderStyle;

        parms[].Value = p_curPage;

        parms[].Value = p_pageSize;

        DataSet ds = ExecuteProduce(prodeceName, parms);

        p_totalRecords = Convert.ToInt32(parms[].Value);

        p_totalPages = Convert.ToInt32(parms[].Value);

        return ds;

    }