# oracle中的函数
# 日期字符串转换
D——一周中的星期几
# 日期到字符串(to_char)
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- ff-毫秒，格式化不区分大小写
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.ff') from dual;
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;
1
2
3
4
5
2
3
4
5
# 字符串到日期(to_date)
select to_date('2022-02-02 02:02:02', 'yyyy-mm-dd hh24:mi:ss') from dual;
1
# 日期时间加减
# 直接加减数字
-- 加减日时分秒
select sysdate as crtDate,
sysdate + 1 as nxtDate,
sysdate + (1/24) as nxtHour,
sysdate + (1/24/60) as nxtMinute,
sysdate – 1 as preDate
from dual;
add_months()
select add_months(sysdate, 1) as nxtMonth,
add_months(sysdate, 1 * 12) as nxtYear,
add_months(to_date('2022-01-31', 'yyyy-mm-dd'), 1) as nxtMonth, -- 2022-02-28
add_months(sysdate, -1) as preMonth
from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# interval
语法：interval '时间差数值' {year|month|day|hour|minute|second}(精度数值)
-- 精度数值范围1-9，默认为2，精度不匹配会报错
select sysdate + interval '1' year as nxtYear,
sysdate + interval '1' month as nxtMonth,
sysdate + interval '1' day as nxtDay,
sysdate + interval '1' hour as nxtHour,
sysdate + interval '1' minute as nxtMinute,
sysdate + interval '-1' minute as preMinute,
sysdate + interval '1' second as nxtSecond
from dual;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# 获取指定日期
-- 获取当前时间所在季度的第一天的00:00:00（2023-07-01 00:00:00）
select trunc(sysdate, 'Q') from dual;
-- 获取上个季度最后一天的23:59:59（2023-06-31 23:59:59）
select trunc(trunc(sysdate, 'Q') - 1) + 0.999999 from dual;
select last_day(add_months(trunc(sysdate, 'Q') + 0.999999, -1)) from dual;
-- 获取上个季度第一天(2023-04-01)
select add_months(trunc(sysdate, 'Q'), -3) from dual;
-- 获取指定日期所在季度信息
select (extract(month from sysdate) + 2) / 3 from dual;
-- 获取指定日期上个季度信息
select to_char(add_months(sysdate, -3), 'Q') from dual;
-- 获取指定日期的最大时间点
-- 23:58:33.600000
select trunc(sysdate) + 0.999 from dual;
-- 23:59:51.360000
select trunc(sysdate) + 0.9999 from dual;
-- 23:59:59.136000
select trunc(sysdate) + 0.99999 from dual;
-- 23:59:59.913600
select trunc(sysdate) + 0.999999 from dual;
-- 格式化（2023-07-01 23:9:59）
select to_char(trunc(sysdate) + 0.999999, 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 获取指定日期所在月份的最后一天（2023-07-31）【时间是00:00:00】
select last_day(sysdate) from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 倒序字符串REVERSE
-- 只包含数字和字符串时
-- gnituh
select REVERSE('huting') from dual;
1
2
3
2
3
# decode
判断字符串是否一样
DECODE(value, if1, then1, if2, then2, if3, then3, elseN)
-- elseN：默认值
1
2
2
# 判断大小
DECODE(sign(var1 – var2) , -1, var1, var2)
1
sign()根据数据是否为0、正数、负数，分别返回0、1、-1
# 函数分段
举例子：分数>=90为A，分数介于70（含）到90为B，分数小于70为C
# 行列转换
select
sum(decode(ENAME, 'huting', SAL, 0)) huting,
sum(decode(ENAME, 'limin', SAL, 0)) limin
from tableName
1
2
3
4
2
3
4
# listagg()列转行
归并拼接字段，listagg(字段名，隔断符号)
select nation, listagg(city, '#') within group (order by city) as cities from temp group by nation;
1
如何去重？推荐先去重再聚合
# instr
select instr('huting', 't') from dual; -- 3
select instr('huting', 'a') from dual; -- 0
1
2
3
2
3
# replace
-- hliminting
select replace('huting', 'u', 'limin') from dual;
1
2
2
# regexp_substr()
(string, pattern, position, occurrence, modifier)
- pattern：正则表达式
- position：起始位置（字符串的最初位置是1而不是0）
- occurrence：获取第几个分割出来的组（分割后最初的字符串会按照分割的顺序排列成组）
- modifier：
i- 不区分大小写，c- 区分大小写，默认为c
select regexp_substr('11a22A33a', '[^A]+', 1, 1, 'c') as str from dual;
select regexp_substr('11a22A33a', '[^A]+', 1, level, 'c') as str from dual connect by level < 5;
-- 此时多余的会输出null，不是我们想要的结果
select regexp_substr('11a22A33a', '[^A]+', 1, level, 'c') as str from dual connect by level <= (regexp_count('11a22A33a', '\A') + 1) ;
-- 这样可规避多余的null值
1
2
3
4
5
6
7
2
3
4
5
6
7
# regexp_like
-- 全模糊匹配（1）
select 1 from dual where regexp_like('我就是我啊牛得很', '是我|牛得|啊牛');
-- 左模糊匹配（1）
select 1 from dual where regexp_like('我就是我啊牛得很', '(是我|牛得|得很)$');
-- （null)
select 1 from dual where regexp_like('我就是我啊牛得很', '(是我|牛得|很牛)$');
-- 右模糊匹配
select 1 from dual where regexp_like('我就是我啊牛得很', '^(是我|牛得|得很)');
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# start with …connect by prior
connect by 是结构化查询中用到的，基本语法如下：
select … from tableName
start with 条件1
connect by 条件2
where 条件3
1
2
3
4
2
3
4
- prior：一侧表示父节点，另一侧表示子节点；
- connect by prior ccbins_id = supccbins_id，表示从根节点向叶节点顺序检索，即自顶向下方式；
- connect by ccbins_id = prior supccbins_id，表示从叶节点向根节点顺序检索，即自底向上方式；
- start with：为可选，用来标识查找树型结构的根节点，若无，则标识所有满足查询条件的行都将作为根节点；
| prior | 表示上一层级的标识符 |
|---|---|
| level | 伪列，代表树形结构中的层级编号，和rownum同等效果 |
| connect_by_root | 显示根节点列 |
| connect_by_isleaf | 1-叶节点，0-非叶节点，制作树状表格时必用的关键字 |
| sys_connect_by_path | 将递归过程中的列进行拼接 |
| nocycle,connect_is_cycle | 在循环结构中查询使用 |
| siblings | 保留树状结构，对兄弟节点进行排序 |
# where条件执行顺序
右—>左
- 1、如果最右边的条件不满足情况返回空结果，此时左边的条件都不会去编译，即时条件是错误的也没关系；
- 2、如果错误的条件在最右边，则会直接编译报错；
- 3、如果条件中含索引字段，则会先执行索引字段，如果索引条件不满足，此时即使索引字段右侧有错误的条件，也不会报错。
# 数值计算公式
整除:div
5 div 2 = 2;
1
取余:mod
5 mod 2 = 1;
1
四舍五入:round
round(1.5) = 2;
1
# sum函数
我们在Java程式中，对于比如select sum(col) col from tab where id=?这样的查询，
当条件id的值在tab中不存在事，查询时有结果集的，只是一个空的值，那么在Java程式中就要注意此类情况，if(rs.nect()){ 。。。} 这样的话，此类情况会进入if判断，因此，需要考虑这类情况逻辑是否正确！
处理办法：加上group by 语句。
SELECT
SUM(TBD.f_frequency)
FROM TB_BAS_DISASTERFREQUENCYP TBD
WHERE
SUBSTR(TO_CHAR(TBD.f_date, 'yyyy-MM-dd'), 1, 4) IN ('2009', '2010') AND
SUBSTR(TO_CHAR(TBD.f_date, 'yyyy-MM-dd'), 6, 2) IN ('02') AND
TBD.f_Disastercode = '1' AND
SUBSTR(TBD.f_area, 1, 6) = '110000'
GROUP BY
SUBSTR(TO_CHAR(TBD.f_date, 'yyyy-MM-dd'), 1, 4),
SUBSTR(TO_CHAR(TBD.f_date, 'yyyy-MM-dd'), 6, 2),
TBD.f_Disastercode,
SUBSTR(TBD.f_area, 1, 6)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13