oracle常用函數(shù)超詳細(xì)整理

前言

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

1. 字符串函數(shù)

（1）lengthb/length

計算字符串長度

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

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

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

數(shù)據(jù)庫中存儲的char(19) 表示占19個字節(jié)。

（2）substr

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

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)指定字符的位置。該函數(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連接兩個字符串

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返回給定字符串中第一個字符的ascii代碼值。

e.g.

select ascii('a') from dual;

輸出

65

（8）nvl

nvl( string1， replace_with)

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

例如，以下查詢將返回一個包含員工的職務(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 將字符串第一個字母變?yōu)榇髮?/li>
• lower 將字符串所有字母小寫
• upper 將字符串所有字母大寫

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

（1） round

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

select round(3.1415926, 2) as roundednumber from dual;

輸出

3.14

（2）trunc

trunc函數(shù)用于將數(shù)字截斷為指定的小數(shù)位數(shù)。例如將數(shù)字3.1415926截斷為兩個小數(shù)位：

select trunc(3.1415926, 2) as truncatednumber from dual;

輸出

3.14

（3）abs

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

select abs(-10) as absolutevalue from dual;

輸出

10

（4）power

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

select power(2, 3) as powervalue from dual;

輸出

8

（5）mod

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

e.g.

select mod(5, 2) from dual;

輸出

1

（6）其他

• ceil: 返回比輸入值大的最小整數(shù)。
• floor: 返回比輸入值小的最大整數(shù)。
• mod: 返回兩個數(shù)值相除的余數(shù)。

3. 日期函數(shù)

（1）current_date

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

select current_date from dual; 

返回當(dāng)前日期，格式為yyyy-mm-dd。

（2）sysdate

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

select sysdate as currentdatetime from dual;

返回當(dāng)前日期和時間，格式為yyyy-mm-dd hh:mi:ss。

（2）add_months函數(shù)

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

select add_months(sysdate, 3) as futuredate from dual;

（3）months_between函數(shù)

months_between函數(shù)用于計算兩個日期之間的月數(shù)。例如，計算兩個日期之間的月數(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ù)用于對數(shù)據(jù)進(jìn)行聚合計算，如求和、平均數(shù)、最大值、最小值等，聚焦函數(shù)不能作為條件用在where子句中，需要與having，group一起使用

（1）count

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

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

select count(*) from employees;

（2）sum

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

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

select sum(salary) from employees;

（3）avg

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

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

select avg(salary) from employees;

（4）max/min

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

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

select max(salary) from employees;

5. 其他

（1）decode

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

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

當(dāng)每個value值被測試，如果value的值為if1，decode 函數(shù)的結(jié)果是then1；如果value等于if2，decode函數(shù)結(jié)果是then2；等等。如果value結(jié)果不等于給出的任何配對時，decode 結(jié)果就返回else 。可以給出多個if/then 配對。

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

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

case expression
when value1 then result1
[when value2 then result2 ...]
[else default]
end

• expression是要比較的值
• value1、value2等是要比較的條件
• result1、result2等是對應(yīng)的返回值（如果expression與某個value相等，則返回相對應(yīng)的result）
• default是一個可選項，表示當(dāng)expression與所有value都不相等時要返回的默認(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ò)展，可以為每個分組返回小計記錄以及為所有分組返回總計記錄。
• cube也是group by子句的一種擴(kuò)展，可以返回每一個列組合的小計記錄，同時在末尾加上總計記錄。

例如形成如下形式的統(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編碼的數(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ù)用于將一個數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型。

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

select cast('123' as number) from dual;

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

select cast('2022-04-21' as date) from dual;

（6）查詢blob/clob類型字段

dbms_lob是oracle數(shù)據(jù)庫提供的一個用于操作大型對象（lob）數(shù)據(jù)的包。其中，lob包括四種類型：clob、nclob、blob和bfile。

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

• dbms_lob.read：用于從lob對象中讀取數(shù)據(jù)；
• dbms_lob.write：用于向lob對象中寫入數(shù)據(jù)；
• dbms_lob.trim：用于截斷l(xiāng)ob對象中的數(shù)據(jù)；
• dbms_lob.copy：用于將lob對象中的數(shù)據(jù)復(fù)制到另一個lob對象中；
• dbms_lob.compare：用于比較兩個lob對象中的數(shù)據(jù)是否相同。

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

• 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('內(nèi)容',1,1))>0;

總結(jié)

關(guān)于oracle常用函數(shù)超詳細(xì)整理的文章就介紹至此，更多相關(guān)oracle常用函數(shù)內(nèi)容請搜索碩編程以前的文章，希望以后支持碩編程！