当先锋百科网

首页 1 2 3 4 5 6 7

这几天写的存储过程语句进行备份下

      
CREATE OR REPLACE PACKAGE scjk_gaugingstation
 ---Author: lshizhan
    ---created:2013-07-23
    ---Purpose:提供水位站实时水位（河道站点、水库站点、水文站点）
IS
PROCEDURE  RiverwayRealTimeWaterLevel(
       --主要用来查询河道水情的实时信息
       --站点编号
       stcd1  in varchar2,
       --今日8时时间
       eightTime  in varchar2,
       ------开始时间
       beginTime  in varchar2,
       ------结束时间
       endTime    in varchar2,
       ---最高水位
       maxZ      out  number,
       ---最低水位
       minZ      out number,
       ---8点水位
       eightTimeZ out number,
       --实时水位
       realTimeZ  out number,
         --数据库入库时间
       updatetime       out  varchar2
       );

PROCEDURE   RiverwayHistroyMaxandMin(
       --主要用来查询河道水情历史最高和最低水位
       stcd1  in varchar2,
       --开始时间
       beginTime in varchar2,
       --结束时间
       endTime  in varchar2,
       ---最高水位
       maxQ      out  number,
       ---最低水位
       minQ      out number);
       
  PROCEDURE ReservoirwayRealTimeWaterLevel(
       --主要用来查询水库水情的实时信息
       --站点编号
       stcd1  in varchar2,
       --今日8时时间
       eightTime  in varchar2,
       ------开始时间
       beginTime  in varchar2,
       ------结束时间
       endTime    in varchar2,
       ---最高水位
       maxZ      out  number,
       ---最低水位
       minZ      out number,
       ---8点水位
       eightTimeZ out number,
       --实时水位
       realTimeZ  out number,
         --数据库入库时间
       updatetime       out  varchar2
       );
  PROCEDURE ReservoirwayHistroyMaxandMin(
       --主要用来查询水库水情历史最高和最低水位
       stcd1  in varchar2,
       --开始时间
       beginTime in varchar2,
       --结束时间
       endTime  in varchar2,
       ---最高水位
       maxQ      out  number,
       ---最低水位
       minQ      out number);
end scjk_gaugingstation;

