/**函数**/
--大小寫轉換
--LOWER小寫、UPPER大寫、INITCAP首字母大寫
SELECT LOWER('ADMIN'),UPPER('admin'),INITCAP('iris') FROM DUAL;

--字符大小
--VSIZE
SELECT VSIZE('CAT_TOYS') FROM DUAL;--8
SELECT VSIZE(NAME) FROM EMP1;--40,當前字段類型為CHAR(40)，CHAR是不可變字符串類型
SELECT VSIZE(SYSDATE) FROM DUAL;--7,日期都為7
SELECT VSIZE('合理') FROM DUAL;--6

--字符串處理
SELECT
LENGTH(' ADMIN'),--長度
CONCAT('admin',':IRIS'),--合并
SUBSTR('iris',2,2),--截取長度第二個字符開始截取2位
INSTR('IRIS','I'),--I所在的位置
LPAD('IRIS',10,'*'),--使用*在LEFT補充到十位數
RPAD('IRIS',10,'*') FROM DUAL;

--數字處理

SELECT ROUND(45,1),ROUND(45.256,1),ROUND(45.256,-1),--保留数标轴往右的数，参数2是小数位数，-1是十位数
TRUNC(45.256,1),TRUNC(45.256,-1),--保留数标轴往左的数，参数2是小数位数，-1是十位数
MOD(100,3),--求余数
POWER(2,3),--2的3次方
SQRT(9),--9的开方
ABS(-1.11),ABS(1.11),--获取绝对值
SIGN(-1.11),SIGN(1.11)FROM DUAL;--正数返回1，负数返回-1,0返回0

