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;
