峰谷划分(PLSQL版)

seawavenews

浏览: 235485 次
性别:
来自: 杭州

最近访客更多访客>>

songhait

OldLearnSheep

theanswerli

newpear8794

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (209)

社区版块

存档分类

Create Or Replace Function Get_Strarraylength
(
Str1 In Varchar2,--要分割的字符串
Split In Varchar2 --分隔符号
) Return Number Is --返回Number类型
Location Number;
Startstr Number;
Length Number;
str Varchar2(1024);
Begin

Str := Ltrim(Rtrim(Str1));
Location := Instr(Str, Split);
Length := 1;

While Location <> 0 Loop
  Startstr := Location + 1;
  Location := Instr(Str, Split, Startstr);
  Length   := Length + 1;
End Loop;
Return(Length);
End Get_Strarraylength;

-----------测试-----------------------------------------
Declare
Str      Varchar2(20);
Split    Varchar2(10);
Startstr Number;
Length   Number;
Location Number;
nextstr Number;
seed Number;
indexstr Number;
Begin

Str      := 'aaaaa|aaa|a';
Split    := '|';
   startstr:=1;
   nextstr:=1;
   seed:=length(split);
Location := Instr(Str, Split); --charindex

While Location <> 0 Loop
  Startstr := Location + 1;
  Location := Instr(Str, Split, Startstr);
  Length   := Length + 1;
End Loop;
Dbms_Output.Put_Line(Length);
End;
-----------------------------------------------------
Create Or Replace Function Get_StrArrayStrOfIndex
(
Str1      In Varchar2,--要分割的字符串
Split    In Varchar2,--分隔符号
Indexstr In Number --取第几个元素
) Return Varchar2 Is
str Varchar(1024);
Startstr Number;
Location Number;
Nextstr Number;
Seed     Number;
Begin

Str := Ltrim(Rtrim(Str1));
Startstr := 1;
Nextstr := 1;
Seed := Length(Split);
Location := Instr(Str, Split); --charindex

While Location <> 0 And Indexstr > Nextstr Loop
  Startstr := Location + Seed;
  Location := Instr(Str, Split, Startstr);
  Nextstr := Nextstr + 1;
End Loop;
If Location = 0 Then
  Location := Length(Str) + 1;
End If;
Return(Substr(Str, Startstr, Location - Startstr));

End;
--------------------test-------------------------------------
Declare
intyear Number;
intmonth Number;
period_id Number;
strType Number;

strTime Varchar2(20);
ostrTag Number;
peak Varchar2(100);
middle Varchar2(100);
valley Varchar2(100);
peak1 Varchar2(20);
middle1 Varchar2(20);
valley1 Varchar2(20);
nextstr Number;

hour Number;
mint Number;
Year_ Char(4);
Month_ Char(2);
fullminit Varchar2(2);

Begin
intyear:=2006;
intmonth:=12;
period_id:=95;
strType:=30;
hour:=trunc((period_id*15)/60);
mint:=(period_id*15) Mod 60;
fullminit:=to_char(mint);
If mint<10 Then
fullminit:='0'||fullminit;
End If;
   dbms_output.put_line(fullminit);--
strTime:=to_char(hour)||':'||fullminit;
     dbms_output.put_line(strTime);--
year_:=to_char(intyear);
month_:=to_char(intmonth);
ostrTag:=0;
Select SYS_PERIOD_PEAK,SYS_PERIOD_MID,SYS_PERIOD_VAL Into peak,middle,valley
    From GBOSS.SYS_PERIOD_TYPE Where SYS_APP_ID=strType and year=year_ and month=month_;
