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

SQL函數(shù)是什么？SQL函數(shù)是Oracle數(shù)據(jù)庫(kù)的內(nèi)置函數(shù)，并且可用于各種SQL語(yǔ)句。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ù)用于改變字符的大小寫。（需要注意的是，當(dāng)輸入的字符為NULL（null）時(shí)，返回值也為NULL）
UPPER（char）：該函數(shù)用于將字符串轉(zhuǎn)換成大寫格式。
LOWER（char）：該函數(shù)用于將字符串轉(zhuǎn)換成小寫格式。
INITCAP（char）：該函數(shù)用于將字符串中的每個(gè)單詞的首字母大寫，其他字符小寫。

示例：

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用于指定從哪個(gè)位置開始截取，n用于指定截取字符串的長(zhǎng)度。如果m為0，則從首字母開始，如果m為負(fù)數(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ù)用于返回字符串的長(zhǎng)度，字符串中的后綴空格也計(jì)作字符串的長(zhǎng)度。例如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用于指定起始搜索位置（默認(rèn)值：1），m用于指定子串的第m次出現(xiàn)的次數(shù)（默認(rèn)值：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的總長(zhǎng)度。

例如：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的總長(zhǎng)度。
例如：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])：四舍五入，說(shuō)明：該函數(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ù)用于取得兩個(gè)數(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í)間類型的函數(shù)。
其常用的日期時(shí)間函數(shù)有：SYSDATE、MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND、TRUNC。

-  SYSDATE：該函數(shù)用于返回當(dāng)前系統(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ù)，否則為負(fù)數(shù)。

舉例：計(jì)算員工入職多長(zhǎng)時(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ù)用于返回特定日期時(shí)間之后或者之前的月份所對(duì)應(yīng)的日期時(shí)間。（求出若干月之后的日期）d用于指定日期時(shí)間，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ù)用于返回特定日期之后的第一個(gè)工作日所對(duì)應(yīng)的日期。d用于指定日期時(shí)間值，char用于指定工作日。
注意：當(dāng)使用該函數(shù)時(shí)，工作日必須與日期語(yǔ)言匹配，假如日期語(yǔ)言為AMERICAN，那么周一對(duì)應(yīng)于MONDAY；假如日期語(yǔ)言為簡(jiǎn)體中文，那么周一對(duì)應(yīng)于“星期一”。

例如：查詢周一的日期，select sysdate, nextday(sysdate, '星期一') from dual;。

SQL> select sysdate, next_day(sysdate, '星期一') 下一個(gè)工作日 from dual;
SYSDATE        下一個(gè)工作日
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ù)用于返回日期時(shí)間的四舍五入的結(jié)果。d用于指定日期的時(shí)間值，fmt用于指定四舍五入的方式。
如果設(shè)置fmt為YEAR，則7月1日為分界線，如果設(shè)置fmt為MONTH，則16日為分界線。

例如：如果系統(tǒng)時(shí)間是“20-7月-17”，round(sysdate,'YEAR')，結(jié)果為：01-1月-18；round(sysdate, 'MONTH')，結(jié)果為：01-8月-17。

SQL> select round(sysdate, 'YEAR') as 時(shí)間的四舍五入 from dual;
時(shí)間的四舍五入
01-1月 -18
SQL> select round(sysdate, 'MONTH') as 時(shí)間的四舍五入 from dual;
時(shí)間的四舍五入
01-12月-17

當(dāng)前的系統(tǒng)時(shí)間為03-12月-17。

TRUNC：TRUNC(d[, fmt])：該函數(shù)用于截?cái)嗳掌跁r(shí)間數(shù)據(jù)。D用于指定日期的時(shí)間值，fmt用于指定截?cái)嗳掌跁r(shí)間數(shù)據(jù)的方法，如果設(shè)置fmt為YEAR，則結(jié)果為本年度的1月1日，如果設(shè)置fmt為MONTH，則結(jié)果為本月的1日。

例如：trunc(sysdate, 'YEAR')；trunc(sysdate, 'MONTH');。

SQL> select trunc(sysdate, 'MONTH') as 截?cái)嗟臅r(shí)間 from dual;
截?cái)嗟臅r(shí)間
01-12月-17
SQL> select trunc(sysdate, 'YEAR') as 截?cái)嗟臅r(shí)間 from dual;
截?cái)嗟臅r(shí)間
01-1月 -17

當(dāng)前的系統(tǒng)時(shí)間為03-12月-17。
4、轉(zhuǎn)換函數(shù)
該函數(shù)用于將數(shù)據(jù)從一種數(shù)據(jù)類型轉(zhuǎn)換成另外一種數(shù)據(jù)類型。
a、隱式數(shù)據(jù)類型轉(zhuǎn)換
Oracle可以隱式的（自動(dòng)的）進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換

源數(shù)據(jù)類型	目標(biāo)數(shù)據(jù)類型
VARCHAR2或者CHAR	NUMBER
VARCHAR2或者CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

如下示例：
創(chuàng)新互聯(lián)公司堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：成都網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時(shí)代的瓊山網(wǎng)站設(shè)計(jì)、移動(dòng)媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
```
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、顯式地?cái)?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'用于指定日期顯示語(yǔ)言（格式：'NLS_DATE_LANGUAGE=language'）

默認(rèn)日期顯示格式為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行。

需要注意，當(dāng)在格式模型中增加字符值時(shí)，必須用雙引號(hào)引住字符值。如：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ù)字格式的模型。
格式模型，常用的元素如下：

常用元素	說(shuō)明
9	顯示數(shù)字，并且會(huì)忽略前導(dǎo)0
0	顯示數(shù)字，如果位數(shù)不足，則用0補(bǔ)齊
.	在指定位置顯示小數(shù)點(diǎn)
,	在指定位置顯示逗號(hào)
$	在數(shù)字前加上美元符號(hào)
L	在數(shù)字前加上本地貨幣符號(hào)

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'用于指定日期語(yǔ)言。

舉例：查看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ù)類型，同時(shí)也適用于空值。如：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ù)用于比較表達(dá)式expr1和expr2，如果二者相等，則返回NULL，否則返回expr1。

COALESCE：COALESCE(expr1[, expr2][,...]。該函數(shù)用于返回表達(dá)式列表中第一個(gè)NOT NULL表達(dá)式的結(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、條件表達(dá)式

條件表達(dá)式中主要有CASE表達(dá)式和Decode()函數(shù)。
a、CASE表達(dá)式
語(yǔ)法格式為：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ù)

語(yǔ)法格式：DECODE(col | expression, search2, result1, [, search3, result2, ...., ] [, default]。Decode函數(shù)和case表達(dá)式都可用于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)到外。
語(yǔ)法格式：F3(F2(col, arg1), arg2), arg3)。
參照雇員信息表，想顯示距聘用日期3個(gè)月后的下一個(gè)星期一的日期，且日期格式如：2017-01-06。

分析：距聘用日期3個(gè)月后：add_months(hiredate, 3)，下一個(gè)星期一：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é)果，然后對(duì)薪資格式以：￥1,182.19'這樣的例子形式進(jìn)行格式化。

分析：雇員日薪并四舍五入到2位小數(shù)的結(jié)果：雇員日薪并四舍五入到2位小數(shù)的結(jié)果：round(sal/30, 2)；對(duì)薪資格式以‘￥1,182.19’這樣的例子形式進(jìn)行格式化：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)頁(yè)題目：Oracle專題5之Oracle的單行函數(shù)
網(wǎng)頁(yè)網(wǎng)址：http://www.chinadenli.net/article40/ppjeeo.html

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

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

猜你還喜歡下面的內(nèi)容

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

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

1、字符函數(shù)

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

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

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

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

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

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

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

b、顯式地?cái)?shù)據(jù)類型轉(zhuǎn)換

5、通用函數(shù)

處理NULL

6、條件表達(dá)式

a、CASE表達(dá)式

b、Decode函數(shù)

7、嵌套函數(shù)