Oracle学习5：SQL单行函数详解

来源：互联网发布：最小的c语言软件编辑：程序博客网时间：2024/06/16 00:34

Oracle学习5：单行函数
注：这里的计算结果字段名均较长，但为了方法看出计算公式，故基本未使用别名进行重命名。

1.概述

SQL函数根据输出结果可以分为单行函数和多行函数。

单行函数，一个输入对应一个输出；
多行函数，多个输入对应一个输出。

如下图：
这里写图片描述

2.单行函数

单行函数主要包括：字符函数、数字函数、日期函数、转换函数、通用函数

1.字符函数

字符函数，顾名思义，是对字符串进行操作的函数，主要包括：

UPPER()

select upper(t.employee) from test_ljb t;

rst：

UPPER(T.EMPLOYEE)-----------------ARVINJAVAHIVEORACLEHADOOPORACLEORACLEORA%CLE8 rows selected

LOWER()

select lower(t.employee) from test_ljb t;

Rst：

LOWER(T.EMPLOYEE)-----------------arvinjavahiveoraclehadooporacleoracleora%cle8 rows selected

LENGTH()

select length(t.employee) from test_ljb t;

Rst：

LENGTH(T.EMPLOYEE)------------------                 5                 4                 4                 6                 6                 6                 6                 78 rows selected

REPLACE()

select t.employee,replace(t.employee,'a','A'), replace(t.employee,'a') from test_ljb t;  --不写默认第三个参数是空

Rst：

EMPLOYEE   REPLACE(T.EMPLOYEE,'A','A') REPLACE(T.EMPLOYEE,'A')---------- --------------------------- -----------------------Arvin      Arvin                       ArvinJava       JAvA                        JvHive       Hive                        HiveOracle     OrAcle                      OrcleHadoop     HAdoop                      HdoopOracle     OrAcle                      OrcleOracle     OrAcle                      OrcleOra%cle    OrA%cle                     Or%cle8 rows selected

INITCAP()

select initcap(employee) from test_ljb;

INITCAP(EMPLOYEE)-----------------ArvinJavaHiveOracleHadoopOracleOracleOra%Cle

SUBSTR()

substr(col_name, a[, b])
从第a个到第a+b个。

select substr(t.employee,0),substr(t.employee,1) from test_ljb t;   --0和1结果一样

Rst：

SUBSTR(T.EMPLOYEE,0)           SUBSTR(T.EMPLOYEE,1)------------------------------ ------------------------------Arvin                          ArvinJava                           JavaHive                           HiveOracle                         OracleHadoop                         HadoopOracle                         OracleOracle                         OracleOra%cle                        Ora%cle8 rows selected

select employee,substr(t.employee,-2),substr(t.employee,-2,0),substr(t.employee,-2,-1),substr(t.employee,-2,1),substr(t.employee,-2,10) from test_ljb t;

EMPLOYEE   SUBSTR(T.EMPLOYEE,-2) SUBSTR(T.EMPLOYEE,-2,0) SUBSTR(T.EMPLOYEE,-2,-1) SUBSTR(T.EMPLOYEE,-2,1) SUBSTR(T.EMPLOYEE,-2,10)---------- --------------------- ----------------------- ------------------------ ----------------------- ------------------------Arvin      in                                                                     i                       inJava       va                                                                     v                       vaHive       ve                                                                     v                       veOracle     le                                                                     l                       leHadoop     op                                                                     o                       opOracle     le                                                                     l                       leOracle     le                                                                     l                       leOra%cle    le                                                                     l                       le8 rows selected

注：关于substr()函数，有几点一定要关注：

在Oracle中，substr()函数从0或1开始结果一样，都是代表从第一个开始；
里面的两个参数均可以设置位负数；

2.数字函数

round

四舍五入

select round(12.12345),round(12.12345,1),round(12.12345,-1) from dual;    --不写则保留0位有效小数,保留一位有效小数,精确到十位

ROUND(12.12345) ROUND(12.12345,1) ROUND(12.12345,-1)--------------- ----------------- ------------------             12              12.1                 10

