Oracle 資料庫中 SQL 分析的主要優勢Oracle 資料庫中分析功能和特性提供以下主要優勢：1. 提高開發人員產能 — 開發人員可以通過更清晰、更簡潔的 SQL 代碼執行複雜分析。現在可以使用一條 SQL 語句表示覆雜任務，編寫和維護速度更快、效率更高。 2. 提高查詢速度 — 資料庫中分析...
Oracle 資料庫中 SQL 分析的主要優勢 |
||
Oracle 資料庫中分析功能和特性提供以下主要優勢：
1. 提高開發人員產能 — 開發人員可以通過更清晰、更簡潔的 SQL 代碼執行複雜分析。現在可以使用一條 SQL 語句表示覆雜任務，編寫和維護速度更快、效率更高。 |
||
概述 |
||
Oracle Database 中內嵌的資料庫中分析函數特性可用於回應各種業務問題。開發人員和業務用戶可以訪問各種分析特性，並將其結果與其他 SQL 查詢和分析管道結合，獲得更深入的洞察。Oracle 資料庫包括以下特性：
|
||
語法:
ABS ( n )
用法：
返回n的絕對值。
參數：
n為數值數據類型，或者可以隱式轉化為數值類型的其他數據類型。
例子：
SELECTABS(-12345.6789) result FROM dual;
RESULT
----------
12345.6789
語法：
ACOS ( n )
用法：
返回n的反餘弦值。n的值必須在-1到1之間；返回值的範圍從0到pi。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。如果n是BINARY_FLOAT，則返回值的類型是BINARY_DOUBLE，否則返回值的類型與參數的數據類型相同。
例子：
SELECT ACOS(-1)result FROM dual;
RESULT
----------
3.14159265
語法：
ADD_MONTHS ( date,integer )
用法：
返回date加上integer個月後的日期。一個月是由會話(session)參數NLS_CALENDAR來定義的。
參數：
date是一個日期時間值或可以隱式轉化為DATE的其他值。integer是一個整數或其他可以轉化為整數的值。返回值永遠是DATE數據類型。如果date是一個月的最後一天，或者得到結果的月份的天數比date所在月份的天數時，返回結果是結果月份的最後一天。
例子：
SELECTTO_CHAR(ADD_MONTHS(TO_DATE('2011-01-31','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual;
RESULT
----------
2011-02-28
SELECTTO_CHAR(ADD_MONTHS(TO_DATE('2011-01-30','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual;
RESULT
----------
2011-02-28
SELECTTO_CHAR(ADD_MONTHS(TO_DATE('2011-01-27','YYYY-MM-DD'),1),'YYYY-MM-DD') result FROM dual;
RESULT
----------
2011-02-27
SELECTTO_CHAR(ADD_MONTHS(TO_DATE('2011-01-31','YYYY-MM-DD'), 1.9),'YYYY-MM-DD') result FROM dual;
RESULT
----------
2011-02-28
語法：
ASCII ( char )
用法：
返回char的第一個字元在資料庫字元集中的十進位表示。
參數：
char可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，或者能轉化成它們的其他數據類型。
例子：
SELECT ASCII('甲骨文') result FROM dual;
RESULT
----------
42450
SELECTASCII('Oracle') result FROM dual;
RESULT
----------
79
語法：
ASCIISTR ( char )
用法：
返回字元串char在資料庫字元集中的ASCII版本。非ASCII字元將會轉化成\XXXX的格式，其中XXXX是UTF-16編碼單元。
例子：
SELECT ASCIISTR('甲骨文') result FROM dual;
RESULT
---------------
\7532\9AA8\6587
SELECTASCIISTR('Oracle') result FROM dual;
RESULT
------
Oracle
語法：
ASIN ( n )
用法：
返回n的反正弦值。
n必須在-1到1之間。返回值的範圍在-pi/2到pi/2。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。如果n是BINARY_FLOAT，則返回值的類型是BINARY_DOUBLE，否則返回值的類型與參數的數據類型相同。
例子：
SELECT ASIN(1)result FROM dual;
RESULT
----------
1.57079633
語法：
ATAN ( n )
用法：
返回n的反正切值。n的取值沒有範圍；而返回值的範圍在-pi/2到pi/2之間。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。如果n是BINARY_FLOAT，則返回值的類型是BINARY_DOUBLE，否則返回值的類型與參數的數據類型相同。
例子：
SELECT ATAN(1)result FROM dual;
RESULT
----------
.785398163
語法：
ATAN2 ( n1, n2 )
用法：
返回n1與n2的反正切值。等同於ATAN(n1/n2)。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。如果n是BINARY_FLOAT，則返回值的類型是BINARY_DOUBLE，否則返回值的類型與參數的數據類型相同。
例子：
SELECT ATAN2(1,0)result FROM dual;
RESULT
----------
1.57079633
SELECT ATAN2(-.1,0)result FROM dual;
RESULT
----------
-1.5707963
語法：
AVG ( [DISTINCT|ALL]expr )
用法：
返回expr的平均值。
參數：
expr可以為任意數值類型或可以轉化為數值類型的其他類型。返回值的數據類型與expr的數據類型相同。
例子：
SELECT AVG(DISTINCTsal) result FROM scott.emp;
RESULT
----------
2064.58333
SELECT AVG(ALL sal)result FROM scott.emp;
RESULT
----------
2073.21429
語法：
BFILENAME ('directory', 'filename' )
用法：
返回一個關於directory和filename的BFILE定位器。
參數：
directory是一個DIRECTORY對象。filename是伺服器文件系統的一個文件名。
例子：
作為SYSDBA
CREATE DIRECTORYMEDIA_DIR AS '/home/oracle';
GRANT READ ONDIRECTORY MEDIA_DIR TO TEST;
作為TEST
CREATE TABLEtest_bfile (id NUMBER, bfiles BFILE);
INSERT INTOtest_bfile VALUES (1, BFILENAME('MEDIA_DIR', 'uname.txt'));
COMMIT;
語法：
BIN_TO_NUM ( expr [,expr, ...] )
用法：
將位元組向量轉化成等值的NUMBER。
參數：
每個expr不是0就是1。
例子：
SELECTBIN_TO_NUM(1,1,0,0) result FROM dual;
RESULT
----------
12
語法：
BITAND ( expr1,expr2 )
用法：
返回expr1與expr2進行位與運算的結果。
參數：
expr1和expr2都是NUMBER，結果也是NUMBER。如果其中一個參數為NULL，結果也為NULL。
例子：
SELECT BITAND(
BIN_TO_NUM(1,1,0,0),
BIN_TO_NUM(1,0,1,1)) result FROM dual;
RESULT
----------
8
語法：
CARDINALITY (nested_table )
用法：
返回內嵌表中元素的個數。
參數：
返回值的類型是NUMBER。如果內嵌表是空的，或者是一個null集合，則返回NULL。
例子：
CREATE OR REPLACETYPE test_nestedtable AS TABLE OF VARCHAR2(20);
/
CREATE TABLE test_nt(id NUMBER, nt test_nestedtable) NESTED TABLE nt STORE AS nt_tab;
INSERT INTO test_nt VALUES(1, test_nestedtable('hello', 'nested', 'table'));
COMMIT;
SELECT id, CARDINALITY(nt) FROM test_nt;
ID CARDINALITY(NT)
-------------------------
1 3
語法：
CAST ( { expr | MULTISET ( subquery ) } AS type_name )
用法：
將一種內置的數據類型或集合類型轉化成另一種內置的數據類型或集合類型。CAST不支持LONG，LONG RAW。
參數：
數據類型可以為BINARY_FLOAT，BINARY_DOUBLE，CHAR，VARCHAR2，NUMBER，DATETIME，INTERVAL，RAW，ROWID，UROWID，NCHAR，NVARCHAR2。
例子：
SELECT CAST(TO_DATE('2011/10/07','YYYY/MM/DD')AS TIMESTAMP WITH LOCAL TIME ZONE) result FROM dual;
RESULT
-----------------------------------
07-OCT-1112.00.00.000000 AM +08:00
CREATE OR REPLACETYPE cust_address_typ
AS OBJECT
( street_address VARCHAR2(40)
, postal_code VARCHAR2(10)
, city VARCHAR2(30)
, state_province VARCHAR2(10)
, country_id CHAR(2)
);
/
CREATE TYPE address_book_tAS TABLE OF cust_address_typ;
/
CREATE TABLEcust_address (
custno NUMBER,
street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(10),
country_id CHAR(2));
CREATE TABLEcust_short (custno NUMBER, name VARCHAR2(31));
INSERT INTOcust_short VALUES (1, 'TEST');
INSERT INTOcust_address VALUES (1, 'O STREET', '9900', 'ORACLE', 'DATABASE', 'CN');
COMMIT;
SELECT s.custno,s.name,
CAST(MULTISET(SELECT ca.street_address,ca.postal_code, ca.city, ca.state_province, ca.country_id
FROM cust_address ca
WHERE s.custno = ca.custno)
AS address_book_t)
FROM cust_short s
ORDER BY s.custno;
CUSTNO NAME RESULT(STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID)
------ -----------------------------------------------------------------------
1 TEST ADDRESS_BOOK_T(CUST_ADDRESS_TYP(O STREET,9900,ORACLE,DATABASE,CN))
語法：
CEIL ( n )
用法：
返回大於或等於n的最小整數。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。返回值的類型與參數的數據類型相同。
例子：
SELECT CEIL(1234.5)result FROM dual;
RESULT
----------
1235
語法：
CHARTOROWID ( char )
用法：
將CHAR，VARCHAR2，NCHAR或NVARCHAR2的數據類型轉換成ROWID數據類型。
例子：
select rowid, id fromtest_nt
wherechartorowid('AAAVLjAAEAAAADmAAA') = rowid;
ROWID ID
----------------------------
AAAVLjAAEAAAADmAAA 1
語法：
CHR ( n [ USINGNCHAR_CS ] )
用法：
返回擁有二進位編碼n的字元，如果使用NCHAR_CS，就使用資料庫字元集。對於單位元組字元集，如果n大於256，那麼會先進行n mod 256取餘數的操作。
參數：
n是一個NUMBER類型的值，或者可以轉化成NUMBER的其他類型的值。返回值是一個字元。
例子：
select CHR(42450)from dual;
CHR(42450)
----------
甲
語法：
COALESCE ( { expr [,expr ] ... } )
用法：
返回第一個非NULL的值，至少有兩個expr。
參數：
expr的數據類型必須一致。
例子：
select COALESCE (null, 123, '123') from dual;
select COALESCE (null, 123, '123') from dual
*
Error at line 1
ORA-00932:inconsistent datatypes: expected NUMBER got CHAR
select COALESCE (null, 123, 456) from dual;
COALESCE(NULL,123,456)
----------------------
123
語法：
COMPOSE ( char )
用法：
將參數的字元串轉化成相同字元集下的Unicode字元串。
參數：
char可以為CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB。
例子：
SELECT COMPOSE( 'a'|| UNISTR('\0308') ) FROM DUAL;
COMPOSE('A'||UNISTR('\0308'))
-----------------------------
ä
語法：
CONCAT ( char1,char2 )
用法：
返回值是char1連接char2，效果與連接符||一樣。
參數：
char1和char2可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB或NCLOB。
例子：
select CONCAT('Hello ', 'Oracle') result fromdual;
RESULT
------------
Hello Oracle
語法：
CONVERT ( char,dest_char_set [, source_char_set ] )
用法：
將char從字元集source_char_set轉成dest_char_set。CHAR和VARCHAR2的返回值是VARCHAR2；NCHAR和NVARCHAR2的返回值是NVARCHAR2；CLOB的返回值是CLOB；NCLOB的返回值是NCLOB。
參數：
char可以是CHAR，VARCHAR2，NCHAR，NVARCHAR2，CLOB和NCLOB。
例子：
select CONVERT('甲骨文', 'US7ASCII', 'ZHT16MSWIN950') result from dual;
RESULT
------
???
語法：
CORR ( expr1, expr2)
用法：
返回一組數值對的相對繫數。
例子：
with tmp as
(select 'a' cd, 95n1, 86 n2 from dual
union all
select 'a' cd, 100n1, 95 n2 from dual
union all
select 'a' cd, 58n1, 65 n2 from dual)
select CORR(n1,n2)result from tmp group by cd;
RESULT
----------
.982482592
語法：
COS ( n )
用法：
返回n的餘弦值。
參數：
n可以為任意數值類型或可以轉化成數值的其他類型。如果n是BINARY_FLOAT，那麼返回值是BINARY_DOUBLE；否則返回值類型與參數類型一樣。
例子：
SQL> SELECT COS(180* 3.14159265359 / 180) result FROM DUAL;
RESULT
----------
-1
語法：
COUNT ( { * } | { [DISTINCT | ALL ] expr } )
語法：
返回查詢結果的行數。如果指定了expr，將返回expr不為NULL的行數。DISTINCT會排除重覆的行；ALL包含重覆的行。指定‘*’時，會返回所有的行，包括重覆的和NULL的行。COUNT永遠不會返回NULL。
例子：
SQL> SELECTCOUNT(*) FROM scott.emp;
COUNT(*)
----------
14
語法：
CURRENT_DATE
用法：
返回會話時區的當前日期時間（使用Gregorian日曆）。
例子：
SQL> ALTERSESSION SET NLS_DATE_FORMAT ='YYYY/MM/DD HH24:MI:SS';
Session altered.
SQL> SELECTSESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
---------------------------------------
+08:00 2011/10/10 15:14:18
SQL> ALTERSESSION SET TIME_ZONE = '0:0';
Session altered.
SQL> SELECTSESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
SESSIONTIMEZONE CURRENT_DATE
---------------------------------------
+00:00 2011/10/10 07:14:43
語法：
CURRENT_TIMESTAMP (precision )
用法：
返回會話時區的當前日期和時間，包括小數秒。返回值類型是TIMESTAMP WITH TIME ZONE。如果沒有指定precision，預設為6。LOCALTIMESTAMP和CURRENT_TIMESTAMP之間的區別是LOCALTIMESTAMP返回一個TIMESTAMP類型的值，而CURRENT_TIMESTAMP返回一個TIMESTAMP WITH TIME ZONE類型的值。
例子：
SQL> SELECTTO_CHAR(SESSIONTIMEZONE) result FROM DUAL
2 UNION
3 SELECT TO_CHAR(CURRENT_TIMESTAMP) FROM DUAL
4 UNION
5 SELECTTO_CHAR(LOCALTIMESTAMP) FROM DUAL;
RESULT
-----------------------------------------------------
+08:00
10-OCT-1104.07.39.860798 PM
10-OCT-1104.07.39.860798 PM +08:00
語法：
DBTIMEZONE
用法：
返回資料庫的時區，格式為‘[+|-]TZH:TZM’或者時區名。
例子：
SQL> SELECT DBTIMEZONEFROM DUAL;
DBTIME
------
+00:00
語法：
DECODE ( expr, {search, result [, ...] } [, default] )
用法：
DECODE比較每個expr的值，如果expr等於其中一個search，那麼返回相應的result。如果沒有匹配的值，返回default。如果沒有default，返回NULL。在DECODE中，Oracle認為兩個NULL是匹配的，所以如果expr是NULL，就會返回第一個為NULL的search的相應的result。DECODE函數中，包括expr，search，result和default，一共可以有255個組件。
參數：
參數必須是數值或字元類型。
例子：
SQL> SELECTENAME, DECODE(SIGN(SAL-4000),1,'RICH',0,'MIDDLE','LOW') result
2 FROMSCOTT.EMP;
ENAME RESULT
---------- ------
SMITH LOW
ALLEN LOW
WARD LOW
JONES LOW
MARTIN LOW
BLAKE LOW
CLARK LOW
SCOTT LOW
KING RICH
TURNER LOW
ADAMS LOW
ENAME RESULT
---------- ------
JAMES LOW
FORD LOW
MILLER LOW
語法：
DEREF ( expr )
用法：
返回參數expr的對象引用，expr必須返回一個對象的REF。如果在查詢中沒有使用這個函數，Oracle會返回該REF的對象ID。
例子：
SQL> CREATE TYPEtest_ref_typ_new AS OBJECT
2 (idNUMBER, name VARCHAR2(20));
3 /
Type created.
SQL> CREATE TABLEtest_ref OF test_ref_typ_new;
Table created.
SQL> CREATE TABLEtest (test_id NUMBER, tr REF test_ref_typ_new SCOPE IS test_ref);
Table created.
SQL> INSERT INTOtest_ref VALUES (1, 'TEST');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTOtest select 101, REF(a) FROM test_ref a;
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT trFROM test;
TR
--------------------------------------------------------------------------
0000220208AEEEFD47B1A256F7E040E10A4A0B0F89AEEEFD47B1A156F7E040E10A4A0B0F89
SQL> SELECTDEREF(tr) FROM test;
DEREF(TR)(ID, NAME)
--------------------------------------------------------------------------
TEST_REF_TYP_NEW(1,'TEST')
語法：
DUMP ( expr [,return_fmt [, start_position [, length ] ] ] )
用法：
返回expr的數據類型編碼，位元組長度和內部表示。它總是以資料庫字元集來返回結果的。return_fmt有以下類型
8表示8進位
10表示10進位
16表示16進位
17表示如果能解析成可列印的字元則返回字元，否則返回16進位碼。
預設地，返回值不包含字元集的信息。要顯示字元集信息，在上面代碼的基礎上加上1000。
參數：
start_position表示開始位置；length表示要解析的長度。
例子：
SQL> SELECTDUMP('abc', 1016) result FROM DUAL;
RESULT
-------------------------------------------------
Typ=96 Len=3 CharacterSet=ZHT16MSWIN950: 61,62,63
SQL> SELECTDUMP('abc', 17) result FROM DUAL;
RESULT
-------------------
Typ=96 Len=3: a,b,c
SQL> SELECTDUMP('abc', 8, 2, 1) result FROM DUAL;
RESULT
-----------------
Typ=96 Len=3: 142
SQL> SELECT DUMP('abc',9,2,1)result FROM DUAL;
RESULT
----------------
Typ=96 Len=3: 98
SQL> SELECTDUMP('abc',10,2,1) result FROM DUAL;
RESULT
----------------
Typ=96 Len=3: 98
SQL> SELECTDUMP('abc',16,2,1) result FROM DUAL;
RESULT
----------------
Typ=96 Len=3: 62
語法：
EXP ( n )
用法：
返回自然對數e的n次方。
例子：
SQL> SELECTEXP(1) result1, EXP(2) result2 FROM DUAL;
RESULT1 RESULT2
--------------------
2.71828183 7.3890561
語法：
EXTRACT ( { YEAR |MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE |TIMEZONE_REGION | TIMEZONE_ABBR } FROM expr )
用法：
返回日期或間隔的特定部分的值。如果是YEAR或MONTH，expr必須是DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL YEAR TO MONTH；如果是DAY，expr必須是DATE，TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND；如果是HOUR，MINUTE或SECOND，expr必須是TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND；如果是TIMEZONE_HOUR，TIMEZONE_MINUTE，TIMEZONE_ABBR，TIMEZONE_REGION或TIMEZONE_OFFSET，expr必須是TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE。
EXTRACT處理DATE時，使用ANSI DATE，不包含HOUR，MINUTE，SECOND。
例子：
SQL> SELECTEXTRACT(YEAR FROM SYSDATE) result FROM DUAL;
RESULT
----------
2011
SQL> SELECTEXTRACT(MONTH FROM SYSDATE) result FROMDUAL;
RESULT
----------
10
SQL> SELECTEXTRACT(DAY FROM SYSDATE) result FROM DUAL;
RESULT
----------
11
SQL> SELECTEXTRACT(HOUR FROM SYSTIMESTAMP) result FROM DUAL;
RESULT
----------
4
SQL> SELECT EXTRACT(TIMEZONE_HOURFROM SYSTIMESTAMP) result FROM DUAL;
RESULT
----------
8
SQL> SELECTEXTRACT(TIMEZONE_OFFSET FROM SYSTIMESTAMP) FROM DUAL;
EXTRACT(TIMEZONE_OFFSETFROMSYSTIMESTAMP)
-----------------------------------------------------------------
+00000000008:00:00.000000000
SQL> SELECTEXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) result FROM DUAL;
RESULT
----------
0
SQL> SELECTEXTRACT(YEAR FROM INTERVAL '50' MONTH) result FROM DUAL;
RESULT
----------
4
SQL> SELECTEXTRACT(SECOND FROM INTERVAL '12 12:34:56' DAY TO SECOND) result FROM DUAL;
RESULT
----------
56
語法：
FLOOR ( n )
用法：
返回小於或等於n的最大整數。
參數：
n可以為任意數值類型或可以隱式轉化成數值的其他類型。返回值的類型與參數的數據類型相同。
例子：
SQL> SELECTFLOOR(1234.5) result FROM dual;
RESULT
----------
1234
語法：
FROM_TZ (timestamp_value, time_zone_value )
用法：
將一個TIMESTAMP值和一個時區轉化為一個TIMESTAMP WITH TIME ZONE值。
參數：
time_zone_value是一個格式為‘TZH:TZM’的字元串或能返回一個TZR的字元串。
例子：
SQL> SELECTFROM_TZ(TIMESTAMP '2011-10-11 12:22:00', '02:00') result FROM DUAL;
RESULT
---------------------------------------------------------------------------
11-OCT-1112.22.00.000000000 PM +02:00
語法：
GREATEST ( { expr [,expr ... ] } )
用法：
返回一個或多個表達式中的最大值。返回值的類型由第一個expr決定。在比較前，將其餘的expr轉化成第一個expr的類型再進行比較。預設地，比較是使用nonpadded語義的、二進位的。可以通過NLS_COMP和NLS_SORT參數來改變預設行為。
例子：
SQL> SELECTGREATEST('HARRY', 'HARRIOT', 'HAROLD') result FROM DUAL;
RESUL
-----
HARRY
語法：
GROUPING ( expr )
用法：
GROUPING可以將超聚合行從常規分組行中區分出來。GROUP BY的擴展ROLLUP和CUBE會產生超聚合行，使用GROUPING可以將其區分出來。
參數：
expr必須是GROUP BY中的表達。如果expr在行中的值為NULL，則返回1，否則返回0。
例子：
SQL> SELECT
2 DECODE(GROUPING(d.dname), 1, 'ALL DEPARTMENTS', d.dname) department,
3 DECODE(GROUPING(e.job), 1, 'ALL JOBS', e.job) job,
4 COUNT(*) "TOTAL EMPLOYEES",
5 AVG(sal) * 12 "AVARAGE SALARY"
6 FROMscott.emp e, scott.dept d
7 WHERE e.deptno=d.deptno
8 GROUP BY ROLLUP (d.dname, e.job)
9 ORDER BY d.dname, e.job;
DEPARTMENT JOB TOTAL EMPLOYEES AVARAGE SALARY
------------------------ --------------- --------------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING ALL JOBS 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH ALL JOBS 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES ALL JOBS 6 18800
ALL DEPARTMENTS ALLJOBS 14 24878.5714
語法：
GROUPING_ID ( expr )
用法：
返回一行中相關GROUPING位元組向量的數值。GROUPING_ID只有在GROUP BY ROLLUP或CUBE中，並且和GROUPING函數一起使用。
例子：
SQL> SELECTdeptno, job, SUM(sal) "Total Salary",
2 GROUPING(deptno) gd,
3 GROUPING(job) gj,
4 GROUPING_ID(deptno, job) gdj,
5 GROUPING_ID(job, deptno) gjd
6 FROMscott.emp
7 GROUP BY CUBE(deptno,job)
8 ORDER BY deptno,job,"Total Salary",gd;
DEPTNO JOB Total Salary GD GJGDJ GJD
---------- --------------------- --- --- --- ---
10 CLERK 1300 0 0 0 0
10 MANAGER 2450 0 0 0 0
10 PRESIDENT 5000 0 0 0 0
10 8750 0 1 1 2
20 ANALYST 6000 0 0 0 0
20 CLERK 1900 0 0 0 0
20 MANAGER 2975 0 0 0 0
20 10875 0 1 1 2
30 CLERK 950 0 0 0 0
30 MANAGER 2850 0 0 0 0
30 SALESMAN 5600 0 0 0 0
30 9400 0 1 1 2
ANALYST 6000 1 0 2 1
CLERK 4150 1 0 2 1
MANAGER 8275 1 0 2 1
PRESIDENT 5000 1 0 2 1
SALESMAN 5600 1 0 2 1
29025 1 1 3 3
語法：
HEXTORAW ( char )
用法：
將包含16進位數字的char轉化為RAW值。
參數：
char可以是CHAR，VARCHAR2，NCHAR或NVARCHAR2。不直接支持CLOB，但傳進來的CLOB會先進行隱式轉化。
例子：
SQL> SELECTUTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('4F5241434C45')) result
2 FROMDUAL;
RESULT
---------------------------------------------------------------------
ORACLE
語法：
INITCAP ( char )
用法：
將char轉化成每個單詞首字母大寫，其他字母小寫的形式。單詞是由空格或其他非字母數字字元來間隔。
參數：
char可以為CHAR，VARCHAR2，NCHAR或NVARCHAR2，返回值類型與char相同。
例子：
SQL> SELECTINITCAP('ORACLE DATABASE') reslt FROM DUAL;
RESLT
---------------
Oracle Database
SQL> SELECT INITCAP('Oracledatabase') reslt FROM DUAL;
RESLT
---------------
Oracle Database
語法：
INSTR ( string,substring [, position [, occurrence ] ] )
用法：
在string中，從position開始搜索substring，返回第occurrence次匹配的位置。如果沒找到，返回0。position和occurrence的預設值是1。當position為負數時，表示從string的後面開始向前搜索。
例子：
SQL> SELECTINSTR('12345678901234567890', '0') result FROM DUAL;
RESULT
----------
10
SQL> SELECTINSTR('12345678901234567890', '0', 11) result
2 FROMDUAL;
RESULT
----------
20
SQL> SELECT INSTR('12345678901234567890','0', 2, 2) result
2 FROMDUAL;
RESULT
----------
20
SQL> SELECTINSTR('12345678901234567890', '0', 11, 2) result
2 FROMDUAL;
RESULT
----------
0
SQL> SELECTINSTR('12345678901234567890', '0', -1) result
2 FROMDUAL;
RESULT
----------
20
SQL> SELECTINSTR('12345678901234567890', '0', -1, 2) result
2 FROMDUAL;
RESULT
----------
10
語法：
LAST_DAY ( date )
用法：
返回date所在月份的最後一天。
例子：
SQL> SELECT LAST_DAY(SYSDATE)result FROM DUAL;
RESULT
------------
31-OCT-11
語法：
LEAST ( { expr [,expr ... ] } )
用法：
返回一個或多個表達式中的最小值。返回值的類型由第一個expr決定。在比較前，將其餘的expr轉化成第一個expr的類型再進行比較。預設地，比較是使用nonpadded語義的、二進位的。可以通過NLS_COMP和NLS_SORT參數來改變預設行為。
例子：
SQL> SELECT LEAST('HARRY','HARRIOT', 'HAROLD') result FROM DUAL;
RESULT
------
HAROLD
語法：
LENGTH ( char )
用法：
返回字元串char的長度。
例子：
SQL> SELECTLENGTH('ORACLE') result FROM DUAL;
RESULT
----------
6
語法：
LN ( n )
用法：
返回n的自然對數。
參數：
n必須大於0。
例子：
SQL> SELECTLN(10) result FROM DUAL;
RESULT
----------
2.30258509
SQL> SELECT LN(0)result FROM DUAL;
SELECT LN(0) resultFROM DUAL
*
ERROR at line 1:
ORA-01428: argument'0' is out of range
語法：
LOCALTIMESTAMP ( timestamp_pecision)
用法：
以會話時區返回當前日期時間。它與CURRENT_TIMESTAMP的區別是，LOCALTIMESTAMP返回類型為TIMESTAMP，CURRENT_TIEMSTAMP返回類型為TIMESTAMP WITH TIME ZONE。
參數：
timestamp_pecision指定小數秒，預設為6。
例子：
SQL> SELECTLOCALTIMESTAMP FROM DUAL;
LOCALTIMESTAMP
-------------------------------------------------------
12-OCT-1102.37.38.969074 PM
SQL> SELECTLOCALTIMESTAMP(0) FROM DUAL;
LOCALTIMESTAMP(0)
-------------------------------------------------------
12-OCT-11 02.38.03PM
SQL> SELECTLOCALTIMESTAMP(9) FROM DUAL;
LOCALTIMESTAMP(9)
-------------------------------------------------------
12-OCT-1102.38.18.770422000 PM
SQL> SELECTLOCALTIMESTAMP(10) FROM DUAL;
SELECTLOCALTIMESTAMP(10) FROM DUAL
*
ERROR at line 1:
ORA-30088: datetime/intervalprecision is out of range
語法：
LOG ( n1, n2 )
用法：
返回以n1為底的n2的對數。
參數：
底n1為任意正數，除了0和1。n2可以為任意正數。
例子：
SQL> SELECTLOG(10,100) result FROM DUAL;
RESULT
----------
2
語法：
LOWER ( char )
用法：
返回字元串的小寫形式。
例子：
SQL> SELECT LOWER('ORACLE')result FROM DUAL;
RESULT
------
oracle
語法：
LPAD ( expr1, n [,expr2 ] )
用法：
返回長度為n的expr1字元串。如果expr1的長度小於n，則用expr2左填充expr1到長度n為止；expr2預設為一個空格符。如果expr1的長度大於n，截取expr1前n個字元返回。
例子：
SQL> SELECTLPAD('ORACLE',10) result FROM DUAL;
RESULT
----------
ORACLE
SQL> SELECTLPAD('ORACLE',10,'-') result FROM DUAL;
RESULT
----------
----ORACLE
SQL> SELECTLPAD('ORACLE',4) result FROM DUAL;
RESU
----
ORAC
語法：
LTRIM ( char [, set] )
用法：
刪除char從左邊開始的包含在set中的字元，直到遇到第一個不再set中的字元。set預設是空格符。
例子：
SQL> SELECTLTRIM('<===>ORACLE<===>', '<>=') result FROM DUAL;
RESULT
-----------
ORACLE<===>
SQL> SELECTLTRIM(' ORACLE ') result FROM DUAL;
RESULT
---------
ORACLE
語法
MAX ( expr )
用法：
聚合函數，返回所有行中expr的最大值。
例子：
SQL> SELECTMAX(sal) FROM scott.emp;
MAX(SAL)
----------
5000
語法：
MEDIAN ( expr )
用法：
聚合函數，返回所有行中expr的中位數。
參數：
expr只能是數值類型或日期時間類型。
例子：
SQL> SELECTMEDIAN(sal) FROM scott.emp;
MEDIAN(SAL)
-----------
1550
SQL> SELECTMEDIAN(ename) FROM scott.emp;
SELECT MEDIAN(ename)FROM scott.emp
*
ERROR at line 1:
ORA-30495: Theargument should be of numeric or date/datetime type.
語法
MIN ( expr )
用法：
聚合函數，返回所有行中expr的最小值。
例子：
SQL> SELECT MIN(sal)FROM scott.emp;
MIN(SAL)
----------
800
語法：
MOD ( n2, n1 )
用法：
返回n2除以n1後的餘數。如果n1為0，則返回n2。
註意：MOD的計算方式不用於數學上的經典模式n2 - n1 *FLOOR(n2/n1)。網上找到的MOD的計算方式為：DECODE(SIGN(n2), -1,'-') || MOD(ABS(n2), ABS(n1))，餘數的符號由被除數n2決定。另外，Oracle另一個關於餘數的函數REMAINDER的計算方式為n2 - n1 * ROUND(n2/n1)。所以要註意使用。
例子：
SQL> SELECTMOD(11,4), MOD(11,-4),
2 MOD(-11,4),MOD(-11,-4) FROM DUAL;
MOD(11,4) MOD(11,-4) MOD(-11,4) MOD(-11,-4)
-------------------- ---------- -----------
3 3 -3 -3
語法：
MONTHS_BETWEEN ( date1,date2 )
用法：
返回date1和date2之間的月份數。如果date1比date2晚，結果為正數；如果date1比date2早，結果為負數。如果date1和date2是該月中的相同日期，或者是該月中的最後一天，那麼結果是整數，否則結果具有小數部分。
例子：
SQL> SELECTMONTHS_BETWEEN(
2 TO_DATE('20111001','YYYYMMDD'),
3 TO_DATE('20111015','YYYYMMDD')) result FROM DUAL;
RESULT
----------
-.4516129
SQL> SELECTMONTHS_BETWEEN(
2 TO_DATE('20111003','YYYYMMDD'),
3 TO_DATE('20111103','YYYYMMDD')) result FROM DUAL;
RESULT
----------
-1
SQL> SELECTMONTHS_BETWEEN(
2 TO_DATE('20110331','YYYYMMDD'),
3 TO_DATE('20110228','YYYYMMDD')) result FROM DUAL;
RESULT
----------
1
語法：
NANVL ( n2, n1 )
用法：
該函數只對BINARY_FLOAT和BINARY_DOUBLE起作用。如果n2為NaN（不是數值），返回n1；否則返回n2。
例子：
CREATE TABLEfloat_point_demo
(dec_num NUMBER(10,2), bin_doubleBINARY_DOUBLE, bin_float BINARY_FLOAT);
INSERT INTOfloat_point_demo
VALUES (0,'NaN','NaN');
SQL> SELECT *
2 FROM float_point_demo;
DEC_NUM BIN_DOUBLE BIN_FLOAT
-------------------- ----------
0 Nan Nan
SQL> SELECTbin_float, NANVL(bin_float,0)
2 FROMfloat_point_demo;
BIN_FLOAT NANVL(BIN_FLOAT,0)
----------------------------
Nan 0
語法：
NEXT_DAY ( date,char )
用法：
返回date之後的第一個由char表示的星期幾。返回值類型是DATE。
例子：
SQL> SELECTNEXT_DAY(SYSDATE, 'SUNDAY') result FROM DUAL;
RESULT
------------
16-OCT-11
語法：
NULLIF ( expr1,expr2 )
用法：
比較expr1和expr2，如果相等，返回NULL，否則返回expr1。expr1不能是NULL的字面量。
例子：
SQL> SELECTNULLIF(1, 4/2) result FROM DUAL;
RESULT
----------
1
SQL> SELECTNVL(NULLIF('ORACLE', 'ORA' || 'CLE'), 'They are the same.') result
2 FROMDUAL;
RESULT
------------------
They are the same.
SQL> SELECTNULLIF(NULL, 'ORACLE') result FROM DUAL;
SELECT NULLIF(NULL,'ORACLE') result FROM DUAL
*
ERROR at line 1:
ORA-00932:inconsistent datatypes: expected - got CHAR
SQL> SELECTNULLIF(NULL, 100) result FROM DUAL;
SELECT NULLIF(NULL,100) result FROM DUAL
*
ERROR at line 1:
ORA-00932:inconsistent datatypes: expected - got CHAR
語法：
NUMTODSINTERVAL ( n,'interval_unit' )
用法：
將數值n轉化為INTERVAL DAY TO SECOND類型。
參數：
interval_unit必須為DAY，HOUR，MINUTE或SECOND。
例子：
SQL> SELECTNUMTODSINTERVAL(100, 'DAY') result FROM DUAL;
RESULT
--------------------------------------------------------------------
+00000010000:00:00.000000000
SQL> SELECTNUMTODSINTERVAL(123.45, 'hour') result FROM DUAL;
RESULT
--------------------------------------------------------------------
+000000005 03:27:00.000000000
SQL> SELECTNUMTODSINTERVAL(12345.6789, 'minute') result FROM DUAL;
RESULT
--------------------------------------------------------------------
+00000000813:45:40.734000000
SQL> SELECTNUMTODSINTERVAL(12345.6789, 'SECOND') result FROM DUAL;
RESULT
--------------------------------------------------------------------
+00000000003:25:45.678900000
語法：
NUMTOYMINTERVAL ( n,'interval_unit' )
用法：
將數值n轉化為INTERVAL YEAR TO MONTH類型。
參數：
interval_unit必須為YEAR或MONTH。
例子：
SQL> SELECTNUMTOYMINTERVAL(12345.6789, 'YEAR') result FROM DUAL;
RESULT
------------------------------------------------------------------
+000012345-08
SQL> SELECTNUMTOYMINTERVAL(12345.6789, 'MONTH') result FROM DUAL;
RESULT
------------------------------------------------------------------
+000001028-10
語法：
NVL ( expr1, expr2 )
用法：
如果expr1為NULL，返回expr2，否則返回expr1。
參數：
如果expr1和expr2的數據類型不同時，Oracle將進行隱式轉化。如果不能轉化，Oracle會返回一個錯誤。
例子：
SQL> SELECTNVL(NULL, 'IS NULL') result FROM DUAL;
RESULT
-------
IS NULL
SQL> SELECTNVL(123, 'ORACLE') result FROM DUAL;
SELECT NVL(123,'ORACLE') result FROM DUAL
*
ERROR at line 1:
ORA-01722: invalidnumber
語法：
NVL2 ( expr1, expr2,expr3 )
用法：
如果expr1不為null，則返回expr2，否則返回expr3。
參數：
expr1可以為任意數據類型；而expr2和expr3，除了LONG，可以為任意數據類型。如果expr2和expr3的數據類型不同，Oracle則會隱式地將其中一種專為另一種數據類型。如果它們不能轉化，會報錯。如select nvl2(null, 123, 'abc') from dual;會報錯ORA-01722 invalid number，因為abc不能轉化為數值。如果expr2是字元或數值類型，則根據以下規則進行隱式轉化：1. 如果expr2是字元類型，Oracle將expr3轉化成expr2的數據類型，除非expr3是null。 2. 如果expr2是數值類型，Oracle會根據數值優先順序來進行轉化。其他情況根據隱式類型轉化表進行轉化。
註：數值優先順序： BINARY_DOUBLE > BINARY_FLOAT > NUMBER
例子：
SELECT NVL2(NULL,'NOT NULL', 'NULL') result FROM dual;
RESULT
------
NULL
語法：
POWER ( n2, n1 )
用法：
返回n2的n1次方。n2和n1可以是任意數值，但當n2為負數時，n1必須是整數。
例子：
SQL> SELECTPOWER(2, 32) result FROM DUAL;
RESULT
----------
4294967296
SQL> SELECTPOWER(10, 2.5) result FROM DUAL;
RESULT
----------
316.227766
SQL> SELECTPOWER(-10, -2) result FROM DUAL;
RESULT
----------
.01
SQL> SELECT POWER(-10,3.3) result FROM DUAL;
SELECT POWER(-10,3.3) result FROM DUAL
*
ERROR at line 1:
ORA-01428: argument'-10' is out of range
語法：
RAWTOHEX ( raw )
用法：
將raw轉化成16進位表示的字元串。
例子：
SQL> SELECTRAWTOHEX(HEXTORAW('3D3E3F')) result FROM DUAL;
RESULT
------
3D3E3F
語法：
REGEXP_COUNT (source_char, pattern [, position [, match_param]] )
用法：
以正則表達式搜索並返回模式pattern在source_char中出現的次數。
參數：
pattern最多可以有512位元組，如果其數據類型與source_char不同，Oracle會將pattern轉化為source_char的數據類型。REGEXP_COUNT會忽略pattern中的子表達式的括弧，如pattern'(123(45))'等於'12345'。position表示從source_char的第幾位開始搜索，預設為1。match_param可以改變匹配行為，如i表示不區分大小寫；c表示區分大小寫；n表示'.'可以匹配換行符；m可以將source_char作為多行處理；x表示忽略空格符。如果指定了矛盾的兩個值，如'ic'，Oracle會取後一個值。如果指定其他不在此範圍的值，Oracle會報錯。如果忽略match_param，預設的大小寫模式由NLS_SORT參數決定；'.'不能匹配換行符；source_char是單行模式。
例子：
SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[AEIOU]') result FROM DUAL
RESULT
----------
7
SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'ic') result
2 FROMDUAL;
RESULT
----------
0
SQL> SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'icu') result
2 FROMDUAL;
SELECTREGEXP_COUNT('ORACLE DATABASE', '[aeiou]', 1, 'icu') result
*
ERROR at line 1:
ORA-01760: illegalargument for function
語法：
REGEXP_INSTR (source_char, pattern [, position [, occurrence [, return_opt [, match_param [,subexpr ] ] ] ] ] )
用法：
以正則表達式搜索，返回匹配pattern的位置，可能是匹配子字元串的開始或結束位置，由參數return_option決定。如果沒有匹配，返回0。
參數：
相同的參數可以參考REGEXP_COUNT。occurrence表示搜索匹配的第幾次，如果大於1，則從第一次匹配後的第一字元開始搜索第二次匹配。這個行為與INSTR不同，INSTR是從第一次匹配的第二個字元開始搜索第二次匹配。return_option預設為0，表示返回匹配的第一個字元的位置，即匹配的開頭；如果為1，則返回匹配之後的位置，即匹配的結尾。subexpr的範圍是從0到9，表示匹配的子模式，如'0123(((abc)(de)f)ghi)45(678)'中，有5個子模式，"abcdefghi"，"abcdef"，"abc"，"de"，"678"。如果subexpr為0，則返回匹配整個模式的位置，如果大於0，則返回匹配整個模式中相應的子模式的位置。如果subexpr為NULL，則返回NULL。預設值為0。
例子：
SQL> SELECTREGEXP_INSTR('BOBOB','BOB',1,2) FROM DUAL;
REGEXP_INSTR('BOBOB','BOB',1,2)
-------------------------------
0
SQL> SELECTINSTR('BOBOB','BOB',1,2) FROM DUAL;
INSTR('BOBOB','BOB',1,2)
------------------------
3
SQL> SELECTREGEXP_INSTR('0123456789','123',1,1,0) result FROM DUAL;
RESULT
----------
2
SQL> SELEC
