（十）单行函数

最新推荐文章于 2023-01-30 16:34:55 发布

weixin_30436891

最新推荐文章于 2023-01-30 16:34:55 发布

阅读量204

点赞数

原文链接：http://www.cnblogs.com/xiangtingshen/p/10711540.html

版权

单行函数：只对一行数据进行变换，产生一个结果，可以没有参数，但必须有返回值，如concat，nvl

1.字符函数

2.数值函数

3.时间函数

4.日期函数

5.转换函数

6.通用函数

7.条件表达式

1.字符函数

操作对象是字符串，大致可分为两类：

一类是大小写控制函数，主要有upper，lower，initcap

SQL> select lower(ename) from emp where comm is not null;

LOWER(ENAM
----------
allen
ward
martin
turner

已用时间:  00: 00: 00.06
SQL>

SQL> select lower('HELLO') 转小写,upper('hello') 转大写,initcap('hello world') 首字母大写 from dual;

转小  转大  首字母大写
----- ----- -----------
hello HELLO Hello World

已用时间:  00: 00: 00.02
SQL>

另一类是字符控制函数

有：CONCAT，SUBSTR , LENGTH / LENGTHB , INSTR , LPAD, RPAD, TRIM , REPLACE

SUBSTR ：

substr(a,b):从a中，第b位开始取（计数从 1 开始），取到结尾

SQL> select substr('hello world',3) from dual;

SUBSTR('H
---------
llo world

已用时间:  00: 00: 00.02

substr(a,b，c):从a中，第b位开始取（计数从 1 开始），向右取 c 位

SQL> ed
已写入 file afiedt.buf

  1* select substr('hello world',3,5) from dual
SQL> /

SUBST
-----
llo w

已用时间:  00: 00: 00.02

LENGTH 字符数/，LENGTHB 字节数

SQL> select length('hello world') from dual;

LENGTH('HELLOWORLD')
--------------------
                  11

已用时间:  00: 00: 00.02
SQL> select length('中国') from dual;

LENGTH('中国')
--------------
             2

已用时间:  00: 00: 00.01
SQL> select lengthb('中国') from dual;

LENGTHB('中国')
---------------
              4

已用时间:  00: 00: 00.01
SQL> select lengthb('hello world') from dual;

LENGTHB('HELLOWORLD')
---------------------
                   11

已用时间:  00: 00: 00.02

instr 在母串中查找子串，找到返回下表，计数从 1 开始，没有返回 0 ；

  1* select instr('hello world','llo') from dual
SQL> /

INSTR('HELLOWORLD','LLO')
-------------------------
                        3

已用时间:  00: 00: 00.02
SQL>

lpad 左填充，参数1：待填充的字符串，参数2：填充后字符串的总长度（字节），参数3：填充什么

SQL> select lpad('abcd',10,'*') '左',rpad('abcd',10,'#') 右 from dual;
select lpad('abcd',10,'*') '左',rpad('abcd',10,'#') 右 from dual
                           *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


已用时间:  00: 00: 00.04
SQL> c /'左'/"左"
  1* select lpad('abcd',10,'*') "左",rpad('abcd',10,'#') 右 from dual
SQL> /

左         右
---------- ----------
******abcd abcd######

已用时间:  00: 00: 00.04
SQL>

trim 去掉前后指定的字符

SQL> select trim('H' from 'HelloH' ) from dual;

TRIM
----
ello

已用时间:  00: 00: 00.02
SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

已选择14行。

已用时间:  00: 00: 00.07
SQL> select trim('S' from ename) from emp;

TRIM('S'FR
----------
MITH
ALLEN
WARD
JONE
MARTIN
BLAKE
CLARK
COTT
KING
TURNER
ADAM
JAME
FORD
MILLER

已选择14行。

已用时间:  00: 00: 00.04
SQL>

replace 替换

已用时间:  00: 00: 00.04
SQL> select replace('hello','l','*') from dual;

REPLA
-----
he**o

已用时间:  00: 00: 00.02
SQL> select replace('hello','l','') from dual;

REP
---
heo

已用时间:  00: 00: 00.02
SQL>

2.数值函数

ROUND：四舍五入

　　ROUND(45.926, 2)　　45.93 　　

TRUNC：截断

　　ROUND(45.926, 2)　　45.92

MOD：求余

　　MOD(1600， 300)

round(45.926, 2) , 2 表达的是保留两位小数，第二个参数如果是 0 ，可以不写，正负表示小数点之后或之前的位数

SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;

        一         二         三         四         五
---------- ---------- ---------- ---------- ----------
     45.93       45.9         46         50          0

已用时间:  00: 00: 00.08
SQL>

SQL> ed
已写入 file afiedt.buf

  1* select TRUNC(45.926,2) 一,TRUNC(45.926,1) 二,TRUNC(45.926,0) 三,TRUNC(45.926,-1) 四,TRUNC(45.926,-2) 五 from dual
SQL> /

        一         二         三         四         五
---------- ---------- ---------- ---------- ----------
     45.92       45.9         45         40          0

已用时间:  00: 00: 00.02
SQL>

SQL> select mod(100,3) from dual;

MOD(100,3)
----------
         1

已用时间:  00: 00: 00.02
SQL>

3.时间函数

SQL> select sysdate from dual;

SYSDATE
--------------
15-4月 -19

已用时间:  00: 00: 00.03
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2019-04-15 15:12:20

已用时间:  00: 00: 00.02
SQL> select to_char(sysdate,'day') from dual;

TO_CHAR(S
---------
星期一

已用时间:  00: 00: 00.02
SQL>

日期加减数字，结果仍为日期。单位：天

SQL> select (sysdate-1) 昨天,(sysdate) 今天,(sysdate+1) 明天 from dual;

昨天           今天           明天
-------------- -------------- --------------
14-4月 -19     15-4月 -19     16-4月 -19

已用时间:  00: 00: 00.02
SQL> ed
已写入 file afiedt.buf

  1* select to_char(sysdate-1,'yyyy-mm-dd') 昨天,to_char(sysdate,'yyyy-mm-dd') 今天,to_char(sysdate+1,'yyyy-mm-dd') 明天 from dual
SQL> /

昨天       今天       明天
---------- ---------- ----------
2019-04-14 2019-04-15 2019-04-16

已用时间:  00: 00: 00.02
SQL>

计算工龄

  1* select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp
SQL> /

ENAME      HIREDATE               天       星期         月         年
---------- -------------- ---------- ---------- ---------- ----------
SMITH      17-12月-80     13998.6403 1999.80575 466.621342 38.3524391
ALLEN      20-2月 -81     13933.6403 1990.52004 464.454676 38.1743569
WARD       22-2月 -81     13931.6403 1990.23432 464.388009 38.1688774
JONES      02-4月 -81     13892.6403  1984.6629 463.088009 38.0620281
MARTIN     28-9月 -81     13713.6403 1959.09147 457.121342 37.5716172
BLAKE      01-5月 -81     13863.6403 1980.52004 462.121342 37.9825761
CLARK      09-6月 -81     13824.6403 1974.94861 460.821342 37.8757268
SCOTT      19-4月 -87     11684.6403 1669.23432 389.488009 32.0127131
KING       17-11月-81     13663.6403 1951.94861 455.454676 37.4346309
TURNER     08-9月 -81     13733.6403 1961.94861 457.788009 37.6264117
ADAMS      23-5月 -87     11650.6403 1664.37718 388.354676 31.9195624
JAMES      03-12月-81     13647.6403  1949.6629 454.921342 37.3907952
FORD       03-12月-81     13647.6403  1949.6629 454.921342 37.3907952
MILLER     23-1月 -82     13596.6403 1942.37718 453.221342 37.2510692

已选择14行。

已用时间:  00: 00: 00.06
SQL>

注意：日期和日期可以相减，但是不允许相加。日期只能和数字相加！

4.日期函数

上述求取员工工龄的结果计算不准确，如果想其算准确，可以使用日期函数来做。

months_between：两个日期值相差的月数（精确值）

SQL> ed
已写入 file afiedt.buf

  1* select (sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 from emp
SQL> /

        一         二
---------- ----------
466.621542 459.956331
464.454876 457.859557
464.388209 457.795041
463.088209 456.440202
457.121542 450.601493
462.121542  455.47246
460.821542 454.214396
389.488209 383.891815
455.454876 448.956331
457.788209 451.246654
388.354876 382.762783
454.921542 448.407944
454.921542 448.407944
453.221542 446.762783

已选择14行。

已用时间:  00: 00: 00.06
SQL>

add_months：在某日期值上加上多少的月，整数向后计算，负数向前计算

计算95个月之后是哪年，哪月，哪天

SQL> select add_months(sysdate,95) 哪一天 from dual;

哪一天
--------------
15-3月 -27

已用时间:  00: 00: 00.01
SQL>

last_day，日期所在月的最后一天

SQL> select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
30-4月 -19

已用时间:  00: 00: 00.02
SQL>

next_day，指定日期的下一个日期

SQL> select next_day(sysdate,'星期一') from dual;

NEXT_DAY(SYSDA
--------------
22-4月 -19

已用时间:  00: 00: 00.02
SQL> select next_day(sysdate,'星期二') from dual;

NEXT_DAY(SYSDA
--------------
16-4月 -19

已用时间:  00: 00: 00.02
SQL>

round,trunc 对日期型数据进行四舍五入和截断

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

ROUND(SYSDATE,
--------------
01-4月 -19

已用时间:  00: 00: 00.02
SQL> ed
已写入 file afiedt.buf

  1* select round(sysdate,'month'),round(sysdate,'year') from dual
SQL> /

ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-4月 -19     01-1月 -19

已用时间:  00: 00: 00.02
SQL> ed
已写入 file afiedt.buf

  1* select trunc(sysdate,'month'),trunc(sysdate,'year') from dual
SQL> /

TRUNC(SYSDATE, TRUNC(SYSDATE,
-------------- --------------
01-4月 -19     01-1月 -19

已用时间:  00: 00: 00.01
SQL> ed
已写入 file afiedt.buf

  1* select round('17-4月-19','month'),round('17-10月-19','year') from dual
SQL> /
select round('17-4月-19','month'),round('17-10月-19','year') from dual
             *
第 1 行出现错误:
ORA-01722: 无效数字


已用时间:  00: 00: 00.03
SQL> ed
已写入 file afiedt.buf

  1* select round(to_date('17-4月-19'),'month'),round(to_date('17-10月-19'),'year') from dual
SQL> /

ROUND(TO_DATE( ROUND(TO_DATE(
-------------- --------------
01-5月 -19     01-1月 -20

已用时间:  00: 00: 00.01

5.转换函数

显示类型转换

to_char, to_date，to_number

隐式类型转换

SQL> select * from emp where hiredate = '02-4月-81';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20

已用时间:  00: 00: 00.02
SQL>

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:S
------------------------------------
2019-04-15 16:01:56 今天是 星期一

已用时间:  00: 00: 00.02
SQL>

反向操作

SQL> ed
已写入 file afiedt.buf

  1* select to_date('2019-04-15 16:01:56 今天是 星期一','yyyy-mm-dd hh24:mi:ss "今天是" day') from dual
SQL> /

TO_DATE('2019-
--------------
15-4月 -19

已用时间:  00: 00: 00.02
SQL>

查询员工薪水：2 位小数，本地货币代码，千位符

SQL> select to_char(sal,'L9,999.99') from emp;

TO_CHAR(SAL,'L9,999
-------------------
           ￥800.00
         ￥1,600.00
         ￥1,250.00
         ￥2,975.00
         ￥1,250.00
         ￥2,850.00
         ￥2,450.00
         ￥3,000.00
         ￥5,000.00
         ￥1,500.00
         ￥1,100.00
           ￥950.00
         ￥3,000.00
         ￥1,300.00

已选择14行。

已用时间:  00: 00: 00.08
SQL>

SQL> select to_number('￥1,666.00','L9,999.99') 转换数字 from dual;

  转换数字
----------
      1666

已用时间:  00: 00: 00.02
SQL>

6.通用函数

NVL(expr1, expr2)

NVL2(expr1, expr2, expr3)

NULLIF(expr1, expr2)

COALESCE(expr1, expr2, ..., exprn)

nvl2 是 nvl 的增强版 nvl2(a, b, c)，若 a = null, 则返回 c, 否则返回 b

用nvl2 求员工的年收入

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

ENAME      SAL*12+NVL2(COMM,COMM,0)
---------- ------------------------
SMITH                          9600
ALLEN                         19500
WARD                          15500
JONES                         35700
MARTIN                        16400
BLAKE                         34200
CLARK                         29400
SCOTT                         36000
KING                          60000
TURNER                        18000
ADAMS                         13200

ENAME      SAL*12+NVL2(COMM,COMM,0)
---------- ------------------------
JAMES                         11400
FORD                          36000
MILLER                        15600

已选择14行。

nullif(a, b), 当a = b 时返回 null, 不相等时返回 a 的值

SQL> select nullif('L9,999.99','L9,999.99') from dual;

NULLIF('L
---------


SQL> select nullif('L9,999.99','L9,999.9') from dual;

NULLIF('L
---------
L9,999.99

SQL>

coalesce(a, b, c, d,..., n)，从左往右找参数中第一个不为空的值

SQL> select comm,sal,coalesce(comm,sal) from emp;

      COMM        SAL COALESCE(COMM,SAL)
---------- ---------- ------------------
                  800                800
       300       1600                300
       500       1250                500
                 2975               2975
      1400       1250               1400
                 2850               2850
                 2450               2450
                 3000               3000
                 5000               5000
         0       1500                  0
                 1100               1100
                  950                950
                 3000               3000
                 1300               1300

已选择14行。

SQL>

7.条件表达式

老板打算给员工涨工资：总裁涨1000，经理涨800，其他人涨400，将涨前，涨后的薪水列出。

SQL中没有if else 语句，可以使用case，或者decode

case, 是一个表达式，其语法为：

CASE expr WHEN comparison_expr1 THEN return_expr1
    [WHEN comparison_expr2 THEN return_expr2
    WHEN comparison_exprn THEN return_exprn
    ELSE else_expr]
END

已写入 file afiedt.buf

  1  select ename,job,sal 涨薪水前, case job when 'PRESIDENT' then sal + 1000
  2  when 'MANAGER' then sal + 800
  3  else sal + 400
  4* end 涨薪水后 from emp
SQL> /

ENAME      JOB         涨薪水前   涨薪水后
---------- --------- ---------- ----------
SMITH      CLERK            800       1200
ALLEN      SALESMAN        1600       2000
WARD       SALESMAN        1250       1650
JONES      MANAGER         2975       3775
MARTIN     SALESMAN        1250       1650
BLAKE      MANAGER         2850       3650
CLARK      MANAGER         2450       3250
SCOTT      ANALYST         3000       3400
KING       PRESIDENT       5000       6000
TURNER     SALESMAN        1500       1900
ADAMS      CLERK           1100       1500
JAMES      CLERK            950       1350
FORD       ANALYST         3000       3400
MILLER     CLERK           1300       1700

已选择14行。

SQL>

decode

DECODE(col | expression, search1 ,result1
[search2 ,result2,...]
[,default])
除第一个和最后一个参数之外，中间参数都是成对出现的（参1，条件，值，条件，值，...，条件，值，尾参）

SQL> ed
已写入 file afiedt.buf

  1  select ename,job,sal 涨薪水前, decode(job, 'PRESIDENT' ,sal + 1000,
  2   'MANAGER' ,sal + 800
  3  ,sal + 400)
  4* as 涨薪水后 from emp
SQL> /

ENAME      JOB         涨薪水前   涨薪水后
---------- --------- ---------- ----------
SMITH      CLERK            800       1200
ALLEN      SALESMAN        1600       2000
WARD       SALESMAN        1250       1650
JONES      MANAGER         2975       3775
MARTIN     SALESMAN        1250       1650
BLAKE      MANAGER         2850       3650
CLARK      MANAGER         2450       3250
SCOTT      ANALYST         3000       3400
KING       PRESIDENT       5000       6000
TURNER     SALESMAN        1500       1900
ADAMS      CLERK           1100       1500
JAMES      CLERK            950       1350
FORD       ANALYST         3000       3400
MILLER     CLERK           1300       1700

已选择14行。

SQL>

转载于:https://www.cnblogs.com/xiangtingshen/p/10711540.html

weixin_30436891

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
（十）单行函数

单行函数：只对一行数据进行变换，产生一个结果，可以没有参数，但必须有返回值，如concat，nvl1.字符函数2.数值函数3.时间函数4.日期函数5.转换函数6.通用函数7.条件表达式1.字符函数操作对象是字符串，大致可分为两类：一类是大小写控制函数，主要有upper，lower，initcapSQL> select lower(ena...
复制链接

扫一扫