oracle NVL 或 EXCEPTION NO_DATA_FOUND
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5571084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NVL or EXCEPTION NO_DATA_FOUND
提问by mcha
i have in a procedure which fills a table the following sql
我在一个填充表的过程中使用了以下 sql
SELECT NVL(SUM(COL1), 0),
NVL(SUM(COL2), 0)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
also, for 99% of the table rows, those columns = 0 and this query take a long time to be executed when it will return 0 for both columns in most cases.
此外，对于 99% 的表行，这些列 = 0 并且此查询需要很长时间才能执行，因为在大多数情况下它会为两列返回 0。
Is it better to use exception handeling as the following :
使用异常处理是否更好，如下所示：
BEGIN
SELECT SUM(COL1),
SUM(COL2)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
EXCEPTION WHEN NO_DATA_FOUND THEN
v_mytable.COLUMN1 := 0 ;
v_mytable.COLUMN2 := 0 ;
END;
Thanks.
谢谢。
回答by Justin Cave
Those two blocks do completely different things. Your SELECT statement would not throw a NO_DATA_FOUNDerror if COL1 and/or COL2 were always NULL. It would simply put a NULL in v_mytable.COLUMN1and v_mytable.COLUMN2.
这两个块做完全不同的事情。NO_DATA_FOUND如果 COL1 和/或 COL2 始终为 NULL，则SELECT 语句不会抛出错误。它只会在v_mytable.COLUMN1and 中放入一个 NULL v_mytable.COLUMN2。
You could do
你可以做
SELECT SUM(COL1),
SUM(COL2)
INTO v_mytable.COLUMN1,
v_mytable.COLUMN2
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
v_mytable.COLUMN1 := NVL( v_mytable.COLUMN1, 0 );
v_mytable.COLUMN2 := NVL( v_mytable.COLUMN2, 0 );
I wouldn't expect that to be any faster, however.
然而，我不希望这会更快。
回答by cagcowboy
Given the choice between these two, I'd go for the first one.
如果在这两者之间做出选择，我会选择第一个。
I prefer to use exception handlers for genuine exceptions / errors, not control flow.
我更喜欢将异常处理程序用于真正的异常/错误，而不是控制流。
YMMV.
天啊。
回答by tbone
NO_DATA_FOUND would be thrown if no rows were returned, NOT if null values were returned in the actual rows that ARE returned from the query. This would throw NO_DATA_FOUND:
如果没有返回任何行，则将抛出 NO_DATA_FOUND，如果从查询返回的实际行中返回空值，则不会抛出。这将抛出 NO_DATA_FOUND：
select sysdate
into myVariable
from dual
where 1=0;
This would NOT throw NO_DATA_FOUND:
这不会抛出 NO_DATA_FOUND：
select null
into myVariable
from dual;
That said, if you simply want to IGNORE the rows where col1 and col2 are null, then you may consider using collections in pl/sql, and use bulk collect into, something like:
也就是说，如果您只是想忽略 col1 和 col2 为空的行，那么您可以考虑在 pl/sql 中使用集合，并使用批量收集，例如：
select sum(col1) as sum_col1, sum(col2) as sum_col2, col3
bulk collect into v_mytable
FROM t1, t2
WHERE t1.id = t2.id
AND t1.date = t2.date
AND col1 is not null
AND col2 is not null
GROUP by col3;
No looping, do in one fell swoop. FYI, you would setup v_mytable something like:
没有循环，一举完成。仅供参考，您可以设置 v_mytable 类似于：
declare
type t_rec is record
(col1_sum number,
col2_sum number,
col3 number);
v_rec t_rec;
type t_tab is table of v_rec%type;
v_mytable t_tab;
begin
...
Later you can loop through v_mytable, which will be only 1% of the original t1,t2 join result (due to the additional not null clauses in query).
稍后您可以循环遍历 v_mytable，它只会是原始 t1,t2 连接结果的 1%（由于查询中的附加非空子句）。
Hope that helps.
希望有帮助。
回答by Rob van Wijk
Your SQL will run a lot faster if you stop joining the rows for which the col values are 0. Below is a small test to prove my point.
如果您停止加入 col 值为 0 的行，您的 SQL 将运行得更快。下面是一个小测试来证明我的观点。
First create two tables with 100,000 rows, where 99% of the rows have their col value set to 0:
首先创建两个包含 100,000 行的表，其中 99% 的行的 col 值设置为 0：
SQL> create table t1 (id,date1,col1)
2 as
3 select level
4 , trunc(sysdate)
5 , case mod(level,100) when 42 then 42 else 0 end
6 from dual
7 connect by level <= 100000
8 /
Table created.
SQL> create table t2 (id,date2,col2)
2 as
3 select level
4 , trunc(sysdate)
5 , case mod(level,100) when 42 then 84 else 0 end
6 from dual
7 connect by level <= 100000
8 /
Table created.
Give the cost based optimizer table statistics:
给出基于成本的优化器表统计信息：
SQL> exec dbms_stats.gather_table_stats(user,'t1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2')
PL/SQL procedure successfully completed.
And gather statistics when running queries:
并在运行查询时收集统计信息：
SQL> set serveroutput off
SQL> alter session set statistics_level = all
2 /
Session altered.
Now your query runs like this:
现在您的查询运行如下：
SQL> SELECT NVL(SUM(t1.COL1), 0)
2 , NVL(SUM(t2.COL2), 0)
3 FROM t1
4 , t2
5 WHERE t1.id = t2.id
6 AND t1.date1 = t2.date2
7 /
NVL(SUM(T1.COL1),0) NVL(SUM(T2.COL2),0)
------------------- -------------------
42000 84000
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID 6q5h7h8ht5232, child number 0
-------------------------------------
SELECT NVL(SUM(t1.COL1), 0) , NVL(SUM(t2.COL2), 0) FROM t1 , t2 WHERE t1.id = t2.id AND
t1.date1 = t2.date2
Plan hash value: 446739472
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.37 | 560 | | | |
|* 2 | HASH JOIN | | 1 | 100K| 100K|00:00:00.24 | 560 | 4669K| 1437K| 7612K (0)|
| 3 | TABLE ACCESS FULL| T1 | 1 | 100K| 100K|00:00:00.01 | 280 | | | |
| 4 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 280 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID" AND "T1"."DATE1"="T2"."DATE2")
21 rows selected.
You can see that the HASH JOIN needs to join 100K rows, and this is where most of the time is spent. Now exclude the 0 values:
可以看到HASH JOIN需要join 100K行，大部分时间都花在了这里。现在排除 0 值：
SQL> SELECT NVL(SUM(t1.COL1), 0)
2 , NVL(SUM(t2.COL2), 0)
3 FROM t1
4 , t2
5 WHERE t1.id = t2.id
6 AND t1.date1 = t2.date2
7 and t1.col1 != 0
8 and t2.col2 != 0
9 /
NVL(SUM(T1.COL1),0) NVL(SUM(T2.COL2),0)
------------------- -------------------
42000 84000
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 /
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID bjr7wrjx5tjvr, child number 0
-------------------------------------
SELECT NVL(SUM(t1.COL1), 0) , NVL(SUM(t2.COL2), 0) FROM t1 , t2 WHERE t1.id = t2.id AND
t1.date1 = t2.date2 and t1.col1 != 0 and t2.col2 != 0
Plan hash value: 446739472
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 560 | | | |
|* 2 | HASH JOIN | | 1 | 25000 | 1000 |00:00:00.02 | 560 | 1063K| 1063K| 1466K (0)|
|* 3 | TABLE ACCESS FULL| T1 | 1 | 50000 | 1000 |00:00:00.01 | 280 | | | |
|* 4 | TABLE ACCESS FULL| T2 | 1 | 50000 | 1000 |00:00:00.01 | 280 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."ID"="T2"."ID" AND "T1"."DATE1"="T2"."DATE2")
3 - filter("T1"."COL1"<>0)
4 - filter("T2"."COL2"<>0)
23 rows selected.
And you can see that the HASH JOIN now only needs to join 1000 rows, leading to a much faster output.
您可以看到 HASH JOIN 现在只需要连接 1000 行，从而产生更快的输出。
Hope this helps.
希望这可以帮助。
Regards,
Rob.
问候，
罗伯。
