oracle函數是什麼
在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中條件值相同，則執行對應的處理。若都沒有，則執行公共處理。