方便数据库的功能操作。

验证一张真实的表，中间处理的数据量会很大，因此Oracle则提供了一个dual的数据表帮助用户进行验证，即dual为虚拟数据表。

字符函数

1. UPPER() & LOWER()

大写 & 小写

1
SELECT * FROM emp WHERE ename=UPPER('smith');

2. INITCAP()

首字母大写

1
SELECT ename，INITCAP(ename) FROM emp;

3. REPLACE()

替换（要求将雇员姓名中所有的字母’A’替换成字母’_’）

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

4. LENGTH()

长度（姓名长度为5的雇员）

1
SELECT * FROM emp WHERE LENGTH(ename)=5;

5. SUBSTR()

截取操作（查出雇员姓名前三个字母是”JAM”的雇员信息）

1
SELECT * FROM emp WHERE SUBSTR(ename,0,3)='JAM';

注：

SUBSTR()函数有两种形式：

从指定位置截取到结尾：SUBSTR(列名称 | 字符串，截取开始点)；

截取部分的字符串：SUBSTR(列名称 | 字符串，截取开始点，截取个数)

（显示每个雇员姓名以及其姓名的后三个字母）

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

注：

Oracle – java

一个面试题：

请问Oracle中的SUBSTR()函数的下标开始点是从0开始还是从1开始

答：可以设置为0也可以设置为1，即使使用了0，也会将其定义为1

6. ASCII()

返回指定字符的ASCII码

1
SELECT ASCII('A'), ASCII('L') FROM dual;

7. CHR()

将ASCII码变回字符

1
SELECT CHR(100) FROM dual;

8. TRIM()

  • 去掉字符串左边空格 — LTRIM()
1
SELECT '    hello world    ', LTRIM('    hello world    ') FROM dual;
  • 去掉字符串右边空格 — RTRIM()
1
SELECT '    hello world    ', RTRIM('    hello world    ') FROM dual;
  • 去掉字符串左右两边空格 — TRIM()
1
SELECT '    hello world    ', TRIM('    hello world    ') FROM dual;

注：

不管如何取消空格，中间的空格是无法消除掉的。

9. LPAD()、RPAD()

字符串左填充、字符串右填充

1
SELCET LPAD('Yuti',10,'*'),RPAD('Yuti',10,'*'),LPAD(RPAD('Yuti',10,'*'),16,'*') FROM dual;

结果：

‘******Yuti’,

‘Yuti******‘,

‘******Yuti******‘

10.INSTR()

字符串查找

1
2
3
4
SELECT INSTR('Yuti Black','Yuti'),
       INSTR('Yuti Black', 'Black'),
       INSTR('Yuti Black', 'BLACK')
FROM dual;

结果：1, 6, 0

注：如果查找到要找的内容，那么此函数会返回位置，如果找不到，就返回0。

数值函数

1. ROUND()

小数进位

1
2
3
4
SELECT ROUND(789.652) 不保留小数,
       ROUND(789.652,2) 保留两位小数，
       ROUND(789.652, -1) 处理整数进位
FROM dual;

答案： 789； 789.65； 790

运用实例：

1
SELECT empno, ename, job, hiredate, sal, ROUND(sal/30, 2) FROM emp;

2. TRUNC()

小数不进位

1
2
3
4
SELECT TRUNC(789.652) 不保留小数,
       TRUNC(789.652,2) 保留两位小数，
       TRUNC(789.652, -1) 处理整数进位
FROM dual;

答案： 790； 789.65； 780

3. MOD()

取模,就是取余数

1
SELECT MOD(10,3) FROM dual;

日期函数

如果要进行日期操作，那么一定会存在一个前提：必须知道当前日期。

在Oracle中可以直接利用SYSDATE为列取得当前日期时间。

所谓的为列是指：不是表中的列，但是又可以直接使用的列。

1. SYSDATE

取得当前日期

SELECT SYSDATE FROM dual;

​

三个日期操作公式：

  • 若干天前的日期：日期 - 数字；
  • 若干天后的日期：日期 + 数字；
  • 两个日期的天数间隔：日期 - 日期

SELECT SYSDATE + 3 三天之后的日期,
SYSDATE - 3 三天之前的日期
FROM dual;

实例：

（查询出每个雇员到今天为止的雇佣天数、以及十天前每个雇员的雇佣天数）

1
2
3
4
5
SELECT empno 雇员编号,
       ename 雇员名字,
       SYSDATE-hiredate 雇佣天数,
       (SYSDATE-10)-hiredate 十天前雇佣天数
FROM emp;

注：
如果想要计算天数唯一可以使用的公式就是“日期1-日期2”，那么日期1肯定使用SYSDATE取得当前的日期，而日期2就可以取得每一位雇员的雇佣日期。

上面运算有小数点，因此进行如下修改

1
2
3
4
SELECT empno 雇员编号, ename 雇员名字,
 		 TRUNC(SYSDATE-hiredate) 雇佣天数,
		 TRUNC((SYSDATE-10)-hiredate) 十天前雇佣天数 
FROM emp;

以上只是针对于当前日期的操作，而对于Oracle而言也提供相应的日期函数，之所以使用日期函数主要是避免闰年的问题，或者是一个月有28、29、30、31天的问题。通过日期函数的计算取得的日期时间是最准确的。

