Oracle日期段資料統計

oracle 日期函數介紹:
在oracle中有很多關於日期的函數，如：
1、add_months()用於從一個日期值增加或減少一些月份
date_value:=add_months(date_value,number_of_months)
例：
SQL> select add_months(sysdate,12) "Next Year" from dual;
 
Next Year
----------
13-11月-04
 
SQL> select add_months(sysdate,112) "Last Year" from dual;
 
Last Year
----------
13-3月 -13
 
SQL> 
 
2、current_date()返回當前會放時區中的當前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;
 
SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00          13-11月-03
 
SQL> alter session set time_zone='-11:00'
   2  /
 
會話已更改。
 
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00          12-11月-03 04.59.13.668000 下午 -11:
                 00
 
SQL> 
 
3、current_timestamp()以timestamp with time zone資料類型返回當前會放時區中的當前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00          13-11月-03 11.56.28.160000 上午 +08:
                 00
 
SQL> alter session set time_zone='-11:00'
   2  /
 
會話已更改。
 
SQL> select sessiontimezone,current_timestamp from dual;
 
SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00          12-11月-03 04.58.00.243000 下午 -11:
                 00
 
SQL> 
 
4、dbtimezone()返回時區
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;
 
DBTIME
------
-07:00
 
SQL> 
 
5、extract()找出日期或間隔值的欄位值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual;
 
This Month
----------
         11
 
SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
 
3 Years Out
-----------
        2006
 
SQL> 
 
6、last_day()返回包含了日期參數的月份的最後一天的日期
date_value:=last_day(date_value)
SQL> select last_day(date'2000-02-01') "Leap Yr?" from dual;
 
Leap Yr?
----------
29-2月 -00
 
SQL> select last_day(sysdate) "Last day of this month" from dual;
 
Last day o
----------
30-11月-03
 
SQL> 
 
7、localtimestamp()返回會話中的日期和時間
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;
 
LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午
 
SQL> select localtimestamp,current_timestamp from dual;
 
LOCALTIMESTAMP               CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-11月-03 12.09.31.006000   13-11月-03 12.09.31.006000 下午 +08:
下午                         00

根據日期計算第幾周:

//計算第幾周
public int GetWeekOfCurrDate(DateTime dt)
        {
            int Week = 1;
            int nYear = dt.Year;
            System.DateTime FirstDayInYear = new DateTime(nYear, 1, 1);
            System.DateTime LastDayInYear = new DateTime(nYear, 12, 31);
            int DaysOfYear = Convert.ToInt32(LastDayInYear.DayOfYear);
            int WeekNow = Convert.ToInt32(FirstDayInYear.DayOfWeek) - 1;
            if (WeekNow < 0) WeekNow = 6;
            int DayAdd = 6 - WeekNow;
            System.DateTime BeginDayOfWeek = new DateTime(nYear, 1, 1);
            System.DateTime EndDayOfWeek = BeginDayOfWeek.AddDays(DayAdd);
            Week = 2;
            for (int i = DayAdd + 1; i <= DaysOfYear; i++)
            {
                BeginDayOfWeek = FirstDayInYear.AddDays(i);
                if (i + 6 > DaysOfYear)
                {
                    EndDayOfWeek = BeginDayOfWeek.AddDays(DaysOfYear - i - 1);
                }
                else
                {
                    EndDayOfWeek = BeginDayOfWeek.AddDays(6);
                }

                if (dt.Month == EndDayOfWeek.Month && dt.Day <= EndDayOfWeek.Day)
                {
                    break;
                }
                Week++;
                i = i + 6;
            }
            return Week;
        }

 

//本周是本年第幾周
private int DatePart(System.DateTime dt)
{
   int weeknow = Convert.ToInt32(dt.DayOfWeek);//今天星期幾
   int daydiff = (-1) * (weeknow+1);//今日與上周末的天數差
   int days = System.DateTime.Now.AddDays(daydiff).DayOfYear;//上周末是本年第幾天
   int weeks = days/7;
   if(days%7 != 0)
{
    weeks++;
   }
   //此時，weeks為上周是本年的第幾周
   return (weeks+1);
  }
//本周起止日期
 private string WeekRange(System.DateTime dt)
{
   int weeknow = Convert.ToInt32(dt.DayOfWeek);
   int daydiff = (-1) * weeknow;
   int dayadd = 6-weeknow;
   string dateBegin = System.DateTime.Now.AddDays(daydiff).Date.ToString("MM月dd日");
   string dateEnd = System.DateTime.Now.AddDays(dayadd).Date.ToString("MM月dd日");
   return dateBegin + " - " +dateEnd;
  }

 

