源码-Oracle数据库管理-第十一章-Oracle内置函数-Part 3（数字型函数和日期时间函数）

这部分不是很难理解，在实际工作中用的不多。

--11.3 数字型函数  

--11.3.1 数字型函数列表

--11.3.2 使用ABS和MOD函数
SELECT ABS(12),ABS(-12),ABS(-12.345) FROM dual;

SELECT MOD(3, 2), MOD(10, 5), MOD(0, 2), MOD(3, 0), MOD(-3, 2), MOD(3, -2)
  FROM dual;

--11.3.3 使用CEIL和FLOOR函数
SELECT CEIL(9.5679),  FLOOR(9.5679) FROM dual;
SELECT CEIL(9.01),FLOOR(9.01) FROM dual;
SELECT CEIL(-9.01),FLOOR(-9.01) FROM dual;

--11.3.4 使用ROUND和TRUNC函数
--ROUND函数使用示例
SELECT ROUND(3.1415927, 3) as "3位小数",
       ROUND(3.1415927, 0) as "没有小数",
       ROUND(3141.5927, -2) as "整数舍入",
       ROUND(-3.1415927, 2) as "负数舍入",
       ROUND(3.1415927) as "不指定小数位"
  FROM dual;

--TRUNC函数使用示例
SELECT TRUNC(3.1415927, 3) as "3位小数",
       TRUNC(3.1415927, 0) as "没有小数",
       TRUNC(3141.5927, -2) as "整数舍入",
       TRUNC(-3.1415927, 2) as "负数舍入",
       TRUNC(3.1415927) as "不指定小数位"
  FROM dual;


--11.4 日期时间函数
--test
select last_day(sysdate) from dual;

--1. SYSDATE函数获取当期日期
 SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as "当前时间"
   FROM dual;
--使用“+”操作符添加日期时间
 SELECT SYSDATE,SYSDATE+20 FROM dual;
 
--添加小时数 
 SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as "当前时间",
        TO_CHAR(SYSDATE+5/24, 'YYYY-MM-DD HH24:MI:SS') as "添加5小时"
   FROM dual;
   
--2. Add_months添加月份数

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'YYYY-MM-DD') as "下个月今天"
  FROM dual;
  
  
--3. LAST_DAY返回月份最后一天日期
SELECT SYSDATE "当前日期",
       LAST_DAY(SYSDATE) "本月最后一天",
       LAST_DAY(SYSDATE) - SYSDATE "本月剩余天数"
  FROM DUAL;


--4. MONTH_BETWEEN计算日期月份间隔
SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE - 100) as "小数差异",
       MONTHS_BETWEEN(LAST_DAY(SYSDATE), LAST_DAY(SYSDATE - 100)) as "整数差异"
  FROM dual;

--5. NEXT_DAY计算指定日期的下一个星期几
SELECT NEXT_DAY(SYSDATE, '星期六') as "下个周六" FROM DUAL;


--6. 使用ROUND和TRUNC操作日期
SELECT SYSDATE as "当前日期",
       ROUND(SYSDATE) as "四舍五入",
       TRUNC(SYSDATE) as "截断"
  FROM dual;
  
--按月份进行舍入或截断  
SELECT ROUND(SYSDATE, 'MONTH') as "月份舍入",
       TRUNC(SYSDATE, 'MONTH') as "月份截断"
  FROM DUAL;


--11.4.3 EXTRACT函数截取日期
SELECT EXTRACT(YEAR FROM SYSDATE) || '年' || 
        EXTRACT(MONTH FROM SYSDATE) || '月' ||
        EXTRACT(DAY FROM SYSDATE) || '日' as "当前时间"
  FROM dual;
  
  
--获取时、分、秒信息
SELECT EXTRACT(HOUR FROM LOCALTIMESTAMP) || '小时' ||
       EXTRACT(MINUTE FROM LOCALTIMESTAMP) || '分' ||
       EXTRACT(SECOND FROM LOCALTIMESTAMP) || '秒' as "当前时间"
  from DUAL;