Chapter 4: Using Simple Functions
...
■Regular Expression Functions
Regular expression functions were introduced in Oracle Database 10g, and additional items have been added to 11g.
･REGEXP_LIKE(x, pattern [, match_option])
To search x for the regular expression defined in the pattern parameter.
･REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [,match_option]]]])
･REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence[, match_option]]]])
･REGEXP_SUBSTR(x, pattern [, start [, occurrence [, match_option]]])
･REGEXP_COUNT(x, pattern [,start [, match_option]])
※REGEXP_COUNT() is new for Oracle Database 11g.
■Using Aggregate Functions
Function Description
AVG(x) Returns the average value of x
COUNT(x) Returns the number of rows returned by a query involving x
MAX(x) Returns the maximum value of x
MIN(x) Returns the minimum value of x
SUM(x) Returns the sum of x
MEDIAN(x) Returns the median value of x 中央值
STDDEV(x) Returns the standard deviation of x 標準偏差
VARIANCE(x) Returns the variance of x [数]方差
Chapter 5: Storing and Processing Dates and Times
■
･ANSI standard dates
You can also use the DATE keyword to supply a date literal to the database. The date must usethe ANSI standard date format YYYY-MM-DD, where
YYYY is a four-digit year.
MM is a two-digit month from 1 to 12.
DD is a two-digit day.
eg:
SQL> desc customers;
名前 NULL? 型
------------------- -------- ------------
CUSTOMER_ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(10)
LAST_NAME NOT NULL VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)
SQL> INSERT INTO customers (
2 customer_id, first_name, last_name, dob, phone
3 ) VALUES (
4 7, 'Steve', 'Purple', DATE '1972-10-25', '800-555-1215'
5 );
1行が作成されました。
･Oracle
SQL> commit;
1 INSERT INTO customers (
2 customer_id, first_name, last_name, dob, phone
3 ) VALUES (
4 6, 'Fred', 'Brown', to_date('05/04/1968','dd/mm/yyyy'), '800-555-1215'
5* )
SQL> /
1行が作成されました。
SQL> commit;
■Setting the Default Date Format
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';
セッションが変更されました。
SQL> select dob from customers
2 ;
DOB
------------
4月 -05-1968
10月-25-1972
■Oracle Interprets Two-Digit Years
The Oracle database stores all four digits of the year, but if you supply only two digits, the database will interpret the century according to whether the YY or RR format is used.
･YY Format
the first two digits of your supplied year are set to the first two digits of the present year.
For example, if your supplied year is 15 and the present year is 2007, your supplied year is set to 2015; similarly, a supplied year of 75 is set to 2075.
･RR Format(最近)
Rule 1 If your supplied year is between 00 and 49 and the present year is between 00 and 49, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year. For example, if your supplied year is 15 and the present year is 2007, your supplied year is set to 2015.
Rule 2 If your supplied year is between 50 and 99 and the present year is between 00 and 49, the century is the present century minus 1. Therefore, the first two digits of your supplied year are set to the present year’s first two digits minus 1. For example, if your supplied year is 75 and the present year is 2007, your supplied year is set to 1975.
Rule 3 If your supplied year is between 00 and 49 and the present year is between 50 and 99, the century is the present century plus 1. Therefore, the first two digits of your supplied year are set to the present year’s first two digits plus 1. For example, if your supplied year is 15 and the present year is 2075, your supplied year is set to 2115.
Rule 4 If your supplied year is between 50 and 99 and the present year is between 50 and 99, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year. For example, if your supplied year is 55 and the present year is 2075, your supplied year is set to 2055.
■Time Zone
･The Database Time Zone
ALTER DATABASE SET TIME_ZONE = offset | region
eg:
ALTER DATABASE SET TIME_ZONE = '-8:00';
ALTER DATABASE SET TIME_ZONE = 'PST';
SQL> SELECT DBTIMEZONE FROM dual;
DBTIME
------
-08:00
･The Session Time Zone
eg:ALTER SESSION SET TIME_ZONE = 'PST'
SQL> SELECT SESSIONTIMEZONE from dual;
SESSIONTIMEZONE
------------------------------------------
+08:00
･Getting the Current Date in the Session Time Zone
The SYSDATE function gets the date from the database. This gives you the date in the database time zone. You can get the date in your session time zone using the CURRENT_DATE function.
･Obtaining Time Zone Offsets
You can get the time zone offset hours using the TZ_OFFSET() function, passing the time zone region name to TZ_OFFSET().
SQL> SELECT TZ_OFFSET('PST') FROM dual;
TZ_OFFS
-------
-07:00
･Obtaining Time Zone Names
v$timezone_names
･Converting a Datetime from One Time Zone to Another
SQL> SELECT TO_CHAR(NEW_TIME(TO_DATE('25-MAY-2008 19:45',
2 'DD-MON-YYYY HH24:MI'), 'PST', 'EST'), 'DD-MON-YYYY HH24:MI')
3 FROM dual;
TO_CHAR(NEW_TIME(
-----------------
25-MAY-2008 22:45
■Timestamps
。。。
■Time Intervals
･INTERVAL YEAR TO MONTH
･INTERVAL DAY TO SECOND
Time Interval Functions
NUMTODSINTERVAL(x, interval_unit）
NUMTOYMINTERVAL(x, interval_unit)
TO_DSINTERVAL(x)
TO_YMINTERVAL(x)