peak:=replace(RTRIM(LTRIM(peak)),':','');
middle:=replace(RTRIM(LTRIM(middle)),':','');
valley:=replace(RTRIM(LTRIM(valley)),':','');
strTime:=replace(RTRIM(LTRIM(strTime)),':','');
dbms_output.put_line(peak||','||strTime);----
--处理Peak
nextstr:=1;
While nextstr<=Get_StrArrayLength(peak,'|') Loop
      peak1:=Get_StrArrayStrOfIndex(peak,'|',nextstr);
      If substr(peak1,1,1)='[' And substr(peak1,length(peak1),1)=']' Then
         If to_number(strtime)>=to_number(substr(peak1,2,instr(peak1,'-')-2)) And
         to_number(strtime)<=to_number(substr(peak1,instr(peak1,'-')+1,length(peak1)-instr(peak1,'-')-1)) Then
         ostrtag:=1;
         End If;
      End If;
      If substr(peak1,1,1)='[' And substr(peak1,length(peak1),1)=')' Then
         If to_number(strtime)>=to_number(substr(peak1,2,instr(peak1,'-')-2)) And
         to_number(strtime)<to_number(substr(peak1,instr(peak1,'-')+1,length(peak1)-instr(peak1,'-')-1)) Then
         ostrtag:=1;
         End If;
      End If;
      If substr(peak1,1,1)='(' And substr(peak1,length(peak1),1)=']' Then
         If to_number(strtime)>to_number(substr(peak1,2,instr(peak1,'-')-2)) And
         to_number(strtime)<=to_number(substr(peak1,instr(peak1,'-')+1,length(peak1)-instr(peak1,'-')-1)) Then
         ostrtag:=1;
         End If;
      End If;
     If substr(peak1,1,1)='(' And substr(peak1,length(peak1),1)=')' Then
         If to_number(strtime)>to_number(substr(peak1,2,instr(peak1,'-')-2)) And
         to_number(strtime)<to_number(substr(peak1,instr(peak1,'-')+1,length(peak1)-instr(peak1,'-')-1)) Then
         ostrtag:=1;
         End If;
      End If;
      nextstr:=nextstr+1;
End Loop;
--处理middle
nextstr:=1;
While ostrtag=0 And nextstr<=Get_StrArrayLength(middle,'|') Loop
      middle1:=Get_StrArrayStrOfIndex(middle,'|',nextstr);
      If substr(middle1,1,1)='[' And substr(middle1,length(middle1),1)=']' Then
         If to_number(strtime)>=to_number(substr(middle1,2,instr(middle1,'-')-2)) And
         to_number(strtime)<=to_number(substr(middle1,instr(middle1,'-')+1,length(middle1)-instr(middle1,'-')-1)) Then
         ostrtag:=2;
         End If;
      End If;
      If substr(middle1,1,1)='[' And substr(middle1,length(middle1),1)=')' Then
         If to_number(strtime)>=to_number(substr(middle1,2,instr(middle1,'-')-2)) And
         to_number(strtime)<to_number(substr(middle1,instr(middle1,'-')+1,length(middle1)-instr(middle1,'-')-1)) Then
         ostrtag:=2;
         End If;
      End If;
      If substr(middle1,1,1)='(' And substr(middle1,length(middle1),1)=']' Then
         If to_number(strtime)>to_number(substr(middle1,2,instr(middle1,'-')-2)) And
         to_number(strtime)<=to_number(substr(middle1,instr(middle1,'-')+1,length(middle1)-instr(middle1,'-')-1)) Then
         ostrtag:=2;
         End If;
      End If;
     If substr(middle1,1,1)='(' And substr(middle1,length(middle1),1)=')' Then
         If to_number(strtime)>to_number(substr(middle1,2,instr(middle1,'-')-2)) And
         to_number(strtime)<to_number(substr(middle1,instr(middle1,'-')+1,length(middle1)-instr(middle1,'-')-1)) Then
         ostrtag:=2;
         End If;
      End If;
      nextstr:=nextstr+1;
End Loop;
--处理valley
nextstr:=1;
While ostrtag=0 And nextstr<=Get_StrArrayLength(valley,'|') Loop
      valley1:=Get_StrArrayStrOfIndex(valley,'|',nextstr);
      If substr(valley1,1,1)='[' And substr(valley1,length(valley1),1)=']' Then
         If to_number(strtime)>=to_number(substr(valley1,2,instr(valley1,'-')-2)) And
         to_number(strtime)<=to_number(substr(valley1,instr(valley1,'-')+1,length(valley1)-instr(valley1,'-')-1)) Then
         ostrtag:=3;
         End If;
      End If;
      If substr(valley1,1,1)='[' And substr(valley1,length(valley1),1)=')' Then
         If to_number(strtime)>=to_number(substr(valley1,2,instr(valley1,'-')-2)) And
         to_number(strtime)<to_number(substr(valley1,instr(valley1,'-')+1,length(valley1)-instr(valley1,'-')-1)) Then
         ostrtag:=3;
         End If;
      End If;
      If substr(valley1,1,1)='(' And substr(valley1,length(valley1),1)=']' Then
         If to_number(strtime)>to_number(substr(valley1,2,instr(valley1,'-')-2)) And
         to_number(strtime)<=to_number(substr(valley1,instr(valley1,'-')+1,length(valley1)-instr(valley1,'-')-1)) Then
         ostrtag:=3;
         End If;
      End If;
     If substr(valley1,1,1)='(' And substr(valley1,length(valley1),1)=')' Then
         If to_number(strtime)>to_number(substr(valley1,2,instr(valley1,'-')-2)) And
         to_number(strtime)<to_number(substr(valley1,instr(valley1,'-')+1,length(valley1)-instr(valley1,'-')-1)) Then
         ostrtag:=3;
         End If;
      End If;
      nextstr:=nextstr+1;
