Oracle-05-函数

最新推荐文章于 2023-11-21 15:08:53 发布

原创最新推荐文章于 2023-11-21 15:08:53 发布 · 221 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#函数

Oracle 专栏收录该内容

38 篇文章

订阅专栏

函数：单行函数、多行函数

单行函数：每一行函数都工作一次，每一次函数都会返回一个值

字符串函数

lower() 字符串变小写

select lower('HelloWorld') from dual;

LOWER('HE
---------
helloworld

Elapsed: 00:00:00.01

upper() 字符串变大写

select upper('HelloWorld') from dual;

UPPER('HE
---------
HELLOWORLD

Elapsed: 00:00:00.00

initcap() 单词首字母大写

select initcap('hello world') from dual;

INITCAP('H
----------
Hello World

Elapsed: 00:00:00.01

concat() 连接字符串

select concat('hello',' world') from dual;

CONCAT('HEL
-----------
hello world

Elapsed: 00:00:00.00

length() 字符串长度

select length('hello') from dual;

LENGTH('HELLO')
---------------
              5

Elapsed: 00:00:00.00

substr() 截取字符串，第一个参数是要截取的字符串，第二个参数是开始位置，负值代表从字符串右边开始截取，第三个参数是要截取的字符长度，如果没有就是截取到末尾

select substr('hello',2,3) from dual;

SUB
---
ell

Elapsed: 00:00:00.00

instr() 得到一个字符串在另外一个字符串中第一次出现的位置，第一个参数为要被查找的字符串，第二个参数为要在之前参数中查找的字符串，第三个参数代表从左数还是从右数，第四个参数代表出现第几次

select instr('hello','el') from dual;

INSTR('HELLO','EL')
-------------------
                  2

Elapsed: 00:00:00.00

select instr('hello','l',-1,2) from dual;

INSTR('HELLO','L',-1,2)
-----------------------
                      3

Elapsed: 00:00:00.01

trim() 从字符串中截掉首尾所有包含的字符（注意不是字符串）

select trim('h' from 'hellohhhhh') from dual;

TRIM
----
ello

Elapsed: 00:00:00.01

replace() 用制定字符串替换另一个字符串中的指定字符串

select replace('helloworld','l','L') from dual;

REPLACE('H
----------
heLLoworLd

Elapsed: 00:00:00.00

lpad() 在指定字符串左边填充字符到规定的字符串长度

select lpad('hello',10,'*') from dual;

LPAD('HELL
----------
*****hello

Elapsed: 00:00:00.00

lpad() 在指定字符串右边填充字符到规定的字符串长度

select rpad('hello',10,'*') from dual;

RPAD('HELL
----------
hello*****

Elapsed: 00:00:00.00

数字函数

round() 四舍五入，第一个参数是要进行四舍五入的数字，第二个参数是保留的小数点位数，负数代表小数点前面的位数

select round(123.456,2) from dual;

ROUND(123.456,2)
----------------
          123.46

Elapsed: 00:00:00.01

select round(123.456,-1) from dual;

ROUND(123.456,-1)
-----------------
              120

Elapsed: 00:00:00.01

trunct() 数字截取

select trunc(123.456,1) from dual;

TRUNC(123.456,1)
----------------
           123.4

Elapsed: 00:00:00.00

ceil() 向上进位到最近整数

select ceil(123.456) from dual;

CEIL(123.456)
-------------
          124

Elapsed: 00:00:00.00

power() 计算指数函数

select power(3,3) from dual;

POWER(3,3)
----------
        27

Elapsed: 00:00:00.00

日期函数

sysdate 返回当前系统的时间

select sysdate from dual;

SYSDATE
---------
27-APR-18

Elapsed: 00:00:00.00

select sysdate-7 from dual;

SYSDATE-7
---------
20-APR-18

Elapsed: 00:00:00.00

months_between() 返回两个时间点相差几个月

select ename,months_between(sysdate,hiredate) from emp;

ENAME      MONTHS_BETWEEN(SYSDATE,HIREDATE)
---------- --------------------------------
SMITH                            448.328198
ALLEN                            446.231424
WARD                             446.166907
JONES                            444.812069
MARTIN                           438.973359
BLAKE                            443.844327
CLARK                            442.586262
SCOTT                            372.263682
KING                             437.328198
TURNER                            439.61852
ADAMS                            371.134649
JAMES                            436.779811
FORD                             436.779811
MILLER                           435.134649

14 rows selected.

Elapsed: 00:00:00.00

add_months() 在一个时间点上增加月份

select add_months(sysdate,2) from dual;

ADD_MONTH
---------
27-JUN-18

Elapsed: 00:00:00.00

next_day() 计算从某一时间点算起，下一个星期几是哪天

select next_day(sysdate,'mon') from dual;

NEXT_DAY(
---------
30-APR-18

Elapsed: 00:00:00.00

last_day() 返回时间点所在月份的最后一天

select last_day(sysdate) from dual;

LAST_DAY(
---------
30-APR-18

Elapsed: 00:00:00.00

日期的四舍五入

select round(sysdate,'month') from dual;

ROUND(SYS
---------
01-MAY-18

Elapsed: 00:00:00.00

select round(sysdate,'year') from dual;

ROUND(SYS
---------
01-JAN-18

Elapsed: 00:00:00.00

select trunc(sysdate,'month') from dual;

TRUNC(SYS
---------
01-APR-18

Elapsed: 00:00:00.01

转换函数（完成数据类型的转换），分隐式转换，显式转换

to_char()

select sysdate from dual;

SYSDATE
---------
27-APR-18

Elapsed: 00:00:00.00

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2018-04-27 23:37:38

Elapsed: 00:00:00.00

select to_char(sysdate,'year-mon-dy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MON-DYHH24:MI:SS')
-----------------------------------------------------------
twenty eighteen-apr-fri 23:40:06

select to_char(sysdate,'year-month-day hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YEAR-MONTH-DAYHH24:MI:SS')
-----------------------------------------------------------------------
twenty eighteen-april    -friday    23:40:40

Elapsed: 00:00:00.00

select to_char(sysdate,'dd " of " mon') from dual;

TO_CHAR(SYS
-----------
27  of  apr

Elapsed: 00:00:00.00

格式字符串中加上fm可以将数字前面的零去掉

select to_char(sysdate,'fmyyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'FM
-------------------
2018-4-27 23:43:12

Elapsed: 00:00:00.00

d表示用数字表示的周几，按照美国人的习惯，周日是星期的第一天

select to_char(sysdate,'yyyy-mm-dd d') from dual;

TO_CHAR(SYSD
------------
2018-04-27 6

Elapsed: 00:00:00.00

sssss表示从凌晨0点开始已经过了多少秒

select to_char(sysdate,'yyyy-mm-dd d sssss') from dual;

TO_CHAR(SYSDATE,'Y
------------------
2018-04-27 6 85655

Elapsed: 00:00:00.00

select to_char(123.45,'L999,999.99') from dual;

TO_CHAR(123.45,'L999,
---------------------
              $123.45

Elapsed: 00:00:00.00

to_number()

select to_number('$123.45','L999,999.99') from dual;

TO_NUMBER('$123.45','L999,999.99')
----------------------------------
                            123.45

Elapsed: 00:00:00.01

to_date

select to_date('2013-03-05','yyyy-mm-dd') from dual;

TO_DATE('
---------
05-MAR-13

Elapsed: 00:00:00.00

number类型和date类型不能直接转换，必须通过to_char()进行转换

通用函数（处理空值），nvl第一个参数如果不是空值返回原值，如果是空值，返回第二个参数

select ename,sal+nvl(comm,0)*12 from emp;

ENAME      SAL+NVL(COMM,0)*12
---------- ------------------
SMITH                     800
ALLEN                    5200
WARD                     7250
JONES                    2975
MARTIN                  18050
BLAKE                    2850
CLARK                    2450
SCOTT                    3000
KING                     5000
TURNER                   1500
ADAMS                    1100
JAMES                     950
FORD                     3000
MILLER                   1300

14 rows selected.

Elapsed: 00:00:00.01

nvl2，如果第一个参数不是空值，返回第二个参数，如果是空值，返回第三个参数

select ename,sal+nvl2(comm,comm,0)*12 from emp;

ENAME      SAL+NVL2(COMM,COMM,0)*12
---------- ------------------------
SMITH                           800
ALLEN                          5200
WARD                           7250
JONES                          2975
MARTIN                        18050
BLAKE                          2850
CLARK                          2450
SCOTT                          3000
KING                           5000
TURNER                         1500
ADAMS                          1100
JAMES                           950
FORD                           3000
MILLER                         1300

14 rows selected.

Elapsed: 00:00:00.00

如果两个值相同，返回null,否则返回第一个值

select nullif(4,5) from dual;

NULLIF(4,5)
-----------
          4

Elapsed: 00:00:00.00

select nullif(5,5) from dual;

NULLIF(5,5)
-----------


Elapsed: 00:00:00.01

coalesce从左到右依次检查值是否是空值，并返回第一个不是空值的值

select ename,coalesce(comm,sal,0) from emp;

ENAME      COALESCE(COMM,SAL,0)
---------- --------------------
SMITH                       800
ALLEN                       300
WARD                        500
JONES                      2975
MARTIN                     1400
BLAKE                      2850
CLARK                      2450
SCOTT                      3000
KING                       5000
TURNER                        0
ADAMS                      1100
JAMES                       950
FORD                       3000
MILLER                     1300

14 rows selected.

Elapsed: 00:00:00.01

case...when...then条件选择语句

select ename,job,sal,
    case job when 'CLERK' then sal*1.1
    when 'SALESMAN' then sal*1.15
    when 'ANALYST' then sal*1.2
    else sal
    end as new_sal
    from emp；

ENAME      JOB              SAL    NEW_SAL
---------- --------- ---------- ----------
SMITH      CLERK            800        880
ALLEN      SALESMAN        1600       1840
WARD       SALESMAN        1250     1437.5
JONES      MANAGER         2975       2975
MARTIN     SALESMAN        1250     1437.5
BLAKE      MANAGER         2850       2850
CLARK      MANAGER         2450       2450
SCOTT      ANALYST         3000       3600
KING       PRESIDENT       5000       5000
TURNER     SALESMAN        1500       1725
ADAMS      CLERK           1100       1210
JAMES      CLERK            950       1045
FORD       ANALYST         3000       3600
MILLER     CLERK           1300       1430

14 rows selected.

Elapsed: 00:00:00.00

decode改写case...when...then

    select ename,job,sal,
    decode(job,'CLERK',sal*1.1,
                'SALESMAN',sal*1.15,
                'ANALYST',sal*1.2,sal) new_sal
    from emp

ENAME      JOB              SAL    NEW_SAL
---------- --------- ---------- ----------
SMITH      CLERK            800        880
ALLEN      SALESMAN        1600       1840
WARD       SALESMAN        1250     1437.5
JONES      MANAGER         2975       2975
MARTIN     SALESMAN        1250     1437.5
BLAKE      MANAGER         2850       2850
CLARK      MANAGER         2450       2450
SCOTT      ANALYST         3000       3600
KING       PRESIDENT       5000       5000
TURNER     SALESMAN        1500       1725
ADAMS      CLERK           1100       1210
JAMES      CLERK            950       1045
FORD       ANALYST         3000       3600
MILLER     CLERK           1300       1430

14 rows selected.

Elapsed: 00:00:00.00

多行函数：针对多行数据才会有的一些计算方式，比如最大值，求和，平均值等，所有组函数都会忽略空值

min()最小值

max()最大值

avg()平均值

sum()求和

select min(sal),max(sal),avg(sal),sum(sal) from emp;

  MIN(SAL)   MAX(SAL)   AVG(SAL)   SUM(SAL)
---------- ---------- ---------- ----------
       800       5000 2073.21429      29025

Elapsed: 00:00:00.01

count()计数函数

select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.01

去重后进行统计

select count(distinct deptno) from emp;

COUNT(DISTINCTDEPTNO)
---------------------
                    3

Elapsed: 00:00:00.00

对日期格式运用组函数

select min(hiredate),max(hiredate) from emp;

MIN(HIRED MAX(HIRED
--------- ---------
17-DEC-80 23-MAY-87

Elapsed: 00:00:00.00

用组函数对数据进行分组后统计，oracle要求所有在select后面未使用分组函数的列，一定要放在group by语句后，而且不能跟列别名，必须跟列原始名

select deptno,count(empno) from emp group by deptno;

    DEPTNO COUNT(EMPNO)
---------- ------------
        30            6
        20            5
        10            3

Elapsed: 00:00:00.00

select deptno,job,count(empno) from emp group by deptno,job order by deptno,job;

    DEPTNO JOB       COUNT(EMPNO)
---------- --------- ------------
        10 CLERK                1
        10 MANAGER              1
        10 PRESIDENT            1
        20 ANALYST              2
        20 CLERK                2
        20 MANAGER              1
        30 CLERK                1
        30 MANAGER              1
        30 SALESMAN             4

9 rows selected.

Elapsed: 00:00:00.00

如果要对分组后的数据进行筛选，可以用having子句，而where子句只能过滤表中的原始数据

select deptno,job,count(empno) from emp group by deptno,job having count(empno)>1 order by deptno,job;

    DEPTNO JOB       COUNT(EMPNO)
---------- --------- ------------
        20 ANALYST              2
        20 CLERK                2
        30 SALESMAN             4

Elapsed: 00:00:00.00