Oracle 基础学习（三） 单行函数

数据库提供一系列的支持函数对数据进行处理。

 

函数基本使用结构：

返回值 函数名称（列|数据）

 

单行函数分为以下几种：字符串函数、数值函数、日期函数、转换函数、通用函数

 

1、 字符串函数

l  UPPER():转大写函数

UPPER(列|字符串)

 

l  LOWER()：转小写函数

LOWER(列|字符串)

验证字符串函数必须保证编写的是完整的SQL函数,为了方便验证，往往会使用一张虚拟表：dual表

例：

SELECT LOWER(‘Hello’)，UPPER(‘Hello’) FROM dual;

用户进行数据输入不会去考虑大小写，为了保证数据可以正常查询，往往需要对数据进行处理

例：

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

输入inputname的值：smith

 SELECT * FROM emp WHERE ename=UPPER(‘&inputname’); --原值

 SELECT * FROM emp WHERE ename=UPPER(‘smith’); --新值

 

l  INITCAP()：首字母大写 

INITCAP(列|数据)

例：将姓名首字母大写

SELECT INITCAP(ename) FROM emp;

 

l  REPLACE():字符串替换 

REPLACE(列|数据，要查找内容，新的内容)

例：

SELECT REPLACE(ename,UPPER 'a', '_') FROM emp; --将姓名中的字母A替换为“_”

SELECT REPLACE('hello world nihao zaijian', '  ' , '') FROM dual; --利用REPLACE()函数可以消除字符串中的空格数据

 

l  LENGTH()：计算字符串长度

LENGTH(列|字符串数据)

例：

SELECT ename,LENGTH(ename) FROM emp; --查询姓名及姓名长度

SELECT * FROM emp WHERE LENGTH(ename)=5; --查询姓名长度为5的雇员信息：

 

l  SUBSTR():字符串截取

SUBSTR(列|数据，开始点)  --从指定的开始点一直截取到结尾；

SUBSTR(列|数据，开始点，长度)  --截取指定范围的字符串

例：helloworldnihao

SELECT SUBSTR(‘helloworldnihao’,11) FROM dual;

SELECT SUBSTR(‘helloworldnihao’,6,5) FROM dual;

截取每位雇员姓名的前三位字符

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

截取每位雇员姓名的后三位字符

两种做法：

• 传统做法：确认截取的开始点，用LENGTH()来计算长度

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

• 只有Oracle支持负数索引：

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

 

另：对于SUBSTR()函数，下标从1开始，即使设置的是0，也会按照1来处理

 

2、 数值函数

l  ROUND():四舍五入函数

ROUND(列|数字，[，保留小数位]) --如果不设置保留小数位表示不保留

例：

SELECT

ROUND(78915.67),  --78916，小数点之后的内容直接进行四舍五入

ROUND(78915.67823823，2), --78915.68，保留两位小数

ROUND(78915.67823823，-2), --78900，把不足5的数据取消了

ROUND(78985.67823823，-2), --79000，如果超过了5则进行进位

ROUND(-15.35)， -- -15

ROUND(-15.65)， -- -16 

FROM dual;

 

l  TRUNC():截取小数，所有的小数都不进位

TRUNC(列|数字[,小数位])

例：

SELECT

TRUNC (78915.67),  --78915

TRUNC (78915.67823823，2), --78915.67

TRUNC (78915.67823823，-2), --78900

TRUNC (78985.67823823，-2), --78900

TRUNC (-15.65)， -- -15

FROM dual;

 

l  MOD():求模（求余数）

MOD(列1|数字1，列2|数字2)

例：

SELECT MOD(10，3) FROM dual;  --商3余1，所以模就是1

 

3、日期函数（Oracle特色）

Oracle中专门提供一个数据伪列SYSDATE，指不存在于表中却可以像表的列一样进行查询的列。

例：

SELECT ename,hiredate,SYSDATE FROM emp;

