orcale存储过程(统计)

create or replace procedure singlecarrecord_pro
as
  --定义从t_dm_ddb201209查找的变量---
   type tdmddb is record(
       v_lineName  varchar2(20),  --线路名称
       v_carName  varchar2(20),   --车辆名称
       v_sjName  varchar2(20),    --司机姓名
       v_cwyName  varchar2(20),   --乘务员姓名
       v_dateTime  varchar2(20),  --日期
       v_carHyp  number(11),    --车次
       v_className varchar2(20),  --班次
       v_classType varchar2(20), --班别
       v_startStation  nvarchar2(20), --始发站
       v_endStation  nvarchar2(20),  --终到站
       v_planCarOutTime  varchar2(20), --计划发车时间
       v_planCarTime  varchar2(20), --计划到站时间
       v_factCarOutTime  varchar2(20), --实际发车时间
       v_factcarouttimec varchar2(50),--发车时间戳
       v_factCarTime  varchar2(20), --实际到站时间
       v_FACTCARINTIMEC varchar2(50),--到站时间戳
       v_scddsj  varchar2(20),--上次到达时间
       v_downline number(11),--离线类型
       v_jhfcsjc_dd number(11),--调度修改计划发车时间戳
       v_jhddsjc_dd number(11),--调度修改计划到达时间戳
       v_strideLineFlag number(20),--跨线标识
       v_strideLineId number(20),--跨线线路编号
       v_strideLineSrc number(11),--线路编号来源
       v_yxgl number(11),--运行公里
       v_yxfx number(11),--运行方向
       v_stopCarReason number(11), --车辆停开原因
       v_remark  varchar2(20),--备注
       o_record_state number(11),--运行状态
       o_starZd number(11),--开始站点
       o_endZd number(11)--结束站点
       );
       --定义从singlecarrecord表中查找的变量-----
      type tctsinglecarrecord is record(
            id varchar(20),
            v_lineName  varchar2(20), --线路名称
            v_carName  varchar2(20),--车辆名称
            v_dateTime  varchar2(20),--日期
            v_factCarOutTime  varchar2(20),--实际发车时间
            v_planCarTime  varchar2(20),--计划到站时间
            v_factCarTime  varchar2(20),--实际到站时间
            v_factcarouttimec varchar2(50),--发车时间戳
            v_FACTCARINTIMEC varchar2(50)--到站时间戳
       );
      --定义从singlecarrecord表中查找的变量-----
      type tctsinglecarrecordAvg is record(
            id varchar(20),
            v_factCarOutTime  varchar2(20),--实际发车时间
            o_linename  varchar(20),
            v_fx  number(11)
       );
     ---定义sql语句变量----
     v_dtbAll_sql varchar(1000);--查询动态表所有sql语句
     v_sql2_sql varchar(1000);--查询组合好了的趟次singlecarrecord表所有sql语句
     v_exe_sql1 varchar(1000);--查询非营运的途中机障
     v_exe_sql2 varchar(1000);--查询非营运的低保
     avg_sql varchar(1000);--查询发车间隔
     v_exe_sql3 varchar(1000);--每辆车的最开头一个车次 低保
     v_exe_sql4 varchar(1000);--每辆车的最后面一个车次 低保
     v_exe_sqladdoil varchar(1000);--添加燃料
     v_exe_sqlastoprun varchar(1000);--暂停营运
     v_exe_publicthing varchar(1000);--公务
     v_sqlpublic varchar(1000);--规则表中的公务
     v_exe_sql2erbao varchar(1000);--二保
     ------
     v_exe_erbao1 varchar(1000);--二保
     v_exe_erbao2 varchar(1000);--二保
     v_exe_oil1 varchar(1000);-
     v_exe_oil2 varchar(1000);--
     v_exe_stop1  varchar(1000);-
     v_exe_stop2  varchar(1000);-
     v_exe_public1  varchar(1000);-
     v_exe_public2  varchar(1000);-
     v_exe_public3  varchar(1000);-
     v_exe_public4  varchar(1000);-
     v_exe_publicthing1  varchar(1000);-
     v_isSplit  varchar(1000);

     erbao1 number(20);--
     erbao2 number(20);--
     oil1 number(20);--
     oil1gl  number(20);--
     ex_addoidgl1 number(20);
     ex_addoidgl2 number(20);
     oil2 number(20);--
     stop1 number(20);--
     stop2 number(20);--
     public1  number(20);
     public2  number(20);
     public3  number(20);
     public4  number(20);
     isSplit  number(20);

     v_erbao1 number(11);
     v_oil1 number(11);
     v_oilgl1  number(20);
     v_stop1 number(11);
     v_public1 number(11);
     o_stop number(11);
     v_ex_sumDibaos3   number(11);

     v_inoutcarname varchar2(20);
     v_inout_yxgl number(10);
     v_exe_inout varchar(200);--进出场公里查询
     --定义游标-----
     type cursor_type is ref cursor;--定义游标
     v_cursor2 cursor_type;--定义游标
     v_dtbAll_cursor cursor_type;--查询动态表的游标
     v_cursorAvgcurr cursor_type;----定义游标（发车间隔）
     cur cursor_type;-- 查询M383线路排班表
     cur1 cursor_type;--保存调度id
     v_inoutcursor cursor_type;--求进出场公里 游标
     --集合----
     tdmddb_last tdmddb;   --上次记录
     tdmddb_curr tdmddb;   --当前记录
     tsingle_last tctsinglecarrecord; --上次记录
     tsingle_curr tctsinglecarrecord; --当前记录
     tdmavg_last1 tctsinglecarrecordAvg;--上次记录（发车间隔）
     tdmavg_curr1 tctsinglecarrecordAvg;--当前记录（发车间隔）
     --常用变量----
     v_carHyp number(10) := 1;--趟次
     v_currDate varchar(20);--时间
     v_tabName varchar(50);--表名
     stopstationTimes number(11);--停站用时
     ex_sumDibaos number(11);--低保修车时间
     ex_sqlyibao number(20);--一保
     ex_sumDibaos3 number(11);--低保修车时间;
     ex_sumDibaos4 number(11);--低保修车时间;
     ex_sumJZs number(11);--机障时间
     ex_sumaddoiltime number(11);--加油时间
     ex_yxgl number(20);--加油时间
     ex_sumstopruntime number(11);--暂停营运时间
     ex_sumpublicthing number(20);--公务
     sumpublicthing1 number(20);--公务
     sqlpublic number(20);--规则表中的特殊公务
     ex_sql2erbao number(20);--二保
     ex_remark number(10);---定义一个标记
     v_remarkvalue varchar(50);--一二保，中文注释

     v_currentDate varchar2(20);--系统年月
     v_oneDateSql varchar2(500);--sql语句
     v_twoDateSql varchar2(500);--sql语句
     v_eOneDate varchar2(20);--当天
     v_eTwoDate varchar2(20);--第二天

     v_onestarttime varchar2(20);--当天开始时间
     v_oneendtime varchar2(20);--当天截至时间
     v_twostarttime varchar2(20);--第二天开始时间
     v_twoendtime varchar2(20);--第二天截至时间

     avgtime number(11);--求得发车间隔
     --定义查询排班表----
     v_sql1 varchar2(200);--查询排班表上行
     v_sqldown varchar2(200);--查询排班表下行
     v_sql2 varchar2(200);--查询调度表id
     v_sql3 varchar2(200);--修改调度表状态
     v_pbxlmc varchar2(20);--排班线路名称
     v_pbclmc varchar2(30);--排班车辆名称
     v_pbsj varchar2(30);--排版时间
     v_pbyxfx varchar2(20);--排班方向
     v_bc number(10);--排班班次
     v_yxsjbbs varchar2(30);--排班日期类型
     v_pbtable varchar(50);--排班表
     v_id varchar2(20);--调度表id
     O_comments_remark number(11);--标记
     ------------
     stoptimerange number(11);--停站用时范围
     v_sqlstoptimerange varchar(500);--停站用时范围sql
     v_day  varchar(11);--日

