Oracle專題5之Oracle的單行函數(shù)

  • SQL函數(shù)是什么？SQL函數(shù)是Oracle數(shù)據(jù)庫的內(nèi)置函數(shù)，并且可用于各種SQL語句。SQL函數(shù)包含單行函數(shù)和多行函數(shù)。

    1、字符函數(shù)

  • 字符函數(shù)的輸入為字符類型，其返回值是字符類型或者是數(shù)字類型。
  • 字符函數(shù)可以分為大小寫控制函數(shù)（UPPER、LOWER和INITCAP）和字符控制函數(shù)（CONCAT、SUBSTR、LENGTH、INSERT、LPAD、RPAD、TRIM、REPLACE）兩類。

    a、大小寫控制函數(shù)

  • 這類函數(shù)用于改變字符的大小寫。（需要注意的是，當輸入的字符為NULL（null）時，返回值也為NULL）
  • UPPER（char）：該函數(shù)用于將字符串轉(zhuǎn)換成大寫格式。
  • LOWER（char）：該函數(shù)用于將字符串轉(zhuǎn)換成小寫格式。
  • INITCAP（char）：該函數(shù)用于將字符串中的每個單詞的首字母大寫，其他字符小寫。
  • 示例： 
    SQL> conn scott/02000059
已連接。
SQL> select * from emp where job = upper('salesman');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
                30
            7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
                30
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
    SQL> select * from emp where lower(job) = 'clerk';
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7369 G_EASON    CLERK           7902 17-12月-80            800
                20
            7876 ADAMS      CLERK           7788 23-5月 -87           1100
                20
            7900 JAMES      CLERK           7698 03-12月-81            950
                30
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
    SQL> select empno, initcap(ename) ename from emp;
         EMPNO ENAME
            7369 G_Eason
            7499 Allen
            7521 Ward
            7566 Jones
            7654 Martin
            7698 Blake
            7782 Clark
            7788 Scott
            7839 King
            7844 Turner
            7876 Adams
         EMPNO ENAME
            7900 James
            7902 Ford
            7934 Miller
已選擇14行。

    b、字符控制函數(shù)

  • CONCAT(str1, str2)：該函數(shù)用于字符串的連接，str1和str2用于指定被連接的字符串。
  • 例如：concat('lello', 'oracle') 結(jié)果：hellooracle 
    SQL> select concat(concat(ename, ':'),sal) from emp;
CONCAT(CONCAT(ENAME,':'),SAL)
G_EASON:800
ALLEN:1600
WARD:1250
JONES:2975
MARTIN:1250
BLAKE:2850
CLARK:2450
SCOTT:3000
KING:5000
TURNER:1500
ADAMS:1100
JAMES:950
FORD:3000
MILLER:1300
已選擇14行。
  • SUBSTR(char, m[,n])：該函數(shù)用于截取字符串，char指定源自符串，m用于指定從哪個位置開始截取，n用于指定截取字符串的長度。如果m為0，則從首字母開始，如果m為負數(shù)，則從尾部開始。
  • 例如：substr('hello'1,3)，結(jié)果為：ell；substr('hello',0,3)，結(jié)果為hel；substr('hello',-1,1)，結(jié)果為：o。 
    SQL> select * from emp where substr(job, 1, 4) = 'SALE';
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
                30
            7521 WARD       SALESMAN        7698 22-2月 -81           1250        500
                30
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
  • LENGTH(char)：該函數(shù)用于返回字符串的長度，字符串中的后綴空格也計作字符串的長度。例如length('oracle ')，結(jié)果為：7。 
    SQL> select * from emp where length(ename) = 6;
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
  • INSTR(char1, char2, [,n[,m]])：該函數(shù)用于取得子串在字符串中的位置，char1用于指定源字符串，char2用于指定子串，n用于指定起始搜索位置（默認值：1），m用于指定子串的第m次出現(xiàn)的次數(shù)（默認值：1）。
  • 例如：instr('hello oracle', 'oracle')，結(jié)果為：7；instr('hello oracle hello oracle', 'oracle', 1, 2)，結(jié)果為：20。 
    SQL> select instr('hello oracle', 'oracle') from dual;
