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_TIME number; DEBUG_END_TIME number; sSql varchar2(20000); sWhereSql varchar2(2000); --sCommonSql varchar2(4000); sRsSql varchar2(8000); iFirst integer; iLast integer; iResult integer;begin DEBUG_BEGIN_TIME := dbms_utility.get_time; iResult := 0; if iRegionType is null then iResult := 1; sErrorDesc := '案卷类型不可为空'; return iResult; end if; if iRegionType is null then iResult := 1; sErrorDesc := '区域类型不可为空'; return iResult; end if; if iQueryType is null then iResult := 1; sErrorDesc := '执法类型不可为空'; return iResult; 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 then sWhereSql := ' and c.DistrictID=' || iRegionID ; elsif iRegionType =2 then sWhereSql := ' 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 then sWhereSql := sWhereSql || ' and c.LawCheckTime >= to_date(''' || sBeginDate || ''',''yyyy-mm-dd'')'; end if; if sEndDate is not null then sWhereSql := 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); execute immediate sRsSql into iTotalCount; iFirst := 1; iLast := iTotalCount; if iCurPage is not null and iPerPageCount is not null then iFirst := 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 for sRsSql; 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 then plog.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; return iResult;exception when 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; return iResult;end fcBSGetRecDetailListEx;