前言

Oracle是一種關系型數據庫管理系統(tǒng)，它提供了許多內置函數，以便用戶可以更輕松地處理數據。

1. 字符串函數

（1）lengthb/length

計算字符串長度

• lengthb求得是字節(jié)（Byte，1Byte=8bit）長度
• length求得是字符長度

select lengthb('中') from dual;
select length('中') from dual;

ZHS16GBK下，lengthb(‘中’)為2字節(jié)，length(‘中’)為1（個字符），即一個字符占兩個字節(jié)

數據庫中存儲的CHAR(19) 表示占19個字節(jié)。

（2）SUBSTR

SUBSTR用于截取字符串的子串，需要注意的是Oracle 數據庫中字符串的下標是從 1 開始而不是從 0 開始的。該函數的語法如下：

SUBSTR( string, start [, length] )

• string是要截取的字符串
• start是要開始截取的位置
• length是要截取的子串長度（可選）

e.g.

select substr('abcdefg',0,3) from dual; 

輸出

abc

select substr('abcdefg',1,3) from dual; 

輸出

abc

select substr('abcdefg',2,3) from dual; 

輸出

bcd

select substr('abcdefg',-3,3) from dual; 

輸出

efg

（3）INSTR

INSTR 在字符串中搜索指定字符，返回發(fā)現(xiàn)指定字符的位置。該函數的語法如下：

INSTR( string, substring [, start_position [, occurrence ]] )

• string是要搜索的字符串
• substring是要查找的子
• start_position是要開始搜索的位置（可選）
• occurrence是要查找的子串出現(xiàn)的次數（可選）

e.g.

Select instr('oracle training','ai') From dual; 

輸出

10

（4）CONCAT

CONCAT連接兩個字符串

e.g.

SELECT CONCAT('Hello ', 'World', '!') FROM dual;

輸出

Hello World!

按要求更新指定列：

Update t_skzy Set website=concat('http://'，website) Where website Not Like 'http%' And website Like '%.%'

（5）REPLACE

REPLACE用于替換字符串的指定子串。該函數的語法如下：

REPLACE( string, substring1 [, substring2] )

• string是要替換子串的字符串
• substring1是要被替換的子串
• substring2是用來替換substring1的字符串（可選）

e.g.

SELECT REPLACE('Hello World!', 'Hello', 'Goodbye') FROM dual;

輸出：

“Goodbye World！”

（6）TRIM, LTRIM, RTRIM

TRIM：去除字符串的空格或指定字符

語法如下：

TRIM([leading|trailing|both] [trim_character] FROM string)

• leading|trailing|both：可選參數，用于指定去除字符串的空格或指定字符是在字符串的前面、后面還是兩邊，默認為 both。
• trim_character：可選參數，用于指定要去除的字符，默認為字符串中的空格。
• string：必需參數，要去除空格或指定字符的字符串。

LTRIM ：去除字符串左側的空格或指定字符。

語法如下：

LTRIM([trim_character] FROM string)

trim_character：可選參數，用于指定要去除的字符，默認為字符串中的空格。string：必需參數，要去除空格或指定字符的字符串。

e.g.

Select ltrim('trimtest ltrim ','trim') From dual 

輸出

test ltrim

RTRIM ：去除字符串右側的空格或指定字符。

語法如下：

RTRIM([trim_character] FROM string)

• trim_character：可選參數，用于指定要去除的字符，默認為字符串中的空格。
• string：必需參數，要去除空格或指定字符的字符串。

（7）ASCII

ASCII返回給定字符串中第一個字符的ASCII代碼值。

e.g.

SELECT ASCII('A') FROM dual;

輸出

65

（8）NVL

NVL( string1， replace_with)

如果string1為NULL，則NVL函數返回replace_with的值，否則返回string1的值。

例如，以下查詢將返回一個包含員工的職務和部門名稱的結果，如果員工所在的部門為空，則返回“Unknown Department”：

SELECT job_id, NVL(department_name, 'Unknown Department') 
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

（9）INITCAP,LOWER,UPPER

• INITCAP 將字符串第一個字母變?yōu)榇髮?/li>
• LOWER 將字符串所有字母小寫
• UPPER 將字符串所有字母大寫

2. 數學函數

（1） ROUND

ROUND函數用于將數字四舍五入到指定的小數位數。例如將數字3.1415926四舍五入到兩個小數位：

SELECT ROUND(3.1415926, 2) AS RoundedNumber FROM dual;

輸出

3.14

（2）TRUNC

