oracle常见函数

虽然各个数据库都是支持SQL语句的，但是每一个数据库也有每一个数据库自己所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的话，除了要会使用SQL之外 ，就是要多学习函数。

单行函数主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数；

一、字符函数

字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：

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

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

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

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

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

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

Oracle之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以在Oracle数据库之中为了用户查询方便，所以专门提供了一个“dual”的虚拟表。

范例：观察转大写的函数

SELECT UPPER('hello') FROM dual;

SQL> SELECT UPPER('hello') FROM dual;

 

UPPER

-----

HELLO

大写转换的用处：在一般的使用之中，用户输入数据的时候去关心数据本身存放的是大写还小写吗？

SELECT * FROM emp WHERE ename='&str';

 

SQL> SELECT * FROM emp WHERE ename='&str';

输入 str 的值:  smith

原值    1: SELECT * FROM emp WHERE ename='&str'

新值    1: SELECT * FROM emp WHERE ename='smith'

 

未选定行

 

SQL> SELECT * FROM emp WHERE ename='&str';

输入 str 的值:  SMITH

原值    1: SELECT * FROM emp WHERE ename='&str'

新值    1: SELECT * FROM emp WHERE ename='SMITH'

 

 

此时如果输入的是小写，则肯定无法查询出数据，所以这个时候不能要求用户这么多，所以这个时候只能由程序自己去适应，加入一个函数：

SELECT * FROM emp WHERE ename=UPPER('&str');

 

SQL> SELECT * FROM emp WHERE ename=UPPER('&str');

输入 str 的值:  smith

原值    1: SELECT * FROM emp WHERE ename=UPPER('&str')

新值    1: SELECT * FROM emp WHERE ename=UPPER('smith')

 

 

当然以上的“&”的操作属于替代变量的内容，此部分内容不做重点。

范例：观察转小写的操作，将所有的雇员姓名按照小写字母返回

SELECT LOWER(ename) FROM emp;

范例：将每一个雇员姓名的开头首字母大写

SELECT INITCAP(ename) FROM emp;

范例：查询出每个雇员姓名的长度

SELECT ename,LENGTH(ename) FROM emp;

范例：要求查询出姓名长度正好是5的雇员信息

SELECT ename,LENGTH(ename) FROM emp

WHERE LENGTH(ename)=5;

范例：使用字母“_”替换掉姓名中的所有字母“A”

SELECT REPLACE(ename,'A','_') FROM emp;

 

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

语法一：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)：取模，取余数；

 

     

以上的三个主要的数学函数，在学习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）。

 

 

 

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

 

 

 

正常人都加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;

 

 

 

 

已选择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;

 

 

 

 

 

已选择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;

 

 

 

已选择14行。

 

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