关于Oracle SQL语句的使用

      今天，帮同事解决网站的一些问题，牵扯到Oracle SQL中时间的相关函数的使用。在此我把常用的时间处理的SQL写出来，供大家参考：

     (1)sysdate ：当前的日期和时间 SQL->select sysdate from dual;

     (2)last_day：本月的最后一天    SQL->select last_day(sysdate) from dual;

     (3)add_months(d,n):当前日期后推几个月，用于从一个日期值增加或减少几个月 n 值可以为负值；select add_months(sysdate,-1) from dual;

     (4)months_between(f,s):日期f和s之间相差的月数 SQL->select months_betwee(sysdate,to_date('2009-8-8','yyyy-mm-dd')) from dual;

     (5)next_day(d,day_of _week):返回由"day_of_week"命名的，在变量“d”指定的日期之后第一个工作日的日期。参数“day_of_week”必须为该星期中的某一天。SQL->select next_day(to_date('2010-1-1','yyyy-mm-dd'),1) from dual;  day_of_week此值表示星期几，范围是1-7

     (6)current_date()返回当前会话时区中的当前日期。select sessiontimezone,current_date from dual; 修改会话: alert session set time_zone='-11:00' 

     (7)current_timestamp()以timestamp with time zone 数据类型返回当前会话时区中的当前日期。SQL->select current_timestamp from dual;

     (8) dbtimezone()返回时区 　SQL->select dbtimezone from dual 

     (9) extract():从时间类型的数据中抽取年、月或者日，如果要抽取时、分用to_date(sysdate,'hh24:mm') SQL-> select extract(month from sysdate) "This month " from dual; 

     (10) localtimestamp 返回会话中的日期和时间;SQL-> select localtimestamp from dual

    

    常用的日期格式：

     (1)Y、YY、YYY 表示年的最后一位、二位、三位

     (2) syear 或 year syear使公元前的年份前加负号 Select to_char(sysdate,'SYEAR') from dual

     (3)Q 季节，1-3月为第一季度 SQL->select to_char(sysdate,'Q') from dual

     (4)WW当年的第几周 Select to_char(sysdate,’WW’) from dual

   

 常用的一些SQL:

(1)上月末天：

 SQL> select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;

(2)上月今天

SQL> select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual

(3)上月首天

SQL> select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual

(4)按照每周进行统计

SQL> select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');   

 

 (5)按照每月进行统计

SQL> select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');

也可以写成：select extract(month from sysdate) from dual group by extract(month from sysdate)

(6)按照每季度进行统计

SQL> select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');

 (7)按照每年进行统计

SQL> select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');

也可以写成:select extract(year from sysdate) from dual group by extract(year from sysdate)

(8).要找到某月中所有周五的具体日期select to_char(t.d,'YY-MM-DD') from (

 select trunc(sysdate,'MM')+rownum-1 as d

 from dba_objects

where rownum < 32) t

where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期

and trim(to_char(t.d, 'Day')) = '星期五'

 

(9)Oracle 中时间的运算：

   内容如下:(a)oracle 支持对日期进行运算.(b)日期运算是以天为单位进行的(c)当需要时、分、秒更小的单位运算时，按时间进行转换即可。(d)进行时间进制转换时需要注意加括号，否则容易出问题

   round (num)：对num取整。SQL->Select (sysdate-(sysdate-3.111))*1440 from dual; 出现小数，因此要去的整数的天可以先把  (sysdate-(sysdate-3.111))*1440  必须用to_number()转换成数字类型,在用round()函数。

 

      我们可以用这些函数把一个消逝时间近似转换成分钟并把这个值写入Oracle表格中。这个例子中，我们有一个离线的系统级触发机制来计算已经开始的会话时间并把他它放入到 一个oracle  statspack user_log 扩展表格之中。

  

Update 

perfstat.stats$user_log 

set 

elapsed_minutes = 

round(to_number(logoff_time-logon_time)*1440) 

where 

user = user_id 

and 

elapsed_minutes is NULL; 

 

查出任一年月所含的工作日

CREATE OR REPLACE FUNCTION Get_WorkingDays(

ny IN VARCHAR2

) RETURN INTEGER IS

 

/*------------------------------------------------------------------

 函数名称:Get_WorkingDays

 中文姓名:求一年月中共有多少工作日

 输入数据:NY:所求包含工作日数的年月，格式为yyyymm,如200510

 返回值:整型值，包含的工作日数目

 算法描述：

  1).列举出参数给出的年月中的每一天。这里使用一个表(ljrq是我的库中的一张表，这个表可以是有访问权限的，记录条数至少为31的任意一张表或视图)来构造出某年月的每一天。

 2).用这些日期和一个已知星期几的日期相减

 

-------------------------------------------------*/

Result INTEGER;

BEGIN

SELECT COUNT(*) INTO Result

FROM (SELECT MOD(MOD(q.rq-to_date('2001-12-30','yyyy-mm-dd'),7),7) weekday

FROM ( SELECT to_date(ny||t.dd,'yyyymmdd') rq

FROM (SELECT substr(100+ROWNUM,2,2) dd 

FROM ljrq z WHERE Rownum<=31

) t

WHERE to_date(ny||t.dd,'yyyymmdd') 

BETWEEN to_date(ny,'yyyymm') 

AND last_day(to_date(ny,'yyyymm'))

)q

) a 

WHERE a.weekday NOT IN(0,6); 

RETURN Result; 

END Get_WorkingDays;

 

______________________________________

 

还有一个版本

CREATE OR REPLACE FUNCTION Get_WorkingDays(

ny IN VARCHAR2

) RETURN INTEGER IS

/*-----------------------------------------------------------------------------------------

函数名称：Get_WorkingDays

中文名称：求某一年月中共有多少工作日

作者姓名: XINGPING

编写时间: 2004-05-23

输入参数：NY:所求包含工作日数的年月，格式为yyyymm,如200405

返 回 值：整型值，包含的工作日数目。

算法描述：使用Last_day函数计算出参数所给年月共包含多少天，根据这个值来构造一个循环。在这个循环中先求这个月的每一天与一个已知是星期天的日期(2001-12-30是星期天)的差，所得的差再对7求模。如果所求日期在2001-12-30以前，那么所得的差既是负数，求模后所得值范围为大于-6,小于0，如-1表示星期六,故先将求模的结果加7，再求7的模. 如过所得值不等于0和6（即不是星期六和星期天），则算一个工作日。 

----------------------------------------------------------------------------------------*/

Result INTEGER := 0;

myts INTEGER; --所给年月的天数

scts INTEGER; --某天距2001-12-30所差的天数

rq DATE;

djt INTEGER := 1; -- 

BEGIN

myts := to_char(last_day(to_date(ny,'yyyymm')),'dd'); 

LOOP 

rq := TO_date(ny||substr(100+djt,2),'yyyymmdd');

scts := rq - to_date('2001-12-30','yyyy-mm-dd');

IF MOD(MOD(scts,7)+7,7) NOT IN(0,6) THEN

Result := Result + 1;

END IF;

djt := djt + 1; 

EXIT WHEN djt>myts;

END LOOP; 

RETURN Result; 

END Get_WorkingDays;

 

以上两个版本的比较

 

第一个版本一条SQL语句就可以得出结果，不需要编程就可以达到目的。但需要使用任意一张有权访问的、记录条数至少为31的一张表或视图。

第二个版本需要编程，但不需要表或者视图。

这两个版本都还存在需要完善的地方，即没有考虑节日，如五一、十一、元旦、春节这些节假期都没有去除。这些节假日应该维护成一张表，然后通过查表来去除这些节假日。