TRUNC函數用于將數字截斷為指定的小數位數。例如將數字3.1415926截斷為兩個小數位：

SELECT TRUNC(3.1415926, 2) AS TruncatedNumber FROM dual;

輸出

3.14

（3）ABS

ABS函數用于計算數字的絕對值。例如計算數字-10的絕對值：

SELECT ABS(-10) AS AbsoluteValue FROM dual;

輸出

10

（4）POWER

POWER函數用于計算數字的冪。例如計算2的3次方：

SELECT POWER(2, 3) AS PowerValue FROM dual;

輸出

8

（5）MOD

MOD取模運算，返回兩個數相除的余數。

e.g.

SELECT MOD(5, 2) FROM dual;

輸出

1

（6）其他

• CEIL: 返回比輸入值大的最小整數。
• FLOOR: 返回比輸入值小的最大整數。
• MOD: 返回兩個數值相除的余數。

3. 日期函數

（1）CURRENT_DATE

CURRENT_DATE是一個SQL標準函數，返回當前日期（不帶時間），可以在SELECT語句中使用。例如：

SELECT CURRENT_DATE FROM DUAL; 

返回當前日期，格式為YYYY-MM-DD。

（2）SYSDATE

SYSDATE是Oracle的系統(tǒng)函數，返回當前日期和時間（數據庫服務器所在時區(qū)的日期和時間，而不是客戶端的時區(qū)），包括日期和精確到秒的時間。例如，獲取當前日期和時間：

SELECT SYSDATE AS CurrentDateTime FROM dual;

返回當前日期和時間，格式為YYYY-MM-DD HH:MI:SS。

（2）ADD_MONTHS函數

ADD_MONTHS函數用于在日期上添加指定的月數。例如，在當前日期上添加3個月：

SELECT ADD_MONTHS(SYSDATE, 3) AS FutureDate FROM dual;

（3）MONTHS_BETWEEN函數

MONTHS_BETWEEN函數用于計算兩個日期之間的月數。例如，計算兩個日期之間的月數：

SELECT MONTHS_BETWEEN('01-JAN-2022', '01-JAN-2021') AS MonthDifference FROM dual;

輸出

12

（4）TO_CHAR/TO_DATE

• TO_CHAR函數可以將日期型數據轉換為字符串
• TO_DATE函數則可以將字符串型數據轉換為日期型數據

e.g.

