圖圖（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 --

【SQL】【思念】請Oracle協助得到孩子的精確年齡（精確到秒）

相關文章