oracle - 常用函数

罗列一些开发过程中常用的语句和函数。

通用分页

双层子查询嵌套，允许嵌套任意查询语句

SELECT * FROM(
    SELECT SUB.*,ROWNUM RN_ FROM (
        -- 可替换成任何查询
        SELECT * FROM F_AGR_DEP_ACCU_SMY
    ) SUB WHERE ROWNUM <= 4
) WHERE RN_ >= 2

与 ORDER BY 混用时的注意事项：

-- 先取前 3，再对这 3 条数据进行排序
SELECT * FROM "T_MAT_PRODUCT" WHERE ROWNUM < 4 ORDER BY TITLE DESC;

-- 对整体排序，之后再取前 3
SELECT * FROM (SELECT * FROM "T_MAT_PRODUCT" ORDER BY TITLE DESC) WHERE ROWNUM < 4;

-- ORDER BY 子句，不要与 ROWNUM 混用，因为 Oracle 是先分页，再排序的，
-- 也就是先取 10 条数据，然后对这 10 条数据排序，
-- 存在索引（主键）的情况下，因为数据本身就是有序的，混用也是对的，
-- 但是对于普通列，需要考虑本身需求，是先取数再排序，还是先排序再取数。

常用函数 模糊查询

-- 做模糊查询 INSTR() 优于 LIKE
-- 语法通用，在 mybatis 环境下，不需要写成：'%'||'关键字'||'%'

INSTR(字段,'关键字') > 0 相当于 LIKE '%关键字%'
INSTR(字段,'关键字') = 1 相当于 LIKE '关键字%'
INSTR(字段,'关键字') = 0 相当于 NOT LIKE '%关键字%'

常用函数 String 相关

-- 将某个字段转换为大写
SELECT UPPER('acc') FROM DUAL;
-- 将某个字段转换为小写
SELECT LOWER('ABCD') FROM DUAL;
-- 返回某个文本字段的长度
SELECT LENGTH('abcde') FROM DUAL;

-- 字符串连接
SELECT CONCAT('abc', 'def') FROM DUAL;
-- 字符串连接
SELECT 'abc'||'def'||'' FROM DUAL;
--同一列的所有值用逗号拼接
SELECT WMSYS.WM_CONCAT(T.ID) FROM TY_ROLE_SYS T;

-- 字符替换
SELECT REPLACE('小鸡快跑','小','大') FROM DUAL;

-- 数据转码（含隐式转码，例：字符串1与数字1相等）
SELECT DECODE(1,'1','相等','不相等') FROM DUAL;
-- 数据转码
SELECT CASE WHEN 1=1 THEN '相等' ELSE '不相等' END FROM DUAL;

常用函数 Date 相关

SELECT SYSDATE FROM DUAL; -- 返回当前的系统日期
SELECT SYSTIMESTAMP FROM DUAL; -- 返回当前的系统日期

字符串转日期

SELECT TO_DATE('05 Dec 2000', 'DD Mon YYYY') FROM DUAL;
SELECT TO_DATE('2004-11-27 13:34:43', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
SELECT TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') FROM DUAL;

字符串转日期

--日期转化为字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
--获取时间的年
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
--获取时间的月
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
--获取时间的日
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
--获取时间的时
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
--获取时间的分
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
--获取时间的秒
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;

日期截断，返回值仍然是日期

SELECT ROUND(SYSDATE) FROM DUAL;
SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;
SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
SELECT ROUND(SYSDATE,'DAY') FROM DUAL;

TO_CHAR 和 TO_DATE 可用的表达式

-- YYYY：四位表示的年份
-- YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪
-- MM：01~12的月份编号
-- MONTH：九个字符表示的月份，右边用空格填补
-- MON：三位字符的月份缩写
-- WW：一年中的星期
-- D：星期中的第几天
-- DD：月份中的第几天
-- DDD：年所中的第几天
-- DAY：九个字符表示的天的全称，右边用空格补齐
-- HH，HH12：一天中的第几个小时，12进制表示法
-- HH24：一天中的第几个小时，取值为00~23
-- MI：一小时中的分钟
-- SS：一分钟中的秒
-- SSSS：从午夜开始过去的秒数

日期运算

-- 月份相加，特殊值：闰年2月29日自动转2月28日
SELECT ADD_MONTHS(TO_DATE('20000229', 'YYYYMMDD'), 12) FROM DUAL;
-- 月份相加，特殊值：次月没有31日自动转30日
SELECT ADD_MONTHS(TO_DATE('20000331', 'YYYYMMDD'), 1) FROM DUAL;
-- 月份相加
SELECT ADD_MONTHS(SYSDATE, -1) FROM DUAL;
-- 天数相加
SELECT SYSDATE - 1 FROM DUAL;


-- 年份的首日、末日
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;
SELECT TO_DATE(TO_CHAR(SYSDATE, 'YYYY')||'1231', 'YYYYMMDD') FROM DUAL;
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12) - 1 FROM DUAL;

-- 月份的首日、末日
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- 一周中的首日、末日（一周的第一天为周一）
SELECT TRUNC(SYSDATE, 'D') - 6 FROM DUAL;
SELECT TRUNC(SYSDATE, 'D') FROM DUAL;

-- 一周中的首日、末日（国际通用，第一天为周日）
SELECT TRUNC(SYSDATE, 'D') FROM DUAL;
SELECT TRUNC(SYSDATE, 'D') + 6 FROM DUAL;

常用函数 Trim

SELECT TRIM(' a ') FROM DUAL;--去除空格
SELECT LTRIM(' a ') FROM DUAL;--去除左边空格
SELECT RTRIM(' a ') FROM DUAL;--去除右边空格

常用函数 Math

AVG() -- 查询平均值
COUNT() -- 返回行数
FIRST() -- SELECT * FROM TABLE1 WHERE ROWNUM <= 2 查询前2
MAX() -- 返回最大值
MIN() -- 返回最小值
SUM() -- 返回总和
ROUND() -- 四舍五入

TRUNC 函数

-- 截断日期
SELECT TRUNC(SYSDATE) FROM DUAL; --2018-06-04 00:00:00 今天的日期为 2018-06-04 00:00:00
SELECT TRUNC(SYSDATE, 'mm') FROM DUAL; --2018-06-01 00:00:00 返回当月第一天.
SELECT TRUNC(SYSDATE,'yy') FROM DUAL; --2018-01-01 00:00:00 返回当年第一天
SELECT TRUNC(SYSDATE,'dd') FROM DUAL; --2018-06-04 00:00:00 返回当前年月日
SELECT TRUNC(SYSDATE,'yyyy') FROM DUAL; --2018-01-01 00:00:00 返回当年第一天
SELECT TRUNC(SYSDATE,'d') FROM DUAL; --2018-06-03 00:00:00 (星期天)返回当前星期的第一天
SELECT TRUNC(SYSDATE, 'hh') FROM DUAL; --2018-06-04 14:00:00 当前时间为17:35
SELECT TRUNC(SYSDATE, 'mi') FROM DUAL; --2018-06-04 14:54:00 TRUNC()函数没有秒的精确

-- 截断数字
select trunc(123.458) FROM DUAL; --123
select trunc(123.458,0) FROM DUAL; --123
select trunc(123.458,1) FROM DUAL; --123.4
select trunc(123.458,-1) FROM DUAL; --120
select trunc(123.458,-4) FROM DUAL; --0
select trunc(123.458,4) FROM DUAL; --123.458
select trunc(123) FROM DUAL; --123
select trunc(123,2) FROM DUAL; --123
select trunc(123,-3) FROM DUAL; --120