begin
     v_eOneDate := to_char(sysdate - interval '1' day,'yyyy-MM-dd');--要算的那天日期
     v_currDate := to_char(sysdate,'yyyyMM');--获得系统年月
     -----------
     v_day :=to_char(sysdate,'dd');
     if v_day=01 then--若是每一个月的第一天
        SELECT to_char(last_day(add_months(SYSDATE, -1)),'yyyy-MM-dd') into  v_eOneDate FROM dual;--求当前月的上一个月的最后一天的日期
        v_currDate := to_char(sysdate - interval '1' month,'yyyyMM');--获得当前月的上一个月日期
     end if;
    -----------
     --v_eOneDate :='2013-02-27';
     v_eTwoDate := to_char(sysdate,'yyyy-MM-dd');--存储过程被调用的日期
     v_tabName := 'T_DM_DDB' || v_currDate || '@linksz';
     --v_tabName:='T_DM_DDB201302@linksz';
     v_oneDateSql := 'select o_starttime,o_endtime from config where o_parameter = ''oneday''';
     v_twoDateSql := 'select o_starttime,o_endtime from config where o_parameter = ''twoday''';
     v_pbxlmc := 'M383';
     v_pbtable := 'ccccc';
      -----------------------------------线路 分班次来设置正点限时标识---------------------------------------
        v_sql1 := 'select O_clmc,O_fcsj,O_yxfx,O_bc,O_yxsjbbs from  '||v_pbtable||' where O_xlmc='''||v_pbxlmc||''' and o_pbrq='''||v_eOneDate||'''and o_bc<=8 and O_fcsj is not null order by O_fcsj ';--查询M383排班表小于9的所有的趟次
          begin
             open cur for v_sql1;
                  loop
                        fetch cur into v_pbclmc,v_pbsj,v_pbyxfx,v_bc,v_yxsjbbs;
                        EXIT WHEN cur%NOTFOUND;--游标找不到数据后退出循环
                        v_pbsj:=v_pbsj||':00';
                        if(v_pbyxfx=0 and (v_yxsjbbs='02'or v_yxsjbbs='03')) or v_pbyxfx=1 then
                             if v_bc<=4 then
                                  O_comments_remark:=1;--设置为90分钟标示
                                   v_sql2 := 'select O_id from '||v_tabName||' where  O_clmc='''||v_pbclmc||''' and O_jhfcsj='''||v_pbsj||''' and O_xlmc=''M383''and o_rq='''||v_eOneDate||'''';
                                      begin
                                          execute immediate v_sql2 into v_id;
                                          EXCEPTION
                                          WHEN NO_DATA_FOUND THEN
                                          dbms_output.put_line('找不到配置的时间!');
                                      end;
                                          v_sql3 := 'update '||v_tabName||' set O_comments='||O_comments_remark||' where O_id='''||v_id||'''';--给调度表中O_comments设置标示

                                          begin
                                            execute immediate v_sql3;
                                            EXCEPTION
                                            WHEN NO_DATA_FOUND THEN
                                            dbms_output.put_line('找不到配置的时间!');
                                          end;
                             elsif v_bc>=5 and v_bc<=8 then
                                  O_comments_remark:=2;--设置为100分钟标示
                                   v_sql2 := 'select O_id from '||v_tabName||' where  O_clmc='''||v_pbclmc||''' and O_jhfcsj='''||v_pbsj||''' and O_xlmc=''M383''and o_rq='''||v_eOneDate||'''';
                                      begin
                                          execute immediate v_sql2 into v_id;
                                          EXCEPTION
                                          WHEN NO_DATA_FOUND THEN
                                          dbms_output.put_line('找不到配置的时间!');
                                      end;
                                        v_sql3 := 'update '||v_tabName||' set O_comments='||O_comments_remark||' where O_id='''||v_id||'''';--给调度表中O_comments设置标示
                                          dbms_output.put_line(v_sql3);
                                          begin
                                            execute immediate v_sql3;
                                            EXCEPTION
                                            WHEN NO_DATA_FOUND THEN
                                            dbms_output.put_line('找不到配置的时间!');
                                          end;
                             end if;
                        elsif v_pbyxfx=0 and v_yxsjbbs='01'  then
                                if v_bc<=5 then

                                     O_comments_remark:=1;--设置为90分钟标示
                                      v_sql2 := 'select O_id from '||v_tabName||' where  O_clmc='''||v_pbclmc||''' and O_jhfcsj='''||v_pbsj||''' and O_xlmc=''M383''and o_rq='''||v_eOneDate||'''';
                                      begin
                                          execute immediate v_sql2 into v_id;
                                          EXCEPTION
                                          WHEN NO_DATA_FOUND THEN
                                          dbms_output.put_line('找不到配置的时间!');
                                      end;
                                       v_sql3 := 'update '||v_tabName||' set O_comments='||O_comments_remark||' where O_id='''||v_id||'''';--给调度表中O_comments设置标示
                                          dbms_output.put_line(v_sql3);
                                          begin
                                            execute immediate v_sql3;
                                            EXCEPTION
                                            WHEN NO_DATA_FOUND THEN
                                            dbms_output.put_line('找不到配置的时间!');
                                          end;
                                elsif v_bc=6 or v_bc=8 then

                                     O_comments_remark:=2;--设置为100分钟标示
                                      v_sql2 := 'select O_id from '||v_tabName||' where  O_clmc='''||v_pbclmc||''' and O_jhfcsj='''||v_pbsj||''' and O_xlmc=''M383''and o_rq='''||v_eOneDate||'''';
                                      begin
                                          execute immediate v_sql2 into v_id;
                                          EXCEPTION
                                          WHEN NO_DATA_FOUND THEN
                                          dbms_output.put_line('找不到配置的时间!');
                                      end;

                                          v_sql3 := 'update '||v_tabName||' set O_comments='||O_comments_remark||' where O_id='''||v_id||'''';--给调度表中O_comments设置标示
                                          dbms_output.put_line(v_sql3);
                                          begin
                                            execute immediate v_sql3;
                                            EXCEPTION
                                            WHEN NO_DATA_FOUND THEN
                                            dbms_output.put_line('找不到配置的时间!');
                                          end;
                                end if;
                        end if;
                   end loop;
              close  cur;
            end;
       ----------------------------------------------------1.合计趟次----------------------------------------------------------------
       begin
          execute immediate v_oneDateSql into v_onestarttime,v_oneendtime;--将查询到的时间放入到这两个字段中去
          execute immediate v_twoDateSql into v_twostarttime,v_twoendtime;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('找不到配置的时间!');
       end;
       v_dtbAll_sql:='select O_XLMC,O_CLMC,O_GH,O_CWY_Q_NAME,O_RQ,O_TC,O_BCBS,O_BB,O_FCZDNAME,O_DDZDNAME,O_JHFCSJ,O_JHDDSJ,O_SJFCSJ,O_SJFCSJC,O_SJDDSJ,O_SJDDSJc,
                     O_SCDDSJ,O_DOWNLINE,O_JHFCSJC_DD, O_JHDDSJC_DD,O_IS_CHANGELINE,O_LINENO_TJ,O_LINENO_SRC,O_YXGL,
                     O_YXFX,O_OPT_MARKS,O_COMMENTS,O_RECORD_STATE,o_fczd,o_ddzd from '|| v_tabName ||'
                     where (O_RECORD_STATE=''1'' and o_rq='''||v_eOneDate||''' and O_SJFCSJ between '''||v_onestarttime||'''
                      and '''||v_oneendtime||''' and O_XLMC in())
                      or
                     (O_RECORD_STATE=''1'' and o_rq='''||v_eOneDate||''' and O_SJFCSJ between '''||v_twostarttime||''' and '''||v_twoendtime||''' and O_XLMC in())
                     order by O_XLMC,O_CLMC,o_sjfcsjc';--从t_dm_ddb201209查找 (根据发车时间戳排序)

        begin
                 open v_dtbAll_cursor for v_dtbAll_sql;
                 fetch v_dtbAll_cursor into tdmddb_last;--取第一条
                 loop
                      fetch v_dtbAll_cursor into tdmddb_curr;--从第二条开始循环
                      EXIT WHEN v_dtbAll_cursor%NOTFOUND;--游标找不到数据后退出循环
                      if tdmddb_curr.v_carName != tdmddb_last.v_carName then --当车号不相等时,即为下一辆车的记录
                           toSinglecarrecord(tdmddb_last.v_lineName,tdmddb_last.v_carName,tdmddb_last.v_sjName,tdmddb_last.v_cwyName,tdmddb_last.v_dateTime,
                                            v_carHyp ,tdmddb_last.v_className,tdmddb_last.v_classType,tdmddb_last.v_startStation,tdmddb_last.v_endStation,
                                            tdmddb_last.v_planCarOutTime,tdmddb_last.v_planCarTime,tdmddb_last.v_factCarOutTime,tdmddb_last.v_factcarouttimec, tdmddb_last.v_factCarTime,tdmddb_last.v_FACTCARINTIMEC,
                                            '',tdmddb_last.v_downline,tdmddb_last.v_jhfcsjc_dd,tdmddb_last.v_jhddsjc_dd,
                                            tdmddb_last.v_strideLineFlag,tdmddb_last.v_strideLineId,tdmddb_last.v_strideLineSrc,tdmddb_last.v_yxgl,tdmddb_last.v_yxfx,
                                            tdmddb_last.v_stopCarReason,tdmddb_last.v_remark,tdmddb_last.o_record_state,tdmddb_last.o_starZd,tdmddb_last.o_endZd);--插入上一趟次记录
                         v_carHyp := 1;
                         tdmddb_last := tdmddb_curr; --更新内存中上一趟次记录
                      elsif (tdmddb_curr.v_yxfx != tdmddb_last.v_yxfx)or(tdmddb_curr.v_yxfx = tdmddb_last.v_yxfx and tdmddb_curr.o_starZd<tdmddb_last.o_endZd and tdmddb_curr.v_factCarOutTime>tdmddb_last.v_factCarTime ) then
                           toSinglecarrecord(tdmddb_last.v_lineName,tdmddb_last.v_carName,tdmddb_last.v_sjName,tdmddb_last.v_cwyName,tdmddb_last.v_dateTime,
                                              v_carHyp,tdmddb_last.v_className,tdmddb_last.v_classType,tdmddb_last.v_startStation,tdmddb_last.v_endStation,
                                              tdmddb_last.v_planCarOutTime,tdmddb_last.v_planCarTime,tdmddb_last.v_factCarOutTime,tdmddb_last.v_factcarouttimec,tdmddb_last.v_factCarTime,tdmddb_last.v_FACTCARINTIMEC,
                                              '',tdmddb_last.v_downline,tdmddb_last.v_jhfcsjc_dd,tdmddb_last.v_jhddsjc_dd,
                                              tdmddb_last.v_strideLineFlag,tdmddb_last.v_strideLineId,tdmddb_last.v_strideLineSrc,tdmddb_last.v_yxgl,tdmddb_last.v_yxfx,
                                              tdmddb_last.v_stopCarReason,tdmddb_last.v_remark,tdmddb_last.o_record_state,tdmddb_last.o_starZd,tdmddb_last.o_endZd);--插入上一趟次记录
                           v_carHyp := v_carHyp+1;--趟次累加
                           tdmddb_last := tdmddb_curr;--更新内存中上一趟次记录
                      else
                         tdmddb_last.v_factCarTime := tdmddb_curr.v_factCarTime;--更新实际到站时间（有非运营的时候，组合趟次）
                         tdmddb_last.o_endZd:= tdmddb_curr.o_endZd;--更新实际到站站点（有非运营的时候，组合趟次）
                         tdmddb_last.v_FACTCARINTIMEC:=tdmddb_curr.v_FACTCARINTIMEC;--更新实际到站时间戳（有非运营的时候，组合趟次）
                      end if;
               end loop;
                         toSinglecarrecord(tdmddb_last.v_lineName,tdmddb_last.v_carName,tdmddb_last.v_sjName,tdmddb_last.v_cwyName,tdmddb_last.v_dateTime,
                                          v_carHyp,tdmddb_last.v_className,tdmddb_last.v_classType,tdmddb_last.v_startStation,tdmddb_last.v_endStation,
                                          tdmddb_last.v_planCarOutTime,tdmddb_last.v_planCarTime,tdmddb_last.v_factCarOutTime,tdmddb_last.v_factcarouttimec,tdmddb_last.v_factCarTime,tdmddb_last.v_FACTCARINTIMEC,
                                          '',tdmddb_last.v_downline,tdmddb_last.v_jhfcsjc_dd,tdmddb_last.v_jhddsjc_dd,
                                          tdmddb_last.v_strideLineFlag,tdmddb_last.v_strideLineId,tdmddb_last.v_strideLineSrc,tdmddb_last.v_yxgl,tdmddb_last.v_yxfx,
                                          tdmddb_last.v_stopCarReason,tdmddb_last.v_remark,tdmddb_last.o_record_state,tdmddb_last.o_starZd,tdmddb_last.o_endZd);--插入最后一条记录
               close v_dtbAll_cursor;
             -------------------------------------------------2.求得每个趟次中可能出现的非营运--------------------------------------
               v_sql2_sql:= 'select o_id,o_linename,o_carname,o_datetime,o_factcarouttime,o_plancarintime,o_factcarintime,o_factcarouttimec,o_FACTCARINTIMEC
                             from t_ct_singlecarrecord where o_datetime = '''||v_eOneDate||''' and
                             o_linename in (''B660'',''B661'',''B662'',''B663'',''B781'',''B783'',''B722'',''B836'',''B807'',''M336'',''720'',''M383'',''M245'',''M247'',''M259'',''B778'',''B827'')
                             order by o_carname asc,o_factcarouttimec asc';--查询算好了趟次
               open v_cursor2 for v_sql2_sql;
                    loop
                          fetch v_cursor2 into tsingle_curr;
                          EXIT WHEN v_cursor2%NOTFOUND;--游标找不到数据后退出循环

                         ------查询t_ct_singlecarrecord_rule表获得停站用时范围设置值,是否是特殊公务----
                          v_sqlstoptimerange:='select O_configvalue from t_ct_singlecarrecord_rule t where o_xlmc='''||tsingle_curr.v_lineName||''' and o_configno=3';--停站用时范围设置
                          v_sqlpublic:='select O_configvalue from t_ct_singlecarrecord_rule t where o_xlmc='''||tsingle_curr.v_lineName||''' and o_configno=5';--公务状态范围设置
                          v_isSplit:='select O_configvalue from t_ct_singlecarrecord_rule t where o_xlmc='''||tsingle_curr.v_lineName||''' and o_configno=6';--是否把非营运和停站用时分开
                          begin
                             execute immediate v_sqlstoptimerange into stoptimerange;
                             execute immediate v_sqlpublic into sqlpublic;
                             execute immediate v_isSplit into isSplit;

                             EXCEPTION
                                WHEN NO_DATA_FOUND THEN
                                stoptimerange:=0;
                          end;

                          ------（1）查询途中机障----
                          v_exe_sql1:='select sum(O_SJDDSJC - O_SJFCSJC)
                          from '||v_tabName||' where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName ||''' and
                          o_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline=''12'' and O_SJFCSJC>'''||tsingle_curr.v_factcarouttimec||''' and
                          O_SJDDSJC<'''||tsingle_curr.v_FACTCARINTIMEC ||''' order by O_XLMC,O_CLMC,o_sjfcsjC';
                          begin
                                 execute immediate v_exe_sql1 into  ex_sumJZs;
                          end;
                          -----有途中机障 就更新----
                          if ex_sumJZs >0 then
                            update t_ct_singlecarrecord set O_wayfault=round(ex_sumJZs/60,0),o_righttime='√',o_errortimelength=0--当有途中机障默认指定为√，0
                            where o_id=tsingle_curr.id;
                          end if;

                          ------------------（2）低保，加油，停场待班，公务-----------------
                          --------（1）这里是求每辆车的第一个，和最后一个趟次之间出现的非营运-
                         if  tsingle_last.v_carName = tsingle_curr.v_carName  then
                                  v_exe_sql2:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  WHERE o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''1301''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--一保

                                  v_exe_sql2erbao:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  WHERE o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''1302'' and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||''' and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--二保

                                  v_exe_sqladdoil:='select sum((O_SJDDSJC- O_SJFCSJC)/60),sum(o_yxgl) from '||v_tabName||'
                                  where o_rq='''||tsingle_curr.v_dateTime||'''  and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||'''  and o_record_state=''21'' and o_downline=''4''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--添加燃料

                                  v_exe_sqlastoprun:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline=''9''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--暂停营运

                                  v_exe_publicthing:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline=''319''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--年检


                                  v_exe_publicthing1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline=''311''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''and O_SJDDSJc <= '''||tsingle_curr.v_factcarouttimec||'''';--专车


                                  begin
                                     execute immediate v_exe_sql2 into ex_sqlyibao;
                                     execute immediate v_exe_sql2erbao into ex_sql2erbao;
                                     execute immediate v_exe_sqladdoil into ex_sumaddoiltime,ex_yxgl;
                                     execute immediate v_exe_sqlastoprun into ex_sumstopruntime;
                                     execute immediate v_exe_publicthing into ex_sumpublicthing;
                                     execute immediate v_exe_publicthing1 into sumpublicthing1;
                                  end;

                                   stopstationTimes:=round((ORACLE_TO_UNIX(to_date(tsingle_curr.v_factCarOutTime, 'hh24:mi:ss'))-ORACLE_TO_UNIX(to_date(tsingle_last.v_factCarTime, 'hh24:mi:ss')))/60,0);
                                   ex_remark:=0; --标记看是否有 低保，加油，停场待班，公务这几种非营运
                                   v_remarkvalue:='';
                                   if ex_sqlyibao>0 then
                                     v_remarkvalue:='一保';
                                   else
                                     ex_sqlyibao:=0;
                                   end if;

                                   if ex_sql2erbao>0 then
                                      v_remarkvalue:=v_remarkvalue||' 二保';
                                   else
                                    ex_sql2erbao:=0;
                                   end if;

                                   ex_sumDibaos:=ex_sqlyibao+ex_sql2erbao;--一保和二保之和
                                   ------有以上情况将进行更行修改-----
                                   if ex_sumDibaos >0 then
                                      ex_remark:=1;

                                       if isSplit=1 then--非营运和停站用时是否分开(目前光明) 1 分开，0 不 分开
                                          update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes-ex_sumDibaos--更新或添加停站用时，没有停场带班
                                          where o_id=tsingle_curr.id;
                                          stopstationTimes:=ex_sumDibaos;
                                       end if;
                                      update t_ct_singlecarrecord set o_lowmend=stopstationTimes,o_remark=v_remarkvalue--更新或添加低保
                                      where o_id=tsingle_curr.id;
                                   end if;

                                   --加油
                                   if ex_sumaddoiltime >0 then
                                       ex_remark:=1;

                                       if ex_yxgl>0 then--更新加油里程
                                           update t_ct_singlecarrecord set  o_addoilkm=ex_yxgl/1000
                                           where o_id=tsingle_curr.id;
                                       end if;

                                       if ex_sumDibaos >0 and isSplit=1 then--当一个趟次里面同时有低保和加油时
                                          stopstationTimes:=round((ORACLE_TO_UNIX(to_date(tsingle_curr.v_factCarOutTime, 'hh24:mi:ss'))-ORACLE_TO_UNIX(to_date(tsingle_last.v_factCarTime, 'hh24:mi:ss')))/60,0);
                                          stopstationTimes:=stopstationTimes-ex_sumDibaos-ex_sumaddoiltime;
                                          update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes--更新或添加停站用时，没有停场带班
                                          where o_id=tsingle_curr.id;

                                          update t_ct_singlecarrecord set  o_remark='添加燃料',o_addoil=ex_sumaddoiltime
                                          where o_id=tsingle_curr.id;
                                       elsif ex_sumDibaos >0 and isSplit<>1 then--当一个趟次里面同时有低保和加油时
                                          update t_ct_singlecarrecord set  o_addoil=ex_sumaddoiltime
                                          where o_id=tsingle_curr.id;
                                       elsif isSplit=1 then--isSplit==1时 加油不用更新（圳美）
                                            update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes-ex_sumaddoiltime--更新或添加停站用时，没有停场带班
                                            where o_id=tsingle_curr.id;

                                            update t_ct_singlecarrecord set  o_remark='添加燃料',o_addoil=ex_sumaddoiltime
                                            where o_id=tsingle_curr.id;
                                       else
                                          update t_ct_singlecarrecord set  o_addoil=stopstationTimes
                                          where o_id=tsingle_curr.id;
                                       end if;
                                   end if;
                                   --暂停营运
                                   if ex_sumstopruntime >0 then
                                      ex_remark:=1;

                                      if isSplit=1 then
                                          update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes-ex_sumstopruntime--更新或添加停站用时，没有停场带班
                                          where o_id=tsingle_curr.id;
                                          stopstationTimes:=ex_sumstopruntime;
                                      end if;

                                      update t_ct_singlecarrecord set  o_stopstationwait=stopstationTimes
                                      where o_id=tsingle_curr.id;
                                   end if;
                                   --公务（年检）
                                   if ex_sumpublicthing >0 then
                                      ex_remark:=1;

                                      if isSplit=1 then
                                          update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes-ex_sumpublicthing--更新或添加停站用时，没有停场带班
                                          where o_id=tsingle_curr.id;
                                          stopstationTimes:=ex_sumpublicthing;
                                      end if;

                                      update t_ct_singlecarrecord set  o_carpublic=stopstationTimes,o_remark='年检'
                                      where o_id=tsingle_curr.id;
                                   end if;
                                   --公务（专车）
                                   if sumpublicthing1 >0 then
                                      ex_remark:=1;

                                      if isSplit=1 then
                                          update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes-sumpublicthing1--更新或添加停站用时，没有停场带班
                                          where o_id=tsingle_curr.id;
                                          stopstationTimes:=sumpublicthing1;
                                      end if;

                                      update t_ct_singlecarrecord set  o_carpublic=stopstationTimes,o_remark='专车'
                                      where o_id=tsingle_curr.id;
                                   end if;
                                   -----上面情况都没有 否则算停站用时-
                                   if  ex_remark=0 then
                                        stopstationTimes:=round((ORACLE_TO_UNIX(to_date(tsingle_curr.v_factCarOutTime, 'hh24:mi:ss'))-ORACLE_TO_UNIX(to_date(tsingle_last.v_factCarTime, 'hh24:mi:ss')))/60,0);
                                        if stoptimerange!=0 then--按自己定义的停站时间超过多少有停场待班
                                             if stopstationTimes>=stoptimerange then--停站用时大于或等于stoptimerange,更新或添加到停场待班
                                                    update t_ct_singlecarrecord set o_stopstationwait=stopstationTimes--更新或添加停场待班
                                                    where o_id=tsingle_curr.id;
                                             elsif stopstationTimes>0 and stopstationTimes<stoptimerange then
                                                    update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes--更新或添加停站用时，没有停场带班
                                                    where o_id=tsingle_curr.id;
                                              end if;
                                        else--默认停站时间不管多少都没有停场待班
                                              update t_ct_singlecarrecord set o_stopstationtime=stopstationTimes--更新或添加停站用时，没有停场带班
                                              where o_id=tsingle_curr.id;
                                        end if;
                                  end if;
                             --------（2）下面是求每辆车的第一个之前或最后一个趟次后出现的非营运------
                           else
                                 v_isSplit:='select O_configvalue from t_ct_singlecarrecord_rule t where o_xlmc='''||tsingle_last.v_lineName||''' and o_configno=6';
                                 begin
                                     execute immediate v_isSplit into isSplit;
                                     EXCEPTION
                                      WHEN NO_DATA_FOUND THEN
                                      stoptimerange:=0;
                                 end;
                                 -----一保----------
                                v_exe_sql3:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21''  and o_downline =''1301'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的

                                v_exe_sql4:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                    O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''1301'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的
                                 -----二保----------
                                v_exe_erbao1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''1302'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的

                                v_exe_erbao2:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                    O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''1302'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的
                                -----加油----------
                               v_exe_oil1:='select sum((O_SJDDSJC- O_SJFCSJC)/60),sum(o_yxgl) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''4'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的

                               v_exe_oil2:='select sum((O_SJDDSJC- O_SJFCSJC)/60),sum(o_yxgl)  from '||v_tabName||'
                                    where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                    O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''4'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的

                               -----暂停营运（停场待班）------
                               v_exe_stop1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''9'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的

                               v_exe_stop2:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                    O_clmc='''||tsingle_curr.v_carName||'''  and o_record_state=''21'' and o_downline =''9'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的
                                 -----一般公务（年检）---------
                               v_exe_public1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''319'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的

                               v_exe_public2:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                    O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline =''319'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的
                               -----专车------------
                               v_exe_public3:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                  where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||''' and
                                  O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21'' and o_downline=''311''  and
                                  O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                               v_exe_public4:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                        where o_rq='''||tsingle_curr.v_dateTime||''' and o_xlmc='''||tsingle_curr.v_lineName||'''and
                                        O_clmc='''||tsingle_curr.v_carName||''' and o_record_state=''21''  and o_downline=''311'' and O_SJDDSJc<= '''||tsingle_curr.v_factcarouttimec||'''';--最上面的


                               begin
                                     execute immediate v_exe_sql3 into ex_sumDibaos3;
                                     execute immediate v_exe_sql4 into ex_sumDibaos4;

                                     execute immediate v_exe_erbao1 into erbao1;
                                     execute immediate v_exe_erbao2 into erbao2;

                                     execute immediate v_exe_oil1 into oil1,ex_addoidgl1;
                                     execute immediate v_exe_oil2 into oil2,ex_addoidgl2;

                                     execute immediate v_exe_stop1 into stop1;
                                     execute immediate v_exe_stop2 into stop2;

                                     execute immediate v_exe_public1 into public1;
                                     execute immediate v_exe_public2 into public2;

                                     execute immediate v_exe_public3 into public3;
                                     execute immediate v_exe_public4 into public4;

                               end;

                                  --------------
                                  if ex_sumDibaos3>0then
                                        select o_lowmend into v_ex_sumDibaos3 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_ex_sumDibaos3 is null then
                                           v_ex_sumDibaos3:=0;
                                        end if;

                                        update t_ct_singlecarrecord set o_lowmend=v_ex_sumDibaos3+round(ex_sumDibaos3,0),o_remark='一保'--更新或添加每辆车最后所跑趟次后出现的低保修车(倒数第二趟后+最后一趟次后出现的低保)
                                        where o_id=tsingle_last.id;
                                  end if;
                                  if  ex_sumDibaos4>0 then
                                        update t_ct_singlecarrecord set o_lowmend=round(ex_sumDibaos4,0),o_remark='一保'--更新或添加每辆车第一趟次之前出现的低保修车
                                        where o_id=tsingle_curr.id;
                                  end if;
                                  ----------------
                                  if erbao1>0then
                                        select o_lowmend into v_erbao1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_erbao1 is null then
                                           v_erbao1:=0;
                                        end if;

                                        update t_ct_singlecarrecord set o_lowmend=v_erbao1+round(erbao1,0),o_remark='二保'
                                        where o_id=tsingle_last.id;
                                  end if;
                                  if  erbao2>0 then
                                        update t_ct_singlecarrecord set o_lowmend=round(erbao2,0),o_remark='二保'
                                        where o_id=tsingle_curr.id;
                                  end if;
                                  -------------
                                  if  ex_addoidgl1>0 then--更新加油里程
                                            select o_addoilkm into v_oilgl1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                              if v_oilgl1 is null then
                                                 v_oilgl1:=0;
                                              end if;

                                              update t_ct_singlecarrecord set o_addoilkm=v_oilgl1+ex_addoidgl1/1000
                                              where o_id=tsingle_last.id;
                                  end if;
                                  if  ex_addoidgl2>0 then
                                     update t_ct_singlecarrecord set  o_addoilkm=ex_addoidgl2/1000
                                     where o_id=tsingle_curr.id;
                                  end if;

                                  if isSplit=1 then--isSplit==1时 加油不用更新
                                         if oil1>0 then
                                             select o_stopstationtime into o_stop from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                              if o_stop is null then
                                                 o_stop:=0;
                                              end if;
                                            update t_ct_singlecarrecord set  o_remark='添加燃料',o_stopstationtime=stopstationTimes,o_addoil=o_stop+oil1
                                            where o_id=tsingle_last.id;
                                         end if;
                                         if oil2>0then
                                              select o_stopstationtime into o_stop from  t_ct_singlecarrecord where o_id=tsingle_curr.id;
                                              if o_stop is null then
                                                 o_stop:=0;
                                              end if;
                                            update t_ct_singlecarrecord set  o_remark='添加燃料',o_addoil=o_stop+oil2
                                            where o_id=tsingle_curr.id;
                                         end if;
                                  else
                                       if oil1>0then
                                              select o_addoil into v_oil1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                              if v_oil1 is null then
                                                 v_oil1:=0;
                                              end if;

                                              update t_ct_singlecarrecord set o_addoil=v_oil1+round(oil1,0)
                                              where o_id=tsingle_last.id;
                                        end if;
                                        if  oil2>0 then
                                              update t_ct_singlecarrecord set o_addoil=round(oil2,0)
                                              where o_id=tsingle_curr.id;
                                        end if;
                                  end if;
                                  ---------------
                                  if stop1>0then
                                        select o_stopstationwait into v_stop1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_stop1 is null then
                                           v_stop1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set o_stopstationwait=v_stop1+round(stop1,0)
                                        where o_id=tsingle_last.id;
                                  end if;

                                  if  stop2>0 then

                                        update t_ct_singlecarrecord set o_stopstationwait=round(stop2,0)
                                        where o_id=tsingle_curr.id;
                                  end if;
                                  ---------------
                                  if public1>0then
                                        select o_carpublic into v_public1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_public1 is null then
                                           v_public1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set  o_carpublic=v_public1+round(public1,0),o_remark='年检'
                                        where o_id=tsingle_last.id;
                                  end if;
                                  if  public2>0 then

                                        update t_ct_singlecarrecord set o_carpublic=round(public2,0),o_remark='年检'
                                        where o_id=tsingle_curr.id;
                                  end if;
                                  ---------------
                                  if public3>0then
                                        select o_carpublic into v_public1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_public1 is null then
                                           v_public1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set  o_carpublic=v_public1+round(public3,0),o_remark='专车'
                                        where o_id=tsingle_last.id;
                                  end if;
                                  if  public4>0 then

                                        update t_ct_singlecarrecord set o_carpublic=round(public4,0),o_remark='专车'
                                        where o_id=tsingle_curr.id;
                                  end if;
                           end if;
                               tsingle_last := tsingle_curr;--计算完成成把当前记录更新为上一趟的记录
                    end loop;
                                ------------（3）----最后一辆车(最后趟次后出现的非营运)比较---------
                                v_isSplit:='select O_configvalue from t_ct_singlecarrecord_rule t where o_xlmc='''||tsingle_last.v_lineName||''' and o_configno=6';
                                 begin
                                     execute immediate v_isSplit into isSplit;
                                      EXCEPTION
                                      WHEN NO_DATA_FOUND THEN
                                      stoptimerange:=0;
                                 end;

                                ---------一保-----
                                v_exe_sql3:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21''  and o_downline =''1301'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                                -----二保-------
                                v_exe_erbao1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''1302'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                                -----加油-------
                               v_exe_oil1:='select sum((O_SJDDSJC- O_SJFCSJC)/60),sum(o_yxgl) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''4'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                               -----暂停营运（停场待班）--
                               v_exe_stop1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''9'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                               -----一般公务（年检）-------
                               v_exe_public1:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                                    where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||'''and
                                    O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline =''319'' and O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的
                               -----特殊公务（专车）-----
                               v_exe_public3:='select sum((O_SJDDSJC- O_SJFCSJC)/60) from '||v_tabName||'
                               where o_rq='''||tsingle_last.v_dateTime||''' and o_xlmc='''||tsingle_last.v_lineName||''' and
                               O_clmc='''||tsingle_last.v_carName||''' and o_record_state=''21'' and o_downline=''311''  and
                               O_SJFCSJc>= '''||tsingle_last.v_FACTCARINTIMEC ||'''';--最下面的


                                 begin
                                     execute immediate v_exe_sql3 into ex_sumDibaos3;
                                     execute immediate v_exe_erbao1 into erbao1;
                                     execute immediate v_exe_oil1 into oil1,oil1gl;
                                     execute immediate v_exe_stop1 into stop1;
                                     execute immediate v_exe_public1 into public1;
                                     execute immediate v_exe_public3 into public3;
                                 end;

                                  if ex_sumDibaos3>0then
                                        select o_lowmend into v_ex_sumDibaos3 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_ex_sumDibaos3 is null then
                                           v_ex_sumDibaos3:=0;
                                        end if;

                                        update t_ct_singlecarrecord set o_lowmend=v_ex_sumDibaos3+round(ex_sumDibaos3,0),o_remark='一保'--更新或添加每辆车最后所跑趟次后出现的低保修车(倒数第二趟后+最后一趟次后出现的低保)
                                        where o_id=tsingle_last.id;
                                  end if;
                                  ---------------
                                  if erbao1>0then
                                        select o_lowmend into v_erbao1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_erbao1 is null then
                                           v_erbao1:=0;
                                        end if;

                                        update t_ct_singlecarrecord set o_lowmend=v_erbao1+round(erbao1,0),o_remark='二保'
                                        where o_id=tsingle_last.id;
                                  end if;
                                  -------------
                                  if  oil1gl>0 then--更新加油公里
                                             select o_addoilkm into v_oilgl1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                              if v_oilgl1 is null then
                                                 v_oilgl1:=0;
                                              end if;

                                              update t_ct_singlecarrecord set o_addoilkm=v_oilgl1+ oil1gl/1000
                                              where o_id=tsingle_last.id;
                                  end if;

                                  if isSplit=1 then--isSplit==1时 加油不用更新
                                      if oil1>0 then
                                            select o_stopstationtime into o_stop from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                              if o_stop is null then
                                                 o_stop:=0;
                                              end if;
                                           update t_ct_singlecarrecord set  o_remark='添加燃料',o_stopstationtime=stopstationTimes,o_addoil=o_stop+oil1
                                           where o_id=tsingle_last.id;
                                      end if;
                                  else
                                      if oil1>0then
                                            select o_addoil into v_oil1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                            if v_oil1 is null then
                                               v_oil1:=0;
                                            end if;

                                            update t_ct_singlecarrecord set o_addoil=v_oil1+round(oil1,0)
                                            where o_id=tsingle_last.id;
                                      end if;
                                   end if;
                                  ---------------
                                 if stop1>0then

                                        select o_stopstationwait into v_stop1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_stop1 is null then
                                           v_stop1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set o_stopstationwait=v_stop1+round(stop1,0)
                                        where o_id=tsingle_last.id;
                                  end if;
                                  ---------------
                                 if public1>0then
                                        select o_carpublic into v_public1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_public1 is null then
                                           v_public1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set  o_carpublic=v_public1+round(public1,0),o_remark='年检'
                                        where o_id=tsingle_last.id;
                                  end if;
                                  ---------------
                                 if public3>0then
                                        select o_carpublic into v_public1 from  t_ct_singlecarrecord where o_id=tsingle_last.id;
                                        if v_public1 is null then
                                           v_public1:=0;
                                        end if;
                                        update t_ct_singlecarrecord set  o_carpublic=v_public1+round(public3,0),o_remark='专车'
                                        where o_id=tsingle_last.id;
                                  end if;
               close v_cursor2;
               ---------------------------------------------3.求发车间隔----------------------------------------------------------
               avg_sql:='select o_id,o_factcarouttime,o_linename,o_fx from t_ct_singlecarrecord where O_datetime='''||v_eOneDate||''' and o_stationout=''1'' order by o_linename,o_fx,O_factcarouttimec';
               begin
                 open v_cursorAvgcurr for avg_sql;
                  fetch v_cursorAvgcurr into tdmavg_last1;--取第一条
                    loop
                          fetch v_cursorAvgcurr into tdmavg_curr1;--从第二条开始
                          EXIT WHEN v_cursorAvgcurr%NOTFOUND;--游标找不到数据后退出循环
                          if tdmavg_last1.o_linename=tdmavg_curr1.o_linename and tdmavg_last1.v_fx=tdmavg_curr1.v_fx then--线路和运行方向 相同时
                            avgtime:=round(ORACLE_TO_UNIX(to_date(tdmavg_curr1.v_factCarOutTime, 'hh24:mi:ss'))/60,0)-round(ORACLE_TO_UNIX(to_date(tdmavg_last1.v_factCarOutTime, 'hh24:mi:ss'))/60,0);
                            update t_ct_singlecarrecord set o_startcarspace=avgtime where o_id=tdmavg_curr1.id;--更新发车间隔
                          end if;

                          tdmavg_last1:=tdmavg_curr1;--计算完成成把当前记录更新为上一趟的记录
                    end loop;
                    close  v_cursorAvgcurr;
               end;
               ---------------------------------------------4.更新进出场公里-------------------------------------------------
                declare cursor v_inoutcursor is select distinct(O_busname) from t_dm_busid order by O_busname;
                begin
                  open v_inoutcursor;
                  loop
                    fetch v_inoutcursor into v_inoutcarname;
                    EXIT WHEN v_inoutcursor%NOTFOUND;
                       v_exe_inout := 'select sum(O_yxgl) from '||v_tabName||' where o_rq='''||v_eOneDate||''' and O_clmc=:1 and O_downline=''5'' and  o_record_state=''21''';
                       execute immediate v_exe_inout
                       into v_inout_yxgl
                       using v_inoutcarname;

                       if v_inout_yxgl>0 then
                          update t_ct_singlecarrecord set o_outinstationkm=v_inout_yxgl/1000 where O_datetime=v_eOneDate and O_carname=v_inoutcarname and o_carhyp=1;
                       end if;

                  end loop;
                  close v_inoutcursor;
               end;
            ---------------------------------------------------------------------------------------------------------------
      end;
      commit;--提交
end singlecarrecord_pro;