Oracle SQL

发表于   |   分类于 ，

记录遇到的一些 Oracle 函数，以及表达式

length 与 lengthb

LENGTH

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- length()
-- 代表的是字符的长度

length('好') = 1 , length('d') = 1

-- lengthb()
-- 代表的是字节的长度，不同字符集，其字节长度可能不同

lengthb('d') = 1 , lengthb('好') = 2  -- 按数据库默认字符集处理

-- convert()
-- 转换字符串的字符集

lengthb(convert('好', 'ZHS16GBK')) = 2
lengthb(convert('好', 'UTF8')) = 3

utl_match.edit_distance

EDIT_DISTANCE Function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- utl_match.edit_distance(s1 IN VARCHAR2, s2 IN VARCHAR2)
-- 字符串 s1 转化为 s2 所需的变动数量（插入，删除，替换）

utl_match.edit_distance('abc', 'acb') = 2

-- 中文涉及到编码

utl_match.edit_distance(convert('人', 'ZHS16GBK'), convert('事', 'ZHS16GBK')) = 2
utl_match.edit_distance(convert('人', 'UTF8'), convert('事', 'UTF8')) = 1

utl_match.edit_distance(convert('学', 'ZHS16GBK'), convert('生', 'ZHS16GBK')) = 2
utl_match.edit_distance(convert('学', 'UTF8'), convert('生', 'UTF8')) = 3

-- utl_match.edit_distance_similarity(s1 IN VARCHAR2, s2 IN VARCHAR2)
-- 相似度，(1 - edit_distance / max(s1.length, s2.length)) * 100，舍弃小数点并进位

utl_match.edit_distance_similarity('abc', 'acb') = 34

last_day

LAST_DAY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- last_day(date)
-- 返回日期所在月份的最后一天

last_day(to_date('2017-7-19', 'YYYY-MM-DD'))  -- 2017/7/31

-- 当存在时，分，秒，last_day 保留当前的时，分，秒

last_day(to_date('2017-7-19 19:10', 'YYYY-MM-DD HH24:MI'))  -- 2017/7/31 19:10:00



-- 当日期只有年份时，to_date 默认获取当前所在月份的第一天

to_date('2017', 'YYYY')  -- 2017/7/1

-- 当日期显示有小时，自动补全不存在的分和秒

to_date('2017-7-19 19', 'YYYY-MM-DD HH24')  -- 2017/7/19 19:00:00

trunc

TRUNC (date)
TRUNC (number)

时间截取格式参考：Date Format Models for the ROUND and TRUNC Date Functions

1
2
3
4
5
6
7
8
9
-- trunc()
-- 可以截取时间，数字

trunc(to_date('2017-7-19', 'YYYY-MM-DD'), 'YYYY')  -- 2017/1/1
trunc(to_date('2017-7-19', 'YYYY-MM-DD'), 'MM')  -- 2017/7/1

trunc(15.79, 1)  -- 15.7
trunc(15.79, 0)  -- 15
trunc(15.79, -1)  -- 10

interval

Interval Expressions
Interval Literals

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Interval Expressions

(sysdate - to_date('2017-6-25', 'YYYY-MM-DD')) year to month  -- +00-01
(sysdate - to_date('2017-6-25', 'YYYY-MM-DD')) year(4) to month  -- +0000-01

(sysdate - to_date('2017-6-25', 'YYYY-MM-DD')) day to second  -- +33 14:43:14.000000
(sysdate - to_date('2017-6-25', 'YYYY-MM-DD')) day(3) to second(2)  -- +033 14:44:33.00

-- Interval Literals

interval '5' year  -- +05-00
interval '2-6' year to month  -- +02-06
interval '20' month  -- +01-08

interval '40' day  -- +40 00:00:00
interval '40 10:30' day to minute  -- +40 10:30:00
interval '100' minute  -- +00 01:40:00

to_date('2017-7-28', 'YYYY-MM-DD') - interval '40' day  -- 2017/6/18
to_date('2017-7-28', 'YYYY-MM-DD') - interval '40' minute  -- 2017/7/27 23:20:00

interval '10' day - interval '400' minute  -- +000000009 17:20:00.000000000
interval '9 17:20' day to minute  -- +09 17:20:00

decode

DECODE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- decode()
-- 类似于 Java 里的 switch

decode(2+3, 5, 'true', 'false')  -- true

decode(to_char(sysdate,'MM'), 
       01, 'Jan', 
       02, 'Feb', 
       03, 'Mar', 
       04, 'Apr', 
       05, 'May', 
       06, 'Jun', 
       07, 'Jul', 
       08, 'Aug', 
       09, 'Sep', 
       10, 'Oct', 
       11, 'Nov', 
       12, 'Dec')