对于日期时间提供三种计算模式：

• 日期 + 数字 = 日期（若干天之后的日期）

例：

SELECT SYSDATE+10,SYSDATE+120 FROM dual;

• 日期 - 数字 = 日期（若干天之前的日期）
• 日期 – 日期 = 数字（两个日期间的天数）

例：计算每位雇员到今天为止的雇佣天数

SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

 

利用日期处理函数避免闰年闰月的问题

 

l  MONTHS_BETWEEN():计算两个日期间的月数总和，

MONTHS_BETWEEN(日期1，日期2)

例：

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

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

 

l  ADD_MONTHS():增加若干月之后的日期,

ADD_MONTHS(日期，月数)

例：

SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

利用这种方式增加的月可以避免掉闰年闰月的问题

例：计算所有还差1年满35年雇佣日期的全部雇员

SELECT * FROM emp WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12)=34;

 

l  LAST_DAY()：计算指定日期所在月的最后一天，

LAST_DAY(日期)

例：查询出所有在雇佣所在月倒数第二天被雇佣的雇员信息

SELECT ename,hiredate,LAST_DAY(hiredate),LAST_DAY(hiredate)-2

FROM emp

WHERE LAST_DAY(hiredate)-2=hiredate;

 

l  NEXT_DAY():计算下一个指定的星期，

NEXT_DAY(日期，一周时间数)

例：计算下个周二

SELECT NEXT_DAY(SYSDATE,‘星期二’) FROM dual;

 

综合分析：要求查询出雇员的编号、姓名、雇用日期，以及每一位雇员到今天为止被雇佣的年月日数

SELECT

empno,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;

 

4、转换函数

l  TO_CHAR():将日期或数字格式化为指定结构的字符串，

TO_CHAR(列|日期|数字，转换格式)

 

转换格式的标记：

• 日期：年（yyyy）、月(mm)、日(dd);
• 时间：时(hh、hh24)、分(mi)、秒(ss);
• 数字：任意数字(9)、本地货币符号(L);

例：

SELECT TO_CHAR(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) FROM dual; --格式化日期时间

SELECT TO_CHAR(SYSDATE,’yyyy’),TO_CHAR(SYSDATE,’mm’),TO_CHAR(SYSDATE,’ss’) FROM dual; --实现日期数据的拆分

SELECT TO_CHAR(54354343534，’L999,999,999,999’) FROM dual; --格式化数字

 

l  TO_DATE():按照指定转换格式编写字符串后将其变为日期型数据，

TO_DATE(列|字符串，转换格式)

例：将字符串变为日期

SELECT TO_DATE(‘1889-10-19’,’yyyy-mm-dd’) FROM dual;

 

l  TO_NUMBER():将字符串变为数字，

 TO_NUMBER(列|字符串)

例：

SELECT TO_NUMBER(‘1’) + TO_NUMBER(‘2’) FROM dual;

--与 SELECT ‘1’+ ‘2’ FROM dual;结果相同

 

Oracle中提供了许多自动转换机制，所以针对转换函数而言，只有TO_CHAR()函数比较有实用性

 

5、通用函数（Oracle特色）

l  NVL():处理null

首先观察这样一个查询：计算出每一个雇员的年薪，包括基本工资和佣金

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

发现：所有没有佣金的雇员在进行年收入计算的时候最终结果都是null，实际上在计算之中，如果发现内容为null，如果是数字则应使用0代替，NVL()函数来解决此类问题。

NVL(列|null,为空的默认值)

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

 

l  DECODE():多数值判断

DECODE(列，匹配内容1，显示内容1，匹配内容2，显示内容2，…[，默认值])

例：

SELECT empno,ename,job,DECODE(job,’CLERK’,’办事员’,’SALESMAN’,’销售’,’暂无此信息’) FROM emp;

 

转载于:https://www.cnblogs.com/cathy960703/p/5464488.html