图图（Daining）已经和妈妈回老家省亲11天了，非常想念，为表达思念之情，编写了一个SQL语句，随时计算他的精确年龄（精度到秒）。
--如果您有兴趣，也可以拿去计算一下自己的小孩子的准确年龄，修改一下出生日期就可以了。

1.编写的SQL语句如下
sec@ora10g> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

Session altered.

sec@ora10g> col "Daining's age:" for a75
sec@ora10g> SELECT    'Hi, My name is Hou Daining. I am Secooler'
2         || ''''
3         || 's son.'
4         || CHR (10)
5         || 'I was born in Beijing on November 11, 2008.'
6         || CHR (10)
7         || 'It is '
8         || SYSDATE
9         || ' o'
10         || ''''
11         || 'clock now.'
12         || CHR (10)
13         || 'So I am '
14         || SUBSTR (dt_from_birth, 7, 4)
15         || ' years '
16         || SUBSTR (dt_from_birth, 12, 2)
17         || ' months '
18         || SUBSTR (dt_from_birth, 22, 2)
19         || ' days '
20         || SUBSTR (dt_from_birth, 25, 2)
21         || ' hours '
22         || SUBSTR (dt_from_birth, 28, 2)
23         || ' minutes '
24         || SUBSTR (dt_from_birth, 31, 2)
25         || ' seconds old.' AS "Daining's age:"
26    FROM (SELECT    NUMTOYMINTERVAL (MONTHS_BETWEEN (tutu_now, tutu_birth),
27                                     'month'
28                                    )
29                 || NUMTODSINTERVAL
30                                 ( tutu_now
31                                  - ADD_MONTHS
32                                              (tutu_birth,
33                                               TRUNC (MONTHS_BETWEEN (tutu_now,
34                                                                      tutu_birth
35                                                                     )
36                                                     )
37                                              ),
38                                  'day'
39                                 ) dt_from_birth
40            FROM (SELECT TO_DATE ('2008-11-11 19:20:00',
41                                  'yyyy-mm-dd hh24:mi:ss'
42                                 ) tutu_birth,
43                         SYSDATE tutu_now
44                    FROM DUAL))
45 /

Daining's age:
-------------------------------------------------------------------------
Hi, My name is Hou Daining. I am Secooler's son.
I was born in Beijing on November 11, 2008.
It is 2009-08-22 17:35:04 o'clock now.
So I am 0000 years 09 months 10 days 22 hours 15 minutes 04 seconds old.

2.小结一下SQL语句中用到的一些小技巧
1）使用连接CHR (10)的方式实现语句的换行
2）使用substr函数截取并定位字符串，格式化输出信息
3）使用NUMTOYMINTERVAL函数得到年和月的信息
4）使用NUMTODSINTERVAL函数得到天和时分秒的信息
5）AS "Daining's age:"，此处使用as语句重命名sqlplus的显示标题
6）col "Daining's age:" for a75，此处格式化sqlplus每行输出占用75列
7）|| ''''，此处使用了单引号的转义处理小技巧

-- The End --

ITPub博客

【SQL】【思念】请Oracle协助得到孩子的精确年龄（精确到秒）

最新文章