java 分页 存储过程_存储过程分页

create or replace function dlmis.fcBSGetRecDetailListEx(iEventTypeID integer, --案卷类型

iRegionType integer, --区域类型 1-市区级别 2-街道级别

iRegionID integer, --区域标识

iRoadID integer, --道路标识

iQueryType integer, --执法类型 1-执法人员 2-执法小组

iQueryID integer, --执法标识

sBeginDate varchar2, --开始日期

sEndDate varchar2, --结束日期

iCurPage integer, --当前页

iPerPageCount integer, --每页记录数

iTotalCount out integer, --一共多少条记录

curRecInfoList out dlsys.pkdual.AnyCursor, --查询结果集

sErrorDesc out varchar2)return integer is

/**

功能： 根据案件类型等条件获取案件列表 (1- 2-)

版本： 监督指挥系统(大屏幕V2)

参数： iRegionType : 1-市区级别 2-街道级别

返回值为0表示成功，-1为失败，>0为提示

作者：liul，2015-03-29

**/DEBUG_BEGIN_TIMEnumber;

DEBUG_END_TIMEnumber;

sSqlvarchar2(20000);

sWhereSqlvarchar2(2000);--sCommonSql varchar2(4000);

sRsSql varchar2(8000);

iFirstinteger;

iLastinteger;

iResultinteger;beginDEBUG_BEGIN_TIME :=dbms_utility.get_time;

iResult := 0;if iRegionType is null theniResult := 1;

sErrorDesc := '案卷类型不可为空';returniResult;end if;if iRegionType is null theniResult := 1;

sErrorDesc := '区域类型不可为空';returniResult;end if;if iQueryType is null theniResult := 1;

sErrorDesc := '执法类型不可为空';returniResult;end if;

sSql := 'select a.MISSIONID, a.MISSIONCODE, a.MISSIONTYPEID, a.CREATETIME, a.STARTDATE, a.ENDDATE, a.TITLE, a.CONTENT,

a.OPERATORID, a.VALIDFLAG, a.VALIDTIME, a.DELETEFLAG, a.DELETETIME, a.MODIFYTIME, a.MODIFYHUMANID, a.PUBLISHFLAG,

b.MissionTypeName, c.LawCheckTime, c.PartName, c.PartHead, c.PartPhone, c.PartCode, c.RoadName, c.CoordinateX,

c.CoordinateY, c.Address, c.RecDesc, c.HumanIDOne, c.HumanIDTwo,d.PatrolName from dlmis.toLCMission a, dlmis.toDicLCMissionType b, dlmis.toLCRec c,dlsys.tcPatrol d

where a.missiontypeid = b.missiontypeid and a.missionid = c.missionid and c.HumanIDOne=d.PatrolID';--处理区域条件

if iRegionType =1 thensWhereSql := 'and c.DistrictID=' ||iRegionID ;

elsif iRegionType=2 thensWhereSql := 'and c.StreetID=' ||iRegionID ;end if;--处理道路条件

if iRoadID is not null then

--sWhereSql := sWhereSql || (' and c.RoadID='|| iRoadID) ;

sWhereSql :=sWhereSql;end if;--处理执法类型条件

if iQueryType =1 then --执法人

sWhereSql := sWhereSql || 'and c.HumanIDOne='||iQueryID;

elsif iQueryType=2 then --执法小组

sWhereSql := sWhereSql || 'and d.PatrolTeamId='||iQueryID;end if;--处理时间

if sBeginDate is not null thensWhereSql := sWhereSql || 'and c.LawCheckTime >= to_date(''' || sBeginDate || ''',''yyyy-mm-dd'')';end if;if sEndDate is not null thensWhereSql := sWhereSql || 'and c.LawCheckTime < to_date(''' || sEndDate || ''',''yyyy-mm-dd'')+1';end if;

sSql := sSql||sWhereSql;--返回

--error

--plog.error(sSql);

--分页

sRsSql := 'select count(1) from (' || sSql || ')';

dbms_output.put_line('sSql:' ||sSql);executeimmediate sRsSqlintoiTotalCount;

iFirst := 1;

iLast :=iTotalCount;if iCurPage is not null and iPerPageCount is not null theniFirst := iPerPageCount * (iCurPage - 1) + 1;

iLast := iPerPageCount *iCurPage;end if;

sRsSql := 'select * from (select rownum as rn, MISSIONID,MISSIONCODE,MISSIONTYPEID,CREATETIME,STARTDATE,ENDDATE,TITLE,CONTENT,OPERATORID,VALIDFLAG,VALIDTIME,DELETEFLAG,DELETETIME,MODIFYTIME,MODIFYHUMANID,PUBLISHFLAG,MissionTypeName,LawCheckTime,PartName,PartHead,PartPhone,PartCode,RoadName,CoordinateX,CoordinateY,Address,RecDesc,HumanIDOne,PatrolName,HumanIDTwo from (' || sSql ||

')) where rn >=' || iFirst || 'and rn <=' ||iLast;--error

--plog.error(sRsSql);

--返回结果集

if iTotalCount > 0 then

open curRecInfoList forsRsSql;else

open curRecInfoList for

select * from dual where 1 = 2;end if;commit;

DEBUG_END_TIME :=dbms_utility.get_time;if DEBUG_END_TIME - DEBUG_BEGIN_TIME > 100 thenplog.info('根据案件类型等条件获取案件列表{耗时=' || (DEBUG_END_TIME - DEBUG_BEGIN_TIME) ||

'}{iEventTypeID=' || iEventTypeID || '}{iRegionType=' ||iRegionType|| '}{iRegionID=' || iRegionID || '}{iRoadID=' ||iRoadID|| '}{iQueryType=' || iQueryType || '}{iQueryID=' ||iQueryID|| '}{sBeginDate=' || sBeginDate ||

'}{sEndDate=' || sEndDate || '}{iCurPage=' || iCurPage ||

'}{iPerPageCount=' || iPerPageCount || '}');end if;returniResult;

exceptionwhen others then

null;rollback;

plog.error('根据案件类型等条件获取案件列表{耗时=' || (DEBUG_END_TIME - DEBUG_BEGIN_TIME) ||

'}{iEventTypeID=' || iEventTypeID || '}{iRegionType=' ||iRegionType|| '}{iRegionID=' || iRegionID || '}{iRoadID=' ||iRoadID|| '}{iQueryType=' || iQueryType || '}{iQueryID=' ||iQueryID|| '}{sBeginDate=' || sBeginDate ||

'}{sEndDate=' || sEndDate || '}{iCurPage=' || iCurPage ||

'}{iPerPageCount=' || iPerPageCount || '}');

sErrorDesc := '根据案件类型等条件获取案件列表失败。' ||SQLERRM(SQLCODE);

iResult := -1;returniResult;end fcBSGetRecDetailListEx;