Oracle學習筆記（6）——函式

• 函式的作用
  • 方便資料的統計
  • 處理查詢結果
• 函式的分類
  • Oracle內建的系統函式
    • 數值函式
      • 四捨五入

                                            ROUND(n[,m])

                                            1、n:表示要進行四捨五入的值

                                            2、m:表示保留小數點後幾位或前幾位

                                            3、省略m : 0

                                            4、m > 0:小數點後m位

                                            5、m < 0:小數點前m位

                                            樣例：select round(23.4),round(23.45,1),round(23.45,-1) from dual;

• 取整函式

                                            取最大值：CEIL(n)

                                            取最小值：FLOOR(n)

                                            樣例：select ceil(23.45),floor(23.45) from dual;

• 經常使用計算

                                              1、 取絕對值：ABS(n)

                                               select abs(23.45),abs(-23),abs(0) from dual;

                                              2、取餘數：MOD(m,n)   假設m和n中有一個值為null值。則結果返回null值

                                               select mod(5,2) from dual;

                                               select mod(5,null) from dual;

                                              3、POWER(m,n):表示返回m的n次冪

                                               select power(2,3),power(null,2) from dual;

                                              4、求平方根：SQRT(m)

                                                select sqrt(16) from dual;

• 三角函式

                                              1、正弦:SIN(n)、反正弦:ASIN(n)     n表示弧度

                                              2、餘弦:COS(n)、反餘弦:ACOS(n)   n表示弧度

                                              3、正切:TAN(n)、反正切:ATAN(n)   n表示弧度

                                                select sin(3.124) from dual;

• 字元函式
  • 大寫和小寫轉換函式

                                            UPPER(char)：轉大寫

                                            LOWER(char)：轉小寫

                                            INITCAP(char)：首字母轉大寫

                                            樣例： select upper('abde'),lower('ADe'),initcap('asd') from dual;

• 獲取子字串函式

                                            SUBSTR(char,[m[,n]])

                                            說明：char:源字串,m:取子串的開始位置,n:擷取子串的位數

                                                       n能夠省略，當n省略時表示從m的位置擷取到字串的末尾

                                                       m為0，表示從字串的首字母開始擷取

                                                       m為負數，表示從字串的尾部開始擷取

                                            select substr('abcde',2,3),substr("abcde",2),substr('abcde',-2,1) from dual;

• 獲取字串長度函式

                                            LENGTH(char):

                                            select length('acd ') from dual;

• 字串連線函式

                                            CONCAT(char1,char2)  與 || 操作符的作用一樣

                                            select concat('ab','cd') from dual;

                                            select  'ab' || 'cd' from dual;

• 去除子串函式

                                           1、 TRIM(c2 FROM c1):該函式表示從字串c1中去除字元c2

                                            select trim('a' from 'abcde') from dual;

                                           2、LTRIM(c1[,c2]):

                                            select ltrim('ababaa','a') from dual;

                                           3、RTRIM(c1[,c3]):

                                            select rtrim('ababaa','a') from dual;

                                           4、TRIM(c1):取除首尾的空格

• 替除函式

                                            1、REPLACE(char,s_string[,r_string]) : 省略r_string用空格替換

                                             樣例：select replace('abced','a','A') from dual;

                                                        select replace('abced','a') from dual;

                                                        select replace('abced','ab','A') from dual;

• 日期函式
  • 系統時間
    • SYSDATE   預設格式：DD-MON-RR

                                                  select sysdate from dual;

• 日期操作
  • ADD_MONTHS(date,i):返回在指定日期上加入的月份。

    i能夠是不論什麼整數。假設i的小數，會自己主動擷取整數。

                                                                                        假設i是負數，則相當於為原日期減去月份。

                                                select add_months(sysdate,3),add_months(sysdate,-3) from dual;

• NEXT_DAY(date,char):假設char的值是'星期一',則返回date指定日期的下週一是那天

                                                select next_day(sysdate,'星期一') from dual;

• LAST_DAY(date):返回某一月的最後一天的日期

                                                select last_day(sysdate) from dual;

• MONTHS_BETWEEN(date1,date2):表示兩個日期之間相隔的月份

                                                select months_between('20_5月_15','10_1月_15') from dual;

• EXTRACT(date FROM datetime):

                                                 select extract(year from sysdate) from dual;

                                                 select extract(month from sysdate) from dual;

                                                 select extract(date from sysdate) from dual;

                                                 select extract(hour from timestamp '2015-10-1 17:25:14') from dual;

• 轉換函式
  • 日期轉換成字元的函式

                                        TO_CHAR(date[,fmt[,params]])

                                        引數說明：

                                        date:將要轉換的日期

                                        fmt:轉換的格式

                                        params:日期的語言。通常不寫

• 預設格式：DD-MON-RR
• YY  YYYY   YEAR
• MM   MONTH
• DD     DAY
• HH24     HH12
• MI    SS

                                        樣例：select to_char(sysdate,'YYYY-MM-DD  HH24:MI:SS') from dual;

                                                   select to_char(sysdate,'YYYY-MM-DD  HH12:MI:SS') from dual;

• 字元轉換成日期的函式

                                        TO_DATE(char[,fmt[,params]])     注意：to_date()依照系統預設格式顯示日期

                                         樣例：select to_date('2015-05-22','YYYY-MM-DD') from dual;

• 數字轉換成字元的函式

                                        TO_CHAR(number[,fmt])

                                        9:顯示數字並忽略前面的0

                                        0:顯示數字。位數不足。用0補齊

                                        .或D:顯示小數點

                                        ,或G:顯示千位符

                                        $:美元符號

                                        S:加正負號（前後都能夠，不能同一時候加上）

                                        select to_char(12345.678,'$99,999.999') from dual;

                                        select to_char(12345.678,'$99,999,999') from dual;

                                        select to_char(12345.678,'S99,999.999') from dual;

• 字元轉換成數字的函式

                                        TO_NUMBER(char[,fmt])

                                        fmt是轉換的格式。能夠省略

                                        select to_number('$1,000','$9999') from dual;

• • 自己定義的函式(臨時不學)
• 在查詢中使用函式
  • 在查詢中使用字元函式

                    1、在員工資訊表查詢出員工的生日(也就是依據員工省份證號碼得到員工生日)

                    select substr(cardid,7,8) from users;

                    2、將部門號01所有替換成'資訊科技'

                    select replace(deptno,'01','資訊科技') from users;

                    3、將員工資訊表中的年齡欄位與10取餘數

                    select mod(age,10) from users;

• 在查詢中使用日期函式

                    1、取得員工入職的年份

                    select extract(year from regdate) from users;

                    2、查詢出5月份入職的員工資訊

                    select * from users where extract(month from regdate)=5;