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;
}
}