End Loop;
dbms_output.put_line(ostrtag);
End;

-------------------------------------------------------------------
Create Or Replace Function Procgettagf
(
Intyear   In Number,--年
Intmonth In Number,--月
Period_Id In Number,--时间段
Strtype   In Number--应用类型
) Return Number Is

Strtime Varchar2(20);
Ostrtag Number;
Peak Varchar2(100);
Middle Varchar2(100);
Valley Varchar2(100);
Peak1 Varchar2(20);
Middle1 Varchar2(20);
Valley1 Varchar2(20);
Nextstr Number;

Hour      Number;
Mint      Number;
Year_     Char(4);
Month_    Char(2);
Fullminit Varchar2(2);

Begin
--intyear:=2006;
--intmonth:=12;
--period_id:=95;
--strType:=30;
Hour      := Trunc((Period_Id * 15) / 60);
Mint      := (Period_Id * 15) Mod 60;
Fullminit := To_Char(Mint);
If Mint < 10 Then
  Fullminit := '0' || Fullminit;
End If;
Dbms_Output.Put_Line(Fullminit); --
Strtime := To_Char(Hour) || ':' || Fullminit;
Dbms_Output.Put_Line(Strtime); --
Year_   := To_Char(Intyear);
Month_ := To_Char(Intmonth);
Ostrtag := 0;
Select Sys_Period_Peak, Sys_Period_Mid, Sys_Period_Val
Into Peak, Middle, Valley
From Gboss.Sys_Period_Type
Where Sys_App_Id = Strtype And Year = Year_ And Month = Month_;
Peak    := Replace(Rtrim(Ltrim(Peak)), ':', '');
Middle := Replace(Rtrim(Ltrim(Middle)), ':', '');
Valley := Replace(Rtrim(Ltrim(Valley)), ':', '');
Strtime := Replace(Rtrim(Ltrim(Strtime)), ':', '');
Dbms_Output.Put_Line(Peak || ',' || Strtime); ----
--处理Peak
Nextstr := 1;
While Nextstr <= Get_Strarraylength(Peak, '|') Loop
  Peak1 := Get_Strarraystrofindex(Peak, '|', Nextstr);
  If Substr(Peak1, 1, 1) = '[' And Substr(Peak1, Length(Peak1), 1) = ']' Then
   If To_Number(Strtime) >= To_Number(Substr(Peak1, 2, Instr(Peak1, '-') - 2)) And
     To_Number(Strtime) <= To_Number(Substr(Peak1, Instr(Peak1, '-') + 1, Length(Peak1) - Instr(Peak1, '-') - 1)) Then
    Ostrtag := 1;
   End If;
  End If;
  If Substr(Peak1, 1, 1) = '[' And Substr(Peak1, Length(Peak1), 1) = ')' Then
   If To_Number(Strtime) >= To_Number(Substr(Peak1, 2, Instr(Peak1, '-') - 2)) And
     To_Number(Strtime) < To_Number(Substr(Peak1, Instr(Peak1, '-') + 1, Length(Peak1) - Instr(Peak1, '-') - 1)) Then
    Ostrtag := 1;
   End If;
  End If;
  If Substr(Peak1, 1, 1) = '(' And Substr(Peak1, Length(Peak1), 1) = ']' Then
   If To_Number(Strtime) > To_Number(Substr(Peak1, 2, Instr(Peak1, '-') - 2)) And
     To_Number(Strtime) <= To_Number(Substr(Peak1, Instr(Peak1, '-') + 1, Length(Peak1) - Instr(Peak1, '-') - 1)) Then
    Ostrtag := 1;
   End If;
  End If;
  If Substr(Peak1, 1, 1) = '(' And Substr(Peak1, Length(Peak1), 1) = ')' Then
   If To_Number(Strtime) > To_Number(Substr(Peak1, 2, Instr(Peak1, '-') - 2)) And
     To_Number(Strtime) < To_Number(Substr(Peak1, Instr(Peak1, '-') + 1, Length(Peak1) - Instr(Peak1, '-') - 1)) Then
    Ostrtag := 1;
   End If;
  End If;
  Nextstr := Nextstr + 1;