INSTR('HELLOORACLE','ORACLE')
                        7
    SQL> select instr('hello oracle hello oracle', 'oracle', 5, 2) from dual;
INSTR('HELLOORACLEHELLOORACLE','ORACLE',5,2)
                                                                                    20

  • 注意，dual為一張?zhí)摂M的表。

  • LPAD(char1, n char2)：該函數(shù)用于在字符串的左端填充字符，char1用于指定源字符串，char2用于指定被填充的字符，n用于指定填充后的char1的總長度。
  • 例如：lpad('hello',10,'#')，結(jié)果是：#####hello。 
        SQL> select lpad(job , 9, '*') from emp;
    LPAD(JOB,9,'*')
    ****CLERK
    *SALESMAN
    *SALESMAN
    **MANAGER
    *SALESMAN
    **MANAGER
    **MANAGER
    **ANALYST
    PRESIDENT
    *SALESMAN
    ****CLERK
    ****CLERK
    **ANALYST
    ****CLERK
    已選擇14行。
  • RPAD(char1, n char2)：該函數(shù)用于在字符串的右端填充字符，char1用于指定源字符串，char2用于指定被填充的字符，n用于指定填充后的char1的總長度。
  • 例如：rpad('hello',10,'#')，結(jié)果是：hello#####。
  • REPLACE(char, search_string[, replacement_string])：該函數(shù)用于替換字符串的子串內(nèi)容。Char用于指定源字符串，search_string用于指定被替換子串，repalcement_string用于指定替換子串。
  • 例如：replace('hello oracle', 'oracle', 'world')，結(jié)果為：hello world。 
    SQL> select replace('hello oracle', 'oracle', 'world') replace_fun from dual;
REPLACE_FUN
hello world

    2、數(shù)值函數(shù)

  • 數(shù)值函數(shù)的輸入?yún)?shù)和返回值都是數(shù)字類型。
  • 常用的數(shù)值函數(shù)有：ROUND()，TRUNC()，MOD()。
  • ROUND(n,[,m])：四舍五入，說明：該函數(shù)用于返回四舍五入的結(jié)果，其中n可以使任意數(shù)字，m必須是整數(shù)。
  • 例如：ROUND(25.328)，結(jié)果為：25；ROUND(25.328, 2)，結(jié)果為：25.33；ROUND(25.328, -1)，結(jié)果為：30。 
    SQL> select sal/30, round(sal/30, 2) from emp;
SAL/30 ROUND(SAL/30,2)
26.6666667           26.67
53.3333333           53.33
41.6666667           41.67
99.1666667           99.17
41.6666667           41.67
    95              95
81.6666667           81.67
   100             100
166.666667          166.67
    50              50
36.6666667           36.67
31.6666667           31.67
   100             100
43.3333333           43.33
已選擇14行。
  • TRANC：截取數(shù)字，TRUNC(n, [m])，該函數(shù)用于截取數(shù)字，其中n可以是任意數(shù)字，m必須是整數(shù)。
  • 例如：trunc(25,328)，結(jié)果為：25；trunc(25.328, 2)，結(jié)果為：25.32；trunc(25.328, -1)，結(jié)果為：20。 
    SQL> select sal/30, trunc(sal/30, 2) from emp;
        SAL/30 TRUNC(SAL/30,2)
26.6666667           26.66
53.3333333           53.33
41.6666667           41.66
99.1666667           99.16
41.6666667           41.66
                95              95
81.6666667           81.66
             100             100
166.666667          166.66
                50              50
36.6666667           36.66
31.6666667           31.66
             100             100
