前言

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

1. 字符串函數(shù)

（1）lengthb/length

計(jì)算字符串長(zhǎng)度

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

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

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

數(shù)據(jù)庫(kù)中存儲(chǔ)的CHAR(19) 表示占19個(gè)字節(jié)。

（2）SUBSTR

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

SUBSTR( string, start [, length] )

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

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)指定字符的位置。該函數(shù)的語法如下：

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

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

e.g.

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

輸出

10

（4）CONCAT

CONCAT連接兩個(gè)字符串

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用于替換字符串的指定子串。該函數(shù)的語法如下：

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：可選參數(shù)，用于指定去除字符串的空格或指定字符是在字符串的前面、后面還是兩邊，默認(rèn)為 both。
• trim_character：可選參數(shù)，用于指定要去除的字符，默認(rèn)為字符串中的空格。
• string：必需參數(shù)，要去除空格或指定字符的字符串。

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

語法如下：

LTRIM([trim_character] FROM string)

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

e.g.

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

輸出

test ltrim

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

語法如下：

RTRIM([trim_character] FROM string)

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

（7）ASCII

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

e.g.

SELECT ASCII('A') FROM dual;

輸出

65

（8）NVL

NVL( string1， replace_with)

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

例如，以下查詢將返回一個(gè)包含員工的職務(wù)和部門名稱的結(jié)果，如果員工所在的部門為空，則返回“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 將字符串第一個(gè)字母變?yōu)榇髮?/li>
• LOWER 將字符串所有字母小寫
• UPPER 將字符串所有字母大寫

2. 數(shù)學(xué)函數(shù)

（1） ROUND

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

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

輸出

3.14

（2）TRUNC

TRUNC函數(shù)用于將數(shù)字截?cái)酁橹付ǖ男?shù)位數(shù)。例如將數(shù)字3.1415926截?cái)酁閮蓚€(gè)小數(shù)位：

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

輸出

3.14

（3）ABS

ABS函數(shù)用于計(jì)算數(shù)字的絕對(duì)值。例如計(jì)算數(shù)字-10的絕對(duì)值：

SELECT ABS(-10) AS AbsoluteValue FROM dual;

輸出

10

（4）POWER

POWER函數(shù)用于計(jì)算數(shù)字的冪。例如計(jì)算2的3次方：

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

輸出

8

（5）MOD

MOD取模運(yùn)算，返回兩個(gè)數(shù)相除的余數(shù)。

e.g.

SELECT MOD(5, 2) FROM dual;

輸出

1

（6）其他

• CEIL: 返回比輸入值大的最小整數(shù)。
• FLOOR: 返回比輸入值小的最大整數(shù)。
• MOD: 返回兩個(gè)數(shù)值相除的余數(shù)。

3. 日期函數(shù)

（1）CURRENT_DATE

CURRENT_DATE是一個(gè)SQL標(biāo)準(zhǔn)函數(shù)，返回當(dāng)前日期（不帶時(shí)間），可以在SELECT語句中使用。例如：

SELECT CURRENT_DATE FROM DUAL; 

返回當(dāng)前日期，格式為YYYY-MM-DD。

（2）SYSDATE

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

SELECT SYSDATE AS CurrentDateTime FROM dual;

返回當(dāng)前日期和時(shí)間，格式為YYYY-MM-DD HH:MI:SS。

（2）ADD_MONTHS函數(shù)

ADD_MONTHS函數(shù)用于在日期上添加指定的月數(shù)。例如，在當(dāng)前日期上添加3個(gè)月：

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

（3）MONTHS_BETWEEN函數(shù)

MONTHS_BETWEEN函數(shù)用于計(jì)算兩個(gè)日期之間的月數(shù)。例如，計(jì)算兩個(gè)日期之間的月數(shù)：

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

輸出

12

（4）TO_CHAR/TO_DATE

• TO_CHAR函數(shù)可以將日期型數(shù)據(jù)轉(zhuǎn)換為字符串
• TO_DATE函數(shù)則可以將字符串型數(shù)據(jù)轉(zhuǎn)換為日期型數(shù)據(jù)

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. 聚合函數(shù)

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

（1）COUNT

