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%';