CREATE OR REPLACE PACKAGE  BODY  scjk_gaugingstation
IS  
PROCEDURE  RiverwayRealTimeWaterLevel(
       --站点编号
       stcd1  in varchar2,
       --今日8时时间
       eightTime  in varchar2,
       ------开始时间
       beginTime  in varchar2,
       ------结束时间
       endTime    in varchar2,
       ---最高水位
       maxZ      out  number,
       ---最低水位
       minZ      out number,
       ---8点水位
       eightTimeZ out number,
        --实时水位
       realTimeZ  out number,
       --数据库入库时间
       updatetime       out  varchar2)
   is
     v_maxZ  number:=-1000000;
     v_minZ  number:=-1000000;
     v_eightZ number:=-1000000;
     v_realTimeZ number:=-1000000;
     v_updateTime varchar2(50);
     v_eightNum  number:=0;
     v_realNum   number:=0;
   begin
       --查询某个时间段内最低，最高水位
       select   nvl(min(t1.z),-1000000),nvl(max(t1.z),-1000000) into v_minZ,v_maxZ  from   st_river_r  t1
        where ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm>= to_date(beginTime,'YYYY-MM-DD HH24:MI:SS')
        and t1.tm<= to_date(endTime,'YYYY-MM-DD HH24:MI:SS');

        minZ := v_minZ;
        maxZ:=  v_maxZ;

        --查询8点水位
        --查询是否有八点水位的数据
        select count(*) into v_eightNum from  st_river_r  t1
        where  ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm = to_date(eightTime,'YYYY-MM-DD HH24:MI:SS');
        
        if(v_eightNum = 0) then
           v_eightZ := -1000000;
        else
        --查询8点水位
        select t1.z into v_eightZ from  st_river_r  t1
        where  ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm = to_date(eightTime,'YYYY-MM-DD HH24:MI:SS');
        end if;
        eightTimeZ:=v_eightZ;

        --查询是否有实时水位的数据
        select count(*) into v_realNum from (select t1.z,t1.tm  from  st_river_r t1
         where ltrim(rtrim(t1.stcd))= stcd1
            order by t1.tm desc ) where ROWNUM =1;
        
        if(v_realNum = 0) then
           v_realTimeZ := -1000000;
        else
         --查询实时水位
        select nvl(z,-1000000),to_char(tm,'YYYY-MM-DD HH24:MI:SS') into v_realTimeZ,v_updateTime from (select t1.z,t1.tm  from  st_river_r t1
         where ltrim(rtrim(t1.stcd))= stcd1
            order by t1.tm desc ) where ROWNUM =1;
        end if;
        realTimeZ:=v_realTimeZ;
        updateTime:=v_updateTime;
 exception
    when no_data_found then
           dbms_output.put_line('error');
 end;


 procedure RiverwayHistroyMaxandMin(
       stcd1  in varchar2,
       --开始时间
       beginTime in varchar2,
       --结束时间
       endTime  in varchar2,
       ---最高水位
       maxQ      out  number,
       ---最低水位
       minQ      out number)
   is
      v_maxQ number:=0;
      v_minQ number:=0;
  begin
       if(beginTime is null or endTime is null)  then
       --查询最低，最高水位
       select   nvl(min(t1.z),-100000),nvl(max(t1.z),-100000) into v_minQ,v_maxQ from   st_river_r  t1
       where ltrim(rtrim(t1.stcd))= stcd1 ;

        else
          --根据时间段查询最低水位
            select    nvl(min(t1.z),-100000),nvl(max(t1.z),-100000) into v_minQ,v_maxQ from   st_river_r  t1
             where ltrim(rtrim(t1.stcd))= stcd1
             and t1.tm>=to_date(beginTime,'YYYY-MM-DD HH24:MI:SS')
              and t1.tm<=to_date(endTime,'YYYY-MM-DD HH24:MI:SS');
        end if;

        maxQ:=v_maxQ;
        minQ:=v_minQ;
  exception
    when no_data_found then
           dbms_output.put_line('error');
 end;
 
 PROCEDURE ReservoirwayRealTimeWaterLevel(
       --主要用来查询水库水情的实时信息
        --站点编号
       stcd1  in varchar2,
       --今日8时时间
       eightTime  in varchar2,
       ------开始时间
       beginTime  in varchar2,
       ------结束时间
       endTime    in varchar2,
       ---最高水位
       maxZ      out  number,
       ---最低水位
       minZ      out number,
       ---8点水位
       eightTimeZ out number,
        --实时水位
       realTimeZ  out number,
       --数据库入库时间
       updatetime       out  varchar2)
   is
     v_maxZ  number:=-1000000;
     v_minZ  number:=-1000000;
     v_eightZ number:=-1000000;
     v_realTimeZ number:=-1000000;
     v_updateTime varchar2(50);
     v_eightNum  number:=0;
     v_realNum   number:=0;
   begin
       --查询某个时间段内最低，最高水位
       select   nvl(min(t1.rz),-1000000),nvl(max(t1.rz),-1000000) into v_minZ,v_maxZ  from   ST_RSVR_R  t1
        where ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm>= to_date(beginTime,'YYYY-MM-DD HH24:MI:SS')
        and t1.tm<= to_date(endTime,'YYYY-MM-DD HH24:MI:SS');

        minZ := v_minZ;
        maxZ:=  v_maxZ;

        --查询8点水位
        --查询是否有八点水位的数据
        select count(*) into v_eightNum from  ST_RSVR_R  t1
        where  ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm = to_date(eightTime,'YYYY-MM-DD HH24:MI:SS');
        
        if(v_eightNum = 0) then
           v_eightZ := -1000000;
        else
        --查询8点水位
        select t1.rz into v_eightZ from  ST_RSVR_R  t1
        where  ltrim(rtrim(t1.stcd))= stcd1
        and t1.tm = to_date(eightTime,'YYYY-MM-DD HH24:MI:SS');
        end if;
        eightTimeZ:=v_eightZ;

        --查询是否有实时水位的数据
        select count(*) into v_realNum from (select t1.rz,t1.tm  from  ST_RSVR_R t1
         where ltrim(rtrim(t1.stcd))= stcd1
            order by t1.tm desc ) where ROWNUM =1;
        
        if(v_realNum = 0) then
           v_realTimeZ := -1000000;
        else
         --查询实时水位
        select nvl(rz,-1000000),to_char(tm,'YYYY-MM-DD HH24:MI:SS') into v_realTimeZ,v_updateTime from (select t1.rz,t1.tm  from  ST_RSVR_R t1
         where ltrim(rtrim(t1.stcd))= stcd1
            order by t1.tm desc ) where ROWNUM =1;
        end if;
        realTimeZ:=v_realTimeZ;
        updateTime:=v_updateTime;
 exception
    when no_data_found then
           dbms_output.put_line('error');
 end;
 
 PROCEDURE ReservoirwayHistroyMaxandMin(
       --主要用来查询历史最高和最低水位
        stcd1  in varchar2,
       --开始时间
       beginTime in varchar2,
       --结束时间
       endTime  in varchar2,
       ---最高水位
       maxQ      out  number,
       ---最低水位
       minQ      out number)
   is
      v_maxQ number:=0;
      v_minQ number:=0;
  begin
       if(beginTime is null or endTime is null)  then
       --查询最低，最高水位
       select   nvl(min(t1.rz),-100000),nvl(max(t1.rz),-100000) into v_minQ,v_maxQ from   ST_RSVR_R  t1
       where ltrim(rtrim(t1.stcd))= stcd1 ;

        else
          --根据时间段查询最低水位
            select    nvl(min(t1.rz),-100000),nvl(max(t1.rz),-100000) into v_minQ,v_maxQ from   ST_RSVR_R  t1
             where ltrim(rtrim(t1.stcd))= stcd1
             and t1.tm>=to_date(beginTime,'YYYY-MM-DD HH24:MI:SS')
              and t1.tm<=to_date(endTime,'YYYY-MM-DD HH24:MI:SS');
        end if;

        maxQ:=v_maxQ;
        minQ:=v_minQ;
  exception
    when no_data_found then
           dbms_output.put_line('error');
 end;