COUNT函數(shù)用來計(jì)算某個(gè)表或某個(gè)查詢語句返回的結(jié)果集中的行數(shù)。如果指定了DISTINCT關(guān)鍵字，則將去除重復(fù)的行計(jì)入計(jì)數(shù)。

例如，查詢含員工總數(shù)：

SELECT COUNT(*) FROM employees;

（2）SUM

SUM函數(shù)用來計(jì)算某個(gè)表或某個(gè)查詢語句返回的結(jié)果集中某個(gè)列的數(shù)值之和。

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

SELECT SUM(salary) FROM employees;

（3）AVG

AVG函數(shù)用來計(jì)算某個(gè)表或某個(gè)查詢語句返回的結(jié)果集中某個(gè)列的數(shù)值平均值，其語法如下：

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

SELECT AVG(salary) FROM employees;

（4）MAX/MIN

MAX/MIN分別用來計(jì)算某個(gè)表或某個(gè)查詢語句返回的結(jié)果集中某個(gè)列的數(shù)值最大值或最小值。

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

SELECT MAX(salary) FROM employees;

5. 其他

（1）DECODE

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

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

當(dāng)每個(gè)value值被測(cè)試，如果value的值為if1，Decode 函數(shù)的結(jié)果是then1；如果value等于if2，Decode函數(shù)結(jié)果是then2；等等。如果value結(jié)果不等于給出的任何配對(duì)時(shí)，Decode 結(jié)果就返回else ?？梢越o出多個(gè)if/then 配對(duì)。

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

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

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根據(jù)指定的條件返回不同的值。該函數(shù)與DECODE功能類似，但它更加靈活，可以嵌套使用，其語法如下：

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

• expression是要比較的值
• value1、value2等是要比較的條件
• result1、result2等是對(duì)應(yīng)的返回值（如果expression與某個(gè)value相等，則返回相對(duì)應(yīng)的result）
• default是一個(gè)可選項(xiàng)，表示當(dāng)expression與所有value都不相等時(shí)要返回的默認(rèn)值。

例如實(shí)現(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子句的一種擴(kuò)展，可以為每個(gè)分組返回小計(jì)記錄以及為所有分組返回總計(jì)記錄。
• CUBE也是GROUP BY子句的一種擴(kuò)展，可以返回每一個(gè)列組合的小計(jì)記錄，同時(shí)在末尾加上總計(jì)記錄。

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

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編碼的數(shù)據(jù)包函數(shù)，該函數(shù)只能直接在程序包中調(diào)用，不能直接應(yīng)用于SELECT語句。

DBMS_OBFUSCATION_TOOLKIT.MD5返回的字串，是RAW類型，要正確顯示，需要經(jīng)過Utl_Raw.Cast_To_Raw轉(zhuǎn)換：

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函數(shù)用于將一個(gè)數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型。

• 將一個(gè)字符串轉(zhuǎn)換為數(shù)值型數(shù)據(jù)：

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

• 將一個(gè)日期字符串轉(zhuǎn)換為日期類型：

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

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

DBMS_LOB是Oracle數(shù)據(jù)庫(kù)提供的一個(gè)用于操作大型對(duì)象（LOB）數(shù)據(jù)的包。其中，LOB包括四種類型：CLOB、NCLOB、BLOB和BFILE。

DBMS_LOB包提供了一系列子程序，可以用于讀取、寫入、截?cái)唷?fù)制、比較等LOB對(duì)象的操作：

• DBMS_LOB.READ：用于從LOB對(duì)象中讀取數(shù)據(jù)；
• DBMS_LOB.WRITE：用于向LOB對(duì)象中寫入數(shù)據(jù)；
• DBMS_LOB.TRIM：用于截?cái)郘OB對(duì)象中的數(shù)據(jù)；
• DBMS_LOB.COPY：用于將LOB對(duì)象中的數(shù)據(jù)復(fù)制到另一個(gè)LOB對(duì)象中；
• DBMS_LOB.COMPARE：用于比較兩個(gè)LOB對(duì)象中的數(shù)據(jù)是否相同。

DBMS_LOB包有多個(gè)內(nèi)置函數(shù)：

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

e.g.

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

總結(jié)

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

最新評(píng)論