在PL/SQL中使用日期類型

在PL/SQL中使用日期類型

在PL/SQL中使用日期類型

之前的文章介紹了在PL/SQL中使用字串和數字了下。毫無疑問，字串和數字很重要，但是可以確定的是沒有哪個應用不依賴於日期的。

Oracle使用由字串索引的二維數組 

你需要記錄事件的發生事件，人們的出生日期等等很多。

通常的使用方式是：

1）聲明日期變數和常量

2）使用內建函數顯示和修改日期值

3）執行日期相關計算

日期類型比字串或數字更複雜。它有多個部分組成（年，月，日，時，分，秒等），而且關於一個有效日期有很多規則。
本文將給你所有資訊以便在PL/SQL程式中使用日期。

1、PL/SQL中的日期，時間戳記和間隔(Intervals)

絕大多數應用需要儲存和操作日期和時間。不像字串和數字，日期要求更複雜：不僅僅因為他們更進階的格式化資料，其對於有效值和有效計算方式都有
很多規則。

Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.

慶幸的是，Oracle資料庫和PL/SQL提供了一系列日期和時間類型以標準形式來儲存日期和時間資訊，還有一系列內建函數來實現日期和時間計算。
有三種相互關聯類型：
1）DATE—This datatype stores a date and a time, resolved to the second. It does not include the time zone【時區】. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications. 也是最古老和最常用的類型。

2）TIMESTAMP—Time stamps are similar to dates, but with these two key distinctions:
(2.1) you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and
儲存精度達到10億分之一秒。
(2.2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp. 可帶時區。

3）INTERVAL—Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.
記錄時間間隔。可以指定年和月或天和秒層級的時間間隔。

來看個例子：
Code Listing 1: Declaring DATE, TIMESTAMP, and INTERVAL variables

DECLARE   l_today_date        DATE := SYSDATE;   l_today_timestamp   TIMESTAMP := SYSTIMESTAMP;   l_today_timetzone   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;   l_interval1         INTERVAL YEAR (4) TO MONTH := '2011-11';   l_interval2         INTERVAL DAY (2) TO SECOND := '15 00:30:44';BEGIN   null;END;/

2、如何選擇使用合適的日期類型？
1）Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.

2）You can, in general, use TIMESTAMP in place of DATE. A time stamp that does not contain subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When your time stamp does contain subsecond【亞秒】 data, it takes up 11 bytes of storage.

3）Use TIMESTAMP WITH TIME ZONE if you need to keep track of the session time zone in which the data was entered.

4）Use TIMESTAMP WITH LOCAL TIME ZONE if you want the database to automatically convert a time between the database and session time zones. 自動轉換資料庫時間和會話時間時區。

5）Use DATE when it’s necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced. 保持向前相容性。

3、如何獲得目前時間？
相信大多數開發人員都是通過SYSDATE函數，但是Oracle資料庫還提供了其他一些函數，一起來看一下：

1）會話層級函數：
CURRENT_DATE 返回：DATE
CURRENT_TIMESTAMP 返回：TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP 返回：TIMESTAMP

2）資料庫層級
SYSDATE 返回：DATE
SYSTIMESTAMP 返回：TIMESTAMP WITH TIME ZONE

Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values

例如：

BEGIN  DBMS_OUTPUT.put_line (SYSDATE);  DBMS_OUTPUT.put_line (SYSTIMESTAMP);  DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);END;/

Here is the output:

07-AUG-11
07-AUG-11 08.46.16.379000000 AM -05:00
-000000000 00:00:00.379000000

因為我給DBMS_OUTPUT.PUT_LINE傳遞了日期和時間戳記，Oracle資料庫使用資料庫或會話級的預設格式(參數：NLS_DATE_FORMAT)將其隱式轉換為字串。
預設安裝資料庫時的格式為：DD-MON-YYYY。而預設時間戳記格式包含了日期和時區的位移（offset）。

如何修改？【譯者註：以下摘自網路】
1）.可以在使用者環境變數中指定（LINUX)。 在使用者的.bash_profile中增加兩句：
export NLS_LANG=AMERICAN —要注意這一句必須指定，不然下一句不生效。
export NLS_DATE_FORMAT=’yyyy/mm/dd hh24:mi:ss’
2）.在SQLPLUS的glogin.sql檔案增加一句： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
3）.直接修改當前會話的日期格式 ： alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
4）.修改資料庫的參數，需要重啟資料庫後生效 SQL> alter system set nls_date_format=’yyyy-mm-dd hh24:mi:ss’ scope=spfile;