END  scjk_gaugingstation;


 

create or replace function getCountryAndCity return str_list
--返回所有县/市的名称
is
       o_data   str_list:=str_list();
       cursor c is
           select distinct(ADDVCD) from ST_ADDVCD_D;
       i        number:=1;
begin
    open c;
       loop
       o_data.extend();
          fetch c into o_data(i);
             exit when(c%notfound);
           i:=i+1;
       end loop;

   return o_data;
end getCountryAndCity;


 

create or replace function getRainSumByAddvcd (
     --统计各个县市某段时间内的降雨总量
     --市县编号
     addvcd1 in varchar2,
     --开始时间
     beginTime in varchar2,
     --结束时间
     endTime   in varchar2
)
return number
is
       o_data   str_list:=str_list();
       cursor c is
           select  t.stcd from st_stbprp_b t 
                where  rtrim(ltrim(t.addvcd))=rtrim(ltrim(addvcd1));
       i        number:=1;
       drpsum   number:=0;
       temp     number:=0;
begin
    --用游标获取县、市下面的站点
    open c;
       loop
         o_data.extend();
            fetch c into o_data(i);
               exit when(c%notfound);
             i:=i+1;
       end loop;
    
    --遍历站点，取得每个站点的降雨量
    FOR i IN 1 .. o_data.count LOOP
       --如果没有开始时间或者结束时间
       if(beginTime is null or endTime is null) then
           return 0;
       else
       --查询这段时间内的降雨总量
          select  getRainSumByStationId(o_data(i),beginTime,endTime) 
          into temp  from   dual;
           drpsum:=drpsum+temp;
       end if;
    END LOOP ;
   return drpsum;
end getRainSumByAddvcd;


 

 

create or replace function getRainSumByStationId (
     --统计各个县市某段时间内的降雨总量
     --市县编号
     stcd1 in varchar2,
     --开始时间
     beginTime in varchar2,
     --结束时间
     endTime   in varchar2
)
return number
is
     drpSum  number:=0;
begin
       --如果没有开始时间或者结束时间
       if(beginTime is null or endTime is null) then
           return 0;
       else
       --查询这段时间内的降雨总量
          select  nvl(sum(r.drp),0) into drpSum from ST_PPTN_R r
              where rtrim(ltrim(r.stcd))=rtrim(ltrim(stcd1))
              and r.tm>=to_date(beginTime,'YYYY-MM-DD HH24:MI:SS') 
              and r.tm<=to_date(endTime,'YYYY-MM-DD HH24:MI:SS');
       end if;
   return drpSum;
end getRainSumByStationId;


 

 

create or replace procedure getRainFallMessage(
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --返回市、县列表
    citys  out str_list,
    --返回市、县降雨总量
    sums   out num_list
)
is
   v_citys   str_list:=str_list();
   v_sums    num_list:=num_list();
   v_cityNames str_list:=str_list();
   v_hasCountory  number:=0;
begin
   --获取所有县/市的行政区划编码
   select getCountryAndCity() into v_citys from dual;
   --遍历，获取每个县/市的降雨总量
   for i in 1 .. v_citys.count loop
      select count(*)  into  v_hasCountory from st_addvcd_d t where  
           rtrim(ltrim(t.addvcd))= rtrim(ltrim(v_citys(i)));
      if(v_hasCountory<>0)  then
         v_sums.extend();
         v_cityNames.extend();
         --取得县/市名称
         select t.addvnm into v_cityNames(i) 
              from st_addvcd_d t where  
               rtrim(ltrim(t.addvcd))= rtrim(ltrim(v_citys(i)));
         --取得降雨量
         select getRainSumByAddvcd(v_citys(i),beginTime,endTime)
           into v_sums(i) from dual;
         dbms_output.put_line(v_cityNames(i) ||' ' || v_sums(i));
       end if;
   end loop;
   
   citys:=v_cityNames;
   sums:=v_sums;
