在oracle中，函數又被稱PL/SQL子程序，是作為數據庫對象存儲在oracle數據庫中的；函數會對傳遞進來的參數進行處理，并返回一個處理結果，也就是返回一個值。oracle函數可分為兩類：系統函數和用戶自定義函數。
本教程操作環境：Windows7系統、Oracle 11g版、Dell G3電腦。
Oracle函數的定義
函數是作為數據庫對象存儲在oracle數據庫中，函數又被稱為PL/SQL子程序。
函數會對傳遞進來的參數進行處理，并返回一個處理結果，也就是返回一個值。通常用于返回特定的數據。
oracle函數可分為兩類：系統函數和用戶自定義函數。
ORACLE函數之單行函數
- 不改變真實數據，只對數據做進一步修飾或處理顯示
- 可和字段混合使用
偽表：真實存在的表，方便進行數據驗證而臨時存在；表名：dual
1.字符函數
| 函數名 | 作用 | 示例 | 結果 |
|---|---|---|---|
| initcap(char) | 首字母大寫 | initcap(‘nice’) | Nice |
| lower(char) | 轉為小寫 | lower(‘NICE’) | nice |
| upper(char) | 轉為大寫 | upper(‘nice’) | NICE |
| ltrim(char,set) | 左剪 | ltrim(‘nice’,’ni’) | ce |
| rtrim(char,set) | 右剪 | rtrim(‘nice’,’e’) | nic |
| concat(char1,char2) | 字符串連接 | concat(‘ni’,’ce’) | nice |
| substr(char,pos,len) | 取子串 | substr(‘nice’,2,2) | ic |
| instr(char,substr) | 查子串位置 | instr(‘nice’,’c’) | 3 |
| translate(char,key,value) | 按字符映射翻譯 | translate(‘nice’,’ne’,’01’) | 0ic1 |
| replace(char,old,new) | 字符串替換 | replace(‘nice’,’c’,’cc’) | nicce |
2.數值函數
| 函數名 | 作用 | 示例 | 結果 |
|---|---|---|---|
| abs(n) | 取絕對值 | abs(-3) | 3 |
| mod(m,n) | 取余 | mod(3,2) | 1 |
| sign(n) | 取符號 | sign(-3) | -1 |
| ceil(n) | 向上取整 | ceil(3.2) | 4 |
| floor(n) | 向下取整 | floor(3.2) | 3 |
| round(m,n) | 四舍五入為指定小數位數 | round(3.236,2) | 3.24 |
| power(m,n) | m的n次冪 | power(3,2) | 9 |
| sqrt(n) | 平方根 | sqrt(4) | 2 |
| trunc（m,n) | 截斷 | trunc(3.233,2) | 3.23 |
| sin(n) | 正弦 | sin(0) | 0 |
| cos(n) | 余弦 | cos(0) | 1 |
3.日期函數
| 函數名 | 作用 | 示例 | 結果 |
|---|---|---|---|
| months_between(date1,date2) | 返回兩日期間的月份(若date1在日歷中比date2早，則返回一個負數，反之則返回一個正數) | months_between(’01-8月-2020’，‘01-8月-2020’) | 0 |
| add_months(date,m) | 返回把月份數加到日期上的新日期 | add_months(’25-8月-2020’，-1) | 25-7月-2020 |
| next_day(date,week) | 返回指定新日期后的星期對應的新日期 | next_day(’23-8月-2020’，‘星期一’) | 24-8月-2020 |
| last_day(date) | 返回指定日期所在月的最后一天 | last_day(’25-8月-2020′) | 31-8月-2020 |
| round(date,p) | 按指定格式對日期進行四舍五入 | round(to_date(’25-8月-2020′)，’YEAR‘) | 1-1月-2021 |
| trunc(date,p) | 對日期按指定方式截斷 | trunc(to_date(’25-8 ?月-2020′),’YEAR’) | 01-1月-2020 |
注：
- p為YEAR，round按1-6月和7-12月，四舍五入到最近的yyyy年1月；trunc截取到本年第一天。
- p為MONTH，round按1-15日和16-30日，四舍五入到最近mm月的1日；trunc截取到本月第一天。
- p為DAY，round按周一至周三和周四至周日，四舍五入到最近的周日。trunc截取到本周第一天。
oracle函數之多行函數
- 對查詢數據進行統計
-
不能和普通字段及單行函數混合使用，除非分組
函數名 作用 max(字段名) 返回該字段最大值 min(字段名) 返回該字段最小值 sum(字段名) 返回該字段的和 avg(字段名) 返回該字段平均值 count(*) 返回表記錄數 count(字段名) 返回非空值數量 count(distinct 字段名) 返回去重后字段值數量
oracle函數之轉換函數
-
to_number（數值類型的字符)：將字符轉換為數值
-
to_char(數值或日期)：將數值或日期轉換為字符
指定顯示格式：
9表示占位，例：999,999,999會將數字按三個一組逗號隔開。
0表示占位，若實際數據位數不足，則用0補位。
L表示人民幣符號，$表示美元符號。
注：數值和字符之間可隱式轉換。
-
to_date(日期格式的字符)：將字符轉換為日期（一般新增使用，查詢用to_char)
常用日期格式：yyyy-mm-dd
yyyy/mm/dd
‘yyyy”年”mm”月”dd”日”‘
注：字符必須符合日期格式；oracle默認轉換格式為日月年，例’25-8月-2020’。
oracle函數之其他函數
-
nvl(字段名，新的值)：若字段值不為null，則返回該字段值；若為null，則返回新的值。
-
nvl2(字段名，處理1，處理2)：若字段值不為null，則執行處理1；若為null，則執行處理2。
-
decode(字段名，值1，處理1，值2，處理2，值3，處理3，…,公共處理)：若字段值和decode中條件值相同，則執行對應的處理。若都沒有，則執行公共處理。
推薦教程：《Oracle教程》
