Oracle單行函數

單行函數格式：

函數名【（參數1，參數2，參數3，...）】

其中參數可以為使用者定義的常量、變數、列名和運算式。

單行函數只對錶中的一行資料進行操作，並且對每一行資料只產生一個輸出結果。

單行函數可用在SELECT、WHERE和ORDER BY的子句中，而且單行函數可以嵌套。

單行函數包含字元型。數字型、日期型、轉換型和一般型函數。

1.單行字元型函數

（1）LOWER（列名|運算式）：該函數用於把字元轉換成小寫。

SELECT LOWER('SQL: Structural Query Language')
FROM dual;

dual是系統的一個虛表（偽表），原因：

在查詢語句中必須包含SELECT和FROM兩個子句，可是LOWER('SQL: Structural Query Language')不屬於任何錶，於是用Oracle提供的虛表dual來解決這一難題。

（2）UPPER（列名|運算式）：該函數用於把字元轉換成大寫。

SELECT UPPER('sql is used exclusively in rdbmses')
FROM dual;

（3）INITCAP（列名|運算式）：該函數用於把每個字的頭一個字元轉換成大寫，其餘轉換成小寫。

SELECT INITCAP('SQL is an ENGLISH LIKE language')
FROM dual;

（4）CONCAT（列名|運算式，列名|運算式）：該函數用於把第1個字串和第2個字串串連成一個字串。

SELECT CONCAT('SQL alows you to manipulate the data in DB',' without any programming knowledge')
FROM dual;

（5）SUBSTR（列名|運算式，m，[n]）：該函數用於返回指定的子串，該子串從第m個字元開始，其長度為n。

SELECT SUBSTR('SQL lets you concentrate on what has to be done',14)
FROM dual;

（6）LENGTH（列名|運算式）：該函數用於返回列或運算式中字串的長度。

SELECT LENGTH('SQL does no let you concentrate on how it will be achieved')
FROM dual;

（7）INSTR（列名|運算式，‘字串’，[m]，[n]）：該函數用於返回所給字串的數字位置，m表示從第m個字元開始搜尋，n表示所給字串出現的次數，它們的預設值都為1。

SELECT INSTR('SQL allows for dynamic DB changes','F')
FROM dual;
SELECT INSTR('SQL allows for dynamic DB changes','f')
FROM dual;

 （8）TRIM（[leading|trailing|both]要去掉的字元FROM源字串）：該函數能夠從“源字串”中的頭（leading）部、尾（trailing)部或頭部和尾部中（both)去掉“要去掉的字串”。如果沒有指定頭或尾，TRIM函數按預設（both）處理（該函數是8i引入的，在8i之前的版本中是LTRIM和RTRIM兩個函數）。

SELECT TRIM('?' FROM '?SQL*PLUS is the SQL implementation used in an Oracle RDBMS or ORDBMS.')
FROM dual;
SELECT TRIM('?' FROM 'It can process data in sets of rows??')
FROM dual;
SELECT TRIM('s' FROM 'sql*plus is a fourth generation query language')
FROM dual;
SELECT TRIM(trailing 's' FROM 'sql*plus is a fourth generation query languages')
FROM dual;

（9）REPLACE（本文運算式，要搜尋的字串，替換字串）：該函數用於在“本文運算式”中尋找“要搜尋的字串”，如果找到了就用“替換字串”替代。

SELECT REPLACE('SQL*PLUS supports loops or if statements','supports','does not support')
FROM dual;

執行個體：

SELECT empno AS "Code",UPPER(ename) NAME,INITCAP(job) "Job"
FROM emp
WHERE LOWER(job)='salesman';

SELECT CONCAT(ename,job) "Employee",SUBSTR(job,1,5) "Title",LENGTH(ename) "LENGTH",INSTR(job,'M')
FROM emp
WHERE LOWER(job)='salesman';

2.數字型函數

ROUND（列名|運算式，n）：該函數將列名或運算式所表示的數值四捨五入到小數點的n位。

TRUNC（列名|運算式，n）：該函數將列名或運算式所表示的數值取到小數點的後n位。

MOD（m，n）：該函數將m除以n並取餘數。

select round(168.888,1),TRUNC(168.888,1)
from dual;

select round(168.888),TRUNC(168.888)
from dual;

 select round(168.888,-1),TRUNC(168.888,-1)
from dual;

select mod(1900,400)
from dual;

3.日期型資料

Oracle的日期型資料的內部儲存格式為：世紀，月，日，時，分，秒。

從Oracle9i開始，日期型資料輸入和輸出的預設格式為DD-MON-RR，而在這之前的版本中為DD-MON-YY。

如果使用的是中文作業系統，而資料庫的字元集為美國英語，為了使日期型資料的顯示正確，輸入如下命令：

alter session set NLS_DATE_LANGUAGE='AMERICAN';

Oracle提供了系統日期函數SYSDATE，它返回當前的系統時間。

select sysdate from dual;

把一個日期型資料和一個數字相加減，結果仍為日期型。

select sysdate-10 from dual;
select sysdate+10 from dual;

兩個日期型資料相減，其結果為數字型。to_date將字串轉換成日期型資料。

select to_date('15-JUL-02')-sysdate from dual;

把一個日期型資料和一個小時數相加減後除以24，其結果仍為日期型。

select sysdate - 22/24 from dual;

select empno,ename,job,sal,(sysdate-hiredate)/365 "Years" from emp where job like 'SAL%';