2. ADD_MONTHS()

ADD_MONTHS(日期，数字)：在指定日期上加上指定的月数，求出新的日期；

例子：

1
2
3
4
SELECT SYSDATE, 
       ADD_MONTHS(SYSDATE, 3) 三个月之后的日期,
       ADD_MONTHS(SYSDATE, -3) 三个月之前的日期
FROM dual;

实际运用：（要求显示所有雇员在被被雇佣三个月之后的日期）

1
SELECT empno, ename, job, sal, hiredate, ADD_MONTHS(hiredate, 3) FROM emp;

3. MONTHS_BETWEEN()

MONTHS_BETWEEN(日期1，日期2)：求出两个日期间的雇佣月数；

4. NEXT_DAY()

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

例子：

1
2
3
SELECT SYSDATE, 当前日期
       NEXT_DAY(SYSDATE, '星期日') 下一个星期日的日期
FROM dual;

5. LAST_DAY()

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

例子：

SELECT SYSDATE, LAST_DAY(SYSDATE) FROM dual;

​
实际运用：（查询所有是在雇佣所在月的倒数第三天被公司雇佣的完整雇员信息）

1
2
3
SELECT empno,ename,job,hiredate,LAST_DAY(hiredate) 
FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;

一个有难度的范例：

题目：查询出每个雇员的编号、姓名、雇佣日期、已雇佣的年数、月数、天数。

1
2
3
4
5
SELECT empno 雇员编号,ename 雇员姓名,hiredate 雇佣日期,
	    TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年数,
	    TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 已雇佣月数,
	    TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) 已雇佣天数
FROM emp;

6. EXTRACT()

EXTRACT(格式 FROM 数据)：日期时间分割，或计算给定两个日期的间隔

例子：分别取出年、月、日、时、分、秒

1
2
3
4
5
6
7
SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) years,
       EXTRACT(MONTH FROM SYSTIMESTAMP) months,
       EXTRACT(DAY FROM SYSTIMESTAMP) days,
       EXTRACT(HOUR FROM SYSTIMESTAMP) hours,
       EXTRACT(MINUTE FROM SYSTIMESTAMP) minutes,
       EXTRACT(SECOND FROM SYSTIMESTAMP) seconds
FROM dual;

转换函数

TO_CHAR()

  • 将数据类型变为字符串（需要两个参数：日期数据、转换格式）

    • 范例：

      SELECT SYSDATE 当前系统时间,
     TO_CHAR(SYSDATE, 'YYYY-MM-DD') 格式化日期,
     TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') 格式化日期时间
  FORM dual;

    • 实例：（查询所有在每年2月份雇佣的雇员信息）

      SELECT * FROM emp WHERE TO_CHAR(hiredate, 'MM')='02';

    • 年月日的拆分

      SELECT empno,ename,job,hiredate,
       TO_CHAR(hiredate,'YYYY') 年,
       TO_CHAR(hiredate,'MM') 月,
       TO_CHAR(hiredate,'DD') 日
FROM emp;

  • 数字格式化（最重要的功能）

    • 范例：

      1
      2
      3
      4
      5
      SELECT TO_CHAR(987654321.789,'999,999,999,999.9999') 格式化数字,
             TO_CHAR(987654321.789,'000,000,000,000.0000') 填零格式化,
             TO_CHAR(987654321.789,'L999,999,999,999.9999') 显示货币,
             TO_CHAR(987654321.789,'$999,999,999,999.9999') 显示美元
      FROM dual;

注：结果如下

987,654,321,7890

000,987,654,321.7890

￥987,654,321,7890

$987,654,321,7890

​

通用函数

Oracle中特有的函数

1. NVL()

处理null数据（如果为null，那么就将其变为0，如果不是null，就继续使用指定的数值）

SELECT NVL(null, 0), NVL(3,0) FROM dual;

具体实例：

1
SELECT empno,ename,job,hiredate,(sal+NVL(comm,0))*12 年薪, sal, comm FROM emp;

2. NVL2()

跟NVL()相比多了一个判断

具体实例：

SELECT empno,ename,job,hiredate,NVL2(comm, sal+comm, sal)*12 年薪, sal, comm FROM emp;

3. NULLIF()

NULLIF(表达式一，表达式二)函数的主要功能判断两个表达式的结果是否相等，如果相等则返回NULL，不相等则返回表达式一。

1
SELECT NULLIF(1,1), NULLIF(1,2) FROM dual;

4. DECODE()

在Oracle中最重要的函数。在JAVA语言中，if…else判断的都是逻辑条件，而在DECODE()中判断的都是数值

例子：

1
SELECT DECODE(2, 1, '内容一', 2, '内容二') FROM dual;  (结果： 内容二)

范例：（要求可以查询雇员的姓名、职位、基本工资等信息，但是要求将所有的职位信息都替换成中文显示）

1
2
3
4
5
6
7
SELECT ename, sal,
	    DECODE(job, 'CLERK', '业务员',
	                'SALESMAN', '销售人员',
	                'MANAGER', '经理',
	                'ANALYST','分析员',
	                'PRESIDENT','总裁') job
FROM dual;