这是STAT_AND_ANALYSE包下的存储过程 procedure stat_gear_box_lugdown(v_start_date in date, --开始检测时间 v_end_date in date, --结束检测时间 v_time_stamp in varchar2) is arr_lugdown tbl_lugdown; arr_onetimepassed tbl_total; arr_passed tbl_total; begin --统计污染物黑烟排放的平均含量, 检测车辆总数 select --/*+ parallel(ap 4)*/ a.gear_box_type, nvl(round(sum(a.smoke_day_sum)/sum(inspection_count), 2),0), sum(a.inspection_count), count(distinct a.vehicle_tag_no ¦ ¦a.undercarriage_no) bulk collect into arr_lugdown from mv_lugdown_verify_result a where a.inspection_date >= v_start_date and a.inspection_date < v_end_date and a.gear_box_type in (select b.criteria from stat_search_criteria b where id = v_time_stamp) group by a.gear_box_type;
--统计一次上线检测就能合格的总车辆次数 select --/*+ parallel(a 4)*/ a.gear_box_type, count(a.onetime_passed) bulk collect into arr_onetimepassed from mv_lugdown_passed a where a.inspection_date >= v_start_date and a.inspection_date < v_end_date and a.gear_box_type in (select b.criteria from stat_search_criteria b where id = v_time_stamp) and a.onetime_passed = 'Y' group by a.gear_box_type;
--总合格车辆数, 拿最后检测为准 select --/*+ parallel(ap 4)*/ ap.gear_box_type, count(*) bulk collect into arr_passed from mv_lugdown_passed ap, (select --/*+ parallel(ap 4)*/ max(a.inspection_date) inspection_date, a.id_vehicle from mv_lugdown_passed a where a.inspection_date >= v_start_date and a.inspection_date < v_end_date and a.gear_box_type in (select b.criteria from stat_search_criteria b where id = v_time_stamp) group by a.id_vehicle) ab where ap.id_vehicle = ab.id_vehicle and ap.inspection_date = ab.inspection_date and ap.passed = 'Y' group by ap.gear_box_type;
