2019070801 单行函数

  • 系统函数的定义基本格式 :返回数据类型 函数名称(数据 | 列名称),而返回的数据类型基本上只有3类,字符串(STR) ,数值(NUMBER),日期(DATE);
  • 单行函数分为如下几类:字符串函数,数值函数和日期函数(避免闰年问题)、转换函数和通用函数。

(一)字符串函数

字符串函数的主要功能是进行字符串的处理:

  • 转大写函数:字符串 UPPER(数据 | 列);
  • 转小写函数:字符串 LOWER(数据 | 列);
  • 首字母大写:字符串 INITCAP(数据 | 列);
  • 计算字符长度:数字 LENGTH(数据 | 列);
  • 字符串截取:字符串 SUBSTR(数据 | 列,截取点,长度);
  • 字符串替换:字符串 REPLACE(数据 | 列,要替换的数据,替换数据);

范例:从实列表中字符串转换为大写,不方便,一般使用有系统虚拟表 dual;

1、
SELECT DISTINCT UPPER('hello') FROM emp ;
2、
SELECT UPPER('hello') FROM dual ;

范例:查询姓名为SMITH的雇员信息 将用户的输入转为大写 查询

  • &inputname 表示需要用户输入的值

3、
SELECT * FROM emp WHERE ename=&inputname ;
SQL> SELECT * FROM emp WHERE ename=&inputname ;
输入 inputname 的值: smith
原值 1: SELECT * FROM emp WHERE ename=&inputname
新值 1: SELECT * FROM emp WHERE ename=smith
SELECT * FROM emp WHERE ename=smith
*
第 1 行出现错误:
ORA-00904: "SMITH": 标识符无效

SQL> SELECT * FROM emp WHERE ename=&inputname ;
输入 inputname 的值: 'smith'
原值 1: SELECT * FROM emp WHERE ename=&inputname
新值 1: SELECT * FROM emp WHERE ename='smith'
未选定行

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

范例:所有雇员姓名小写显示

4、
SELECT LOWER(ename) FROM emp ;

范例:所有雇员姓名首字母大写显示

5、
SELECT INITCAP(ename) FROM emp ;

范例:字符串替换操作

6、
SELECT REPLACE('helloworld','l','_') FROM dual ;

范例:所有雇员姓名及姓名字符长度

7、
SELECT ename,LENGTH(ename) FROM emp ;

范例:查询姓名长度为5的雇员信息

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

范例:截取字符串

9、
从下标截取到最后
SELECT SUBSTR('helloworld',6) FROM dual ;

索引下标+长度 截取
SELECT SUBSTR('helloworld',6,2) FROM dual ;

  • ORACLE中的下标是用1开始,就是写成0,也是1;

SELECT SUBSTR('helloworld',0,5),SUBSTR('helloworld',1,5) FROM dual ;

  • 负数,是从后往前算

SELECT SUBSTR('helloworld',-5) FROM dual ;

范例:查询所有雇员姓名的前三个字符和后三个字符

10、
SELECT ename,SUBSTR(ename,1,3),SUBSTR(ename,-3) FROM emp ;

(二)字符串函数

范例:ROUND 函数表示四舍五入法; ROUND(数值,小位数)

11、
SELECT ROUND(678.63),ROUND(678.63,1),ROUND(678.63,-2) FROM dual ;
SELECT ROUND(-678.63) FROM dual ;

SQL> SELECT ROUND(678.63),ROUND(678.63,1),ROUND(678.63,-2) FROM dual ;

ROUND(678.63) ROUND(678.63,1) ROUND(678.63,-2)
------------- --------------- ----------------
          679           678.6              700

SQL> SELECT ROUND(-678.63) FROM dual ;

ROUND(-678.63)
--------------
          -679

SQL>

范例:TRUNC函数直接截取,不进行四舍五入;

12、

SELECT TRUNC(678.63),TRUNC(678.63,-2) FROM dual ;

范例:雇员日工资查看,只呈现整数部分,不进行四舍五入;

13、
SELECT ename,job,TRUNC(sal/30) FROM emp ;

14、取余操作
SELECT MOD(10,3) FROM dual ;

