Oracle的莫名奇妙错误ORA-01403等错误

最新推荐文章于 2022-08-31 10:39:04 发布

原创最新推荐文章于 2022-08-31 10:39:04 发布 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

Oralce 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了 Oracle SQL 中的几个实用技巧，包括使用 casewhen 计算特定时间段内的平均值、处理空值以及 select into 语句在无数据时的异常处理。通过具体的示例，帮助读者更好地理解和掌握这些技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、case when 用于avg

需求：获得总体的平均值和最后三个月的平均值

with temp as(
select 100 d1,to_date('20170901','yyyyMMdd') d2 from dual
union all  
select 100 d1,to_date('20171001','yyyyMMdd') d2 from dual
union all  
select 100 d1,to_date('20171101','yyyyMMdd') d2 from dual
union all  
select 100 d1,to_date('20171201','yyyyMMdd') d2 from dual
union all  
select 100 d1,to_date('20180101','yyyyMMdd') d2 from dual
union all  
select 100 d1,to_date('20180201','yyyyMMdd') d2 from dual
)
select avg(d1) avg_d1,
       avg(case when d2>=to_date('20171201','yyyyMMdd') then d1 else 0 end) avg_error,--错误
       avg(case when d2>=to_date('20171201','yyyyMMdd') then d1 else null end) avg_three--对的
 from temp;

结果

d1	avg_error	avg_three
100	50	100

解释：oracle在计算平均值时，不会统计null值。所以，不符合条件时置为null。

2、！= 。not like 过滤掉空值

with temp as(
select 100 d1,'2018' d2 from dual
union all  
select 100 d1,'2016' d2 from dual
union all  
select 100 d1,'2017' d2 from dual
union all  
select 100 d1,'2018' d2 from dual
union all  
select 100 d1,null d2 from dual
union all  
select 100 d1,null d2 from dual
)
select d1,
       d2
 from temp
where d2!='2018';--运用了！= ，该处使用not like '2018'结果将一致

结果:null值不见了，是不是感觉到很疑惑

d1	d2
100	2016
100	2017

解释:Oralce 在计算比较值时(<>,!=,not like )会自动忽略null，相当于 <列名>is not null
解决：

select d1,
       d2
 from temp
where nvl(d2,'0000')!='2018';--将null替换
--第二种
select d1,
       d2
 from temp   
where d2!='2018' or d2 is null;

结果：

d1	d2
100	2016
100	2017
100
100

3、select into 没有数据 ORA-01403

表table1数据如下：

c1	c2
100	2016
100	2017
100	2015
100	2014

代码块如下：

declare
    v_d1 varchar2(32);
begin
    select c1 into v_d1 from where c2='8888';--没有任务记录
    bdms_output.put_line(v_d1);
end;

错误 ORA-01403：not found data/未找到任何数据
解决
加上聚合函数，好处：
1、多条记录返回时不会报错
2、为null值不会替换

select max(c1) into v_d1 from where c2='8888';--没有任务记录

解释：Oracle聚合函数底层可能在没有值时将数据替换为null。
为了保险起见，select into 最好加上聚合函数，或者在使用之前if判断记录数是否为1，即大于0。