Oracle - 函数相关

 

1. 单行函数

　　不改变真实数据, 只是对数据的显示进行了修饰

　　可以和字段混合使用 : select 字段1, 字段2, LOWER('xxx')... from 表名

-- 1. 字符函数

-- 首字母大写 : INITCAP(CHAR), 首字母大写, 其他变为小写
select INITCAP('heLLo') from dual;

-- 转换为小写 : LOWER(CHAR)
select LOWER('HELLO') from dual;

-- 转换为大写 : UPPER(CHAR)
select UPPER('hello') from dual;

-- 左剪裁 : LTRIM(CHAR,SET), 会去掉最左边的h
select LTRIM('hello', 'h') from dual;

-- 右剪裁 : RTRIM(CHAR,SET), 会去掉最右边的o
select RTRIM('hello', 'o') from dual;

-- 按字符翻译 : TRANSLATE(CHAR,FROM,TO), 会把hello中的e变成x, l变成y
select TRANSLATE('hello', 'el', 'xy') from dual;

-- 字符串替换 : REPLACE(CHAR, SEARCH_STR, REPLACE_STR), 把hello字符串中的he替换为xy
select REPLACE('hello', 'he', 'xy') from dual;

-- 查找子串位置 : INSTR(CHAR, SUB_STR), 结果为2, 索引从1开始
select INSTR('hello', 'e') from dual;

-- 取子字符串 : SUBSTR(CHAR, POS, LEN), 结果为ell, 索引从1开始
select SUBSTR('hello', 2, 3) from dual;

-- 连接字符串 : CONCAT(CHAR1, CHAR2)
select CONCAT('Hello', 'World') from dual;



-- 2. 数值函数 : 对数值类型的数据进行运算

-- 取绝对值 : ABS(N)
select ABS(-3) from dual; -- 3

-- 向上取整 : CEIL(N)
select CEIL(6.3) from dual; -- 7

-- 向下取整 : FLOOR(N)
select FLOOR(6.3) from dual; -- 6

-- 取余数 : MOD(M,N)
select MOD(6,4) from dual; -- 2

-- 四舍五入 : ROUND(M,N), 结果保留2位小数
select ROUND(1.235,2) from dual; -- 1.24

-- 截断 : TRUNC(M,N), 保留1位小数
select TRUNC(1.265,1) from dual; -- 1.2



-- 3. 日期函数

-- 返回两个日期间的月份
select MONTHS_BETWEEN('1-5月-20', '1-2月-20') from dual; -- 3

-- 修改月份, 返回新日期
select ADD_MONTHS('1-5月-20', 3) from dual; -- 2020/8/1
select ADD_MONTHS('1-5月-20', -3) from dual; -- 2020/2/1

-- 根据给定的日期, 返回下个星期几的日期
select NEXT_DAY('2-1月-20', '星期一') from dual; -- 2020/1/6

-- 返回给定日期所在月份的最后一天
select LAST_DAY('2-1月-20') from dual; -- 2020/1/31

 

2. 多行函数 ( max, min, avg, sum, count ) : 对查询的数据进行统计

　　where子句中不允许出现多行函数

-- max : 最大值
-- min : 最小值
-- avg : 平均值
-- sum : 求和
-- count : 数量, 值为null时, count不会统计, 会自动过滤
--       count(*) : 返回表的记录数
--       count(字段名) : 返回非空值的数量
--       count(distinct 字段名) : 去除重复后的数量

-- 多行函数不能和字段直接混用, 除非分组 
-- 多行函数也不能和单行函数直接混用
-- 多行函数之间可以混用
-- select max(字段名), min(字段名), ..... from 表

 

3. 转换函数

-- to_number(数值类型的字符) : 将字符转换为数值
select to_number('123') from dual; -- 123

-- to_char(数值或日期) : 将数值或日期转换为字符
select to_char(123) from dual; -- 123
select to_char(sysdate) from dual; -- sysdate : 系统当前时间
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual; -- 指定日期格式, 汉字需要使用双引号

-- 指定显示格式
-- 9 : 表示位置的占位
-- L : 表示人民币符号
-- $ : 表示美元符号
-- 0 : 和9一样, 同时真实数据位数不足时, 用0补位
select to_char(123456789, '999,999,999') from dual; -- 123,456,789

-- 数值和字符之间的转换是可以隐士转换的
select 2 + '123' from dual; -- 125

-- to_date(日期格式的字符, 日期格式) : 以指定的格式将将字符转换为日期
-- Oracle默认的日期转换格式为 : 日月年, 例 : 01-1月-2020
select to_date('2020-01-01', 'yyyy-mm-dd') from dual;

 

4. 其他函数

-- nvl(字段, 0) : 如果字段的值为null, 则返回值为0, 如果不是null, 则返回自己本身
-- nvl2(字段, 处理1, 处理2) : 如果字段为null, 则执行处理1, 否则执行处理2 
-- decode(字段, 值1, 处理1, 值2, 处理2, ..., 公共处理) : 
--     如果字段的值和decode中的条件值相同, 则执行对应的处理, 如果都不匹配, 则执行公共处理