Oracle函數，當任意一個參數為空字符串或者null時，結果返回null

聚合函數

count

使用方式有5種

select count(*) from student;--統計總行數
select count(0) from student;--統計總行數,括號里面的0表示什么意思？
select count(id) from student;--統計列id不為null的總行數select count(rowid) from student;--統計總行數select count(distinct id) from student;--統計id不為null并且去掉id重復的總行數

ps:前面四種count的方式，在沒有索引的情況是一樣的，都會全表掃描。選中SQL按F5會打開解釋計劃窗口，可以看到前四種count的方式執行過程都是一樣。如果有索引，一般情況下，count(*),count(0),count(rowid)會走索引，count(id)走索引掃描還是走全表掃描要看字段id是否有索引。

sum

select sum(idno) from student;--任意行的idno不能為非數字，可以為null

avg

select avg(idno) from student;--計算之前會把null值排除在外

max

select max(idno) from student;--

列中的數據可以是數值、字符串或是日期時間數據類型，會忽略null值，但是如果所有的值都為null，則會返回null，字符串是怎么比較大小的？

min

select min(idno) from student;--同上

字符函數

大小寫

select lower(name) from dual; --lower()轉小寫
select upper(name) from dual; --upper()轉大寫
select initcap(name) from dual; --initcap()首字母大寫

非英文字母不會進行轉換，但任然會查詢出來，null值也會查詢出來

數據內容合并

select concat(id,name) from student;--concat函數只能是兩個參數，多行數據內容合并成一行可以使用||

字符串截取

substr

只針對字符類型

select substr('HelloWorld',0,3) value from dual;--返回結果：Hel，截取從“H”開始3個字符
select substr('HelloWorld',1,3) value from dual;--返回結果：Hel，截取從“H”開始3個字符
select substr('HelloWorld',2,3) value from dual;--返回結果：ell，截取從“e”開始3個字符
select substr('HelloWorld',0,100) value from dual;--返回結果：HelloWorld，100雖然超出預處理的字符串最長度，但不會影響返回結果，系統按預處理字符串最大數量返回。
select substr('HelloWorld',5,3) value from dual;--返回結果：oWo
select substr('Hello World',5,3) value from dual;--返回結果：o W (中間的空格也算一個字符串，結果是：o空格W)
select substr('HelloWorld',-1,3) value from dual;--返回結果：d （從后面倒數第一位開始往后取1個字符，而不是3個。）
select substr('HelloWorld',-2,3) value from dual;--返回結果：ld （從后面倒數第二位開始往后取2個字符，而不是3個。）
select substr('HelloWorld',-3,3) value from dual;--返回結果：rld （從后面倒數第三位開始往后取3個字符）
select substr('HelloWorld',-4,3) value from dual;--返回結果：orl （從后面倒數第四位開始往后取3個字符）select substr('HelloWorld',0) value from dual;--返回結果：HelloWorld，截取所有字符
select substr('HelloWorld',1) value from dual;--返回結果：HelloWorld，截取所有字符
select substr('HelloWorld',2) value from dual;--返回結果：elloWorld，截取從“e”開始之后所有字符
select substr('HelloWorld',3) value from dual;--返回結果：lloWorld，截取從“l”開始之后所有字符
select substr('HelloWorld',-1) value from dual;--返回結果：d，從倒數一個“d”開始,截取所有字符
select substr('HelloWorld',-2) value from dual;--返回結果：ld，從倒數第二個“l”開始,截取所有字符
select substr('HelloWorld',-3) value from dual;--返回結果：rld，從倒數第三個“r”開始,截取所有字符

trunc

trunc可以對日期進行格式化，有關日期的格式參數，參考https://blog.csdn.net/qq_26565861/article/details/83110968

trunc可以對數字進行截取，與round的區別是不進行四舍五入

字符串長度計算

--length計算字符數，lengthb計算字節數，編碼方式為UTF8/GBK時，一個中文占3/2個字節長度
select length('hello你好') from dual;
select lengthb('hello你好') from dual;

查找字符的位置

select instr('helloworld', 'o') from dual;--instr從左向右找第一次出現的位置，從1開始
select instr('helloworld', 'z') from dual;--找不到返回0

填充

select lpad('hello',10,'#')from dual;--lpad如果位數不夠，從左邊開始補位
select lpad('hello',10)from dual;--第三個參數不傳時，用空格
select lpad('甲骨文',10,'$') from dual;--注意字符集編碼為UTF8時中文也是占用兩個長度
select rpad('hello',3,'#')from dual;--rpad如果位數不夠，從右邊邊開始補位

去除空格

--基本用法，注意全角空格去不掉
SELECT trim('    aaa  bbb  ccc     ')  trim FROM dual;
SELECT ltrim('    aaa  bbb  ccc     ')  trim FROM dual;
SELECT rtrim('    aaa  bbb  ccc     ')  trim FROM dual;
--高級用法，去除特定字符 trim( leading | trailing | both string1 FROM string2) 注意string1只能是單個字符，如果沒有設置string1參數，默認去除空字符串
SELECT trim(leading  '$'  from  '$aaa  bbb  ccc$')  FROM dual;
SELECT trim(trailing  '$'  from  '$aaa  bbb  ccc$')  FROM dual;
SELECT trim(both '$'  from  '$aaa  bbb  ccc$')  FROM dual; 
SELECT trim(both  from  '  aaa  bbb  ccc  ')  FROM dual;