--TRUNC(DBMS_RANDOM.VALUE*900+100)获取100到1000的随机数
SELECT TRUNC((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'mi') FROM DUAL;
SELECT ROUND((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'DD') FROM DUAL;

/**
数字5261按大小排 ，字4102符按首字符比较1653（如果相等则向下比较）
GREATEST(2, 5, 12, 3) WOULD RETURN 12
GREATEST('2', '5', '12', '3') WOULD RETURN '5'
GREATEST('apples', 'oranges', 'bananas') WOULD RETURN 'oranges'
GREATEST('apples', 'applis', 'applas') WOULD RETURN 'applis'
**/
SELECT GREATEST(1,2,3) FROM DUAL;--最大值
SELECT GREATEST(SYSDATE,SYSDATE+2) FROM DUAL;
SELECT LEAST(1,2,3) FROM DUAL;--最小值
SELECT UID FROM DUAL;--当前用户ID
SELECT USER FROM DUAL;--当前用户信息
SELECT USERENV('isdba'),USERENV('sessionid'),USERENV('TERMINAL'),USERENV('LANGUAGE')  FROM DUAL;--用户信息

--DECODE、COALESCE、NVL
----根据条件显示不同的值D_UNAME值为A显示NO1，为B显示NO2...其他显示OTHER
SELECT DECODE(D_UNAME,'A','NO1','B','NO2','C','NO3','D','NO4','NO','OTHER'),D_UID FROM DEPT_USER;
--EG：
SELECT DNAME,DECODE(DNAME,'人事部',MIN_SAR,'技术部',MAX_SAR)AS SAR FROM DEPT WHERE DNAME IN('人事部','技术部');
--SELECT DECODE(条件1,值1,翻译值1,条件N,值N,翻译值N,缺省值) FROM DEPT_USER;--根据条件显示不同的值
SELECT COALESCE(NULL,'NO1','B') FROM DUAL;--显示第一个不为空的值
SELECT NVL(NULL,0),NVL('1',0)FROM DUAL;--D_UPHONE为空，显示0
SELECT NVL2(NULL,'1','2'),NVL2('0','1','2')FROM DUAL;--D_UPHONE为空，返回2，否則返回1

--替换REPLACE和TRANSLATE
SELECT REPLACE('ABC','B','H')FROM DUAL;
SELECT REPLACE('ABC','B','H'),
REPLACE('ABC','BA','H'),
REPLACE('ABC','BC','H'),
TRANSLATE('ABC','B','H'),
TRANSLATE('ABC','BA','H'),
TRANSLATE('ABC','BD','H'),
TRANSLATE('ABC','BC','H')
FROM DUAL;

SELECT REPLACE(UPPER('abc'),'BC','H') FROM DUAL;

--拆分字符串，可使用到IN()裏面,IN里面不可使用字符串拼接',',因此使用此方法是较好的
SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, ROWNUM)
  FROM DUAL
CONNECT BY ROWNUM <=
           LENGTH('1,2,3') - LENGTH(REGEXP_REPLACE('1,2,3', ',', '')) + 1;

/**
聚合函数（不可用在WHERE后）
**/
SELECT AVG(D_SAR) FROM DEPT_USER WHERE D_DID=4; 

/**
數據類型轉換
**/
SELECT TO_NUMBER('123,456.07','999G999D00') FROM DUAL;--G:逗號；D:小數點
SELECT TO_CHAR(123456.07,'$999,999.00') FROM DUAL;--.  ,  $  9  0
SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE,'yyyy/MM/dd hh24:mi:ss'),'yyyy/MM/dd hh24:mi:ss') FROM DUAL;
SELECT TO_TIMESTAMP_TZ(TO_CHAR(SYSDATE,'yyyy/MM/dd hh24:mi:ss'),'yyyy/MM/dd hh24:mi:ss') FROM DUAL;--計算時差
SELECT CAST('11.2' AS NUMBER) FROM DUAL;
SELECT TO_DATE('2019-06-27 12:12:12','yyyy-MM-dd HH24:mi:ss') FROM DUAL;--MM要写成MI，否则出现冲突
SELECT TO_DATE((TO_CHAR((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'yyyy-MM-dd HH24:mi:ss')),'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

/**
CEIL（取大于或等于当前数的整数）
FLOOR（取小于或等于当前数的整数）
ROUND（四舍五入取值）
TRUNC（根据位数直接取值）
FIX(向0方向取整，ORACLE中不支持)
**/
SELECT CEIL(-1.3),CEIL(1.3),
FLOOR(-1.3),FLOOR(1.3),
ROUND(156.456,-1),ROUND(153.556,0),
TRUNC(156.456,-1),TRUNC(153.556)
FROM DUAL;

/**
LTRIM：从左边移除
RTRIM：从右边移除
TRIM( [ LEADING | TRAILING | BOTH  [ TRIM_CHARACTER ]  ]   STRING1 )从头、尾、两边移除
**/
SELECT LTRIM('abshafoiuwh','absh'),
LTRIM('  abshafoiuwh'),
RTRIM('abshafouuwh','uwh'),
RTRIM('abshafouuwh  '),
TRIM(BOTH '1' FROM '123Tech111')
FROM DUAL;
  

/**
--------------------------------時間處理--------------------------------------------------
**/

--日期轉換
SELECT TO_DATE('2019-06-27 12:12:12','yyyy-MM-dd HH24:mi:ss') FROM DUAL;--MM要写成MI，否则出现冲突
SELECT TO_DATE((TO_CHAR((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'yyyy-MM-dd HH24:mi:ss')),'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

--日期函數
SELECT MONTHS_BETWEEN(TO_DATE('2019-06-27 12:12:12','yyyy-MM-dd HH24:mi:ss'),SYSDATE) FROM DUAL;--两个月的差距(前面是大日期後面是小日期為整數，否則為負數)
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;--几个月后的日期信息
SELECT NEXT_DAY((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'WEDNESDAY'),NEXT_DAY((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1),'MONDAY') FROM DUAL;--下个星期几的日期信息
SELECT LAST_DAY((SELECT D_BRITHDATE FROM DEPT_USER WHERE D_UID=1)) FROM DUAL;--当前月最后一天

--獲取相隔的年月信息
SELECT TRUNC((SYSDATE-HIREDATE)/365)||'年',
       TRUNC(MOD((SYSDATE-HIREDATE),365)/30)||'月',
       TRUNC(MOD(MOD((SYSDATE-HIREDATE),365),30))||'日'
       FROM EMP WHERE DEPTNO=10;
      
SELECT * FROM EMP;
--TRUNC的使用
SELECT SYSDATE, --2019/7/8 上午 11:31:20
       TRUNC(SYSDATE),--2019/7/8
       TRUNC(SYSDATE,'DD'),--2019/7/8
       TRUNC(SYSDATE,'day')+7,--2019/7/14
       TRUNC(SYSDATE,'month'),--2019/7/1
       TRUNC(SYSDATE,'mm'),--2019/7/1
       TRUNC(SYSDATE,'year'),--2019/1/1
       TRUNC(SYSDATE,'yyyy'),--2019/1/1
       TRUNC(SYSDATE,'HH24')FROM DUAL;--2019/7/8 上午 11:00:00
       

SELECT 
ROUND(SYSDATE,'year'),
ROUND(SYSDATE,'month'),
ROUND(SYSDATE,'day'),
ROUND(SYSDATE,'y'),
ROUND(SYSDATE,'mm'),
ROUND(SYSDATE,'dd'),
ROUND(SYSDATE,'HH24'),
ROUND(SYSDATE,'mi')
FROM DUAL;

SELECT 
TRUNC(SYSDATE,'year'),
TRUNC(SYSDATE,'month'),
TRUNC(SYSDATE,'day')
FROM DUAL;

--日期优化
SELECT D.CHECKIN_DATE AS 登记时间,
ADD_MONTHS(D.CHECKIN_DATE,-5) AS 减五个月，
D.CHECKIN_DATE-5 AS 减五天，
D.CHECKIN_DATE-5/24 AS 减五小时，
D.CHECKIN_DATE-5/24/60 AS 减五分钟，
D.CHECKIN_DATE-5/24/60/60 AS 减五秒
 FROM DEPT_USER D;
 
 --时间间隔
 SELECT MAD-MID 间隔天数,
 MONTHS_BETWEEN(MAD,MID)/12 间隔年,
 MONTHS_BETWEEN(MAD,MID)间隔月,
 (MAD-MID)*24 间隔时,
 (MAD-MID)*24*60 间隔分,
 (MAD-MID)*24*60*60 间隔秒
 FROM(SELECT MAX(CHECKIN_DATE) MAD,MIN(CHECKIN_DATE) MID FROM DEPT_USER)A;
  

 --计算两个日期之间的工作日
 SELECT * FROM T500;
 DELETE TABLE T500;
 COMMIT
 CREATE TABLE T500 AS SELECT LEVEL AS ID FROM DUAL CONNECT BY LEVEL<=500;
 
 
 SELECT COUNT(*)FROM (
 SELECT (A.MAD-A.MID+1) 相隔天数,
 A.MID+(T500.ID-1) 日期,
 --TO_CHAR(A.MID+(T500.ID-1),'day') AS DY错误？
 TO_CHAR(A.MID+(T500.ID-1),'DY','nls_date_language=American') AS DY
 FROM (SELECT MAX(TRUNC(D_BRITHDATE,'dd')) MAD,MIN(TRUNC(D_BRITHDATE,'dd')) MID FROM DEPT_USER)A,T500
 WHERE T500.ID<=(A.MAD-A.MID+1))B
 WHERE DY IN('SUN');
 
 --当前日期和下一条日期相差天数LAG(),LEAD()
SELECT A.*,A.NEXT_VAL-A.BD AS 相差天数 FROM
 (SELECT D_UID,TRUNC(D_BRITHDATE,'dd') AS BD,LEAD(TRUNC(D_BRITHDATE,'dd')) OVER(ORDER BY D_BRITHDATE)AS NEXT_VAL FROM DEPT_USER)A;
 

--获取当前年月日的DATE类型信息
SELECT TO_DATE(TO_CHAR(D_BRITHDATE,'yyyy-MM-dd'),'yyyy-MM-dd')FROM DEPT_USER;

--获取月初信息
SELECT TRUNC(SYSDATE,'mm')AS 本月初,
TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM')||'01','yyyy-MM-dd') AS 本月初方法2,
TRUNC(SYSDATE,'dd') AS 一天之始,
TO_CHAR(SYSDATE,'dd')AS 星期几,
TO_CHAR(SYSDATE,'month')AS 月份,
TO_NUMBER(TO_CHAR(SYSDATE,'ddd'))AS 年内第几天,
LAST_DAY(SYSDATE)AS 月末,
NEXT_DAY(SYSDATE,3)下周的第几天的日期,
LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'y'),1)) 是否为闰年,--判断今年是否为闰年
TO_CHAR(SYSDATE,'ddd')今年第几天
 FROM DUAL;

--函数
--INTERVAL时间间隔,获取INTERVAL的数据类型的值
SELECT INTERVAL '2'YEAR AS "year",
INTERVAL '20' MONTH AS "month",
INTERVAL '90' DAY AS "day",--最大只能写99
INTERVAL '99' HOUR AS "hour",
INTERVAL '99' MINUTE AS "minute",
INTERVAL '99' SECOND AS "second",
INTERVAL '3 12:02:50' DAY TO SECOND AS "day to second",
INTERVAL '15-4' YEAR TO MONTH AS "year to month"
FROM DUAL;

--EXTRACT提取时间信息
SELECT 
EXTRACT(YEAR FROM SYSDATE) AS "year",
EXTRACT(MONTH FROM SYSDATE) AS "month", 
EXTRACT(DAY FROM SYSDATE) AS "day", 
EXTRACT(HOUR FROM SYSTIMESTAMP) AS "hour", 
EXTRACT(MINUTE FROM SYSTIMESTAMP) AS "minute", 
EXTRACT(SECOND FROM (SELECT INTERVAL '99' SECOND AS S FROM DUAL)) AS "second" --与INTERVAL联合使用
FROM DUAL;

--确定一年以内属于星期几的所有日期
--枚举全部日期
SELECT ADD_MONTHS(TRUNC(SYSDATE,'y'),12)AS 年底,TRUNC(SYSDATE,'y') AS 年初,
ADD_MONTHS(TRUNC(SYSDATE,'y'),12)-TRUNC(SYSDATE,'y') AS 年差值 FROM DUAL

SELECT TRUNC(SYSDATE,'y')+365 第一天加365天后的日期,
TRUNC(SYSDATE,'day') 当前星期天的日期,--星期天就是第一天，日期型
TO_CHAR(TRUNC(SYSDATE,'day'))当前星期天的日期,--字符串
TO_CHAR(SYSDATE,'day')当前日期是星期几 FROM DUAL;

WITH A AS
(SELECT TRUNC(SYSDATE,'y')+(LEVEL-1) AS RN FROM DUAL CONNECT BY LEVEL <=365)--这里的365就是上面的年差值，可以调用
SELECT RN,TO_CHAR(RN,'day')星期几,ROW_NUMBER() OVER(ORDER BY RN) FROM A WHERE TO_CHAR(RN,'d')=2
WITH A AS(
SELECT TRUNC(TO_DATE('2019-01-01','YYYY-MM-DD'),'y')+(LEVEL-1) AS RN FROM DUAL CONNECT BY LEVEL <=365)
SELECT RN,TO_CHAR(RN,'day')星期几,ROW_NUMBER() OVER(ORDER BY RN) FROM A 
--创建本月日历
WITH X1 AS 
(SELECT TRUNC(SYSDATE,'mm') AS CURMONTH,ADD_MONTHS(TRUNC(SYSDATE,'mm'),1) AS NEXTMONTH  FROM DUAL),
 X2 AS
(SELECT CURMONTH+(LEVEL-1) AS ALLDATE FROM X1 CONNECT BY LEVEL<=NEXTMONTH-CURMONTH),
X3 AS
(SELECT TO_CHAR(ALLDATE,'dd') AS ALLDAY,
        TO_CHAR(ALLDATE,'day')AS ALLWEKDAY,
        TO_NUMBER(TO_CHAR(ALLDATE,'d'))AS ALLWEKNUM,
        TO_CHAR(ALLDATE+2,'ww')AS INWEEK FROM X2)
SELECT MAX(CASE ALLWEKNUM WHEN 1 THEN ALLDAY END)周日,
MAX(CASE ALLWEKNUM WHEN 2 THEN ALLDAY END)周一,
MAX(CASE ALLWEKNUM WHEN 3 THEN ALLDAY END)周二,
MAX(CASE ALLWEKNUM WHEN 4 THEN ALLDAY END)周三,
MAX(CASE ALLWEKNUM WHEN 5 THEN ALLDAY END)周四,
MAX(CASE ALLWEKNUM WHEN 6 THEN ALLDAY END)周五,
MAX(CASE ALLWEKNUM WHEN 7 THEN ALLDAY END)周六
FROM X3
GROUP BY INWEEK ORDER BY INWEEK;

--创建本年日历
WITH X1 AS 
(SELECT TRUNC(SYSDATE,'year') AS CURYEAR,ADD_MONTHS(TRUNC(SYSDATE,'year'),12) AS NEXTYEAR  FROM DUAL),
 X2 AS
(SELECT CURYEAR+(LEVEL-1) AS ALLDATE,
        TO_NUMBER(TO_CHAR(CURYEAR+(LEVEL-1)+2,'ww'))AS INWEEK FROM X1 CONNECT BY LEVEL<=NEXTYEAR-CURYEAR),
X3 AS
(SELECT TO_CHAR(ALLDATE,'dd') AS ALLDAY,
        TO_CHAR(ALLDATE,'day')AS ALLWEKDAY,
        TO_NUMBER(TO_CHAR(ALLDATE,'d'))AS ALLWEKNUM,
        CASE WHEN  TO_CHAR(ALLDATE,'mm')=12 AND INWEEK=01 THEN 53 ELSE INWEEK END AS INWEEK2
           FROM X2)
SELECT INWEEK2,MAX(CASE ALLWEKNUM WHEN 1 THEN ALLDAY END)周日,
MAX(CASE ALLWEKNUM WHEN 2 THEN ALLDAY END)周一,
MAX(CASE ALLWEKNUM WHEN 3 THEN ALLDAY END)周二,
MAX(CASE ALLWEKNUM WHEN 4 THEN ALLDAY END)周三,
MAX(CASE ALLWEKNUM WHEN 5 THEN ALLDAY END)周四,
MAX(CASE ALLWEKNUM WHEN 6 THEN ALLDAY END)周五,
MAX(CASE ALLWEKNUM WHEN 7 THEN ALLDAY END)周六
FROM X3
GROUP BY INWEEK2 ORDER BY INWEEK2;

--季度开始日期和结束日期
SELECT SN AS JIDU,
 (SN-1)*3+1 AS STARMONTH,
 TO_DATE('2018-01-01','yyyy-MM-dd'),
 ADD_MONTHS(TO_DATE('2018-01-01','yyyy-MM-dd'),(SN-1)*3)STARDATE,
 ADD_MONTHS(TO_DATE('2018-01-01','yyyy-MM-dd'),SN*3)-1 ENDDATE
 FROM (SELECT LEVEL AS SN FROM DUAL CONNECT BY LEVEL<=4);

--当数据库用3个字段分别记录年、月、日，可以使用以下方法合并
SELECT 
EXTRACT(YEAR FROM SYSDATE)||'/'||EXTRACT(MONTH FROM SYSDATE)||'/'||EXTRACT(DAY FROM SYSDATE) AS DATETIME
FROM DUAL;