mtll2.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);
     	vsite_count       number(2);
        v_count           number(2);
     	vno               number(10);
     	vbz               char(2);
     	vremark           char(5);
        vtime             char(14);
begin
        tag:=0;
     	vvno:=1;
        if iagency_no='02' then 
        	valarm_time:=substr(ltrim(rtrim(ialarm_time)),1,18); 
        	valarm_time:=to_char(to_date(valarm_time,'dd/mm/yyyyhh24:mi:ss'),
                'yyyymmddhh24miss');
  	end if;
 	if iagency_no='03' then
		valarm_time:=substr(ltrim(rtrim(ialarm_time)),1,14);
        end if;

        if ltrim(rtrim(ialarm_site_name))='jizhanname' then
                tag:=4; 
        else
     		select nvl(count(city_no),0)
      		into vsite_count
      		from para_alarm_site_no
     		where 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')
                        where alarm_site_no=vno
                        and  alarm_name_no=ltrim(rtrim(ialarm_name_no));
                        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) into vtime from alarm
						where alarm_site_no=vvno
                				and alarm_name_no=ltrim(rtrim(ialarm_name_no));
               					if ltrim(rtrim(vtime))<ltrim(rtrim(valarm_time)) then
                       					update alarm set alarm_time=to_date(valarm_time,'yyyymmddhh24miss')
                       					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
					tag:=1;
     				end if;
			end if;
		end if;
	end if;
end mtll2;
/