字符串替換

select replace('heLLo','LL','ll') from dual;
select replace('heLLo','LL') from dual;--未設置第三個參數時表示用空字符串替換

數字函數

四舍五入

--第二個參數為正整數表示小數點后多少位，0表示取整，負數表示小數點前n+1位
select   round(1234.5678,4)   from   dual;
select   round(1234.5678,3)   from   dual;
select   round(1234.5678,0)   from   dual;
select   round(1234.5678,-1)   from   dual;
--對日期處理
select round(to_date('2003-10-15','yyyy-mm-dd'),'month')from dual;
select round(to_date('2003-10-16','yyyy-mm-dd'),'month')from dual;
select round(to_date('2003-6','yyyy-mm'),'year')from dual;
select round(to_date('2003-7','yyyy-mm'),'year')from dual;

轉換函數

to_char

日期類型轉字符串TO_CHAR(DATETIME)

select ? to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ? from ? dual;

關于日期格式不同符號代表什么意思，可以參考https://blog.csdn.net/qq_26565861/article/details/83110968

數字格式化TO_CHAR(NUMBER)

select to_char(1234,'9,9,9,9')   from dual;
select to_char(1234.34,'9,9,9,9.99')   from dual;
select to_char(1234.34,'9,9,9,9.$99')    from dual;

更多數字格式化的參數，參考https://blog.csdn.net/jinlong5200/article/details/3135949

把NCLOB,CLOB,NCHAR轉換為VARCHAR2 TO_CHAR(CHARACTER)

to_date

參考https://blog.csdn.net/qq_26565861/article/details/83110968

to_number

可以將char和varchar2類型轉換成number類型

select to_number('$234234.4350','$999999.0000') from dual;

格式值	含義
9	代表一個數字
0	強迫0顯示
$	顯示美元符號
L	強制顯示一個當地的貨幣符號
.	顯示一個小數點
,?	顯示一個千位分隔符號

cast

cast可以進行數據類型轉換

語法：cast( 列名/值 as 數據類型 )

select cast(1 as varchar2(10))  from dual;
select cast('1' as number) from dual;

判斷函數

decode

用來判斷值是否相等，語法格式為：decode(列或值,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

具體含義為：如果列或值等于第一個值，那么返回第一個返回值，如果等于第二個值，那么返回第二個返回值，否則返回缺省值

select decode('小米','華為','android','三星','android','蘋果','ios','其他') from dual;
select decode('小米','華為','android','三星','android','蘋果','ios') from dual;--如果沒有設置默認值參數,返回null

nvl

語法：NVL(參數1,參數2 )

含義：如果參數1不為null，則返回參數1，否則返回參數2

select nvl('測試','默認值') from dual;

nvl2

語法：nvl2(參數1，參數2，參數3)

含義：如果參數1不為null，返回參數2，否則返回參數3

select nvl2('','參數2','參數3') from dual;
select nvl2('參數1','參數2','參數3') from dual;

nullif

語法：NULLIF (參數1，參數2)

含義：若兩個參數相等，返回NULL；不相等，等返回參數1

其他函數

ROW_NUMBER()OVER

一對多取多的一方最新的一條數據，分類（PARTITION BY）排序（ORDER BY ）

SELECT * FROM(SELECT u.*,ROW_NUMBER()OVER(PARTITION BY p.user_id ORDER BY p.flush_time desc)RNFROM STS_LPATROL_APPUSER_T uleft JOIN STS_LPATROL_PHONEDEV_T p ON u.user_id=p.user_id)T where? RN=1

pivot

行轉列：將多行數據轉換成一行數據的多個列

create table tb_student_grade(name varchar2(50),id varchar2(32),course varchar2(50),grade number);insert into tb_student_grade(name,id,course,grade) values('cc','1','math',100);
insert into tb_student_grade(name,id,course,grade) values('cc','1','chinese',110);
insert into tb_student_grade(name,id,course,grade) values('cc','1','english',120);insert into tb_student_grade(name,id,course,grade) values('cc','2','math',100);
insert into tb_student_grade(name,id,course,grade) values('cc','2','chinese',110);
insert into tb_student_grade(name,id,course,grade) values('cc','2','english',120);insert into tb_student_grade(name,id,course,grade) values('lp','3','math',50);
insert into tb_student_grade(name,id,course,grade) values('lp','3','chinese',60);
insert into tb_student_grade(name,id,course,grade) values('lp','3','english',70);commit;select * from tb_student_grade pivot(max(grade) for course in('math' as 數學,'chinese' as 語文,'english' as 英語));

unpivot

列轉行：將一行數據的多個列轉換成多行數據

select * from tb_student_grade pivot(max(grade) for course in('math' as 數學,'chinese' as 語文,'english' as 英語)) unpivot(grade for course in(數學,語文,英語));

合并多行數據

wm_concat

用來將分組后的多行數據合并成一行

select t.deptno,wm_concat(t.ename) from emp t group by t.deptno;

ename拼接的順序不是按照ename來的

listagg

select t.deptno,listagg(t.ename,':') within group (order by t.ename) from emp t group by t.deptno;

ename拼接的順序可以按照某個字段來，拼接的字符也可以指定。是11g新增的函數

Oracle常用函數總結