Oracle单行函数

最新推荐文章于 2022-11-16 17:48:14 发布

ZHOU_VIP

最新推荐文章于 2022-11-16 17:48:14 发布

阅读量249

点赞数

分类专栏： Orale MySQL 文章标签： Oracle单行函数

本文链接：https://blog.csdn.net/ZHOU_VIP/article/details/53638142

版权

Orale MySQL 专栏收录该内容

73 篇文章 3 订阅

订阅专栏

一、字符函数

字符函数的功能主要是进行字符串数据的操作

UPPER(字符串 | 列)：将输入的字符串变为大写返回；

LOWER(字符串 | 列)：将输入的字符串变为小写返回；

INITCAP(字符串 | 列)：开头首字母大写；

LENGTH(字符串 | 列)：求出字符串的长度；

REPLACE(字符串 | 列)：进行替换；

SUBSTR(字符串 | 列，开始点 [,结束点])：字符串截取；

字符串截取操作有两种语法：

语法一：SUBSTR(字符串 | 列，开始点)，表示从开始点一直截取到结尾；

    SELECT ename,SUBSTR(ename,3) FROM emp;

语法二：SUBSTR(字符串 | 列，开始点，结束点)，表示从开始点截取到结束点，截取部分内容；

    SELECT ename,SUBSTR(ename,0,3) FROM emp;
    SELECT ename,SUBSTR(ename,1,3) FROM emp;

范例：要求截取每个雇员姓名的后三个字母

正常思路：通过长度-2确定开始点

    SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;

新思路：设置负数，表示从后指定截取位置；

    SELECT ename,SUBSTR(ename,-3) FROM emp;

面试题：请问SUBSTR()函数截取的时候下标从0还是从1开始？

在Oracle数据库之中，SUBSTR()函数从0或1开始都是一样的；
SUBSTR()也可以设置为负数，表示由后指定截取的开始点；

二、数字函数

数字函数一共有三个：

ROUND(数字 | 列 [,保留小数的位数])：四舍五入的操作；
TRUNC(数字 | 列 [,保留小数的位数])：舍弃指定位置的内容；
MOD(数字 1，数字2)：取模，取余数；

范例：验证ROUND()函数

SELECT ROUND(903.53567),ROUND(-903.53567), ROUND(903.53567,2), ROUND(-90353567,-1) FROM dual;

ROUND(903.53567) ROUND(-903.53567) ROUND(903.53567,2) ROUND(-90353567,-1)
---------------- ----------------- ------------------ -------------------
             904              -904             903.54           -90353570

范例：验证TRUNC()函数

SELECT TRUNC(903.53567),TRUNC(-903.53567), TRUNC(903.53567,2), TRUNC(-90353567,-1) FROM dual;

TRUNC(903.53567) TRUNC(-903.53567) TRUNC(903.53567,2) TRUNC(-90353567,-1)
---------------- ----------------- ------------------ -------------------
             903              -903             903.53           -90353560

范例：取模操作

SELECT MOD(10,3) FROM dual;

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

以上的三个主要的数学函数，在学习Java中也会有相匹配的内容。

三、日期函数

如果现在要想进行日期的操作，则首先有一个必须要解决的问题，就是如何取得当前的日期，这个当前日期可以使用“SYSDATE”取得，代码如下：

SELECT SYSDATE FROM dual;

除了以上的当前日期之外，在日期中也可以进行若干计算：

日期 + 数字 = 日期，表示若干天之后的日期；

    SELECT SYSDATE + 3,SYSDATE + 300 FROM dual;

日期 – 数字 = 日期，表示若干天前的日期；

    SELECT SYSDATE - 3,SYSDATE - 300 FROM dual;

日期 – 日期 = 数字，表示的是两个日期间的天数，但是肯定是大日期 – 小日期；

范例：求出每个雇员到今天为止的雇佣天数

SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

而且很多的编程语言之中，也都会提出一种概念，日期可以通过数字表示出来。

除了以上的三个公式之外，也提供了如下的四个操作函数：

LAST_DAY(日期)：求出指定日期的最后一天；

范例：求出本月的最后一天日期

SELECT LAST_DAY(SYSDATE) FROM dual;

NEXT_DAY(日期，星期数)：求出下一个指定星期X的日期；

范例：求出下一个周一

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

ADD_MONTHS(日期，数字)：求出若干月之后的日期；

范例：求出四个月后的日期

SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间所经历的月份；

范例：求出每个雇员到今天为止的雇佣月份

SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp;

在所有的开发之中，如果是日期的操作，建议使用以上的函数，因为这些函数可以避免闰年的问题。

四、转换函数

现在已经接触到了Oracle数据库之中的三种数据：数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），转换函数的主要功能是完成这几种数据间的互相转换操作，一共有三种转换函数：

TO_CHAR(字符串 | 列，格式字符串)：将日期或者是数字变为字符串显示；
TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；
TO_NUMBER(字符串)：将字符串变为数字显示；

a、TO_CHAR()函数

在之前查询过当前的系统日期时间：

SELECT SYSDATE FROM dual;

