Oracle常用函数

注：dual是伪表

1、字符函数

• 求字符串长度

select length('abcde') from dual;--5

• 字符串截取

select substr('abcde',2,2) from dual;--bc

• 字符串拼接

select concat('hello','world') from dual;--helloworld
select 'hello'||'world'||'lsflsjf' from dual;--helloworldlsflsjf--推荐

2、数值函数

• 四舍五入

select round(100.124) from dual;--100--默认整数四舍五入
select round(100.125,2) from dual;--100.13

• 数字截取

select trunc(100.567) from dual;--100
select trunc(100.567,2) from dual;--100.56

• 取模

select mod(10,3) from dual;--1

3、日期函数

• 加月

select add_months(sysdate,2) from dual;--2020/6/23 17:36:00
select add_months(sysdate,-2) from dual;--2020/2/23 17:36:00

• 求当月最后一天

select last_day(sysdate) from dual;--2020/4/30 17:36:34
select last_day(sysdate)-4 from dual;--2020/4/26 17:36:34

• 日期截取

select sysdate from dual;--2020/4/23 17:37:04
select trunc(sysdate) from dual; --2020/4/23--按日截取（把时间截掉）
select trunc(sysdate,'mm') from dual;--2020/4/1--按月截取（截掉日），返回当月第一天
select trunc(sysdate,'yyyy') from dual;--2020/1/1--按年截取，返回当年第一天
select trunc(sysdate,'hh') from dual;--2020/4/23 10:00:00--按小时截取
select trunc(sysdate,'mi') from dual;--2020/4/23 10:38:00--按分钟截取

4、转换函数

• 数字转字符串

select to_char(100)||'分' from dual;--100分
select 100||'分' from dual;--100分

• 日期转字符串

select to_char(sysdate,'yyyy-mm-dd') from dual;--2020-04-23
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual;--2020年04月
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;--2020-04-23 10:42:23

• 字符串转日期

select to_date('2019-03-10','yyyy-mm-dd') from dual;--2019/3/10

• 字符串转数字

select to_number('100')+10 from dual;--110
select '100' + 10 from dual;--110
select '100' || 10 from dual;--'10010'

5、空值处理、条件判断

• 空值处理

select nvl(null,0) from dual;--0
select nvl(100,0) from dual;--100

• nvl2

select nvl2(null,0,100) from dual;--100--值不为null，显示0；值为null,显示100
select nvl2(5,to_char(10),'不限') from dual;--10
select nvl2(null,to_char(10),'不限') from dual;--不限

• decode条件判

select decode(100,1,2,3,4,100,200) from dual;--200
select decode(400,1,2,3,4,100,200) from dual;--200
select decode(400,1,2,3,4,100,200,300) from dual;--300--缺省值
select stu_id,
       score,
       decode(score, 100, '满分', 88, '80-90', 99, '90-100', '其它')
  from scores;

• 等同于case when then --sql1999

select stu_id,
       score,
       (case score
         when 100 then
          '满分'
         when 88 then
          '80-10'
         when 99 then
          '10-100'
         else
          '其它'
       end)
  from scores;

• 更复杂需求还可以按下面这样写

select stu_id,
       score,
       (case
         when score = 100 then
          '满分'
         when score = 88 then
          '80-10'
         when score = 99 then
          '10-100'
         else
          '其它'
       end)
  from scores;

select stu_id,
       score,
       (case
         when score = 100 then
          '满分'
         when score>=80 then
          '优秀'
         when score>=60 then
          '及格'
         else
          '不及格'
       end)
  from scores;

6、分析函数(可用于排名)

• 值相同，排名相同，序号跳跃

select rank() over(order by class_id) as num,t.* from students t;

• 值相同，排名相同，序号连续

select dense_rank() over(order by class_id) as num,t.* from students t;

• 序号连续不管值是否相同

select row_number() over(order by class_id) as num,t.* from students t;

• 用分析函数来实现分页

select * from(select row_number() over(order by stu_id desc) as num1,t.* from students t)
where num1 <=6 and num1 >3;

7、集合运算

• 并集（包括重复记录）

select * from students where stu_id <1006
union all
select * from students where stu_id > 1003;

• 并集（去掉重复记录）

select * from students where stu_id <1006
union
select * from students where stu_id > 1003;

• 交集

select * from students where stu_id <1006
intersect
select * from students where stu_id > 1003;

• 差集

select * from students where stu_id <1006
minus
select * from students where stu_id > 1003;

select * from students where stu_id > 1003
minus
select * from students where stu_id <1006;

• 差集实现分页

select rownum,t.* from students t where rownum <=6
minus
select rownum,t.* from students t where rownum <=3;