1. nvl(expression1, expression2)
函數(shù)作用：從兩個表達式中返回一個非null值
用例：select nvl(father_name, mother_name) parent_name from student where student_id = ‘12345’
注意：
?如果expression1的值非空，優(yōu)先取expression1的值；
如果expression1的值空且expression2的值非空，則取expression2的值；
如果expression1和expression2均為空，則結(jié)果為NULL
2. decode(field_name, value1, new_value1, value2, new_value2, default_value)
函數(shù)作用：類似if…else…語句塊，針對某個字段，如果它的值為value1,則轉(zhuǎn)換為newValue1，如果值為value2，則轉(zhuǎn)換為newValue2，其他情況顯示默認值
用例：select decode(id,’1′,’A’,’2′,’B’,id) from A;
注意：
?decode(field_name, value1, new_value1, value2, new_value2, default_value)其中的value1，newValue1等可以是一個表達式
3. row_number(order by field_name)
函數(shù)作用：將數(shù)據(jù)集按照某個字段排序，并產(chǎn)生序號字段
用例：select row_number() over(order by name) no,id,name from a;
4. to_date(source_string, formater_string)
函數(shù)作用：將字符串轉(zhuǎn)換為日期類型
用例：select to_date(‘20190809′,’yyyyMMdd’) from dual;
注意：
‘yyyyMMdd’,’yyyymmdd’,’yyyy-MM-dd’,’yyyy-mm-dd’都可以
5. to_char()
函數(shù)作用：將其他類型轉(zhuǎn)換為字符串類型
用例1：select to_char(sysdate, ‘yyyymmdd’) from dual
用例2：select to_char(99, ‘fm999.00’) from dual
注意：
用例1中還有很多其他的日期格式，如yyyy,mm,dd,D,DD,DDD等
用例2中fm,9,0都有不同的含義，如下表所示
| 字符標(biāo)志 | 含義 |
|---|---|
| 9 | 如果存在數(shù)字則顯示數(shù)字，不存在則顯示空格 |
| 0 | 如果存在數(shù)字則顯示數(shù)字，不存在則顯示0，即占位符 |
| fm | 刪除因為9帶來的空格 |
6. wm_concat
函數(shù)作用：行轉(zhuǎn)列，將多行查詢結(jié)果聚合到一行的某一列中
用例：select wm_concat(distinct name) from student
注意：高版本oracle可能會去掉
7. listagg() within group(order by field_name) over(partition by field_name)
函數(shù)作用：同wm_concat
用例：select listagg(distinct name) within group(order by name desc) from student
8. concat(expression1, expression2)
函數(shù)作用：字符串拼接函數(shù)
用例：select concat(‘left’, ‘Right’) from dual
注意：也可以利用 || 進行拼接，select ‘a’||’b’ from dual
9. sys_guid()
函數(shù)作用：產(chǎn)生并返回一個全球唯一的標(biāo)識符(原始值)由16個字節(jié)組成，32個字符
用例：select sys_guid() from dual;
注意：經(jīng)常用來做表的主鍵
10. over(partition by field_name, order by field_name)
函數(shù)作用：over函數(shù)是一個分析函數(shù)，和聚合函數(shù)搭配在一起使用可以簡潔代碼
用例：
select?name,?job,?sal,?deptno, sum(sal)?over(partition?by?deptno)?sum_sal,?????--統(tǒng)計某組中的總計值 avg(sal)?over(partition?by?deptno)?avg_sal,?????--統(tǒng)計某組中的平均值 from?emp;
注意：按照以前的寫法先進分組統(tǒng)計產(chǎn)生臨時表關(guān)聯(lián)原表才可以取到其他信息，現(xiàn)在則不需要了
通常和max(),min(),avg(),sum()等聚合函數(shù)一起使用
11. nlssort
函數(shù)作用：提供簡體中文的特殊排序
用例：
select?*?from?student?order?by?nlssort(name,?'nls_sort?=?schinese_pinyin_m')?--拼音 select?*?from?team?order?by?nlssort(name,?'nls_sort?=?schinese_stroke_m')????--筆畫 select?*?from?team?order?by?nlssort(name,?'nls_sort?=?schinese_radical_m')???--部首
12. trunc
函數(shù)作用：是截取日期或數(shù)字，根據(jù)規(guī)則返回指定的值
用例1：select trunc(sysdate, ‘yyyy’) from dual
用例2：select trunc(126.56, 0) from dual
注意：
用例1還有其他規(guī)則代表不同的含義
| 規(guī)則 | 含義 |
|---|---|
| mm | 返回當(dāng)月第一天 |
| yy | 返回當(dāng)年第一天 |
| dd | 返回當(dāng)前年月日 |
| yyyy | 返回當(dāng)年第一天 |
| d | 返回當(dāng)前星期的第一天 |
用例2還有其他規(guī)則代表不同含義
| 規(guī)則 | 含義 |
|---|---|
| 0 | 默認值，取下取整 |
| 正數(shù)a | 保留小數(shù)點后面a位小數(shù)，其他的抹掉，如果a比小數(shù)點后的位數(shù)多，則保留原值 |
| 負數(shù)b | 小數(shù)點后面的全部去掉，小數(shù)點向左移動abs(b)位，用0代替被抹掉的數(shù)字，如果所有數(shù)字都被抹掉，則返回0 |
13. rank() over(partition by field_name order by field_name)
函數(shù)作用：讓返回結(jié)果根據(jù)分區(qū)和排序字段產(chǎn)生排名關(guān)系
用例：select rank() over(partition by birthday order by score), s.* from student s;
注意：dense_rank()用法和rank()一樣，區(qū)別在于排名是否跳躍
14. substr(source, start [,length])
函數(shù)作用：截取字符串
用例：select substr(‘abcde’, 2, 3) from dual
注意：oracle字符串索引從1開始
15. replace(field_name, sub_str, replace_str)
函數(shù)作用：將指定的字符串替換為指定的字符串
用例：select replace(name, ‘hello’, ‘world’) from student;
注意：也可以用在update語句set部分
16. trim
函數(shù)作用：去掉左右兩端的空白字符
用例：select trim(‘ dsf ‘) from dual;
注意：僅去掉左邊空白字符用ltrim，僅去掉右邊空白字符用rtrim
17. sign
函數(shù)作用：取數(shù)字n的符號,大于0返回1, 小于0返回-1, 等于0返回0
用例：select sign(50),sign(-100),sign(0) from dual;
18. round(number[,decimal])
函數(shù)作用：對數(shù)字n進行四舍五入處理，保留decimal位小數(shù)
用例：select round(123.34),round(123.51),round(123.56,1),round(123.34,-1) from dual;
19. coalesce(expression1,expression2…)
函數(shù)作用：返回表達式中第一個不為空的值，如果全為空則返回空值
用例：select coalesce(null,3+5,4+6) value from dual;
推薦教程：《Oracle教程》