end;


 

 

create or replace procedure getStationRainFallMessage(
--获取站点的时段降雨总量和经纬度
    --站点编号
    stcd1     in varchar2,
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --站点总降雨量
    drpSum    out  number,
    --站点经度
    jingdu    out varchar2,
    --站点纬度
    weidu     out  varchar2
)
is
    v_drpSum  number:=0;
    v_weidu   number:=0;
    v_jingdu  number:=0;
begin
     --获取该站点的降雨总量
     select getRainSumByStationId(stcd1,beginTime,endTime)
        into v_drpSum from dual;
     drpSum:=v_drpSum;
     
     begin
     --获取该站点的经度和纬度
     select  t.lgtd into v_jingdu from st_stbprp_b t 
     where t.stcd =stcd1;
         EXCEPTION WHEN NO_DATA_FOUND THEN
              v_jingdu:=-10000;
     end;
     
     begin
     select  t.lttd into v_weidu from st_stbprp_b t 
     where t.stcd =stcd1;
         EXCEPTION WHEN NO_DATA_FOUND THEN
              v_weidu:=-10000;
     end;      
     jingdu:=v_jingdu;
     weidu:=v_weidu;
end;


 

CREATE OR REPLACE PACKAGE SCJK_RAINFALLMESSAGE is
  ---Author: lshizhan
    ---created:2013-07-23
    ---Purpose:提供降雨量情况的查询，包括县/市降雨量和站点降雨量
 procedure getRainFallMessage(
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --返回市、县列表
    citys  out str_list,
    --返回市、县降雨总量
    sums   out num_list,
    cityHasStation out num_list
);
procedure getStationRainFallMessage(
--获取站点的时段降雨总量和经纬度
    --站点编号
    stcd1     in varchar2,
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --站点总降雨量
    drpSum    out  number,
    --站点经度
    jingdu    out number,
    --站点纬度
    weidu     out  number,
    --返回站点所在的区域
    location  out varchar
);
end  SCJK_RAINFALLMESSAGE;



CREATE OR REPLACE PACKAGE  BODY  SCJK_RAINFALLMESSAGE
IS
procedure getRainFallMessage(
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --返回市、县列表
    citys  out str_list,
    --返回市、县降雨总量
    sums   out num_list,
    cityHasStation out num_list
)
is
   v_citys   str_list:=str_list();
   v_sums    num_list:=num_list();
   v_cityNames str_list:=str_list();
   v_cityHasStation  num_list:=num_list();
   v_hasCountory  number:=0;
begin
   --获取所有县/市的行政区划编码
   select getCountryAndCity() into v_citys from dual;
   --遍历，获取每个县/市的降雨总量
   for i in 1 .. v_citys.count loop
      select count(*)  into  v_hasCountory from st_addvcd_d t where
           rtrim(ltrim(t.addvcd))= rtrim(ltrim(v_citys(i)));
      if(v_hasCountory<>0)  then
         v_sums.extend();
         v_cityNames.extend();
         v_cityHasStation.extend();
         --取得县/市名称
         select t.addvnm into v_cityNames(i)
              from st_addvcd_d t where
               rtrim(ltrim(t.addvcd))= rtrim(ltrim(v_citys(i)));
         --取得县/市下面有几个降雨量统计站点
         select count(*)   into v_cityHasStation(i)
                from  st_stbprp_b t where
             rtrim(ltrim(t.addvcd))= rtrim(ltrim(v_citys(i)));
         --取得降雨量
         select getRainSumByAddvcd(v_citys(i),beginTime,endTime)
           into v_sums(i) from dual;
         dbms_output.put_line(v_cityNames(i) ||' ' || v_sums(i));
       end if;
   end loop;

   citys:=v_cityNames;
   sums:=v_sums;
   cityHasStation:=v_cityHasStation;
end;
procedure getStationRainFallMessage(
--获取站点的时段降雨总量和经纬度
    --站点编号
    stcd1     in varchar2,
    --开始时间
    beginTime in varchar2,
    --结束时间
    endTime   in varchar2,
    --站点总降雨量
    drpSum    out  number,
    --站点经度
    jingdu    out number,
    --站点纬度
    weidu     out  number,
    --返回站点所在的区域
    location  out varchar
)
is
    v_drpSum  number:=0;
    v_weidu   number:=0;
    v_jingdu  number:=0;
    v_location varchar(50);