43.3333333           43.33
已選擇14行。
  • MOD：求模（求余數(shù)），MOD(m, n)，該函數(shù)用于取得兩個數(shù)字相除后的余數(shù)。如果數(shù)字n為0，則返回結(jié)果為m。
  • 例如：mod(25,6)，結(jié)果為：1；mod(25, 0)，結(jié)果為：25。 
    SQL> select * from emp where mod(empno, 2) = 0;
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7566 JONES      MANAGER         7839 02-4月 -81           2975
                20
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
            7698 BLAKE      MANAGER         7839 01-5月 -81           2850
                30
            7782 CLARK      MANAGER         7839 09-6月 -81           2450
                10
            7788 SCOTT      ANALYST         7566 19-4月 -87           3000
                20
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
        DEPTNO
            7876 ADAMS      CLERK           7788 23-5月 -87           1100
                20
            7900 JAMES      CLERK           7698 03-12月-81            950
                30
            7902 FORD       ANALYST         7566 03-12月-81           3000
                20
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
已選擇10行。

    3、日期和時間函數(shù)

    • 用于處理日期時間類型的函數(shù)。
    • 其常用的日期時間函數(shù)有：SYSDATE、MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND、TRUNC。
-  SYSDATE：該函數(shù)用于返回當前系統(tǒng)日期。
-  舉例：select sysdate-1 昨天, sysdate 今天，sysdate + 1明天 from dual;。
    SQL> select sysdate-1 昨天, sysdate 今天, sysdate + 1 明天 from dual;
    昨天           今天           明天
    02-12月-17     03-12月-17     04-12月-17
  • MONTHS_BETWEEN(d1, d2)：改函數(shù)用于返回日期d1和d2之間相差的月數(shù)。d1大于d2結(jié)果為正數(shù)，否則為負數(shù)。
  • 舉例：計算員工入職多長時間，select months_between(sysdate, hiredate) from emp;。 
    SQL> select round(months_between(sysdate, hiredate)/12) hiredate_year from emp;
HIREDATE_YEAR
                     37
                     37
                     37
                     37
                     36
                     37
                     36
                     31
                     36
                     36
                     31
                     36
                     36
                     36
已選擇14行。
  • ADD_MONTHS：add_months(d, n)該函數(shù)用于返回特定日期時間之后或者之前的月份所對應的日期時間。（求出若干月之后的日期）d用于指定日期時間，n可以是任意整數(shù)。
  • 舉例：查詢每位員工入職30年后的年份，select ename, add_months(hiredate, 30* 12) from emp;。 
    SQL> select ename, add_months(hiredate, 30* 12) from emp;