4、實現日期到字串和字串到日期的轉換？

正如to_char函數對於數字一樣，我們使用另外一個版本的to_char實現日期或時間戳記類型到字串的轉換。

如果使用了不帶格式參數的to_char。則資料庫使用隱式轉換。

BEGIN   DBMS_OUTPUT.put_line (     TO_CHAR (SYSDATE));   DBMS_OUTPUT.put_line (     TO_CHAR (SYSTIMESTAMP));END;/ 07-AUG-1107-AUG-11 08.55.00.470000000 AM -05:00

Use TO_CHAR to display the full names of both the day and the month in the date:

BEGIN   DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'Day, DDth Month YYYY'));END;/Sunday   , 07TH August    2011

Note: The language used to display these names is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to TO_CHAR, as in
注意：用語顯示的日期語言由參數：NLS_DATE_LANGUAGE決定。其可以作為to_char的第3個參數使用：
如下：

BEGIN  DBMS_OUTPUT.put_line (    TO_CHAR (SYSDATE, 'Day, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Spanish'));END;/Domingo  , 07TH Agosto     2011

另外為了去除顯示結果中多餘的0和空格，我可以使用FM元素修飾符。

BEGIN  DBMS_OUTPUT.put_line (     TO_CHAR (SYSDATE, 'FMDay, DDth Month YYYY'));END;/Sunday, 7TH August 2011

You can also use the format mask to extract just a portion of, or information about, the date, as shown in the following examples:
你還可以使用格式參數僅提取日期的一部分內容：
What quarter is it? 目前時間的下一時刻？

TO_CHAR (SYSDATE, ‘Q’)

SCOTT@orcl> select sysdate from dual;SYSDATE-------------------2015-07-25 06:37:17SCOTT@orcl> select to_char(sysdate, 'Q') from dual;T-3

What is the day of the year (1-366) for today’s date? 當前日期處於所在年份第幾天？

TO_CHAR (SYSDATE, ‘DDD’)

SCOTT@orcl> select TO_CHAR (SYSDATE, 'DDD') from dual;TO_---206

What are the date and time of a DATE variable?

BEGIN  DBMS_OUTPUT.put_line (    TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));END;/

You can also use EXTRACT to extract and return the value of a specified element of a date. For example
還可以使用EXTRACT提取日期指定元素值：

What year is it? 當前年份？

EXTRACT (YEAR FROM SYSDATE)

SCOTT@orcl> select EXTRACT (YEAR FROM SYSDATE) from dual;EXTRACT(YEARFROMSYSDATE)------------------------                    2015 

 

What is the day for today’s date? 所在當前月份的天數？

EXTRACT (DAY FROM SYSDATE)

SCOTT@orcl> select EXTRACT (DAY FROM SYSDATE)  from dual;EXTRACT(DAYFROMSYSDATE)-----------------------                     25

如何將字串轉換為日期？使用to_date或to_timestamp內建函數。

DECLARE  l_date   DATE;BEGIN  l_date := TO_DATE ('12-JAN-2011');END ;

If the string you provide does not match the default format, Oracle Database will raise an exception:
注意：如果你提供的字串參數與資料庫或會話設定的格式參數模型不一致，Oracle資料庫將拋出異常：

DECLARE  l_date   DATE;BEGIN  l_date := TO_DATE ('January 12 2011');END;/ORA-01858: a non-numeric character was found where a numeric was expected

You should not assume that the literal value you provide in your call to TO_DATE matches the default format. What if the format changes over time? Instead, always provide a format mask when converting strings to dates, as in
我們應該始終指定格式，因為你無法確定格式參數何時會改變。

例如：
l_date := TO_DATE (‘January 12 2011’, ‘Month DD YYYY’);

5、日期截取Date truncation

使用TRUNC內建函數來截取一個日期的指定單元。最常見的用法是TRUNC(DATE)-不指定任何參數。這時，TRUNC僅將time部分設定為00:00:00。
例如：

Set l_date to today’s date, but with the time set to 00:00:00:

l_date := TRUNC (SYSDATE);

擷取當前日期所在月份第一天 Get the first day of the month for the specified date:

l_date := TRUNC (SYSDATE, ‘MM’);

Get the first day of the quarter for the specified date:

l_date := TRUNC (SYSDATE, ‘Q’);

擷取當前日期所在年份的第一天 Get the first day of the year for the specified date:

l_date := TRUNC (SYSDATE, ‘Y’);

SCOTT@orcl> select TRUNC (SYSDATE, 'Y') from dual;TRUNC(SYSDATE,'Y')-------------------2015-01-01 12:00:00

6、日期運算 Date arithmetic

針對日期和時間戳記的運算，Oracle資料庫提供了以下方式：

Add a numeric value to or subtract it from a date, as in SYSDATE + 7; Oracle Database treats the number as the number of days.
給指定日期加上或減去一個數值，例如：SYSDATE + 7; Oracle認為該數字單位為：天。

Add one date to or subtract it from another, as in l_hiredate - SYSDATE.
兩個日期直接相加減，例如：l_hiredate - SYSDATE。

Use a built-in function to “move” a date by a specified number of months or to another date in a week.
使用內建函數使日期移動指定月數或到達周內的另外一個日期。

例如：
Set a local variable to tomorrow’s date: 設定日期變數為明天

l_date := SYSDATE + 1;

Move back one hour: 向前推1個小時
l_date := SYSDATE - 1/24;

Move ahead 10 seconds: 向前推10秒
l_date := SYSDATE + 10 / (60 * 60 * 24);

When you add one date to or subtract it from another, the result is the number of days between the two. As a result, executing this block:
如果你對2個日期相加減，結果是2個日期間隔的天數。

DECLARE   l_date1   DATE := SYSDATE;   l_date2   DATE := SYSDATE + 10;BEGIN   DBMS_OUTPUT.put_line (      l_date2 - l_date1);   DBMS_OUTPUT.put_line (      l_date1 - l_date2);END;returns the following output:10-10

And the following function can be used to compute the age of a person, assuming that the person’s correct birth date is passed as the function’s only argument:
另外以下函數可用於計算一個人的年齡：

CREATE OR REPLACE FUNCTION your_age (birthdate_in IN DATE)   RETURN NUMBERISBEGIN   RETURN SYSDATE -           birthdate_in;END your_age;

下面來看下幾個內建函數：
ADD_MONTHS—針對日期或時間戳記加減指定的月數

NEXT_DAY— 當前系統時間的下一星期？的時間 指定時間的下一個星期幾（由char指定）所在的日期
NEXT_DAY(date,char)
date參數為日期型，
char:為1~7或Monday~Sunday

指定時間的下一個星期幾（由char指定）所在的日期，
char也可用1～7替代，1表示星期日，2代表星期一。。。。
還可以是星期一、星期二。。。星期日

LAST_DAY—返回指定日期所在月份最後一天的日期

Move ahead one month: 往後推1個月【譯者註：one month later】

l_date := ADD_MONTHS (SYSDATE, 1);

Move backward three months: 往前推3個月【譯者註：one month ago】

l_date := ADD_MONTHS (SYSDATE, -3);

SCOTT@orcl> SELECT SYSDATE,  2   LAST_DAY(SYSDATE) "Last",  3   LAST_DAY(SYSDATE) - SYSDATE "Days Left"  4   FROM DUAL;SYSDATE             Last                 Days Left------------------- ------------------- ----------2015-07-25 07:04:17 2015-07-31 07:04:17          6

Code Listing 3: Calls to ADD_MONTHS

BEGIN   DBMS_OUTPUT.put_line (      ADD_MONTHS (TO_DATE ('31-jan-2011', 'DD-MON-YYYY'), 1));   DBMS_OUTPUT.put_line (      ADD_MONTHS (TO_DATE ('27-feb-2011', 'DD-MON-YYYY'), -1));   DBMS_OUTPUT.put_line (      ADD_MONTHS (TO_DATE ('28-feb-2011', 'DD-MON-YYYY'), -1));END; Here is the output: 28-FEB-1127-JAN-11 31-JAN-11 

Find the next Saturday after today’s date:
找到今天過後的下一個星期六！

l_date := NEXT_DAY (SYSDATE, ‘SAT’);
– or
l_date := NEXT_DAY (SYSDATE, ‘SATURDAY’);

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享

使用PL/Scope分析PL/SQL代碼