19個常用Oracle內建函數

1. nvl(expression1, expression2)

函數作用：從兩個表達式傳回一個非null值

用例：select nvl(father_name, mother_name) parent_name from student where student_id = '12345'

注意：

 如果expression1的值非空，優先取expression1的值；

如果expression1的值空且expression2的值非空，則取expression2的值；

如果expression1和expression2均為空，則結果為NULL

2. decode(field_name, value1, new_value1, value2 , new_value2, default_value)

函數作用：類似if...else...語句區塊，針對某個字段，如果它的值為value1,則轉換為newValue1，如果值為value2 ，則轉換為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 )

函數作用：將資料集依照某個欄位排序，並產生序號欄位

使用案例：select row_number() over(order by name) no,id,name from a;

4. to_date(source_string, formater_string)

##函數作用：將字串轉換為日期類型

用例：select to_date(' 20190809','yyyyMMdd') from dual;

注意：

'yyyyMMdd','yyyymmdd','yyyy-MM-dd','yyyy-mm-dd'都可以

5. to_char()

函數作用：將其他型別轉換為字串型別

使用案例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都有不同的意義，如下表所示

字元標誌意義9如果存在數字則顯示數字，不存在則顯示空格0如果存在數字則顯示數字，不存在則顯示0，即佔位符fm刪除因為

	9帶來的空格

#6. wm_concat

函數作用：行轉列，將多行查詢結果聚合到一行的某一列

使用案例：select wm_concat(distinct name) from student

注意：高版本oracle可能會去掉

7. listagg() within group(order by field_name) over(partition by field_name)

函數作用：同wm_concat

用例：select listagg(distinct name) within group(order by name desc) from student

8. concat(expression1, expression2)

函數作用：字串拼接函數

用例：select concat('left', 'Right') from dual

注意：也可以利用|| 進行拼接，select 'a'||'b' from dual

9. sys_guid()

函數作用：產生並傳回一個全球唯一的識別碼(原始值)由16個位元組組成，32個字元

用例：select sys_guid () from dual;

注意：常用來做表格的主鍵

#10. over(partition by field_name, order by field_name)

函數作用：over函數是一個分析函數，和聚合函數搭配在一起使用可以簡潔代碼

用例：

select name, job, sal, deptno,
sum(sal) over(partition by deptno) sum_sal,     --统计某组中的总计值
avg(sal) over(partition by deptno) avg_sal,     --统计某组中的平均值
from emp;

注意：按照先前的寫法先進分組統計產生臨時表關聯原始表才可以取到其他信息，現在則不需要了

通常和max(),min(),avg(),sum()等聚合函數一起使用

11 . nlssort

函數作用：提供簡體中文的特殊排序

#使用案例：

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

#函數作用：是截取日期或數字，依照規則傳回指定的值

使用案例1：select trunc(sysdate, 'yyyy') from dual

用例2：select trunc(126.56, 0 ) from dual

注意：

用例1還有其他規則代表不同的意義

規則意思mm返回當月第一天##yyddyyyyd用例2還有其他規則代表不同意義

返回當年第一天
返回目前年月日
返回當年第一天
傳回目前星期的第一天

規則0正數a負數b

13. rank() over(partition by field_name order by field_name)

函數作用：讓傳回結果根據分區和排序欄位產生排名關係

用例：select rank() over(partition by birthday order by score), s.* from student s;

注意：dense_rank()用法和rank()一樣，差別在於排名是否跳躍

14. substr(source, start [,length])

函數作用：截取字串

用例：select substr('abcde', 2, 3) from dual

注意：oracle字串索引從1開始

15. replace(field_name, sub_str, replace_str)

##函數作用：將指定的字串替換為指定的字串

用例：select replace(name, 'hello', 'world') from student;

注意：也可以用在update語句set部分

16. trim

函數作用：去掉左右兩端的空白字元

用例：select trim(' dsf ') from dual;

注意：只去掉左邊空白字元用ltrim，只去掉右邊空白字元用rtrim

#17. sign##函數作用：取數字n的符號,大於0返回1, 小於0返回-1, 等於0返回0

#使用案例：select sign(50),sign(-100),sign(0) from dual;

#18. round(number[,decimal])

函數作用：對數字n進行四捨五入處理，保留decimal位小數

用例：select round(123.34),round( 123.51),round(123.56,1),round(123.34,-1) from dual;

19. coalesce(expression1,expression2...)

#函數作用：傳回表達式中第一個不為空的值，如果全為空則傳回空值

使用案例：select coalesce(null,3 5,4 6) value from dual;

推薦教學：《

Oracle教學

》

意思
#預設值，取下取整
保留小數點後面a位小數，其他的抹掉，如果a比小數點後的位數多，則保留原值
小數點後面的全部去掉，小數點向左移動abs(b)位，用0代替被抹掉的數字，如果所有數字都被抹掉，則回傳0

以上是19個常用Oracle內建函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！