擷取某年指定周的開始日期和結束日期的通用方法:

擷取某年指定周的開始日期和結束日期的通用方法
/**//// <summary>
/// 擷取一年中指定的一周的開始日期和結束日期。開始日期遵循ISO 8601即星期一。
/// </summary>
/// <remarks>Write by vrhero</remarks>
/// <param name="year">年（1 到 9999）</param>
/// <param name="weeks">周（1 到 53）</param>
/// <param name="weekrule">確定首周的規則</param>
/// <param name="first">當此方法返回時，則包含參數 year 和 weeks 指定的周的開始日期的 System.DateTime 值；如果失敗，則為 System.DateTime.MinValue。如果參數 year 或 weeks 超出有效範圍，則操作失敗。該參數未經初始化即被傳遞。</param>
/// <param name="last">當此方法返回時，則包含參數 year 和 weeks 指定的周的結束日期的 System.DateTime 值；如果失敗，則為 System.DateTime.MinValue。如果參數 year 或 weeks 超出有效範圍，則操作失敗。該參數未經初始化即被傳遞。</param>
/// <returns>成功返回 true，否則為 false。</returns>
public static bool GetDaysOfWeeks(int year, int weeks, CalendarWeekRule weekrule, out DateTime first, out DateTime last)
{
    //初始化 out 參數
    first = DateTime.MinValue;
    last = DateTime.MinValue;

    //不用解釋了吧
    if (year < 1 | year > 9999)
        return false;

    //一年最多53周地球人都知道
    if (weeks < 1 | weeks > 53)
        return false;

    //取當年首日為基準為什嗎？容易得唄
    DateTime firstCurr = new DateTime(year, 1, 1);
    //取下一年首日用於計算
    DateTime firstNext = new DateTime(year + 1, 1, 1);

    //將當年首日星期幾轉換為數字星期日特別處理ISO 8601 標準
    int dayOfWeekFirst = (int)firstCurr.DayOfWeek;
    if (dayOfWeekFirst == 0) dayOfWeekFirst = 7;

    //得到未經驗證的周首日
    first = firstCurr.AddDays((weeks - 1) * 7 - dayOfWeekFirst + 1);

    //周首日是上一年日期的情況
    if (first.Year < year)
    {
        switch (weekrule)
        {
            case CalendarWeekRule.FirstDay:
                //不用解釋了吧
                first = firstCurr;
                break;
            case CalendarWeekRule.FirstFullWeek:
                //順延一周
                first = first.AddDays(7);
                break;
            case CalendarWeekRule.FirstFourDayWeek:
                //周首日距年首日不足4天則順延一周
                if (firstCurr.Subtract(first).Days > 3)
                {
                    first = first.AddDays(7);
                }
                break;
            default:
                break;
        }
    }

    //得到未經驗證的周末日
    last = first.AddDays(7).AddSeconds(-1);

    //周末日是下一年日期的情況
    if (last.Year > year)
    {
        switch (weekrule)
        {
            case CalendarWeekRule.FirstDay:
                last = firstNext.AddSeconds(-1);
                break;
            case CalendarWeekRule.FirstFullWeek:
                //不用處理
                break;
            case CalendarWeekRule.FirstFourDayWeek:
                //周末日距下一年首日不足4天則提前一周
                if (firstNext.Subtract(first).Days < 4)
                {
                    first = first.AddDays(-7);
                    last = last.AddDays(-7);
                }
                break;
            default:
                break;
        }
    }
    return true;
}

 

關於按周統計資料:

關於按周統計資料(Oracle)
方法1：使用to_char函數

select sum(sal) ,
to_char(HIREDATE,'yyyy')||':'||to_char(HIREDATE,'IW') week_sn
from scott.emp
group by to_char(HIREDATE,'yyyy')||':'||to_char(HIREDATE,'IW');

格式'IW'返回當前日期是當年的第幾周

方法2：使用next_day()函數

select sum(sal) ,
NEXT_DAY(trunc(HIREDATE),'星期一')-7 weekstart,
NEXT_DAY(trunc(HIREDATE),'星期一') weekend
from scott.emp
group by NEXT_DAY(trunc(HIREDATE),'星期一');

上面這個例子是在中文字元集下使用，如果在英文字元集下則使用：

