函数就是和 Java 语言之中的方法的功能是一样的,都是为了完成某些特定操作的功能支持,而在 Oracle 数据库里面也包含了大量的单行函数,这些函数掌握了以后,可以方便的帮助进行数据库的相关开发。
对开发者而言,最为重要的就是 SQL 语法和单行函数,可是 Oracle 中的单行函数的数量是非常多的。本章只讲解使用,后面会讲解如何去开发用户自己的函数(PL/SQL 编程)
funcation_name(列|表达式[,参数1,参数2,])
函数名称(列 | 表达式 | 数值),而且在Oracle 书中只讲解基本的一些单行函数。
NO. | 函数名称 | 描述 |
---|---|---|
1 | UPPER(列|字符串) | 将字符串的内容全部转大写 |
2 | LOWER(列|字符串) | 将字符串的内容全部转小写 |
3 | INITCAP(列|字符串) | 将字符串的开头首字母大写 |
4 | REPLACE(列|字符串,新的字符串) | 使用新的字符串替换旧的字符串 |
5 | LENGTH(列|字符串) | 求出字符串长度 |
6 | SUBSTR(列|字符串,开始点[,长度]) | 字符串截取 |
7 | ASCII(字符) | 返回与指定字符对应的十进制数字 |
8 | CHR(数字) | 给出一个整数,并返回与之对应的字符 |
9 | RPAD(列|字符串,长度,填充字符)LPAD(列|字符串,长度,填充字符) | 在左或右填充指定长度字符串 |
10 | LTRIM(字符串) , RTRIM(字符串) | 去掉左或右空格 |
11 | TRIM(列|字符串) | 去掉左右空格 |
12 | INSTR(列|字符串,要查找的字符串,开始位置,出现位置) | 查找一个字符串是否在指定位置上出现 |
scott 用户下:
SELECT UPPER('li xing hua')
FORM emp;
范例:验证 UPPER() 和 LOWER() 函数
SELECT UPPER('li xing hua'),LOWER('MLDN')
FORM dual;
范例:现在查询出雇员姓名是“smith”的完整信息,但是由于失误,没有考虑到大小写问题,此时可以使用UPPER() 函数将全部内容变为大写。
SELECT *
FORM emp
WHERE ename = UPPER('smith');
范例:查询所有雇员姓名,要求每个雇员的姓名以首字母大写的形式出现
SELECT ename 原始姓名,INITCAP(ename) 姓名开头首字母大写
FORM emp;
范例:查询所有雇员信息,要求将雇员姓名中所有的字母“A”替换成“_”
SELECT ename,REPLACE(ename,'A','_')
FORM emp;
范例:查出姓名长度是5的所有雇员信息
SELECT *
FORM emp
WHERE LENGTH(ename) = 5;
范例:查询出雇员姓名前三个字母是“JAM”的雇员信息
SELECT *
FORM emp
WHERE SUBSTR(ename,1,3) = 'JAM';
或者:
SELECT *
FORM emp
WHERE SUBSTR(ename,0,3) = 'JAM';
范例: 查询所有10部门雇员姓名,但不显示每个雇员姓名的前三个字母
SELECT ename 原姓名,SUBSTR(ename,3) 截取之后的姓名
FORM emp
WHERE deptno = 10;
范例:显示每个雇员姓名及姓名的后三个字母
范例:返回指定字符的 ASCII 码
SELECT ASCII('A'),ASCII('L')
FORM dual;
范例:验证 CHR() 函数,将ASCII 码变回字符
SELECT CHR(100) FORM dual;
范例:去掉字符串左边空格 - LTRIM() ,去掉右边空格 - RTRIM()
SELECT ' MLDN LiXingHua ' 原始字符串,LTRIM(' ') 去掉左空格
FORM dual;
SELECT ' MLDN LiXingHua ' 原始字符串,RTRIM(' ') 去掉右空格
FORM dual;
范例:去掉左右空格
SELECT ' MLDN LiXingHua ' 原始字符串,TRIM(' ') 去掉左右空格
FORM dual;
范例:字符串左填充 - LPAD() ,字符串右填充 - RPAD()
SELECT LPAD('MLDN',10,'*') LPAD函数使用,RPAD('MLDN',10,'*') RPAD函数使用,
LPAD(RPAD('MLDN',10,'*'),16,'*') 组合使用
FORM dual;
范例:字符串查找 - INSTR()
SELECT
INSTR('MLDN Java','MLDN') 查找得到,
INSTR('MLDN Java','Java') 查找得到,
INSTR('MLDN Java','JAVA') 查找不到
FORM dual;
No. | 函数名称 | 描述 |
---|---|---|
1 | ROUND(数字[,保留小数]) | 对小数进行四舍五入,可以指定保留位数,如果不指定,则表示将小数点之后的数字全部进行四舍五入 |
2 | TRUNC(数字[,截取位数]) | 保留指定位数的小数,如果不指定,则表示不保留小数 |
3 | MOD(数字,数字) | 取模 |
范例:验证 ROUND() 函数的使用
SELECT
ROUND(789.652) 不保留小数,
ROUND(789.652,2) 不保留小数,
ROUND(789.652,-1) 不保留小数,
FROM dual;
范例:列出每个雇员的一些基本信息和日工资情况
SELECT empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金
FROM emp;
范例:验证 TRUNC() 函数的使用
SELECT
TRUNC(789.652) 截取小数,
TRUNC(789.652,2) 截取2位小数,
TRUNC(789.652,-2) 取整
FROM dual;
范例:MOD() 函数验证,模就是取余
SELECT MOD(10,3) FROM dual;
SELECT empno,ename,SYSDATE FROM emp;
SELECT SYSDATE FROM dual;
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
SELECT SYSDATE FROM dual;
SELECT
SYSDATE+3 三天之后的日期,
SYSDATE-3 三天之前的日期
FROM dual;
范例:查询出每个雇员的到今天为止的雇佣天数,以及十天前每天雇员的雇佣天数
SELECT empno 雇员编号, ename 雇员姓名,
SYSDATE-hiredate 雇佣天数,
(SYSDATE-10)-hiredate 10天前雇佣天数
FROM emp;
SELECT empno 雇员编号, ename 雇员姓名,
TRUNC(SYSDATE-hiredate) 雇佣天数,
TRUNC((SYSDATE-10)-hiredate) 10天前雇佣天数
FROM emp;
No. | 函数名称 | 描述 |
---|---|---|
1 | ADD_MONTHS(日期,数字) | 在指定的日期上加入指定的月数,求出新的日期 |
2 | MONTHS_BETWEEN(日期1,日期2) | 求出两个日期间的雇佣月数 |
3 | NEXT_DAY(日期,星期数) | 求出下一个星期几的具体日期 |
4 | LAST_DAY(日期) | 求出指定日期所在月的最后一天日期 |
5 | EXTRACT(格式 FROM 数据) | 日期时间分隔,或计算给定两个日期的间隔 |
范例:验证 ADD_MONTHS() 函数
SELECT SYSDATE,
ADD_MONTHS(SYSDATE,3) 三个月之后的日期,
ADD_MONTHS(SYSDATE,-3) 三个月之前的日期,
ADD_MONTHS(SYSDATE,60) 六十个月之后的日期,
FROM dual;
范例:要求显示所有雇员在被雇佣三个月之后的日期
SELECT empno,ename,job,sal,hiredate,ADD_MONTHS(hiredate,3)
FROM emp;
范例:验证 NEXT_DAY() 函数
SELECT
SYSDATE,
NEXT_DAY(SYSDATE,'星期日') 下一个星期日,
NEXT_DAY(SYSDATE,'星期一') 下一个星期一
FROM dual;
范例:LAST_DAY() 函数验证,求当月的最后一天
SELECT SYSDATE,
LAST_DAY(SYSDATE)
FROM dual;
范例:查询所有是在其雇佣所在月的倒数第三天被公司雇佣的完整雇佣信息
SELECT empno,ename,job,hiredate,LAST_DAY(hiredate)
FROM emp
WHERE LAST_DAY(hiredate)-2=hiredate;
范例:MONTHS_BETWEEN() 函数的验证:查询出每个雇员的编号、姓名、雇员佣日期,雇佣的月数及年份
SELECT
empno 雇员编号, ename 雇员姓名, hiredate 雇佣日期,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) 雇佣总月数,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 雇佣总年数
FROM emp;
范例:查询出每个雇员的编号、姓名、雇佣日期、已雇佣的年数、月数、天数
SELECT
empno 雇员编号, ename 雇员姓名, hiredate 雇佣日期,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 雇佣总年数
FROM emp;
SELECT
empno 雇员编号, ename 雇员姓名, hiredate 雇佣日期,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 已雇佣年数,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) 已雇佣月数
FROM emp;
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;
范例:EXTRACT() 函数
EXTRACT
([YEAR | MONTH | DAY | HOUR | MINUTE | SECOND]
| [TIMEZONE_HOUR | TIMEZONE_MINUTE]
| [TIMEZONE_REGION | TIMEZONE_ABBR]
FROM
[日期(date_value) | 时间间隔(interval_value)]);
范例:从日期时间之中取出年、月、日数据
SELECT
EXTRACT(YEAR FROM DATE '2001-09-19') years,
EXTRACT(MONTH FROM DATE '2001-09-19') months,
EXTRACT(DAY FROM DATE '2001-09-19') days
FROM dual;
SELECT SYSDATE,SYSTIMESTAMP
FROM dual;
范例:从时间戳之中取出年、月、日、时、分、秒
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;
范例:取得两个日期之间的间隔
SELECT
EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
- TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days
FROM dual;
范例:取得两个日期时间之间间隔的天、时、分、秒
SELECT
EXTRACT(DAY FROM datetime_one - datetime_two) days,
EXTRACT(HOUR FROM datetime_one - datetime_two) hours,
EXTRACT(MINUTE FROM datetime_one - datetime_two) minutes,
EXTRACT(SECOND FROM datetime_one - datetime_two) seconds
FROM (
SELECT TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss') datetime_one,
TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss') datetime_two
FROM dual);
SELECT
EXTRACT(DAY FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
- TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) days,
EXTRACT(HOUR FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
- TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) hours,
EXTRACT(MINUTE FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
- TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) minutes,
EXTRACT(SECOND FROM TO_TIMESTAMP('1982-08-13 12:17:57','yyyy-mm-dd hh24:mi:ss')
- TO_TIMESTAMP('1981-09-27 09:08:33','yyyy-mm-dd hh24:mi:ss')) seconds
FROM dual;
No. | 函数名称 | 描述 |
---|---|---|
1 | TO_CHAR(日期|数字|列, 转换格式) | 将指定的数据按照指定的格式变为字符串型 |
2 | TO_DATE(字符串|列, 转换格式) | 将指定的字符串按照指定的格式变为DATE型 |
3 | TO_NUMBER(字符串|列) | 将指定的数据类型变为数字型 |
日期格式化标记:
No. | 转换格式 | 描述 |
---|---|---|
1 | YYYY | 完整的年份数字表示,年有四位,所以使用4个Y |
2 | Y,YYY | 带逗号的年 |
3 | YYY | 年的后三位 |
4 | YY | 年的后两位 |
5 | Y | 年的最后一位 |
6 | YEAR | 年份的文字表示,直接表示四位的年 |
7 | MONTH | 月份的文字表示,直接表示两位的月 |
8 | MM | 用两位数字来表示月份,月有两位,使用两个M |
9 | DAY | 天数的文字表示 |
10 | DDD | 表示一年里的天数(001~366) |
11 | DD | 表示一月里的天数(01~31) |
12 | D | 表示一周里的天数(1~7) |
13 | DY | 用文字表示星期几 |
14 | WW | 表示一年里的周数 |
15 | W | 表示一月里的周数 |
16 | HH | 表示12小时制,小时是两位数字,使用两个H |
17 | HH24 | 表示24小时制 |
18 | MI | 表示分钟 |
19 | SS | 表示秒,秒是两位数字,使用两个S |
20 | SSSSS | 午夜之后的秒数字表示(0~86399) |
21 | AM|PM (A.M | P.M) | 表示上午或下午 |
22 | FM | 去掉查询后的前导0,该标记用于时间模板的后缀 |
范例:格式化日期时间
SELECT SYSDATE 当前系统时间,
TO_CHAR(SYSDATE,'YYYY-MM-DD') 格式化日期,
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') 格式化日期时间,
TO_CHAR(SYSDATE,'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的时间
FROM dual;
范例:使用其他方式格式化年、月、日
SELECT SYSDATE 当前系统时间,
TO_CHAR(SYSDATE,'YEAR-MONTH-DAY') 格式化日期
FROM dual;
或者:
SELECT SYSDATE 当前系统时间,
TO_CHAR(SYSDATE,'YEAR-MONTH-DY') 格式化日期
FROM dual;
范例:查询出所有在每年2月份雇佣的雇员信息
SELECT *
FROM emp
WHERE TO_CHAR(hiredate,'MM') = 2;
或者:
SELECT *
FROM emp
WHERE TO_CHAR(hiredate,'MM') = '02';
范例:将每个雇员的雇佣日期进行格式化显示,要求所有的雇佣日期可以按照“年-月-日”的形式显示,也可将雇佣的年、月、日拆开分别显示
SELECT empno,ename,job,hiredate,
TO_CHAR(hiredate,'YYYY-MM-DD') 格式化雇佣日期,
TO_CHAR(hiredate,'YYYY') 年,
TO_CHAR(hiredate,'MM') 月,
TO_CHAR(hiredate,'DD') 日
FROM emp;
数字格式化标记
No. | 转换格式 | 描述 |
---|---|---|
1 | 9 | 表示一位数字 |
2 | 0 | 表示前导0 |
3 | $ | 将货币的符号信息显示为美元符号 |
4 | L | 根据语言环境不同,自动选择货币符号 |
5 | . | 显示小数点 |
6 | , | 显示千位符 |
范例:格式化数字显示
SELECT
TO_CHAR(987654321.789,'999,999,999,999.9999') 格式化数字,
TO_CHAR(987654321.789,'000,000,000,000.0000') 格式化数字
FROM dual;
SELECT
TO_CHAR(987654321.789,'L999,999,999,999.9999') 显示货币,
TO_CHAR(987654321.789,'$999,999,999,999.9999') 显示美元
FROM dual;
范例:转换时间显示
SELECT
TO_DATE('1979-09-19','YYYY-MM-DD')
FROM dual;
范例:时间戳转换
SELECT
TO_TIMESTAMP('1981-09-27 18:07:10','YYYY-MM-DD HH24:MI:SS') datetime
FROM dual;
范例:将字符串变为数字
SELECT
TO_NUMBER('09') + TO_NUMBER('19') 加法操作,
TO_NUMBER('09') * TO_NUMBER('19') 乘法操作
FROM dual;
SELECT '09' + '19' 加法操作,
'09' * '19' 乘法操作
FROM dual;
No. | 函数名称 | 描述 |
---|---|---|
1 | NVL(数字|列 , 默认值) | 如果显示的数字是null的话,则使用默认数值表示 |
2 | NVL2(数字|列,返回结果一(不为空显示),返回结果二(为空显示)) | 判断指定的列是否是null,如果不为null则返回结果一,为空则返回结果二 |
3 | NULLIF(表达式一,表达式二) | 比较表达式一和表达式二的结果是否相等,如果相等返回NULL,如果不相等返回表达式一 |
4 | DECODE(列|值,判断值1,显示结果1,判断值2,显示结果2,...,默认值) | 多值判断,如果某一个列(或一个值)与判断值相同,则使用指定的显示结果输出,如果没有满足条件,在显示默认值 |
5 | CASE 列|数值 WHEN 表达式1 THEN 显示结果1 ... ELSE 表达式N ... END | 用于实现多条件判断,在WHEN之后编写条件,而在THEN之后编写条件满足的显示操作,如果都不满足则使用ELSE 中的表达式处理 |
6 | COALESCE(表达式1,表达式2,...表达式n) | 将表达式逐个判断,如果表达式1的内容是null,则显示表达式2,如果表达式2的内容是null,则显示表达式3,依次类推,如果表达式n的结果还是null,则返回null |
范例:查询出每个雇员的编号、姓名、职位、雇佣日期、年薪
SELECT empno,ename,job,hiredate,(sal+comm)*12 年薪,sal,comm
FROM emp;
范例:验证 NVL()
SELECT NVL(null,0),NVL(3,0)
FROM dual;
SELECT empno,ename,job,hiredate,(sal+NVL(comm,0))*12 年薪,sal,comm
FROM emp;
范例:查询每个雇员的编号、姓名、年薪(sal + comm)、基本工资、奖金
SELECT empno,ename,job,hiredate,NVL2(comm,sal+comm,sal),sal,comm
FROM emp;
范例:验证 NULLIF() 函数
SELECT NULLIF(1,1),NULLIF(1,2)
FROM dual;
范例:查询雇员编号、姓名、职位,比较姓名和职位的长度
SELECT empno,ename,job,LENGTH(ename),LENGTH(job),NULLIF(LENGTH(ename),LENGTH(job)) nullif
FROM emp;
范例:测试DECODE() 函数
SELECT
DECODE(2,1,'内容为一',2,'内容为二'),
DECODE(2,1,'内容为一','没有条件满足')
FROM dual;
范例:现在雇员表中的工作有以下几种:CLERK:业务员, SALESMAN:销售人员, MANAGER:经理, ANALYST:分析员, PRESIDENT:总裁 ,要求查询雇员的姓名、职位、基本工资等信息,但是要求将所有的职位信息都替换为中文显示。
SELECT ename,sal,
DECODE(job,
'CLERK','业务员',
'SALESMAN','销售人员',
'MANAGER','经理',
'ANALYST','分析员',
'PRESIDENT','总裁') job
FROM emp;
范例:显示每个雇员的工资、姓名、职位,同时显示新的工资(新的工资标准:办事员增长10%,销售人员增长20%,经理增长30%,其他职位的人增长50%)
SELECT ename,sal,
CASE job WHEN 'CLERK' THEN sal * 1.1
WHEN 'SALESMAN' THEN sal * 1.2
WHEN 'MANAGER' THEN sal * 1.3
ELSE sal * 1.5
END 新工资
FROM emp;
范例:验证 COALESCE() 函数
SELECT ename,sal,comm,COALESCE(comm,100,2000),
COALESCE(comm,null,null)
FROM emp;
说明:本学习资料是根据李兴华的Oracle开发实战经典整理