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; } }
转载于:https://www.cnblogs.com/LYunF/archive/2012/02/19/2357920.html
相关资源：db2编写的存储过程分页