ENAME      ADD_MONTHS(HIR
G_EASON    17-12月-10
ALLEN      20-2月 -11
WARD       22-2月 -11
JONES      02-4月 -11
MARTIN     28-9月 -11
BLAKE      01-5月 -11
CLARK      09-6月 -11
SCOTT      19-4月 -17
KING       17-11月-11
TURNER     08-9月 -11
ADAMS      23-5月 -17
JAMES      03-12月-11
FORD       03-12月-11
MILLER     23-1月 -12
已選擇14行。
  • NEXT_DAY(d,char)：該函數(shù)用于返回特定日期之后的第一個工作日所對應的日期。d用于指定日期時間值，char用于指定工作日。
  • 注意：當使用該函數(shù)時，工作日必須與日期語言匹配，假如日期語言為AMERICAN，那么周一對應于MONDAY；假如日期語言為簡體中文，那么周一對應于“星期一”。
  • 例如：查詢周一的日期，select sysdate, nextday(sysdate, '星期一') from dual;。 
    SQL> select sysdate, next_day(sysdate, '星期一') 下一個工作日 from dual;
SYSDATE        下一個工作日
03-12月-17     04-12月-17
  • LAST_DAY：該函數(shù)用于返回特定日期所在月份的最后一天。
  • 例如：select sysdate, last_day(sysdate) from dual;。 
    SQL> select sysdate, last_day(sysdate) from dual;
SYSDATE        LAST_DAY(SYSDA
03-12月-17     31-12月-17
  • ROUND(d[, fmt])：該函數(shù)用于返回日期時間的四舍五入的結(jié)果。d用于指定日期的時間值，fmt用于指定四舍五入的方式。
  • 如果設置fmt為YEAR，則7月1日為分界線，如果設置fmt為MONTH，則16日為分界線。
  • 例如：如果系統(tǒng)時間是“20-7月-17”，round(sysdate,'YEAR')，結(jié)果為：01-1月-18；round(sysdate, 'MONTH')，結(jié)果為：01-8月-17。 
    SQL> select round(sysdate, 'YEAR') as 時間的四舍五入 from dual;
時間的四舍五入
01-1月 -18
SQL> select round(sysdate, 'MONTH') as 時間的四舍五入 from dual;
時間的四舍五入
01-12月-17
  • 當前的系統(tǒng)時間為03-12月-17。
  • TRUNC：TRUNC(d[, fmt])：該函數(shù)用于截斷日期時間數(shù)據(jù)。D用于指定日期的時間值，fmt用于指定截斷日期時間數(shù)據(jù)的方法，如果設置fmt為YEAR，則結(jié)果為本年度的1月1日，如果設置fmt為MONTH，則結(jié)果為本月的1日。
  • 例如：trunc(sysdate, 'YEAR')；trunc(sysdate, 'MONTH');。 
    SQL> select trunc(sysdate, 'MONTH') as 截斷的時間 from dual;
截斷的時間
01-12月-17
SQL> select trunc(sysdate, 'YEAR') as 截斷的時間 from dual;
截斷的時間
01-1月 -17
  • 當前的系統(tǒng)時間為03-12月-17。

    4、轉(zhuǎn)換函數(shù)

  • 該函數(shù)用于將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換成另外一種數(shù)據(jù)類型。

    a、隱式數(shù)據(jù)類型轉(zhuǎn)換

  • Oracle可以隱式的（自動的）進行數(shù)據(jù)類型轉(zhuǎn)換
源數(shù)據(jù)類型目標數(shù)據(jù)類型
VARCHAR2或者CHAR NUMBER
VARCHAR2或者CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2

  • 如下示例：

    創(chuàng)新互聯(lián)公司堅持“要么做到，要么別承諾”的工作理念，服務領(lǐng)域包括：成都網(wǎng)站設計、網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務，滿足客戶于互聯(lián)網(wǎng)時代的瓊山網(wǎng)站設計、移動媒體設計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡建設合作伙伴！

    SQL> select * from emp where sal > '3000';
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7839 KING       PRESIDENT            17-11月-81           5000
                10
    SQL> select * from emp where hiredate = '17-11月-81';

 EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
DEPTNO
  7839 KING       PRESIDENT            17-11月-81           5000
    10

    b、顯式地數(shù)據(jù)類型轉(zhuǎn)換

  • 日期類型 -> 字符類型（使用TO_CHAR()）、字符類型 -> 日期類型（使用TO_DATE()）。
  • 字符類型 -> 數(shù)值類型（使用TO_NUMBER()）、數(shù)值類型 -> 字符類型（使用TO_CHAR()）。
  • TO_CHAR：將日期類型轉(zhuǎn)換為字符類型。函數(shù)格式為：TO_CHAR(d, [, fmt [, 'nlsparams']])。
  • d用于知道你跟日起值，fmt用于指定日期格式模型，'nlsparams'用于指定日期顯示語言（格式：'NLS_DATE_LANGUAGE=language'）
  • 默認日期顯示格式為DD-MON-RR。select to_char(hiredate, 'DD-MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN') from emp;。 
    SQL> select to_char(hiredate, 'DD-MON-RR', 'NLS_DATE_LANGUAGE=AMERICAN') to_char from emp;
TO_CHAR
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
19-APR-87
17-NOV-81
08-SEP-81
23-MAY-87
03-DEC-81
03-DEC-81
23-JAN-82
已選擇14行。
  • 需要注意，當在格式模型中增加字符值時，必須用雙引號引住字符值。如：select to_char(hiredate, 'YYYY"年"MM"月"DD"日"') to_char from emp;。 
    SQL> select to_char(hiredate, 'YYYY"年"MM"月"DD"日"') to_char from emp;
TO_CHAR
1980年12月17日
1981年02月20日
1981年02月22日
1981年04月02日
1981年09月28日
1981年05月01日
1981年06月09日
1987年04月19日
1981年11月17日
1981年09月08日
1987年05月23日
1981年12月03日
1981年12月03日
1982年01月23日
已選擇14行。
  • TO_CHAR：將數(shù)值類型轉(zhuǎn)換成字符類型。函數(shù)格式為：TO_CHAR(n[, fmt])。
  • n用于指定數(shù)值，fmt用于指定數(shù)字格式的模型。
  • 格式模型，常用的元素如下：
常用元素說明
9 顯示數(shù)字，并且會忽略前導0
0 顯示數(shù)字，如果位數(shù)不足，則用0補齊
. 在指定位置顯示小數(shù)點
, 在指定位置顯示逗號
$ 在數(shù)字前加上美元符號
L 在數(shù)字前加上本地貨幣符號 
SQL> SELECT sal, to_char(sal, 'L00,000,000.00') to_char FROM emp;
       SAL           TO_CHAR
       800           ￥00,000,800.00
      1600          ￥00,001,600.00
      1250          ￥00,001,250.00
      2975          ￥00,002,975.00
      1250          ￥00,001,250.00
      2850          ￥00,002,850.00
      2450          ￥00,002,450.00
      3000          ￥00,003,000.00
      5000          ￥00,005,000.00
      1500          ￥00,001,500.00
      1100          ￥00,001,100.00
       950          ￥00,000,950.00
      3000          ￥00,003,000.00
      1300          ￥00,001,300.00
已選擇14行。
SQL> SELECT sal, to_char(sal, 'L99,999,999.99')  to_char FROM emp;
       SAL                 TO_CHAR
       800                 ￥800.00
      1600               ￥1,600.00
      1250               ￥1,250.00
      2975               ￥2,975.00
      1250               ￥1,250.00
      2850               ￥2,850.00
      2450               ￥2,450.00
      3000               ￥3,000.00
      5000               ￥5,000.00
      1500               ￥1,500.00
      1100               ￥1,100.00
       950                 ￥950.00
      3000               ￥3,000.00
      1300               ￥1,300.00
已選擇14行。
  • TO_DATE(char[, fmt[, 'nlsparams']])：該函數(shù)用于將字符串轉(zhuǎn)換成日期類型的數(shù)據(jù)。char用于匹配日期類型的字符串，fmt用于指定日期格式模型，'nlsparams'用于指定日期語言。
  • 舉例：查看1982之后入職的員工信息：select ename, hiredate from emp where hiredate > to_date('1981-12-31', 'YYYY-MM-DD'); 
    SQL> select ename, hiredate from emp where hiredate > to_date('1981-12-31', 'YYYY-MM-DD');
ENAME      HIREDATE
SCOTT      19-4月 -87
ADAMS      23-5月 -87
MILLER     23-1月 -82
  • TO_NUMBER(n[, fmt])：該函數(shù)可以將包含數(shù)字的字符串轉(zhuǎn)換成數(shù)值類型。n是包含數(shù)字的字符串，fmt用于指定數(shù)字格式模型。
  • 顯示工資大于3000的所有員工的姓名和工資：
  • select ename, sal from emp where sal > to_number('￥3000', 'L99999'); 
    SQL> select ename, sal from emp where sal > to_number('￥3000', 'L99999');
ENAME             SAL
KING             5000

    5、通用函數(shù)

  • 此類函數(shù)適用于任何數(shù)據(jù)類型，同時也適用于空值。如：NVL(expr1, expr2)、NVL2(expr1, expr2, expr3)、NULLIF(expr1, expr2)、COALESCE(expr1 [ , expr2 ] [, ... ])。

    處理NULL

  • 例如：select ename, sal, comm, sal + comm from emp;
  • 在Oracle9i之前，處理null只能使用函數(shù)NVL。
  • 但是從Oracle9i之后，NVL或者NVL2都可以處理NULL。
  • NVL(expr1, expr2)：該函數(shù)用于處理NULL。如果expr是null，則返回expr2，如果expr1不是null，則返回expr1。
  • 例如：select ename, sal, comm, sal + nvl(comm, 0) from emp; 
    SQL> select ename, sal, comm, sal + nvl(comm, 0) from emp;
ENAME             SAL       COMM SAL+NVL(COMM,0)
G_EASON           800                        800
ALLEN            1600        300            1900
WARD             1250        500            1750
JONES            2975                       2975
MARTIN           1250       1400            2650
BLAKE            2850                       2850
CLARK            2450                       2450
SCOTT            3000                       3000
KING             5000                       5000
TURNER           1500          0            1500
ADAMS            1100                       1100
JAMES             950                        950
FORD             3000                       3000
MILLER           1300                       1300
已選擇14行。
  • NVL2(expr1, expr2, expr3)：該函數(shù)用于處理NULL，如果expr1不是null，則返回expr2，如果expr1是null，則返回expr3。例如：select ename, sal, comm, nvl2(comm, sal+ comm, sal) nvl2 from emp;。 
        SQL> select ename, sal, comm, nvl2(comm, sal+ comm, sal)  nvl2 from emp;
    ENAME             SAL       COMM       NVL2
    G_EASON        800                            800
    ALLEN            1600          300          1900
    WARD             1250        500            1750
    JONES            2975                           2975
    MARTIN           1250       1400           2650
    BLAKE            2850                           2850
    CLARK            2450                           2450
    SCOTT            3000                           3000
    KING                5000                           5000
    TURNER           1500          0            1500
    ADAMS            1100                         1100
    JAMES             950                           950
    FORD               3000                        3000
    MILLER            1300                         1300
    已選擇14行。
  • NULLIF(expr1, expr2)：該函數(shù)用于比較表達式expr1和expr2，如果二者相等，則返回NULL，否則返回expr1。
  • COALESCE：COALESCE(expr1[, expr2][,...]。該函數(shù)用于返回表達式列表中第一個NOT NULL表達式的結(jié)果。例如：select ename, sal, comm, coalesce(sal + comm, sal) coalesce from emp; 
    SQL> select ename, sal, comm, coalesce(sal + comm, sal)  coalesce from emp;
ENAME             SAL       COMM   COALESCE
EASON            3000                             3000
G_EASON         800                             800
ALLEN            1600          300             1900
WARD             1250          500            1750
JONES            2975                             2975
MARTIN           1250         1400           2650
BLAKE            2850                             2850
CLARK            2450                             2450
SCOTT            3000                             3000
KING                5000                              5000
TURNER           1500          0              1500
ADAMS            1100                           1100
JAMES             950                             950
FORD             3000                             3000
MILLER           1300                           1300
已選擇15行。

    6、條件表達式

  • 條件表達式中主要有CASE表達式和Decode()函數(shù)。

    a、CASE表達式

  • 語法格式為：CASE expr WEHRE comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 ...... WHEN comparison_exprn THEN return_exprn ELSE else_expr] END。

  • 示例：想顯示全部雇員的職位，但是這些職位要求替換為中文顯示。

        SQL> select empno, ename, case job when 'CLERK' then '辦事員' when 'SALESMAE' then '銷售' when 'MANAGER' then '經(jīng)理' when 'ANALYST' then '分析員' else
 '總裁' end case from emp;
         EMPNO ENAME      CASE
            7951 EASON      分析員
            7369 G_EASON    辦事員
            7499 ALLEN      總裁
            7521 WARD       總裁
            7566 JONES      經(jīng)理
            7654 MARTIN     總裁
            7698 BLAKE      經(jīng)理
            7782 CLARK      經(jīng)理
            7788 SCOTT      分析員
            7839 KING       總裁
            7844 TURNER     總裁
            7876 ADAMS      辦事員
            7900 JAMES      辦事員
            7902 FORD       分析員
            7934 MILLER     辦事員
已選擇15行。
SQL>

    b、Decode函數(shù)

  • 語法格式：DECODE(col | expression, search2, result1, [, search3, result2, ...., ] [, default]。Decode函數(shù)和case表達式都可用于IF-THEN-ELSE邏輯判斷的。
  • 示例：想顯示全部雇員的職位，但是這些職位要求替換為中文顯示。 
    SQL> select empno, ename, job, decode(job, 'CLERK', '辦事員', 'SALESMAN', '銷售', 'MANAGER', '經(jīng)理', 'ANALYST', '分析員', '總裁') from emp;
         EMPNO ENAME      JOB       DECODE
            7951 EASON      ANALYST   分析員
            7369 G_EASON    CLERK     辦事員
            7499 ALLEN      SALESMAN  銷售
            7521 WARD       SALESMAN  銷售
            7566 JONES      MANAGER   經(jīng)理
            7654 MARTIN     SALESMAN  銷售
            7698 BLAKE      MANAGER   經(jīng)理
            7782 CLARK      MANAGER   經(jīng)理
            7788 SCOTT      ANALYST   分析員
            7839 KING       PRESIDENT 總裁
            7844 TURNER     SALESMAN  銷售
            7876 ADAMS      CLERK     辦事員
            7900 JAMES      CLERK     辦事員
            7902 FORD       ANALYST   分析員
            7934 MILLER     CLERK     辦事員
已選擇15行。

    7、嵌套函數(shù)

  • 嵌套函數(shù)的執(zhí)行熟順序是由內(nèi)到外。
  • 語法格式：F3(F2(col, arg1), arg2), arg3)。
  • 參照雇員信息表，想顯示距聘用日期3個月后的下一個星期一的日期，且日期格式如：2017-01-06。
  • 分析：距聘用日期3個月后：add_months(hiredate, 3)，下一個星期一：next_day(add_months(hiredate,3), '星期一')，且日期格式如2017-01-06： to_char(next_day(add_months(hiredate,3), '星期一'), 'YYYY-MM-DD')。 
        SQL> select to_char(next_day(add_months(hiredate,3), '星期一'), 'YYYY-MM-DD') new_date from emp;
NEW_DATE
2018-03-05
1981-03-23
1981-05-25
1981-05-25
1981-07-06
1982-01-04
1981-08-03
1981-09-14
1987-07-20
1982-02-22
1981-12-14
1987-08-24
1982-03-08
1982-03-08
1982-04-26
已選擇15行。
  • 參照雇員信息表，顯示雇員日薪并四舍五入到2位小數(shù)的結(jié)果，然后對薪資格式以：￥1,182.19'這樣的例子形式進行格式化。
  • 分析：雇員日薪并四舍五入到2位小數(shù)的結(jié)果：雇員日薪并四舍五入到2位小數(shù)的結(jié)果：round(sal/30, 2)；對薪資格式以‘￥1,182.19’這樣的例子形式進行格式化：to_char(round(sal/30, 2), 'L9,999.99')。 
    SQL> select to_char(round(sal/30, 2), 'L9,999.99') from emp;
TO_CHAR(ROUND(SAL/3
                     ￥100.00
                        ￥26.67
                        ￥53.33
                        ￥41.67
                        ￥99.17
                        ￥41.67
                        ￥95.00
                        ￥81.67
                     ￥100.00
                     ￥166.67
                        ￥50.00
                        ￥36.67
                        ￥31.67
                     ￥100.00
                        ￥43.33
已選擇15行。

網(wǎng)頁題目：Oracle專題5之Oracle的單行函數(shù)
網(wǎng)頁網(wǎng)址：http://muchs.cn/article40/ppjeeo.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、虛擬主機、用戶體驗、定制網(wǎng)站、網(wǎng)站制作、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

h5響應式網(wǎng)站建設