4.4 Oracle常用系統函數

視頻講解：光盤\TM\lx\4\常用系統函數．mp4

SQL語言是一種腳本語言，它提供了大量內置函數，使用這些內置函數可以大大增強SQL語言的運算和判斷功能。本節將對Oracle中的一些常用函數進行介紹，如字符類函數、數字類函數、日期和時間類函數、轉換類函數、聚集類函數等。

4.4.1 字符類函數

字符類函數是專門用于字符處理的函數，處理的對象可以是字符或字符串常量，也可以是字符類型的列，常用的字符類函數有如下幾種。

1. ASCII(c)函數和CHR(i)函數

ASCII（c）函數用于返回一個字符的ASCII碼，其中參數c表示一個字符；CHR（i）函數用于返回給出ASCII碼值所對應的字符，i表示一個ASCII碼值。從這兩個函數的功能中可以看出，它們二者之間具有互逆的關系，如圖4.65所示。

圖4.65 ASCII（c）函數

【例4.59】 分別求得字符“Z、H、D和空格”的ASCII值，具體代碼及運行結果如下：

        SQL> select ascii('Z') Z, ascii('H') H, ascii('D') D , ascii(' ') space
          2  from dual;

【例4.60】 對于例4.59中求得的ASCII值，使用CHR函數再返回其對應的字符，如圖4.66所示。具體代碼及運行結果如下：

圖4.66 CHR（i）函數

        SQL>select  chr(90), chr(72), chr(68), (32)S from dual;

2. CONCAT（s1, s2）函數

該函數將字符串s2連接到字符串s1的后面，如果s1為null，則返回s2；如果s2為null，則返回s1；如果s1和s2都為空，則返回null。

【例4.61】 使用concat（）函數連接“Hello”和“World”兩個字符串，具體代碼及運行結果如下：

        SQL>select concat('Hello', 'World! ')information  from dual;


        INFORMATION
        ------------------
        Hello World!

3. INITCAP（s）函數

該函數將字符串s的每個單詞的第一個字母大寫，其他字母小寫。單詞之間用空格、控制字符、標點符號來區分。

【例4.62】 使用initcap（）函數轉換字符串“oh my god! ”的輸出，具體代碼及運行結果如下：

        SQL> select initcap('oh my god! ') information from dual;


        INFORMATION
        -----------------
        Oh My God!

4. INSTR（s1, s2[, i][, j]）函數

該函數用于返回字符s2在字符串s1中第j次出現時的位置，搜索從字符串s1的第i個字符開始。當沒有發現要查找的字符時，該函數返回值為0；如果i為負數，那么搜索將從右到左進行，但函數的返回位置還是按從左到右來計算。其中，s1和s2均為字符串；i和j均為整數，默認值為1。

【例4.63】 在字符串“oracle 11g”中，從第3個字符開始查詢字符串“1”第2次出現的位置，具體代碼及運行結果如下：

        SQL> select instr('oracle 11g', '1',3,2) abc from dual;


              ABC
        --------------
                9

5. LENGTH（s）函數

該函數用于返回字符串s的長度，如果s為null，則返回值為null。

【例4.64】 在SCOTT模式下，通過使用length（）函數返回雇員名稱長度大于5的雇員信息及所在部門信息，具體代碼如下：

        SQL> select e.empno, e.ename, d.dname
          2  from emp e inner join dept d
          3  on e.deptno=d.deptno
          4  where length(e.ename)>5;

本例運行結果如圖4.67所示。

圖4.67 雇員名稱長度大于5的雇員信息

6. LOWER（s）函數和UPPER函數（s）

LOWER（s）函數和UPPER函數（s）分別用于返回字符串s的小寫形式和大寫形式，這兩個函數經常出現在WHERE子句中。

【例4.65】 在HR模式下，在employees表中檢索雇員名稱以字母“a”開頭的員工信息，并將first_name字段的值轉換為小寫，將last_name字段的值轉換為大寫，具體代碼如下（實例位置：光盤\TM\sl\4\11）。

        SQL> select employee_id, lower(first_name), upper(last_name) from employees where
        lower(first_name) like 'a%';

本例運行結果如圖4.68所示。

圖4.68 大小寫轉換

7. LTRIM（s1, s2）函數、RTRIM（s1, s2）函數和TRIM（s1, s2）函數

這3個函數分別用來刪除字符串s1左邊的字符串s2、刪除字符串s1右邊的字符串s2、刪除字符串s1左右兩端的字符串s2。如果在這3個函數中不指定字符串s2，則表示去除相應方位的空格。

