hwei2.sql

linux下c 编写的 某项目代理服务端部分的代码。其中包括网络通信

drop procedure mtll2
/
create or replace procedure mtll2
(
        iagency_no        IN char,
        ialarm_status_no  IN char,
	ialarm_site_name  IN char,
        ialarm_name_no    IN char,
        ialarm_time       IN char,
     	icity_no          IN char,
     	tag              OUT number,
     	vvno             OUT number
)
as
        valarm_time       char(18);
        vmax_alarm_time   char(18);
     	vsite_count       number(2);
        valarm_status_no  char(1);
        v_count           number(2);
     	vno               number(10);
     	vbz               char(2);
     	vremark           char(5);
        vtime             char(14);
begin
        tag:=0;
     	vvno:=1;
	valarm_time:=substr(ltrim(rtrim(ialarm_time)),1,18);
	valarm_time:=to_char(to_date(valarm_time,'yyyy-mm-ddhh24:mi:ss'),
               'yyyymmddhh24miss');

     	select nvl(count(city_no),0)
      	into vsite_count
      	from para_alarm_site_no
     	where city_no=icity_no
        and name=ltrim(rtrim(ialarm_site_name));
     	if vsite_count=0 then
       		select seq_alarm_site_no.nextval into vno from dual;
         	insert into para_alarm_site_no (city_no,agency_no,alarm_site_no,name,bound)
          	values(ltrim(rtrim(icity_no)),ltrim(rtrim(iagency_no)),vno,ltrim(rtrim(ialarm_site_name)),60);
          	insert into alarm(city_no,agency_no,alarm_site_no,alarm_level_no,
           	alarm_status_no,alarm_name_no,alarm_time,cancel_time,bz)
           	select ltrim(rtrim(icity_no)),ltrim(rtrim(iagency_no)),vno,
           	ltrim(rtrim(alarm_level_no)),'2',ltrim(rtrim(alarm_name_no)),NULL,NULL,'0'
           	from para_alarm_name_no where alarm_name_no<>'0';
                commit;

                update alarm set alarm_time=to_date(ltrim(rtrim(valarm_time)),'yyyymmddhh24miss'),
				 alarm_status_no='1'
                where alarm_name_no=ltrim(rtrim(ialarm_name_no))
		and alarm_site_no=vno;
                if ltrim(rtrim(ialarm_name_no)) in ('00','01')  then
                	if ltrim(rtrim(ialarm_name_no))='00' then
                             	update alarm set 
                               	alarm_time=to_date(ltrim(rtrim(valarm_time)),'yyyymmddhh24miss')+2/(24*60)
                               	where alarm_site_no=vno
                               	and  alarm_name_no<>'00';
			else                       
                                update alarm set
                                alarm_time=to_date(ltrim(rtrim(valarm_time)),'yyyymmddhh24miss')+2/(24*60)
                                where alarm_site_no=vno
                                and alarm_name_no in('02','03','08','09');
                        end if;
                end if;
          		tag:=2;
          		vvno:=vno;
     	else
		select alarm_site_no,nvl(bz,0) 
        	into vvno,vbz
        	from para_alarm_site_no
        	where name=ltrim(rtrim(ialarm_site_name));
               	if ltrim(rtrim(vbz))<>'2' then
                   	tag:=2;
		else
            		if ialarm_status_no='1' then
                		select nvl(count(alarm_name_no),0) into vsite_count from alarm
                        	where alarm_site_no=vvno
                        	and alarm_name_no=ltrim(rtrim(ialarm_name_no))
                        	and alarm_time=to_date(ltrim(rtrim(valarm_time)),'yyyymmddhh24miss');
                        	if vsite_count=0 then
                			select nvl(to_char(alarm_time,'yyyymmddhh24miss'),0),alarm_status_no 
					into vtime,valarm_status_no from alarm
                			where alarm_site_no=vvno
					and alarm_name_no=ltrim(rtrim(ialarm_name_no));
               				if valarm_status_no<>'1' and ltrim(rtrim(vtime))<ltrim(rtrim(valarm_time)) then
                       				update alarm set alarm_time=to_date(valarm_time,'yyyymmddhh24miss'),
						alarm_status_no='1'
                       				where alarm_site_no=vvno
                       				and alarm_name_no=ltrim(rtrim(ialarm_name_no));
                               			if ltrim(rtrim(ialarm_name_no))='00' then
                               				update alarm set 
                               				alarm_time=to_date(valarm_time,'yyyymmddhh24miss')+2/(24*60)
                               				where alarm_site_no=vvno
                               				and  alarm_name_no<>'00';
                               			end if;
                            			if ltrim(rtrim(ialarm_name_no))='01' then
                                			update alarm set 
                                			alarm_time=to_date(valarm_time,'yyyymmddhh24miss')+2/(24*60)
                               				where alarm_site_no=vvno
                               				and alarm_name_no in('02','03','08','09');
                               			end if;
                        			tag:=1;
                        		end if;
         			end if;
			else
				select nvl(to_char(alarm_time,'yyyymmddhh24miss'),'0'),alarm_status_no
                        	into vmax_alarm_time,valarm_status_no
                        	from alarm
                        	where alarm_site_no=vvno
                        	and alarm_name_no=ltrim(rtrim(ialarm_name_no));
                        	if valarm_status_no='1' and vmax_alarm_time<=valarm_time then
                                	update alarm set alarm_status_no='2'
                                	where alarm_site_no=vvno
                                	and alarm_name_no=ltrim(rtrim(ialarm_name_no));
				end if;	
				tag:=1;
     			end if;
                end if;
	end if;
end mtll2;
/