程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2通用分頁存儲過程

DB2通用分頁存儲過程

編輯:DB2教程

sql代碼
 
CREATE PROCEDURE "P_GETPAGEDATANEW"
(
p_TableName varchar(500),--表名稱
p_NickName varchar(200),--表昵稱
p_IDName varchar(200),--表主鍵名稱
p_PageIndex integer,--當前頁數
p_PageSize integer,--每頁大小
p_Strwhere varchar(8000),
OUT p_count integer --總記錄
)
LANGUAGE SQL
BEGIN
    DECLARE startNumber Integer;--起始記錄
    DECLARE endNumber Integer;--終止記錄
    DECLARE PageLowerBound integer;
   --DECLARE StartID integer;
   DECLARE strSql VARCHAR(4000);
   DECLARE mySQL VARCHAR(4000);
   DECLARE myCursor CURSOR WITH RETURN TO CLIENT FOR mySQL;
  
   IF p_PageIndex<1 THEN
    SET p_PageIndex=1;
   END IF;
   SET PageLowerBound = p_PageSize * (p_PageIndex-1);
   IF PageLowerBound<1 THEN
    SET PageLowerBound=1;
   end if;                                                   
  
  --查詢總記錄
   SET strSql='SELECT count(*) FROM (Select '||p_NickName||', rownumber() over(ORDER BY  '||p_IDName|| ' ASC) AS rn from '||p_TableName|| ' WHERE  '||p_Strwhere||' ) AS au ';
   prepare   mySQL   from   strSql;  
   open myCursor;
   fetch myCursor into p_count;-- 總記錄數
   close myCursor;
 
 
  --查詢結果
   SET startNumber=(p_PageIndex-1)*p_PageSize+1;
   SET endNumber=startNumber+p_PageSize;
   --SET strSql='SELECT * FROM (Select '||p_NickName||', rownumber() over(ORDER BY  '||p_IDName|| ' ASC) AS rn from '||p_TableName|| ' WHERE  '||p_Strwhere||' ) AS au WHERE au.rn BETWEEN ' ||char(startNumber)|| ' AND '||char(endNumber-1);
   SET strSql='select * from (Select '||p_NickName||', rownumber() over(ORDER BY  '||p_IDName|| ' ) as rownum from '||p_TableName|| ' WHERE  '||p_Strwhere||') as temp  where rownum BETWEEN ' ||char(startNumber)|| ' AND '||char(endNumber-1);
   prepare   mySQL   from   strSql;  
   open myCursor;
--set nocount off;
END
 
 
 
調用例子
 
      /// <summary>
        /// 制度建設分頁
        /// 2011-12-19
        /// </summary>
        /// <param name="statue">制度狀態</param>
        /// <param name="pageIndex">當前頁</param>
        /// <param name="pageSize">頁大小</param>
        /// <param name="departId">部門ID</param>
        /// <param name="m_disOperate">制度建設操作實體</param>
        /// <param name="result"></param>
        public void GetInsAllPage(string statue, int pageIndex, int pageSize, out int count, string unitId, CUEntityINSOPerate m_insOperate, CResult result)
        {
            StringBuilder sql = new StringBuilder();//
            sql.AppendFormat(" II.INS_ID =IO.INS_OPID AND IO.INS_OPUNITID = '{0}'", unitId);// AND IO.INS_OPRANGE = 0
 
            if (!string.IsNullOrEmpty(statue))
            {
                sql.AppendFormat(" AND II.INS_STATE in ({0}) ", statue);
            }
            if (m_insOperate != null)
            {
                //制度名稱
                if (!string.IsNullOrEmpty(m_insOperate.INS_NAME))
                {
                    sql.AppendFormat(" AND II.INS_NAME like '%{0}%'", m_insOperate.INS_NAME);
                }
                //制度類型
                if (!string.IsNullOrEmpty(m_insOperate.INS_TYPEID))
                {
                    sql.AppendFormat(" AND II.INS_TYPEID = '{0}'", m_insOperate.INS_TYPEID);
                }
                //制度狀態
                if (m_insOperate.INS_STATE != CommonStatus.GetProStatus(CommonStatus.ProjectStatus.Select))
                {
                    sql.AppendFormat(" AND II.INS_STATE = {0}", m_insOperate.INS_STATE);
                }
                //編輯部門
                if (!string.IsNullOrEmpty(m_insOperate.INS_OPDEPID.ToString()))
                {
                    sql.AppendFormat(" AND IO.INS_OPDEPID = {0}", m_insOperate.INS_OPDEPID);
                }
                //編輯人
                if (!string.IsNullOrEmpty(m_insOperate.INS_OPUSERID))
                {
                    sql.AppendFormat(" AND IO.INS_OPUSERID IN  ( select USERID from USERINFO  where USERNAME  like '%{0}%' )", m_insOperate.INS_OPUSERID);
                }
                //是否審核
                //if (!string.IsNullOrEmpty(m_insOperate.INS_ISAUDIT.ToString()))
                //{
                //    sql.AppendFormat(" AND IO.INS_ISAUDIT = '{0}'", m_insOperate.INS_ISAUDIT);
                //}
                //編輯時間
                //if (m_insOperate.INS_OPDATE.ToShortDateString() != "0001-1-1")
                //{
                //    sql.AppendFormat(" AND IO.INS_OPDATE BETWEEN TIMESTAMP('{0}')  AND  TIMESTAMP('{1}')", m_insOperate.INS_OPDATE, m_insOperate.INS_OPDATE.AddDays(1));
                //}
            }
 
            try
            {
 
                #region 插入數據參數列表
                DB2Parameter[] parameters = {
                    new DB2Parameter("p_TableName", DB2Type.VarChar,500),
                    new DB2Parameter("p_NickName", DB2Type.VarChar,200),
                    new DB2Parameter("p_IDName", DB2Type.VarChar,200),
                    new DB2Parameter("p_PageIndex", DB2Type.Integer),
                    new DB2Parameter("p_PageSize", DB2Type.Integer),
                    new DB2Parameter("p_Strwhere", DB2Type.VarChar,8000),
                    new DB2Parameter("p_count", DB2Type.Integer)
                };
                parameters[0].Value = "INSTITUTIONALINFO II,INS_OPERATE IO";
                parameters[1].Value = "II.*,IO.*";
                parameters[2].Value = "II.INS_ID";
                parameters[3].Value = pageIndex;
                parameters[4].Value = pageSize;
                parameters[5].Value = sql.ToString();
                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;
                #endregion
                this.IDataBase.FillDataSet("P_GETPAGEDATANEW", parameters, result.Value as CUTEntityINSOPerate);
                result.Success = true;
                count = CDataConvert.ConToValue<int>(parameters[6].Value);
                result.CurrOperateType = EOperateType.Seach;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.ErrorInformation = ex.Message;
                result.CurrOperateType = EOperateType.Error;
                count = 0;
            }
        }
 
 
 摘自 C#ASP.NET程序員

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved