Oracle相关语句（四）- 单行函数

7.单行函数

       函数的分类：单行函数、多行函数

 

7.1 字符函数

字符函数又分为两类：大小写控制函数、字符控制函数

1.        大小写控制函数

1)       LOWER：全部转换为小写字母

2)       UPPER：全部转换为大写字母

3)       INITCAP：首字母大写

范例：

SELECT LOWER('ORACLE DATABASE') FROM dual;

SELECT UPPER('Oracle database') FROM dual;

SELECT initcap('oracle database') FROM dual;

 

2.        字符控制函数

1)       CONCAT：连接

CONCAT(a,b):只能连接两个

SELECT CONCAT(ename,sal) FROM emp;

 

2)       SUBSTR：截取

 

SUBSTR(值，起始位置，截取的长度）

注：*截取长度省略，取起始位置开始的所有字符

      *起始位置为负值，表示从后面第起始位置开始向右截取

SELECT SUBSTR('helloworld',2,3) FROM dual;

SUB

---

ell

 

SELECT SUBSTR('helloworld',-5,3) FROM dual;

wor

3)       LENGTH(A)：求长度

4)       INSTR(A,B,n1,n2)：位置

注：B在A中，从n1开始，第n2次出现的位置

       n1和n2可省略，默认值为1

SELECT INSTR('hello,world','w') FROM dual;

INSTR('HELLO,WORLD','W')

------------------------

                       7

5)       LPAD(A,值,B)：左填充

SELECT sal,LPAD(sal,10,'*') FROM emp;

  LPAD(SAL,10,'*')

 --------------------

*******800

******1600

******1250

******2975

6)       RPAD(A,值,B)：右填充,属性同左填充

7)       TRIM：去掉指定的字符(只能去两边的)

SELECT trim('h' from 'hello,world') FROM dual;

TRIM('H'FR

----------

ello,world

TRIM：去掉左右两边的空格

SELECT trim('                   hhh                    hh                  ') FROM dual;

TRIM('HHHHH')

-------------------------

hhh                    hh

8）REPLACE(a,b,c):把a中的b用c替换

7.2 数学函数

       1）ROUND：四舍五入

注：正数指小数点后面，负数指小数点前面，第二个属性默认0，指取整

SELECT ROUND(45.688,2) FROM DUAL;

ROUND(45.688,2)

---------------

         45.69

SELECT ROUND(45.688,-2) FROM DUAL;

ROUND(45.688,-2)

----------------

               0

2）  TRUNC：截断

注：正数指小数点后面，负数指小数点前面，第二个属性默认0，指取整

select TRUNC(45.878,2) from dual;

结果：

TRUNC(45.878,2)

---------------

         45.87

       3）MOD：求余数

SELECT MOD(10,3) FROM DUAL;

结果：

 MOD(10,3)

----------

        1

7.3 日期函数

默认日期格式：DD-MON-RR

日期格式模型的元素：YYYY(数字表示），YEAR（英文表示）,

                                MM（两位数字）,MONTH（月份全名）,

                                DAY（星期几）,DY（星期几的英文缩写）

格式码：世纪CC，年YY，月MM，日DD，小时HH24，分MI，秒SS

通过双引号“ ”可添加字符

1.SYSDATE——系统日期

SELECT SYSDATE FROM dual;

结果：

SYSDATE

-----------

09-3月 -18

2.日期的算术运算

l  日期 – 数字（天数） = 日期

l  日期 + 数字（天数） = 日期

l  日期 – 日期 = 数字（天数）

注：加减小时的话将数字除以24，即

      日期 +或- 数字/24 =日期

SELECT SYSDATE, SYSDATE+1 FROM dual;

结果：

SYSDATE        SYSDATE+1

-------------- ------------

09-3月 -18     10-3月 -18

 

SELECT SYSDATE, SYSDATE-1 FROM dual;

结果：

SYSDATE        SYSDATE-1

-------------- -------------

09-3月 -18     08-3月 -18

 

SELECT TRUNC(SYSDATE-hiredate) FROM emp;

结果：

TRUNC(SYSDATE-HIREDATE)

-----------------------

                  13596

                  13531

                  13529

3.函数

1)       MONTHS_BETWEEN:两个日期之间相差的月份（前大后小）

SELECT MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;

MONTHS_BETWEEN(SYSDATE,HIREDATE)

--------------------------------

                      446.768443

                      444.671669

                            441

2)       ADD_MONTHS:在指定的日期上加上月数

SELECT ADD_MONTHS(SYSDATE,2) FROM dual;

 

ADD_MONTHS(SYS

--------------

09-5月 -18

3)       NEXT_DAY:指定日期的下一个日期

SELECT SYSDATE, NEXT_DAY(SYSDATE,'星期一') FROM dual;

 

SYSDATE        NEXT_DAY(SYSDA

-------------- --------------

09-3月 -18     12-3月 -18

4)       LAST_DAY:月末最后一天

SELECT LAST_DAY(SYSDATE) FROM dual;

LAST_DAY(SYSDA

--------------

31-3月 -18

5）ROUND（date，'fmt'):将date按指定格式fmt进行四舍五入

注：fmt默认格式DD，四舍五入为最近的天

6）TRUNC（date，'fmt')：将date按指定格式fmt进行截断

用法同ROUND

7）EXTRACT（a from date）：返回日期类型数据中的年份，月份或者日

a的值为YEAR,MONTH,DAY

7.4 转换函数

       显性、隐性

1)       TO_CHAR():转换为字符

转换数字的格式：9（1位数字），0（显示前导0），$（显示美元符号），

                          L（显示本地货币符号），.（显示小数点），

                          ，（显示千位符）

注：用9表位数时，格式中的宽度一定要超过或等于实际宽度，否则会显示为#

SELECT ename,hiredate,TO_CHAR (hiredate,'yyyy-mm-dd') FROM emp;

ENAME     HIREDATE       TO_CHAR(HI

---------- -------------- ----------

SMITH     17-12月-80     1980-12-17

ALLEN     20-2月 -81     1981-02-20

WARD      22-2月 -81     1981-02-22

SELECT ename,sal,TO_CHAR(sal,'99,999') FROM emp;

ENAME             SAL TO_CHAR

---------- ---------- -------

SMITH             800     800

ALLEN            1600   1,600

WARD             1250   1,250

2)       TO_NUMBER():转换为数字

SELECT TO_NUMBER ('123')+ TO_NUMBER ('123') FROM dual;

结果：

TO_NUMBER('123')+TO_NUMBER('123')

---------------------------------

                              246

3)       TO_DATE():转换为日期

SELECT TO_DATE ('2018-12-12','yyyy-mm-dd') FROM dual;

结果：

TO_DATE('2018-

--------------

12-12月-18

7.5 通用函数

1)       NVL(a,expr)：a为空时，返回expr

2)       NVL2(a，expr1,expr2):a不是空，返回expr1；a是空，返回expr2

3）     NULLIF(expr1,expr2):expr1,expr2相等，返回null，否则返回expr1

4）     COALESCE(expr1,expr2,....,exprn):返回第一个不为空的参数

范例：查询雇员姓名、雇员月总收入。

SELECT ename,NVL2(comm,sal+comm,sal) FROM emp;

ENAME     NVL2(COMM,SAL+COMM,SAL)

---------- -----------------------

SMITH                          800

ALLEN                         1900

WARD                          1750

JONES                         2975

5）  DECODE（）：类似于if….elseif…else语句

DECODE(列名，条件1，结果1，                条件2，结果2，                  ….                条件n，结果n，                结果 )

6）   case语法

    CASE expr

                 WHEN 条件1 THEN 结果1

                 WHEN 条件2 THEN 结果2

                 ...

                 WHEN 条件n THEN 结果n

                 ELSE 结果

         END

或

        CASE

                 WHEN 条件1 THEN 结果1

                 WHEN 条件2 THEN 结果2

                 ...

                 WHEN 条件n THEN 结果n

                 ELSE 结果

         END