nokdata.sql

来自「linux下c 编写的 某项目代理服务端部分的代码。其中包括网络通信」· SQL 代码 · 共 43 行

create or replace PACKAGE nokia_pack AS
      type out_new_fx_alarm_type is ref cursor;
      type out_fx_alarm_type  is ref cursor;
      PROCEDURE nokia_get_notcancel(out_fx_alarm in out out_fx_alarm_type,outreturn out char);
      PROCEDURE nokia_get_new(out_new_fx_alarm in out out_fx_alarm_type,outreturn out char);
END nokia_pack;
/
create or replace PACKAGE BODY nokia_pack AS

      PROCEDURE nokia_get_notcancel(out_fx_alarm in out out_fx_alarm_type,outreturn out char)is
      cur_count number(4);
      BEGIN
          outreturn:='1000';
          OPEN out_fx_alarm  FOR SELECT to_char(consec_nbr),to_char(int_id),
                 alarm_status,to_char(a.alarm_time,'yyyymmddhh24miss'),
                 (case when a.cancel_time is null then '0' else to_char(a.cancel_time,'yyyymmddhh24miss')  end),
                 alarm_number,nvl(severity,0),name,'000',123 from v_fx_alarm a, alarm b
                 where b.cancel_time is null and a.consec_nbr=b.alarm_csn;
                 select count(consec_nbr) into cur_count from v_fx_alarm a, alarm b
                 where b.cancel_time is null and a.consec_nbr=b.alarm_csn;
                 if cur_count=0 then
                 outreturn:='2000';
                 end if;
      END;
      PROCEDURE nokia_get_new(out_new_fx_alarm in out out_fx_alarm_type,outreturn out char)is
      max_csn  number(10);
      cur_count number(4);
      BEGIN
                 outreturn:='1000';
         select nvl(max(alarm_csn),0) into max_csn from alarm_env;
         OPEN out_new_fx_alarm FOR SELECT to_char(consec_nbr),to_char(int_id),
                 ltrim(rtrim(alarm_status)),to_char(alarm_time,'yyyymmddhh24miss'),
                 (case when cancel_time is null then '0' else to_char(cancel_time,'yyyymmddhh24miss')  end),
                 trim(rtrim(alarm_number)),nvl(severity,0),trim(rtrim(name)),'111',123 from v_fx_alarm where consec_nbr>max_csn;
              select count(consec_nbr) into cur_count from v_fx_alarm where consec_nbr>max_csn;
                 if cur_count=0 then
                 	outreturn:='2000';
                 end if;
      END;
END nokia_pack;
    
/