(三)日期函数

范例:获取系统日期 +/- 天数

15、
SELECT SYSDATE FROM dual ;
SELECT SYSDATE+120 FROM dual ;
SELECT SYSDATE+12000 FROM dual ;
SELECT SYSDATE-10 FROM dual ;

范例:查询所有雇员入职到今天一共有多少天,取整;

16、
SELECT ename,TRUNC(SYSDATE-hiredate) FROM emp ;

范例:当前日期向前或向后几个月时间: ADD_MONTHS(系统日期,数值)

17、
SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;
SELECT ADD_MONTHS(SYSDATE,-5) FROM dual ;
SELECT ADD_MONTHS(SYSDATE,500) FROM dual ;

MONTHS_BETWEEN日期函数

MONTHS_BETWEEN (date1, date2)
用于计算date1和date2之间有几个月。

如果date1在日历中比date2晚,那么MONTHS_BETWEEN()就返回一个正数。
如果date1在日历中比date2早,那么MONTHS_BETWEEN()就返回一个负数。
如果date1和date2日期一样,那么MONTHS_BETWEEN()就返回一个0。

范例:雇员入职到现在有几个月了

18、
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate),MONTHS_BETWEEN(SYSDATE,hiredate)/12 FROM emp ;

NEXT_DAY函数

NEXT_DAY(date,char) 

date参数为日期型, 

char:为1~7或Monday/Mon~Sunday/ 

指定时间的下一个星期几(由char指定)所在的日期, 

char也可用1~7替代,1表示星期日,2代表星期一等 

还可以是星期一、星期二...、星期日 

范例:指定时间的下一个星期二所在的日期,

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

last_day(某个日期) 返回特定日期所在月份的最后一天

范例:返回当前日期所在月份的最后一天的当前时间

20
SELECT LAST_DAY(SYSDATE) FROM dual ;

范例:查询当入职日期是入职当月最后一天的前两天的雇员信息;

21、
SELECT ename,hiredate,LAST_DAY(hiredate),LAST_DAY(hiredate)-1 FROM emp
WHERE hiredate=LAST_DAY(hiredate)-2 ;

范例:查询当入职日期是入职当月最后一天的前两天的雇员信息;

21、查询员工入职时间以及到当前时间的年,月,日分别是多少;
SELECT ename,hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months ,
TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day
FROM emp ;

TO_CHAR(SYSDATE,'yyyy-mm-dd') 日期函数

范例:时间日期格式化显示

22、
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM dual ;
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual ;

范例:分别显示年月日

23、
SELECT TO_CHAR(SYSDATE,'yyyy') year,TO_CHAR(SYSDATE,'mm') months,TO_CHAR(SYSDATE,'dd') day FROM dual ;

范例:在每年2月份入职的雇员

24、
SELECT * FROM emp WHERE TO_CHAR(hiredate,'mm')='02' ;

  • 按照固定格式显示
    SELECT TO_CHAR(78902345890234,'L999,999,999,999,999,') FROM dual ;
  • 字符串日期转日期,并计算相差天数
    SELECT TO_DATE('2016-09-10','yyyy-mm-dd')-TO_DATE('2016-08-10','yyyy-mm-dd') FROM dual ;
  • 指定时间比较
    SELECT * FROM emp WHERE hiredate<TO_DATE('2016-09-10','yyyy-mm-dd') ;
    SELECT * FROM emp WHERE hiredate<'10-9月-2016' ;

范例:TO_NUMBER函数()是Oracle中常用的类型转换函数之一,主要是将字符串转换为数值型的格式,与TO_CHAR()函数的作用正好相反。

25、
SELECT TO_NUMBER('100')-TO_NUMBER('20') FROM dual ;
SELECT '100'-'20' FROM dual ;

NVL函数 判断某个值是否为空值,若不为空值则输出,若为空值,返回指定值。

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

27、
SELECT ename,job,
DECODE(job,'CLERK','办事员','SALESMAN','销售专员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁','******')
FROM emp ;
【ORACLE】Oracle提高篇之DECODE

推荐阅读更多精彩内容