Oracle 计算年龄的公式

按年龄段进行统计，分别统计出 0-6岁，7-12岁，13-18岁，60岁以上和百岁老人，存储过程执行需要8秒时间，处理优化的时，只将计算年龄的方法进行优化，即将时间缩短至2秒，看来之前写法害死个人呀

 

计算年龄使用的方法为extract函数；

select count(1) into N0D6SBDHJN ---0-6岁本地户籍男
from T_RK_HJRK a where a.xbdm='1' and (EXTRACT(year FROM sysdate) - EXTRACT(year FROM a.csrq)) between 0 and 6;

-----以下为网络资料，关于extract的用法-----

//oracle中extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分
//语法如下：
EXTRACT (
        { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
        | { TIMEZONE_HOUR | TIMEZONE_MINUTE }
        | { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )
//我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd);
//我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE;
select extract(year from date'2011-05-17') year from dual;
      YEAR
----------
      2011
select extract(month from date'2011-05-17') month from dual;
     MONTH
----------
         5
select extract(day from date'2011-05-17') day from dual;
       DAY
----------
        17
//获取两个日期之间的具体时间间隔，extract函数是最好的选择
select extract(day from dt2-dt1) day
      ,extract(hour from dt2-dt1) hour
      ,extract(minute from dt2-dt1) minute
      ,extract(second from dt2-dt1) second
from (
     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1
           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2
     from dual)
/
       DAY       HOUR     MINUTE     SECOND
---------- ---------- ---------- ----------
       102          4          1         46
--
select extract(year from systimestamp) year
      ,extract(month from systimestamp) month
      ,extract(day from systimestamp) day
      ,extract(minute from systimestamp) minute
      ,extract(second from systimestamp) second
      ,extract(timezone_hour from systimestamp) th
      ,extract(timezone_minute from systimestamp) tm
      ,extract(timezone_region from systimestamp) tr
      ,extract(timezone_abbr from systimestamp) ta
from dual
/
     YEAR      MONTH        DAY     MINUTE     SECOND         TH         TM TR         TA
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ----------
      2011          5         17          7     14.843          8          0 UNKNOWN   UNK
//