job调用的存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[statistics_perf_hour]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[statistics_perf_hour] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE procedure statistics_perf_hour as declare @vCurTime datetime--=getdate(),--当前时间，通常为sysdate declare @vStaBeginTime datetime--统计开始时间 declare @vStaEndTime datetime--统计结束时间 declare @vInHour smallint--统计开始时间的小时 declare @vInDay smallint--统计开始时间是星期几，周日为0，周一为1 declare @vInDate smallint--统计开始时间的天 set @vCurTime = getdate() /*计算统计的起止时间*/ set @vStaEndTime=@vCurTime set @vStaBeginTime=dateadd(hour,-1,@vCurTime) set @vInHour=datepart(hour,@vStaBeginTime) set @vInDay=datepart(day,@vStaBeginTime) set @vInDate=datepart(weekday,@vStaBeginTime) /*perf_ne:================================================*/ insert into perf_ne_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, ne_memory_usage,ne_cache_usage,ne_allcpu_usage,ne_in_traffic,ne_out_traffic,ne_in_pkts,ne_out_pkts,ne_in_errpkts,ne_out_errpkts,ne_loss,ne_error,ne_in_usage,ne_out_usage,ne_in_multicast_pkts,ne_out_multicast_pkts) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(ne_memory_usage), avg(ne_cache_usage), avg(ne_allcpu_usage), sum(ne_in_traffic), sum(ne_out_traffic), sum(ne_in_pkts), sum(ne_out_pkts), sum(ne_in_errpkts), sum(ne_out_errpkts), avg(ne_loss), avg(ne_error), avg(ne_in_usage), avg(ne_out_usage), sum(ne_in_multicast_pkts), sum(ne_out_multicast_pkts) from perf_ne where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_ne_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_ne'); /*perf_network_link:======================================*/ insert into perf_network_link_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, ne_if_traffic,ne_if_pkts,ne_if_errpkts,ne_ifloss,ne_iferror,ne_wan_usage,ne_if_multicast_pkts,anomal_flag,amend_traffic) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, sum(ne_if_traffic), sum(ne_if_pkts), sum(ne_if_errpkts), avg(ne_ifloss), avg(ne_iferror), avg(ne_wan_usage), sum(ne_if_multicast_pkts), round(avg(anomal_flag),1),--布尔值??? sum(amend_traffic)--待确认 from perf_network_link where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_network_link_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_network_link'); /*perf_ne_cpu:============================================*/ insert into perf_ne_cpu_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, ne_cpu_usage) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(ne_cpu_usage) from perf_ne_cpu where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_ne_cpu_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_ne_cpu'); /*perf_ne_port:===========================================*/ insert into perf_ne_port_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, ne_ifloss,ne_iferror,ne_wanin_usage,ne_wanout_usage,ne_if_in_traffic,ne_if_out_traffic,ne_if_in_pkts,ne_if_out_pkts,ne_if_in_errpkts,ne_if_out_errpkts,ne_if_in_multicast_pkts,ne_if_out_multicast_pkts) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(ne_ifloss), avg(ne_iferror), avg(ne_wanin_usage), avg(ne_wanout_usage), sum(ne_if_in_traffic), sum(ne_if_out_traffic), sum(ne_if_in_pkts), sum(ne_if_out_pkts), sum(ne_if_in_errpkts), sum(ne_if_out_errpkts), sum(ne_if_in_multicast_pkts), sum(ne_if_out_multicast_pkts) from perf_ne_port where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_ne_port_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_ne_port'); /*perf_pc_cpu:============================================*/ insert into perf_pc_cpu_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, pc_cpuidle_rate,pc_cpusys_rate,pc_cpuuser_rate,pc_cpuwait_rate,pc_cpu_usage,pc_cpurunprocess_num) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(pc_cpuidle_rate), avg(pc_cpusys_rate), avg(pc_cpuuser_rate), avg(pc_cpuwait_rate), avg(pc_cpu_usage), avg(pc_cpurunprocess_num) from perf_pc_cpu where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_pc_cpu_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_pc_cpu'); /*perf_pc_disk:===========================================*/ insert into perf_pc_disk_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, pc_diskio_velocity,pc_diskwait_process,pc_diskbusy_rate,pc_diskread_bps,pc_diskwrite_bps,pc_freedisk_percentage) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(pc_diskio_velocity), avg(pc_diskwait_process),--待确认 avg(pc_diskbusy_rate), avg(pc_diskread_bps), avg(pc_diskwrite_bps), avg(pc_freedisk_percentage) from perf_pc_disk where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_pc_disk_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_pc_disk'); /*perf_pc_server:=========================================*/ insert into perf_pc_server_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, pc_memory_usage,pc_pagerequest_num,pc_pagein_velocity,pc_pageout_velocity,pc_memoryquene_num,pc_sysmemory_usage,pc_usermemory_usage,pc_filesys_buffer,pc_swap_usage,pc_allcpu_usage,pc_filesys_usage) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(pc_memory_usage), avg(pc_pagerequest_num), avg(pc_pagein_velocity), avg(pc_pageout_velocity), avg(pc_memoryquene_num), avg(pc_sysmemory_usage), avg(pc_usermemory_usage), avg(pc_filesys_buffer), avg(pc_swap_usage), avg(pc_allcpu_usage), avg(pc_filesys_usage) from perf_pc_server where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_pc_server_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_pc_server'); /*perf_protocol_smtp:=====================================*/ insert into perf_protocol_smtp_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, mta_received_msgs,mta_stored_msgs,mta_transmitted_msgs,mta_received_volume,mta_stored_volume,mta_transmitted_volume,mta_received_recipients,mta_stored_recipients,mta_transmitted_recipients) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, sum(mta_received_msgs), avg(mta_stored_msgs), sum(mta_transmitted_msgs), sum(mta_received_volume), avg(mta_stored_volume), sum(mta_transmitted_volume), sum(mta_received_recipients), avg(mta_stored_recipients), sum(mta_transmitted_recipients) from perf_protocol_smtp where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_protocol_smtp_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_protocol_smtp'); /*perf_sec_firewall:======================================*/ insert into perf_sec_firewall_h (obj_id,start_time,stop_time,in_hour,in_day,in_date, fw_cur_connect,tun_active,tun_usage,fw_memory,fw_memory_free,fw_memory_usage) select obj_id,@vStaBeginTime,@vStaEndTime,@vInHour,@vInDay,@vInDate, avg(fw_cur_connect), avg(tun_active), avg(tun_usage), avg(fw_memory),--待确认 avg(fw_memory_free), avg(fw_memory_usage) from perf_sec_firewall where start_time>=@vStaBeginTime and stop_time<=@vStaEndTime group by obj_id; --delete from perf_sec_firewall_h where start_time>=vStaBeginTime and stop_time<=vStaEndTime; --dbms_output.put_line('perf_sec_firewall'); /*perf_sec_firewall_port:====