begin
     --获取该站点的降雨总量
     select getRainSumByStationId(stcd1,beginTime,endTime)
        into v_drpSum from dual;
     drpSum:=v_drpSum;

     begin
     --获取该站点的经度和纬度
     select  t.lgtd into v_jingdu from st_stbprp_b t
     where   rtrim(ltrim(t.stcd))=rtrim(ltrim(stcd1));
         EXCEPTION WHEN NO_DATA_FOUND THEN
              v_jingdu:=-10000;
     end;

     begin
     --获取站点所在区域
       select t2.addvnm into v_location from  st_stbprp_b t1,ST_ADDVCD_D t2
       where  rtrim(ltrim(t1.stcd))=rtrim(ltrim(stcd1)) and
         rtrim(ltrim(t1.addvcd))=rtrim(ltrim(t2.addvcd));
       EXCEPTION WHEN NO_DATA_FOUND THEN
             v_location:='';
     end;
     begin
     --获取纬度
     select  t.lttd into v_weidu from st_stbprp_b t
     where  rtrim(ltrim(t.stcd))=rtrim(ltrim(stcd1));
         EXCEPTION WHEN NO_DATA_FOUND THEN
              v_weidu:=-10000;
     end;
     jingdu:=v_jingdu;
     weidu:=v_weidu;
     location:=v_location;
end;
END  SCJK_RAINFALLMESSAGE;


 

 

CREATE OR REPLACE PACKAGE scjk_ReservoirReportForms
 ---Author: lshizhan
    ---created:2013-07-27
    ---Purpose:提供水库综合报表
is
procedure getReservoirReportForms(
      --水库编号
      stcds    in    str_list,
      --水库名称列表
      names    out   str_list,
      --水库实时水位列表
      shuiweis  out   num_list,
      --水库实时降雨列表
      jiangyus  out   num_list,
      --水库库容
      kurongs   out   num_list
);
end  scjk_ReservoirReportForms;

CREATE OR REPLACE PACKAGE  BODY scjk_ReservoirReportForms
is
procedure getReservoirReportForms(
      --水库编号
      stcds    in    str_list,
      --水库名称列表
      names    out   str_list,
      --水库实时水位列表
      shuiweis  out   num_list,
      --水库实时降雨列表
      jiangyus  out   num_list,
      --水库库容
      kurongs   out   num_list
)
is
    v_names   str_list:=str_list();
    v_shuiweis  num_list:=num_list();
    v_jiangyus  num_list:=num_list();
    v_kurongs   num_list:=num_list();
begin
   --遍历水库编号获取信息
   for i in 1.. stcds.count loop
     v_names.extend();
     v_shuiweis.extend();
     v_jiangyus.extend();
     v_kurongs.extend();

     begin
       --获取水库名称
       select  t1.stnm into v_names(i) from st_stbprp_b t1
       where  rtrim(ltrim(t1.stcd)) =  rtrim(ltrim(stcds(i)));
       EXCEPTION WHEN NO_DATA_FOUND THEN
             v_names(i):='';
     end;
     begin
        --获取水库实时水位
        select rz into v_shuiweis(i)
         from (select t1.rz  from  ST_RSVR_R t1
           where ltrim(rtrim(t1.stcd))= stcds(i)
            order by t1.tm desc ) where ROWNUM =1;
      EXCEPTION WHEN NO_DATA_FOUND THEN
             v_shuiweis(i):=-10000;
      end;

      begin
        --获取水库实时库容
        select w into v_kurongs(i)
         from (select t1.w  from  ST_RSVR_R t1
           where ltrim(rtrim(t1.stcd))= stcds(i)
            order by t1.tm desc ) where ROWNUM =1;
      EXCEPTION WHEN NO_DATA_FOUND THEN
             v_kurongs(i):=-10000;
      end;

      begin
        --获取水库实时雨量
        select drp into v_jiangyus(i)
         from (select t1.drp  from  ST_PPTN_R t1
           where ltrim(rtrim(t1.stcd))= stcds(i)
            order by t1.tm desc ) where ROWNUM =1;
      EXCEPTION WHEN NO_DATA_FOUND THEN
             v_jiangyus(i):=-10000;
      end;

   end loop;

   names:=v_names;
   shuiweis:=v_shuiweis;
   jiangyus:=v_jiangyus;
   kurongs:=v_kurongs;
end;
end  scjk_ReservoirReportForms;