【例4.66】 使用LTRIM、RTRIM和TRIM函數分別去掉字符串“####East####”、“East ”和“####East###”中左側“#”、右側空格和左右兩側的“#”，具體代碼及運行結果如下：

        SQL>select ltrim('####East####', '#'), rtrim('East   '), trim('#'from'####East###')from dual;


        LTRIM('#     RTRI    TRIM
        -------------     -------    ---
        East####     East     East

8. REPLACE（s1, s2[, s3]）函數

該函數使用s3字符串替換出現在s1字符串中的所有s2字符串，并返回替換后的新字符串，其中，s3的默認值為空字符串。

【例4.67】 使用replace（）函數把字符串“Bad Luck Bad Gril”中的“Bad”字符串用“Good”替換掉，具體代碼及運行結果如下：

        SQL> select replace('Bad Luck Bad Gril', 'Bad', 'Good') from dual;


        REPLACE('BADLUCKBAD
        --------------------------------
        Good Luck Good Gril

9. SUBSTR（s, i, [j]）函數

該函數表示從字符串s的第i個位置開始截取長度為j的子字符串。如果省略參數j，則直接截取到尾部。其中，i和j為整數。

【例4.68】 使用substr（）函數在字符串’MessageBox’中從第8個位置截取長度為3的子字符串，具體代碼及運行結果如下：

        SQL> select substr('MessageBox',8,3) from dual;


        SUB
        -----
        Box

4.4.2 數字類函數

數字類函數主要用于執行各種數據計算，所有的數字類函數都有數字參數并返回數字值。Oracle系統提供了大量的數字類函數，這些函數大大增強了Oracle系統的科學計算能力。下面就列出Oracle系統中常見的數字類函數，如表4.6所示。

表4.6 數字類函數及其說明

在上表中列舉了若干三角函數，這些三角函數的操作數和返回值都是弧度，而不是角度，這一點需要讀者注意。接下來，對表4.6中常用的幾個函數進行舉例說明。

1. CEIL（n）函數

該函數返回大于或等于數值n的最小整數，它適合于一些比較運算。

【例4.69】 使用ceil（）函數返回3個指定小數的整數值，具體代碼及運行結果如下：

        SQL> select ceil(7.3), ceil(7), ceil(-7.3) from dual;


         CEIL(7.3)               CEIL(7)          CEIL(-7.3)
        ---------------        ---------           ---------
                8                    7               -7

2. ROUND(n1, n2)函數

該函數返回舍入小數點右邊n2位的n1的值，n2的默認值為0，這會返回小數點最接近的整數。如果n2為負數，就舍入到小數點左邊相應的位上，n2必須是整數。

【例4.70】 使用round（）函數返回PI為兩位小數的值，具體代碼及運行結果如下：

        SQL> select round(3.1415926,2) from dual;


        ROUND(3.1415926,2)
        ------------------------
                    3.14

3. POWER(n1, n2)函數

該函數返回n1的n2次方。其中n1和n2都為整數。

【例4.71】 使用power（）函數計算2的3次方的值，具體代碼及運行結果如下：

        SQL> select power(2,3) from dual;


        POWER(2,3)
        ---------------
                8

4.4.3 日期和時間類函數

在Oracle 11g中，系統提供了許多用于處理日期和時間的函數，通過這些函數可以實現計算需要的特定日期和時間，常用的日期和時間函數如表4.7所示。

表4.7 日期和時間類函數

日期類型的默認格式是“DD-MON-YY”，其中DD表示兩位數字的“日”, MON表示3位數字的“月份”。YY表示兩位數字的“年份”，例如，“01-10月-11”表示2011年10月1日。下面看幾個常用函數的具體應用。

1. SYSDATE()函數

該函數返回系統當前的日期。

【例4.72】 使用sysdate（）函數返回系統當前的日期，具體代碼及運行結果如下：

        SQL>select sysdate as系統日期from dual;


        系統日期
        -------------
        29-9月 -11

2. ADD_MONTHS(d, i)函數

該函數返回日期d加上i個月之后的結果。其中，i為任意整數。

【例4.73】 使用ADD_MONTHS函數在當前日期下加上6個月，并顯示其值，具體代碼及運行結果如下：

        SQL> select ADD_MONTHS(sysdate,6) from dual;


        ADD_MONTHS(SYS
        --------------------------
        29-3月 -12

4.4.4 轉換類函數

在操作表中的數據時，經常需要將某個數據從一種類型轉換為另外一種數據類型，這時就需要轉換類型函數。比如常見的，有把具有“特定格式”字符串轉換為日期、把數字轉換成字符等。常用的轉換函數如表4.8所示。

表4.8 轉換類函數

下面來看幾個常用轉換函數的具體應用。

1. TO_CHAR()函數

該函數實現將表達式轉換為字符串，format表示字符串格式。

【例4.74】 使用to_char（）函數轉換系統日期為“YYYY-MM-DD”格式，具體代碼及運行結果如下（實例位置：光盤\TM\sl\4\12）。

        SQL>select sysdate as默認格式日期，to_char(sysdate, 'YYYY-MM-DD')as轉換后日期
         from dual;


        默認格式日期            轉換后日期
        -----------------      ---------
        29-9月 -11             2011-09-29

2. TO_NUMBER(s[, format[lan]])函數

該函數將返回字符串s代表的數字，返回值按照format格式進行顯示，format表示字符串格式，lan表示所使用的語言。

【例4.75】 使用to_number（）函數把十六進制數“18f”轉換為十進制數，具體代碼及運行結果如下：

        SQL>select to_number('18f', 'xxx')as十進制數from dual;


          十進制數
        -------------
              399

4.4.5 聚合類函數

使用聚合類函數可以針對一組數據進行計算，并得到相應的結果。比如常用的操作有計算平均值、統計記錄數、計算最大值等。Oracle 11g所提供的主要聚合函數如表4.9所示。

表4.9 聚合函數

在實際的應用系統開發中，聚合函數應用比較廣泛，比如統計平均值、記錄總數等。下面來看一個例子。

【例4.76】 在SCOTT模式下，使用count（）函數計算員工總數，使用avg（）函數計算平均工資，具體代碼及運行結果如下：

        SQL>select count(empno)as員工總數，round(avg(sal),2)as平均工資from emp;


          員工總數              平均工資
        ---------------        ---------
              14                2073.21