select sum(sal) ,
NEXT_DAY(trunc(HIREDATE),'Monday')-7 weekstart,
NEXT_DAY(trunc(HIREDATE),'Monday') weekend
from scott.emp
group by NEXT_DAY(trunc(HIREDATE),'Monday');

兩種方法比較，第二種方法能夠方便的給出周開始和周結束日期，更方便些。

 

暫存資料表:

需要建立一個暫存資料表，請舉例說明，謝謝！      
  ---------------------------------------------------------------      
     
  是TEMPORARY      
  CREATE     GLOBAL     TEMPORARY     TABLE     flight_schedule     (      
            startdate     DATE,          
            enddate     DATE,          
            cost     NUMBER)      
     
  ---------------------------------------------------------------      
     
  create     proecdure     name_pro      
  as      
  str     varchar2(100);      
  begin      
  str:='CREATE     GLOBAL     TEMPORARY     TABLE     TABLENAME     ON     COMMIT     PRESERVE     ROWS     as     select     *     from     others_table';      
  execute     immediate     str;      
  end;      
  /  
   
   
   
  可以把暫存資料表指定為事務相關（預設）或者是會話相關：  
  ON   COMMIT   DELETE   ROWS：指定暫存資料表是事務相關的，Oracle在每次提交後截斷表。  
  ON   COMMIT   PRESERVE   ROWS：指定暫存資料表是會話相關的，Oracle在會話中止後截斷表。  
   
  =================  
  可以建立以下兩種暫存資料表：  
  1。會話特有的暫存資料表  
        CREATE   GLOBAL   TEMPORARY   <TABLE_NAME>   (<column   specification>)  
        ON   COMMIT   PRESERVE   ROWS；  
  ========  
  對全域暫存資料表的總結  
   
  在暫存資料表上的操作比在一般的表上的操作要快。因為：  
  1建立暫存資料表不需要往編目表中插入條目，暫存資料表的使用也不需要訪問編目表，因此也沒有對編目表的爭用。  
  2僅有建立暫存資料表的app才可存取暫存資料表，所以在處理暫存資料表時沒有鎖。  
  3如果指定NOT   LOGGED選項，在處理暫存資料表時不記日誌。所以如果有僅在資料庫的一個會話中使用的大量臨時資料，把這些資料存入暫存資料表能大大提高效能。  
  DECLARE   GLOBAL   TEMPORARY   TABLE   TT(C1   INT,   C2   CHAR(20))；  
  在CONNECT   RESET命令後，暫存資料表不再存在。  
  建暫存資料表是動態編譯的，所以對暫存資料表的使用也必須放在DECLARE   CURSER   後面  
  CREATE   PROCEDURE   INSTT2(P1   INT,   P2   CHAR(20))  
  BEGIN  
      DECLARE   GLOBAL   TEMPORARY   TABLE   TT(C1   INT,   C2   CHAR(20))   %  
      INSERT   INTO   SESSION.TT   VALUES(P1,   P2);  
      BEGIN  
          DECLARE   C1   CURSOR   WITH   RETURN   FOR   SELECT   *   FROM   SESSION.TT;  
      END;  
  END   %  
   
  2。事務特有的暫存資料表  
        CREATE   GLOBAL   TEMPORARY   <TABLE_NAME>   (<column   specification>)  
        ON   COMMIT   DELETE   ROWS；  
       
      在Oracle中，全域暫存資料表並不會刪除，實際上你只需要建立一次，以後直接應用就行了，這與MS和Sybase不一樣。實際上在斷開資料庫連接時，暫存資料表中資料自動清空，不同的Session之間是隔離的，不許要當心相互影響，不過如果起用了串連共用的話，你要用On   Commit   delete   rows使資料僅在事物內部有效。

