Oracle提供了不少內建函數，熟練使用這些函數，可以大大提高我們工作效率。函數可以接受0個或多個入參，並返回一個輸出結果。
Oracle函數分為單行函數和聚合函數
單行函數：對每一個函數應用在表的記錄中時，只能輸入一行結果，返回一個結果。
常見的單行函數有如下四種
① 數值型函數：對數位進行計算，返回一個數位。
②字元函數：對字串操作。
③轉換函數：可以將一種資料型別轉換為另外一種資料型別。
④日期函數：對日期和時間進行處理。
⑤ null函數：處理null值的相關函數
聚合函數：聚合函數同時可以對多行資料進行操作，並返回一個結果。
abs（n）函數
用於返回絕對值
SQL> select abs(10),abs(-10),abs('100') from dual;
ABS(10) ABS(-10) ABS('100')
---------- ---------- ----------
10 10 100
mod(n2,n1)
返回n2除以n1的餘數
SQL> select mod(5,2),mod(8/3,3),mod('10',2),mod(-10,6),mod(3,0) from dual;
MOD(5,2) MOD(8/3,3) MOD('10',2) MOD(-10,6) MOD(3,0)
---------- ---------- ----------- ---------- ----------
1 2.66666666 0 -4 3
sign(n) 函數
n為正返回1，n為0返回0，n為負返回-1
SQL> select sign(-2),sign(2),sign(0.0),sign(-3*2) from dual;
SIGN(-2) SIGN(2) SIGN(0.0) SIGN(-3*2)
---------- ---------- ---------- ----------
-1 1 0 -1
cos(n):返回餘弦值 acos(n):返回反餘弦值 sin(n):返回正弦值 asin(n) tan(n):返回正切值 atan(n):返回反正切值
SQL> select cos(3.1415926),acos(1),sin(0.5),asin(1),tan(1),atan(1) from dual;
COS(3.1415926) ACOS(1) SIN(0.5) ASIN(1) TAN(1) ATAN(1)
-------------- ---------- ---------- ---------- ---------- ----------
-0.99999999999 0 0.47942553 1.57079632 1.55740772 0.78539816
ceil(n)函數
返回大於等於n的最小整數
SQL> select ceil(15),ceil(15.6),ceil(-10.2),ceil('10.2') from dual;
CEIL(15) CEIL(15.6) CEIL(-10.2) CEIL('10.2')
---------- ---------- ----------- ------------
15 16 -10 11
floor(n)函數
返回小於或等於n的最大整數
SQL> select floor(15),floor(15.6),floor(-10.2),floor('10.2') from dual;
FLOOR(15) FLOOR(15.6) FLOOR(-10.2) FLOOR('10.2')
---------- ----------- ------------ -------------
15 15 -11 10
sqrt(n)函數
返回n的平方根
SQL> select sqrt(4),sqrt('8.9') from dual;
SQRT(4) SQRT('8.9')
---------- -----------
2 2.983286778
power(n1,n2)函數
返回n1的n2次冪
SQL> select power(3,2),power('4',2),power(2.5,4),power(-5,2)from dual;
POWER(3,2) POWER('4',2) POWER(2.5,4) POWER(-5,2)
---------- ------------ ------------ -----------
9 16 39.0625 25
log(n1,n2)
返回以n1為底n2的對數
SQL> select log(10,100),log(2.4,'10') from dual;
LOG(10,100) LOG(2.4,'10')
----------- -------------
2 2.63011686739
round(x[,y]) 函數
SQL> select round(3.564),round(3.456,2),round(3456.345,-2) from dual;
ROUND(3.564) ROUND(3.456,2) ROUND(3456.345,-2)
------------ -------------- ------------------
4 3.46 3500
trunc(x[,y])函數
SQL> select trunc(3.564),trunc(3.456,2),trunc(3456.345,-2) from dual;
TRUNC(3.564) TRUNC(3.456,2) TRUNC(3456.345,-2)
------------ -------------- ------------------
3 3.45 3400
ASSCII(x) 函數
返回字元x首字母的ASSCII值
SQL> select ascii('榮'),ascii('Xiezhr'),ascii('xiezhr') from dual;
ASCII('榮') ASCII('XIEZHR') ASCII('XIEZHR')
----------- --------------- ---------------
51417 88 120
chr(n)函數
與ASSCII函數相反，將ASSCII碼轉換為字元
SQL> select chr(51417),chr(88),chr(120) from dual;
CHR(51417) CHR(88) CHR(120)
---------- ------- --------
榮 X x
length(string)
返回string所佔的位元組長度，單位是位元組
SQL> select length('公眾號XiezhrSpace') from dual;
LENGTH('公眾號XIEZHRSPACE')
---------------------------
14
lengthb(string)
返回string所佔的字元長度，單位是字元
SQL> select lengthb('公眾號XiezhrSpace') from dual;
LENGTHB('公眾號XIEZHRSPACE')
----------------------------
17
注意：上面例子，字串是統一個，但是兩個函數返回的值是不一樣的。所以可以根據length(‘string’)=lengthb(‘string’)判斷字串是否含有中文
substr(x,start[,length])
從start處開始，擷取length個字元；
預設length，預設到結尾；
length為正，從左邊擷取；
length為負從右邊擷取
SQL> select substr('公眾號XiezhrSpace',4) a,substr('公眾號XiezhrSpace',4,11) b,substr('公眾號XiezhrSpace',-11,11) c from dual;
A B C
----------- ----------- -----------
XiezhrSpace XiezhrSpace XiezhrSpace
函數與substr不同之處是按照位元組擷取
SQL> select substrb('公眾號XiezhrSpace',7) a,substrb('公眾號XiezhrSpace',7,17) b,substrb('公眾號XiezhrSpace',-11,11) c from dual;
A B C
----------- ----------- -----------
XiezhrSpace XiezhrSpace XiezhrSpace
還有幾個擷取函數，但是不常用
concat（x,y）
SQL> select concat('公眾號','XiezhrSpace'),'公眾號'||'XiezhrSpace' from dual;
CONCAT('公眾號','XIEZHRSPACE') '公眾號'||'XIEZHRSPACE'
------------------------------ -----------------------
公眾號XiezhrSpace 公眾號XiezhrSpace
instr( string1, string2 [, start_position [, n ] ] )
從start_position開始，目標字串string2在源字串string1中出現第n次的位置；
start_position為正表示從string1左邊開始，為負表示從string1右邊開始;
start_position、n可以去掉，表示string2在string1第一次出現位置
SQL> select instr('公眾號XiezhrSpace','Xiezhr') a,instr('公眾號XiezhrSpace','xiezhr') b,instr('公眾號XiezhrSpace','Xiezhr',2,1) c,instr('公眾號XiezhrSpace','Xiezhr',-1,1) d from dual;
A B C D
---------- ---------- ---------- ----------
4 0 4 4
以位元組為單位搜尋
SQL> select instrb('公眾號XiezhrSpace','Xiezhr') a,instrb('公眾號XiezhrSpace','xiezhr') b,instrb('公眾號XiezhrSpace','Xiezhr',2,1) c,instrb('公眾號XiezhrSpace','Xiezhr',-1,1) d from dual;
A B C D
---------- ---------- ---------- ----------
7 0 7 7
跟字串擷取函數一樣，還有幾個搜尋函數
將字串n全部轉換為大寫
SQL> select upper('xiezhrspace'),upper('x') from dual;
UPPER('XIEZHRSPACE') UPPER('X')
-------------------- ----------
XIEZHRSPACE X
將字串n全部轉換為小寫
SQL> select lower('X'),lower('XIEZHRSPACE') from dual;
LOWER('X') LOWER('XIEZHRSPACE')
---------- --------------------
x xiezhrspace
replace(char,search_string[,replacement_string])
SQL> select replace('公眾號XiezhrSpace','公眾號','公眾號:')a,replace('公眾號XiezhrSpace','公眾號')b from dual;
A B
------------------ -----------
公眾號:XiezhrSpace XiezhrSpace
在字串exp1右邊用字串exp2填充，直到整個字串長度為n為止；如果exp2引數沒有，則以空格填充
SQL> select rpad('xiezhr',10,'0'),rpad('xiezhr',10) from dual;
RPAD('XIEZHR',10,'0') RPAD('XIEZHR',10)
--------------------- -----------------
xiezhr0000 xiezhr
在字串exp1左邊用字串exp2填充，直到整個字串長度為n為止；如果exp2引數沒有，則以空格填充
SQL> select lpad('xiezhr',10,'0'),lpad('xiezhr',10) from dual;
LPAD('XIEZHR',10,'0') LPAD('XIEZHR',10)
--------------------- -----------------
0000xiezhr xiezhr
trim([leading|trailing|both][trim_target from trim_source])函數
SQL> select trim(trailing '公' from '公眾號XiezhrSpace公') as a ,trim(leading '我' from '我公眾號XiezhrSpace')as b ,trim(' 公眾號XiezhrSpace ')as c from dual;
A B C
----------------- ----------------- -----------------
公眾號XiezhrSpace 公眾號XiezhrSpace 公眾號XiezhrSpace
rtrim(char[,str])函數
與rpad函數相反，將char右邊出現在str中的字元刪掉，str引數不叫則預設刪除空格
SQL> select rtrim('公眾號XiezhrSpace我的','我的') a,'公眾號XiezhrSpace ' b from dual;
A B
----------------- -------------------
公眾號XiezhrSpace 公眾號XiezhrSpace
ltrim(char[,str])函數
將char右邊出現在str中的字元刪掉，str引數不叫則預設刪除空格
SQL> select ltrim('我的公眾號XiezhrSpace','我的') a ,' 公眾號XiezhrSpace' b from dual;
A B
----------------- --------------------
公眾號XiezhrSpace 公眾號XiezhrSpace
dbtimezone函數
函數沒有引數，返回資料庫時區
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00
sysdate函數
函數沒有引數，可以得到系統當前日期
SQL> select sysdate from dual;
SYSDATE
-----------
2021-02-27
systimestamp函數
函數沒有引數，返回系統時間。時間包含時區資訊，精確到微秒。函數可以用於返回遠端資料庫伺服器時間
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
27-2月 -21 03.06.44.403049 下午 +08:00
add_months(date,i)函數
SQL> select add_months(to_date('2021-01-01','yyyy-mm-dd'),10) a ,add_months(to_date('2021-01-01','yyyy-mm-dd'),-10) b from dual;
A B
----------- -----------
2021-11-01 2020-03-01
last_day(date)函數
返回date日期的最後一天
SQL> select last_day(to_date('2020-01-01','yyyy-mm-dd')) a from dual;
A
-----------
2020-01-31
next_day(date,char)函數
SQL> select sysdate, next_day(sysdate,'星期二') a from dual;
SYSDATE A
----------- -----------
2021-02-27 2021-03-02
extract(datetime)函數
從給定的datetime中得到年、月、日、時、分、秒
SQL> select sysdate "date",
2 extract(year from sysdate)"year",
3 extract(month from sysdate)"month",
4 extract(day from sysdate)"day",
5 extract(hour from systimestamp)"hour",
6 extract(minute from systimestamp)"minute",
7 extract(second from systimestamp)"second"
8 from dual;
date year month day hour minute second
----------- ---------- ---------- ---------- ---------- ---------- ----------
2021-02-27 2021 2 27 7 25 3.72008
month_between(date1,date2)
獲取data1,date2日期之間的月份
SQL> select months_between(to_date('2021-03-02','yyyy-mm-dd'),to_date('2020-01-02','yyyy-mm-dd')) a from dual;
A
----------
14
ronud(date[,fmt])
將date舍入到fmt指定形式，如果fmt引數不加，date被處理到最近一天
SQL> select round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'month') a,
2 round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'day') b,
3 round(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss')) c
4 from dual;
A B C
----------- ----------- -----------
2020-01-01 2020-01-05 2020-01-04
trunc(date[,fmt])
將date擷取到fmt指定形式，如果fmt引數不加，date被處理到最近一天
SQL> select trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'month') a,
2 trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss'),'day') b,
3 trunc(to_date('2020-01-03 21:00:00', 'yyyy-mm-dd hh24:mi:ss')) c
4 from dual;
A B C
----------- ----------- -----------
2020-01-01 2019-12-29 2020-01-03
一般用於數位與字元、字元與日期之間轉換
SQL> select cast(sysdate as varchar2(12)) a,cast('123' as number) b, cast(123 as varchar2(3)) c from dual;
A B C
------------ ---------- ---
27-2月 -21 123 123
convert(char,a[,b])函數
SQL> select convert('測試','US7ASCII','ZHS16GBK') a from dual;
A
------------------------------
??
to_char(number[,fmt])函數
將數值、日期按照指定格式轉換成字串
SQL> select to_char(3.45)a,to_char(3.45,'99.9')b,to_char(sysdate,'yyyy-mm-dd')c from dual;
A B C
---- ----- ----------
3.45 3.5 2021-02-27
to_date(char,fmt)函數
將字元按照fmt格式轉換
SQL> select to_date('2020-12-01','yyyy-mm-dd') a from dual;
A
-----------
2020-12-01
to_number(char[,fmt])函數
將字串char 轉換為數值
SQL> select to_number('34.562','9999.999') from dual;
TO_NUMBER('34.562','9999.999')
------------------------------
34.562
null值我們經常會遇到的，這時候我們就要學會怎麼處理null值
coalesce(expr)函數
返回列表中第一個不為null的表示式，如果都為null，則返回null
SQL> select coalesce(null,'9',null,'b')a,coalesce(null,null,null,null)b from dual;
A B
- -
9
lnnvl(condition)函數
返回滿足condition條件以外的資料，包含null的條件，一般放到where語句中
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果 100
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select * from productinfo where lnnvl(qty<100);
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果 100
nvl(expr1,expr2)函數
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select id,productcode,productname, nvl(qty,0) qty from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果 0
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
nvl2(expr1,expr2,expr3)
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select id,productcode,productname, nvl2(qty,55,0) qty from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果 0
1 1002 栗子 55
1 1004 香蕉 55
1 1004 西瓜 55
avg(expr)函數
該函數可以求指定列的平均值
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select avg(qty) from productinfo;
AVG(QTY)
----------
23.6666666
count(expr)
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select count(1) from productinfo;
COUNT(1)
----------
4
max(expr)
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select max(qty) from productinfo;
MAX(QTY)
----------
34
min(expr)
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select min(qty) from productinfo;
MIN(QTY)
----------
3
sum(expr)
SQL> select * from productinfo;
ID PRODUCTCODE PRODUCTNAME QTY
---------- ----------- ------------------------------ ----------
1 1001 蘋果
1 1002 栗子 3
1 1004 香蕉 34
1 1004 西瓜 34
SQL> select sum(qty) from productinfo;
SUM(QTY)
----------
71
decode(expr,search,result,defalut)函數
當expr滿足search時候返回result,改過程可以重複多個，如果都沒有匹配的結果則返回default
SQL> select t.*, decode(qty,null,'不足',3,'不足','充足')a from productinfo t;
ID PRODUCTCODE PRODUCTNAME QTY A
---------- ----------- ------------------------------ ---------- ----
1 1001 蘋果 不足
1 1002 栗子 3 不足
1 1004 香蕉 34 充足
1 1004 西瓜 34 充足