TRUNC

保留几位，没有四舍五入

select TRUNC(12.18345),TRUNC(12.18345,1),TRUNC(-12.18345,2),TRUNC(-12.18345,-1) from dual;

TRUNC(12.18345) TRUNC(12.18345,1) TRUNC(-12.18345,2) TRUNC(-12.18345,-1)--------------- ----------------- ------------------ -------------------             12              12.1             -12.18                 -10

MOD()

取模运算，注意负数

select mod(10,2),mod(10,3),mod(10,-3),mod(-10,3),mod(-10,-3) from dual;

 MOD(10,2)  MOD(10,3) MOD(10,-3) MOD(-10,3) MOD(-10,-3)---------- ---------- ---------- ---------- -----------         0          1          1         -1          -1

3.转换函数

Oracle数据库常见的数据类型：数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），转换函数的主要用于三种类型的相互转换，共三种：
TO_CHAR()、TO_DATE()、TO_NUMBER()。

3.1 TO_CHAR()：将日期（数字）转为字符串

3.1.1日期转化为字符串

将当前时间转化为指定的格式：

select to_char(sysdate,'YYYY-MM-DD'),to_char(sysdate,'YYYYMMYY HH:MI:SS'),to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'YYYY-MM-DD') TO_CHAR(SYSDATE,'YYYYMMYYHH:MI TO_CHAR(SYSDATE,'YYYYMMDDHH24:----------------------------- ------------------------------ ------------------------------2017-12-25                    20171217 04:22:35              20171225 16:22:35

也可以获得年月份小时等：

select to_char(sysdate,'YYYY'),to_char(sysdate,'MM'),to_char(sysdate,'DD'),to_char(sysdate,'HH24') from dual;

TO_CHAR(SYSDATE,'YYYY') TO_CHAR(SYSDATE,'MM') TO_CHAR(SYSDATE,'DD') TO_CHAR(SYSDATE,'HH24')----------------------- --------------------- --------------------- -----------------------2017                    12                    25                    16

此外，还可以把08变为8的格式，只需要在格式之前加一个fm即可，如下：

select to_char(to_date('20170101','YYYYMMDD'),'MM'),to_char(to_date('20170101','YYYYMMDD'),'fmMM') from dual;

TO_CHAR(TO_DATE('20170101','YY TO_CHAR(TO_DATE('20170101','YY------------------------------ ------------------------------01                             1

3.1.2数字转化为字符串

L表示Local，即转化为本地货币。
9表示有则占位，没有则不占位；0表示无论有没有，都占位。

select to_char(6000,'L999,999.999'),to_char(6000,'$999,999.999'),to_char(6000,'$000,000.000') from dual;

TO_CHAR(6000,'L999,999.999') TO_CHAR(6000,'$999,999.999') TO_CHAR(6000,'$000,000.000')---------------------------- ---------------------------- ----------------------------          ￥6,000.000           $6,000.000                 $006,000.000

3.2 TO_DATE()函数：字符转化为日期

将20171010转化为日期格式等；
注：只要可以匹配上，怎么写都可以。

 select to_date('20171010','YYYYMMDD'),to_date('2017-1010105930','YYYY-MMDDHH24MISS') from dual;

TO_DATE('20171010','YYYYMMDD') TO_DATE('2017-1010105930','YYY------------------------------ ------------------------------2017/10/10                     2017/10/10 10:59:30

3.3 TO_NUMBER()函数：字符串转化为数字

与to_char相对应的，有时我们需要针对形如’$6,000.000’的结果进行数据计算，此时可以用to_number()；

select     to_number('￥6,000.000','L999,999.999'),    to_char(to_number('￥6,000.000','L999,999.999')*12,'L999,999.999') annual_salary from dual;

TO_NUMBER('￥6,000.000','L999, ANNUAL_SALARY----------------------------- ----------------------                         6000          ￥72,000.000

4.日期函数

4.1当前时间

SELECT SYSDATE FROM dual;

SYSDATE-----------2017/12/22

4.2日期运算

日期 ± 数字 = 日期，表示若干天、若干小时之后（之前）的日期
计算明天、昨天

select sysdate+1 tomorrow,sysdate-1 yesterday from dual;

TOMORROW    YESTERDAY----------- -----------2017/12/26  2017/12/24

计算一个小时前，一个小时候

select to_char(sysdate-1/24,'YYYY-MM-DD HH24:MI:SS'),to_char(sysdate+1/24,'YYYY-MM-DD HH24:MI:SS') from dual;

关于to_char函数见上文。

TO_CHAR(SYSDATE-1/24,'YYYY-MM- TO_CHAR(SYSDATE+1/24,'YYYY-MM------------------------------- ------------------------------2017-12-25 14:45:50            2017-12-25 16:45:50

日期 – 日期 = 数字，表示相差的天数
计算当前时间到20171220的相差天数。
注：关于to_date函数详解，见上文。

select sysdate-to_date('2017-12-20','YYYY-MM-DD') from dual;

SYSDATE-TO_DATE('2017-12-20','------------------------------               5.6591087962963

LAST_DAY(日期)：求出指定日期的本月最后一天的当前时刻
计算本月最后一天的当前时刻：

select last_day(sysdate) from dual;

LAST_DAY(SYSDATE)-----------------2017/12/31 15:54:

NEXT_DAY(日期，星期数)：求出下一个星期X的日期和当前时刻；

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

NEXT_DAY(SYSDATE,'星期一')-----------------------2018/1/1 15:57:35

前（后）几个月的日期

SELECT ADD_MONTHS(SYSDATE,-5),ADD_MONTHS(SYSDATE,5) FROM dual;

ADD_MONTHS(SYSDATE,-5) ADD_MONTHS(SYSDATE,5)---------------------- ---------------------2017/7/25 16:00:10     2018/5/25 16:00:10

MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间的月份差（可以为小数）

select months_between(to_date('2017/11/11','YYYY/MM/DD'),to_date('2017/01/12','YYYY/MM/DD')) from dual;

MONTHS_BETWEEN(TO_DATE('2017/1------------------------------              9.96774193548387

5.通用函数

nvl()、nvl2()、encode()、greatest()、least()等。

5.1 nvl(a, b)

返回第一个不是null的值，如果全为null，则返回null。
注：只能传递两个参数

select nvl(1,2),nvl(null,3),nvl(null,null) from dual;

  NVL(1,2) NVL(NULL,3) NVL(NULL,NULL)---------- ----------- --------------         1           3

该函数类似于coalesce()，不同的是coalesce()可以大于等于两个的参数：

select coalesce(100,null,1,2) from dual;

COALESCE(100,NULL,1,2)----------------------                   100

5.2 nvl2(a, b, c)

如果a为null，则返回c，如果a不为null，则返回c

select nvl2(1,10,20),nvl2(null,10,20) from dual;

NVL2(1,10,20) NVL2(NULL,10,20)------------- ----------------           10               20

5.3 encode()

DECODE(数值 | 列，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…)

select employee,decode(employee,'Arvin','阿尔文','Java','jv','Hive','hv') from test_ljb;

EMPLOYEE   DECODE(EMPLOYEE,'ARVIN','阿尔?---------- ----------------------------Arvin      阿尔文Java       jvHive       hvOracle     Hadoop     Oracle     Oracle     Ora%cle    8 rows selected

5.4 greatest(a, …)

选取参数列表中的最大值，参数大于等于1个。
如果参数存在null，则结果为null。

select greatest(1,null,10,100),greatest(10,20,50) from dual;

GREATEST(1,NULL,10,100) GREATEST(10,20,50)----------------------- ------------------                                        50

5.5 least(a, …)

选取参数列表中的最小值，参数大于等于1个。
如果参数存在null，则结果为null。

select least(1),least(1,-10),least(1,null,100) from dual;

  LEAST(1) LEAST(1,-10) LEAST(1,NULL,100)---------- ------------ -----------------         1          -10

阅读全文

1 0