这个时候是按照“日-月-年”的格式显示，很明显这种显示格式不符合正常的思路，正常是“年-月-日”，所以这种情况下可以使用TO_CHAR()函数，但是使用此函数的话需要一些格式字符串：年（yyyy），月（mm），日（dd）。

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year, TO_CHAR(SYSDATE,'mm') month, TO_CHAR(SYSDATE,'dd') day FROM dual;

TO_CHAR(SY YEAR MO DA
---------- ---- -- --
2012-08-12 2012 08 12

但是这个时候的显示数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加入一个“fm”。

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM dual;

DAY
----------
2012-8-12

正常人都加0，所以这个标记知道就行了，可是在Oracle之中，DATE里面是包含了时间的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记：

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual;

DAY
-------------------
2012-8-12 16:13:38

一定要注意，使用TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR()函数也可以用于数字的格式化上，这个时候每一个“9”表示一位数字的概念，而不是数字9的概念。

SELECT TO_CHAR(89078907890,'L999,999,999,999,999') FROM dual;

TO_CHAR(89078907890,'L999,999,
------------------------------
              ￥89,078,907,890

其中的字母“L”，表示的是“Local”的含义，即：当前的所在的语言环境下的货币符号。

b、TO_DATE()函数

此函数的主要功能是将一个字符串变为DATE型数据。

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

TO_DATE('1989-
--------------
12-9月 -89

一般此函数在更新数据库的时候使用较多；

c、TO_NUMBER()函数：基本不用

TO_NUMBER()函数一看就知道是将字符串变数字的：

SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;

但是在Oracle之中真的很智能，所以以上的功能不使用TO_NUMBER()也可完成：

SELECT '1' + '2' FROM dual;

SQL> SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;

TO_NUMBER('1')+TO_NUMBER('2')
-----------------------------
                            3

SQL> SELECT '1' + '2' FROM dual;

   '1'+'2'
----------
         3

所以现在的TO_NUMBER()函数基本上已经是不考虑了，重点的函数在TO_CHAR()上，其次是TO_DATE()函数。

五、通用函数

通用函数主要有两个：NVL()、DECODE()，这两个函数算是Oracle自己的特色函数了；

a、NVL()函数，处理null

范例：要求查询出每个雇员的全部年薪

SELECT ename,sal,comm,(sal+comm)*12 FROM emp;

SQL> SELECT ename,sal,comm,(sal+comm)*12 FROM emp;

ENAME             SAL       COMM (SAL+COMM)*12
---------- ---------- ---------- -------------
SMITH             800
ALLEN            1600        300         22800
WARD             1250        500         21000
JONES            2975
MARTIN           1250       1400         31800
BLAKE            2850
CLARK            2450
SCOTT             800
KING             5000
TURNER           1500          0         18000
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

已选择14行。

这个时候有的雇员的年薪就变成了null，而造成这种问题的关键是在于comm字段上为null，那么要想解决这个问题，就必须做一种处理：将null变为0，而这个就是NVL()函数的作用。

SELECT ename,sal,comm,(sal+NVL(comm,0))*12,NVL(comm,0) FROM emp;

SQL> SELECT ename,sal,comm,(sal+NVL(comm,0))*12,NVL(comm,0) FROM emp;

ENAME             SAL       COMM (SAL+NVL(COMM,0))*12 NVL(COMM,0)
---------- ---------- ---------- -------------------- -----------
SMITH             800                            9600           0
ALLEN            1600        300                22800         300
WARD             1250        500                21000         500
JONES            2975                           35700           0
MARTIN           1250       1400                31800        1400
BLAKE            2850                           34200           0
CLARK            2450                           29400           0
SCOTT             800                            9600           0
KING             5000                           60000           0
TURNER           1500          0                18000           0
ADAMS            1100                           13200           0
JAMES             950                           11400           0
FORD             3000                           36000           0
MILLER           1300                           15600           0

已选择14行。

b、DECODE()函数：多数值判断

DECODE()函数非常类似于程序中的if…else…语句，唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。

例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示：

CLERK：办事员；
SALESMAN：销售；
MANAGER：经理；
ANALYST：分析员；
PRESIDENT：总裁；

这种判断肯定是逐行进行判断，所以这个时候就必须采用DECODE(),而此函数的语法如下：

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

范例：实现显示的操作功能

SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')
FROM emp;

SQL> SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁')
     FROM emp;

     EMPNO ENAME      JOB       DECODE(J
---------- ---------- --------- --------
      7369 SMITH      CLERK     办事员
      7499 ALLEN      SALESMAN  销售人员
      7521 WARD       SALESMAN  销售人员
      7566 JONES      MANAGER   经理
      7654 MARTIN     SALESMAN  销售人员
      7698 BLAKE      MANAGER   经理
      7782 CLARK      MANAGER   经理
      7788 SCOTT      CLERK     办事员
      7839 KING       PRESIDENT 总裁
      7844 TURNER     SALESMAN  销售人员
      7876 ADAMS      CLERK     办事员
      7900 JAMES      CLERK     办事员
      7902 FORD       ANALYST   分析员
      7934 MILLER     CLERK     办事员

已选择14行。

DECODE()函数是整个Oracle之中最具特点的函数，一定要将其掌握。