峰谷划分(PLSQL版)

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;