select to_char(current_date,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_date('1999/01/01','yyyy/mm/dd') from dual;

4. 聚合函數

聚合函數用于對數據進行聚合計算，如求和、平均數、最大值、最小值等，聚焦函數不能作為條件用在where子句中，需要與having，group一起使用

（1）COUNT

COUNT函數用來計算某個表或某個查詢語句返回的結果集中的行數。如果指定了DISTINCT關鍵字，則將去除重復的行計入計數。

例如，查詢含員工總數：

SELECT COUNT(*) FROM employees;

（2）SUM

SUM函數用來計算某個表或某個查詢語句返回的結果集中某個列的數值之和。

例如，查詢員工月薪總和：

SELECT SUM(salary) FROM employees;

（3）AVG

AVG函數用來計算某個表或某個查詢語句返回的結果集中某個列的數值平均值，其語法如下：

例如，查詢員工平均月薪：

SELECT AVG(salary) FROM employees;

（4）MAX/MIN

MAX/MIN分別用來計算某個表或某個查詢語句返回的結果集中某個列的數值最大值或最小值。

例如，查詢含員工最高月薪：

SELECT MAX(salary) FROM employees;

5. 其他

（1）DECODE

DECODE：函數用來根據不同的條件返回不同的值，其語法如下：

DECODE(value， if1， then1， if2，then2， if3，then3， . . . else )

當每個value值被測試，如果value的值為if1，Decode 函數的結果是then1；如果value等于if2，Decode函數結果是then2；等等。如果value結果不等于給出的任何配對時，Decode 結果就返回else ?？梢越o出多個if/then 配對。

例如，員工信息表中有出生年份和州名，需要統(tǒng)計不同年份、不同大洲的人數。

即形成如下形式的統(tǒng)計表：

Select csrq 年份,
sum(decode(zm,'大洋洲',cou)) 大洋洲,
sum(decode(zm,'歐洲',cou)) 歐洲,
sum(decode(zm,'亞洲',cou)) 亞洲,
sum(decode(zm,'非洲',cou)) 非洲,
sum(decode(zm,'美洲',cou)) 美洲 From (
select t.zm,substr(csrq,1,4) csrq,Count(*) cou from employee t Group By t.zm,substr(csrq,1,4)) Group By csrq Order By csrq

（2）CASE

CASE根據指定的條件返回不同的值。該函數與DECODE功能類似，但它更加靈活，可以嵌套使用，其語法如下：

CASE expression
  WHEN value1 THEN result1
  [WHEN value2 THEN result2 ...]
  [ELSE default]
END

• expression是要比較的值
• value1、value2等是要比較的條件
• result1、result2等是對應的返回值（如果expression與某個value相等，則返回相對應的result）
• default是一個可選項，表示當expression與所有value都不相等時要返回的默認值。

例如實現(xiàn)前述功能的SQL語句如下：

Select substr(csrq,1,4) 出生年份, 
Sum(Case When zm='大洋洲' Then 1 else 0 End) 大洋洲,
Sum(Case When zm='歐洲' Then 1 Else 0 End) 歐洲,
Sum(Case When zm='亞洲' Then 1 Else 0 End) 亞洲,
Sum(Case When zm='非洲' Then 1 Else 0 End) 非洲,
Sum(Case When zm='美洲' Then 1 Else 0 End) 美洲
From employee Group By substr(csrq,1,4) Order By 出生年份

（3）ROLLUP/CUBE

• ROLLUP是GROUP BY子句的一種擴展，可以為每個分組返回小計記錄以及為所有分組返回總計記錄。
• CUBE也是GROUP BY子句的一種擴展，可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。

例如形成如下形式的統(tǒng)計表：

Select 年份,Sum(大洋洲) 大洋洲,Sum(歐洲) 歐洲,Sum(亞洲) 亞洲,Sum(非洲) 非洲,Sum(美洲) 美洲 From(
Select csrq 年份,
sum(decode(zm,'大洋洲',cou)) 大洋洲,
sum(decode(zm,'歐洲',cou)) 歐洲,
sum(decode(zm,'亞洲',cou)) 亞洲,
sum(decode(zm,'非洲',cou)) 非洲,
sum(decode(zm,'美洲',cou)) 美洲 From (
select t.zm,substr(csrq,1,4) csrq,Count(*) cou from employee t Group By t.zm,substr(csrq,1,4)) Group By csrq)
Group By Rollup(年份) Order By 年份

（4）MD5

DBMS_OBFUSCATION_TOOLKIT.MD5是MD5編碼的數據包函數，該函數只能直接在程序包中調用，不能直接應用于SELECT語句。

DBMS_OBFUSCATION_TOOLKIT.MD5返回的字串，是RAW類型，要正確顯示，需要經過Utl_Raw.Cast_To_Raw轉換：

CREATE OR REPLACE Function MD5(passwd Varchar2)
Return Varchar
Is
md5_output Varchar2(32);
Begin
md5_output:=utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>passwd));
Return md5_output;
End;

（5）CAST

CAST函數用于將一個數據類型轉換為另一種數據類型。

• 將一個字符串轉換為數值型數據：

SELECT CAST('123' AS NUMBER) FROM dual;

• 將一個日期字符串轉換為日期類型：

SELECT CAST('2022-04-21' AS DATE) FROM dual;

（6）查詢Blob/Clob類型字段

DBMS_LOB是Oracle數據庫提供的一個用于操作大型對象（LOB）數據的包。其中，LOB包括四種類型：CLOB、NCLOB、BLOB和BFILE。

DBMS_LOB包提供了一系列子程序，可以用于讀取、寫入、截斷、復制、比較等LOB對象的操作：

• DBMS_LOB.READ：用于從LOB對象中讀取數據；
• DBMS_LOB.WRITE：用于向LOB對象中寫入數據；
• DBMS_LOB.TRIM：用于截斷LOB對象中的數據；
• DBMS_LOB.COPY：用于將LOB對象中的數據復制到另一個LOB對象中；
• DBMS_LOB.COMPARE：用于比較兩個LOB對象中的數據是否相同。

DBMS_LOB包有多個內置函數：

• dbms_lob.append：追加LOB值
• dbms_lob.substr：截取LOB值
• dbms_lob.instr：查找LOB值中的字符串位置
• dbms_lob.getlength：查詢Blob/Clob類型字段的長度

e.g.

Select * From table_name Where dbms_lob.instr(Column,utl_raw.cast_to_raw('內容',1,1))>0;

總結

到此這篇關于Oracle常用函數超詳細整理的文章就介紹到這了,更多相關Oracle常用函數內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論