oracle小例子

《oracle小例子》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、createtriggerInsert_Vehicleafterinsertont_Vehicle_dforeachrowdeclareDEVICE_NOT_EXISTEXCEPTION;/*统计表字段*/strDcodevarchar2(12);nVehicleCountnumber;--新的车流量数据nAccidentCountnumber;--新违章车辆数strPassDatetimevarchar2(19);--存放经过时间取到小时/*报警匹配表字段*/nDmrpIdnumber;--循环标识nMatchBitnumber;--匹配度nMa

2、tchCountnumber;--匹配的数量strCarLicensevarchar2(15);--布控的车牌strAccidentLicensevarchar2(15);--需要比较的车牌strMontiorCodechar(4);--卡口代码strErrorvarchar2(100);CURSORcur_suspectisselectt_dmrp_id,match_bit,Car_Licensefromt_dmrp_bwhereINSTR(strAccidentLicense,substr(car_license,1,5))>0orINSTR(

3、strAccidentLicense,substr(car_license,2,5))>0orINSTR(strAccidentLicense,SUBSTR(car_license,3,5))>0;BEGIN--统计流量beginif:new.accident_type>0thennAccidentCount:=1;nVehicleCount:=0;elsenAccidentCount:=0;nVehicleCount:=1;endif;SELECTdept_codeINTOstrDcodeFROMt_device_id_bWHEREdevice_

4、id=@NEW.device_id;--部门--dbms_output.put_line(@new.pass_datetime);strPassDatetime:=to_char(to_date(@new.pass_datetime,'YYYY-MM-DDHH24@MI@SS'),'YYYY-MM-DDHH24');UPDATEt_monitor_total_dsetvehicle_count=vehicle_count+nVehicleCount,accident_vehicle_count=accident_vehicle_count+nAcc

5、identCountWHEREtrim(device_id)=trim(@new.device_id)andtrim(direction_id)=trim(@new.direction_id)ANDto_char(total_time,'YYYY-MM-DDHH24')liketrim(strPassDatetime)

6、

7、'%';ifsql%notfoundthenraiseDEVICE_NOT_EXIST;endif;exceptionwhenDEVICE_NOT_EXISTtheninsertintoT_Monitor_Total_D(devi

8、ce_id,dcode,direction_id,vehicle_count,accident_vehicle_count,Total_Time)values(@new.device_id,strDcode,@new.direction_id,nVehicleCount,nAccidentCount,@new.pass_datetime);whenothersthennull;end;/*报警匹配*/strAccidentLicense:=@new.car_license;selectmonitor_codeintostrMontiorCodefr

9、omt_device_id_bwheretrim(device_id)=trim(@new.device_id);OPENcur_suspect;FETCHcur_suspectINTOnDmrpId,nMatchBit,strCarLicense;LOOPnMatchCount:=0;EXITWHENcur_suspect%NOTFOUND;ifnMatchBit<5thennMatchBit:=5;endif;casenMatchBitwhen7thenselectINSTR(strAccidentLicense,SUBSTR(strCarLi

10、cense,1,7))intonMatchCountfromdual;--全匹配when6thenselectINSTR(