3建立暫存資料表  
        暫存資料表的定義對所有會話SESSION都是可見的,但是表中的資料只對當前的會話或者事務有效.    
        建立方法:  
  1)   ON   COMMIT   DELETE   ROWS   定義了建立事務級暫存資料表的方法.  
  CREATE   GLOBAL   TEMPORARY   TABLE   admin_work_area  
                  (startdate   DATE,  
                    enddate   DATE,  
                    class   CHAR(20))  
              ON   COMMIT   DELETE   ROWS;  
  EXAMPLE:  
  SQL>   CREATE   GLOBAL   TEMPORARY   TABLE   admin_work_area  
      2                     (startdate   DATE,  
      3                       enddate   DATE,  
      4                       class   CHAR(20))  
      5                 ON   COMMIT   DELETE   ROWS;  
  SQL>   create   table   permernate(   a   number);  
  SQL>   insert   into   admin_work_area   values(sysdate,sysdate,'temperary   table');  
  SQL>   insert   into   permernate   values(1);  
  SQL>   commit;  
  SQL>   select   *   from   admin_work_area;  
  SQL>   select     *   from   permernate;  
    A  
  1  
  2)ON   COMMIT   PRESERVE   ROWS   定義了建立會話級暫存資料表的方法.  
  CREATE   GLOBAL   TEMPORARY   TABLE   admin_work_area  
                  (startdate   DATE,  
                    enddate   DATE,  
                    class   CHAR(20))  
            ON   COMMIT   PRESERVE   ROWS;  
  EXAMPLE:  
   
  會話1:  
  SQL>   drop   table   admin_work_area;  
  SQL>   CREATE   GLOBAL   TEMPORARY   TABLE   admin_work_area  
      2                     (startdate   DATE,  
      3                       enddate   DATE,  
      4                       class   CHAR(20))  
      5               ON   COMMIT   PRESERVE   ROWS;  
  SQL>   insert   into   permernate   values(2);  
  SQL>   insert   into   admin_work_area   values(sysdate,sysdate,'session   temperary');  
  SQL>   commit;  
  SQL>   select   *   from   permernate;  
   
                    A  
  ----------  
                    1  
                    2  
   
  SQL>   select   *   from   admin_work_area;  
   
  STARTDATE     ENDDATE         CLASS  
  ----------   ----------   --------------------  
  17-1&Ocirc;&Acirc;   -03   17-1&Ocirc;&Acirc;   -03   session   temperary  
   
  會話2:  
   
  SQL>   select   *   from   permernate;  
   
                    A  
  ----------  
                    1  
                    2  
   
  SQL>   select   *   from   admin_work_area;  
   
    未選擇行.  
   
  會話2看不見會話1中暫存資料表的資料. 

 

根據上面的資訊實現預存程序：

CREATE OR REPLACE PACKAGE BODY TodayALL_zjz IS

  --第一層首頁統計資訊--資料
  PROCEDURE GetIndexTj(Index_OUT OUT MYCURSOR) AS
  BEGIN
    --單維數組
    DECLARE
      --
      fa integer;
      --
      pa integer;
      --
      zhtf integer;
      --
      djclry integer;
      --
      kywp integer;
      --
      blzz integer;
      --
      lgzs integer;
   
      -- mydate  date;
      strdate  varchar(8);
      strdate1 varchar(12);
      strdate2 varchar(12);
   
    begin
      strdate  := TO_CHAR(SYSDATE, 'YYYYMMDD');
      strdate1 := TO_CHAR(SYSDATE, 'YYYYMMDD') + '0000';
      strdate2 := TO_CHAR(SYSDATE + 1, 'YYYYMMDD') + '0000';
   
      --結果1
      SELECT count(*) into blzz FROM ZZRK.JB_TAB WHERE FZRQ = strdate;

      --結果2
      SELECT count(*)
        into lgzs
        FROM lgy.lgy_gnlkjbxx
       WHERE RZSJ >= strdate1
         and RZSJ < strdate2;
   
      OPEN Index_OUT FOR
        SELECT blzz,lgzs
    END;
  END;
end;

 

調用預存程序:

    /// <summary>
    /// 得到統計資料集
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="Stime"></param>
    /// <param name="Etime"></param>
    /// <param name="Type"></param>
    private DataSet GetTjData(string cmdText, string Stime, string Etime, string Type)
    {
        DataSet ds = new DataSet();
        OracleConnection Con = new System.Data.OracleClient.OracleConnection(AppConfig.ZHCX);
        Con.Open();
        OracleCommand cmd = new OracleCommand(cmdText, Con);
        cmd.CommandType = CommandType.StoredProcedure;
        OracleParameter[] parm ={
            new OracleParameter("Stime", OracleType.DateTime),
            new OracleParameter("Etime", OracleType.DateTime),
            new OracleParameter("Type", OracleType.Int16),
            new OracleParameter("cur_OUT", OracleType.Cursor) };
        parm[0].Value = DateTime.Parse(Stime);
        parm[1].Value = DateTime.Parse(Etime).AddDays(1);
        parm[2].Value = int.Parse(Type);
        parm[3].Direction = ParameterDirection.Output;

        foreach (OracleParameter op in parm)
        {
            cmd.Parameters.Add(op);
        }
        OracleDataAdapter oda = new OracleDataAdapter(cmd);
        oda.Fill(ds);
        Con.Close();
        return ds;
    }