End Loop;
--处理middle
Nextstr := 1;
While Ostrtag = 0 And Nextstr <= Get_Strarraylength(Middle, '|') Loop
  Middle1 := Get_Strarraystrofindex(Middle, '|', Nextstr);
  If Substr(Middle1, 1, 1) = '[' And Substr(Middle1, Length(Middle1), 1) = ']' Then
   If To_Number(Strtime) >= To_Number(Substr(Middle1, 2, Instr(Middle1, '-') - 2)) And
     To_Number(Strtime) <=
     To_Number(Substr(Middle1, Instr(Middle1, '-') + 1, Length(Middle1) - Instr(Middle1, '-') - 1)) Then
    Ostrtag := 2;
   End If;
  End If;
  If Substr(Middle1, 1, 1) = '[' And Substr(Middle1, Length(Middle1), 1) = ')' Then
   If To_Number(Strtime) >= To_Number(Substr(Middle1, 2, Instr(Middle1, '-') - 2)) And
     To_Number(Strtime) <
     To_Number(Substr(Middle1, Instr(Middle1, '-') + 1, Length(Middle1) - Instr(Middle1, '-') - 1)) Then
    Ostrtag := 2;
   End If;
  End If;
  If Substr(Middle1, 1, 1) = '(' And Substr(Middle1, Length(Middle1), 1) = ']' Then
   If To_Number(Strtime) > To_Number(Substr(Middle1, 2, Instr(Middle1, '-') - 2)) And
     To_Number(Strtime) <=
     To_Number(Substr(Middle1, Instr(Middle1, '-') + 1, Length(Middle1) - Instr(Middle1, '-') - 1)) Then
    Ostrtag := 2;
   End If;
  End If;
  If Substr(Middle1, 1, 1) = '(' And Substr(Middle1, Length(Middle1), 1) = ')' Then
   If To_Number(Strtime) > To_Number(Substr(Middle1, 2, Instr(Middle1, '-') - 2)) And
     To_Number(Strtime) <
     To_Number(Substr(Middle1, Instr(Middle1, '-') + 1, Length(Middle1) - Instr(Middle1, '-') - 1)) Then
    Ostrtag := 2;
   End If;
  End If;
  Nextstr := Nextstr + 1;
End Loop;
--处理valley
Nextstr := 1;
While Ostrtag = 0 And Nextstr <= Get_Strarraylength(Valley, '|') Loop
  Valley1 := Get_Strarraystrofindex(Valley, '|', Nextstr);
  If Substr(Valley1, 1, 1) = '[' And Substr(Valley1, Length(Valley1), 1) = ']' Then
   If To_Number(Strtime) >= To_Number(Substr(Valley1, 2, Instr(Valley1, '-') - 2)) And
     To_Number(Strtime) <=
     To_Number(Substr(Valley1, Instr(Valley1, '-') + 1, Length(Valley1) - Instr(Valley1, '-') - 1)) Then
    Ostrtag := 3;
   End If;
  End If;
  If Substr(Valley1, 1, 1) = '[' And Substr(Valley1, Length(Valley1), 1) = ')' Then
   If To_Number(Strtime) >= To_Number(Substr(Valley1, 2, Instr(Valley1, '-') - 2)) And
     To_Number(Strtime) <
     To_Number(Substr(Valley1, Instr(Valley1, '-') + 1, Length(Valley1) - Instr(Valley1, '-') - 1)) Then
    Ostrtag := 3;
   End If;
  End If;
  If Substr(Valley1, 1, 1) = '(' And Substr(Valley1, Length(Valley1), 1) = ']' Then
   If To_Number(Strtime) > To_Number(Substr(Valley1, 2, Instr(Valley1, '-') - 2)) And
     To_Number(Strtime) <=
     To_Number(Substr(Valley1, Instr(Valley1, '-') + 1, Length(Valley1) - Instr(Valley1, '-') - 1)) Then
    Ostrtag := 3;
   End If;
  End If;
  If Substr(Valley1, 1, 1) = '(' And Substr(Valley1, Length(Valley1), 1) = ')' Then
   If To_Number(Strtime) > To_Number(Substr(Valley1, 2, Instr(Valley1, '-') - 2)) And
     To_Number(Strtime) <
     To_Number(Substr(Valley1, Instr(Valley1, '-') + 1, Length(Valley1) - Instr(Valley1, '-') - 1)) Then
    Ostrtag := 3;
   End If;
  End If;
  Nextstr := Nextstr + 1;
End Loop;
Dbms_Output.Put_Line(Ostrtag);
Return(Ostrtag);
End;