Oracle Ref Cursor Vs Select into 异常处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/801805/
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
Oracle Ref Cursor Vs Select into with Exception handling
提问by Dinesh Manne
I have a couple of scenarios:
我有几个场景：
Need to read the value of a column from three different tables in a predefined order and only 1 table will have the data
Read data from table1 if records are present for criteria given else read data from Table2 for given criteria
需要以预定义的顺序从三个不同的表中读取列的值，并且只有 1 个表会有数据
如果给定条件的记录存在，则从表 1 中读取数据，否则根据给定条件从表 2 中读取数据
In Oracle Stored Procedures
在 Oracle 存储过程中
The way these are being handled right now is to first get the count for a given query into a variable, and if the count > 0, then we execute the same query to read the actual data as in:
现在处理这些的方式是首先将给定查询的计数放入变量中，如果计数 > 0，则我们执行相同的查询以读取实际数据，如下所示：
select count(*) from table1 into v_count
if v_count > 0
then
select data into v_data from table1
end if;
Return v_data
This is being done to avoid the no_data_found exception, otherwise I would need three exception handler blocks to catch the no_data_found exception for each table access.
这样做是为了避免 no_data_found 异常，否则我需要三个异常处理程序块来捕获每个表访问的 no_data_found 异常。
Currently I am reimplementing this with Cursors so that I have something like this:
目前我正在用 Cursors 重新实现它，以便我有这样的东西：
cursor C1 is
select data from table1;
Open C1
Fetch C1 into v_data
if C1%FOUND
then
Close C1
Return v_data
End If
I wanted to find out which one is better from a performance point of view--the one with Cursors, or the one which does a Select into a variable and has three no_data_found Exception blocks. I don't want to use the two stage query process which we have currently.
我想从性能的角度找出哪个更好 - 带有 Cursors 的一种，或者对变量执行 Select 并具有三个 no_data_found Exception 块的一种。我不想使用我们目前拥有的两阶段查询过程。
回答by Tony Andrews
I don't know why you are so keen to avoid the exception? What is wrong with:
我不知道你为什么如此热衷于避免异常？出什么问题了：
begin
begin
select data into v_data from table1;
exception
when no_data_found then
begin
select data into v_data from table2;
exception
when no_data_found then
begin
select data into v_data from table3;
exception
when no_data_found then
v_data := null;
end;
end;
end;
return v_data;
end;
I believe this will perform better than your other solution because it does the minimum possible work to achieve the desired result.
我相信这会比你的其他解决方案表现得更好，因为它做了最少的工作来达到预期的结果。
See How bad is ignoring Oracle DUP_VAL_ON_INDEX exception?where I demonstrate that using exceptions performs better than counting to see if there is any data.
请参阅忽略 Oracle DUP_VAL_ON_INDEX 异常有多糟糕？我证明了使用异常比计数来查看是否有任何数据的性能更好。
回答by erikkallen
select count(*) from table1 into v_count
if v_count > 0 then
select data into v_data from table1;
else
v_data := null;
end if;
return v_data;
is NOT equivalent to
不等于
begin
select data into v_data from table1;
return v_data;
exception
when no_data_found then
return null;
end;
in a multi-user environment. In the first case, someone could update the table between the points where you check for existence and when you read the data.
在多用户环境中。在第一种情况下，有人可以在您检查存在的点和读取数据的时间点之间更新表。
Performance-wise, I have no idea which is better, but I know that the first option makes two context switches to the sql engine and the second one only does one context switch.
在性能方面，我不知道哪个更好，但我知道第一个选项使两个上下文切换到 sql 引擎，第二个只执行一个上下文切换。
回答by Dave Costa
The way you're handling scenario 1 now is not good. Not only are you doing two queries when one will suffice, but as Erik pointed out, it opens up the possibility of data changing between the two queries (unless you use a read-only or serializable transaction).
您现在处理场景 1 的方式并不好。您不仅在一个就足够的情况下执行两个查询，而且正如 Erik 指出的那样，它开启了在两个查询之间更改数据的可能性（除非您使用只读或可序列化事务）。
Given that you say that in this case the data will be in exactly one of three tables, how about this?
鉴于您说在这种情况下数据将恰好位于三个表之一中，那么这个怎么样？
SELECT data
INTO v_data FROM
(SELECT data FROM table1
UNION ALL
SELECT data FROM table2
UNION ALL
SELECT data FROM table3
)
Another "trick" you can use to avoid writing multiple no-data-found handlers would be:
另一个可以用来避免编写多个 no-data-found 处理程序的“技巧”是：
SELECT MIN(data) INTO v_data FROM table1;
IF v_data IS NOT NULL THEN
return v_data;
END IF;
SELECT MIN(data) INTO v_data FROM table2;
...etc...
but I don't really see any reason that's better than having three exception handlers.
但我真的看不出有什么理由比拥有三个异常处理程序更好。
For your second scenario, I think what you mean is that there may be data in both tables and you want to use the data from table1 if present, otherwise use the data from table 2. Again you could do this in a single query:
对于您的第二种情况，我认为您的意思是两个表中可能都有数据，并且您希望使用 table1 中的数据（如果存在），否则使用表 2 中的数据。同样，您可以在单个查询中执行此操作：
SELECT data
INTO v_data FROM
(SELECT data FROM
(SELECT 1 sort_key, data FROM table1
UNION ALL
SELECT 2 sort_key, data FROM table2
)
ORDER BY sort_key ASC
)
WHERE ROWNUM = 1
回答by ValiTl
DECLARE
A VARCHAR(35);
B VARCHAR(35);
BEGIN
WITH t AS
(SELECT OM_MARCA, MAGAZIA FROM ifsapp.AKER_EFECTE_STOC WHERE (BARCODE = 1000000491009))
SELECT
(SELECT OM_MARCA FROM t) OM_MARCA,
(SELECT MAGAZIA FROM t) MAGAZIA
INTO A, B
FROM DUAL;
IF A IS NULL THEN
dbms_output.put_line('A este null');
END IF;
dbms_output.put_line(A);
dbms_output.put_line(B);
END;
/
回答by siddagrl
An enhanced version of "Dave Costa"'s MIN option...
“戴夫·科斯塔”的 MIN 选项的增强版...
SELECT COUNT(1), MIN(data) INTO v_rowcount, v_data FROM table2;
Now v_rowcountcan be checked for values 0, >1 (greater than 1) where normal select query will throw NO_DATA_FOUNDor TOO_MANY_ROWSexception. Value "1" will indicate that exact one row exists and will serve our purpose.
现在v_rowcount可以检查值 0、>1（大于 1），其中正常的选择查询将抛出NO_DATA_FOUND或TOO_MANY_ROWS异常。值“1”将指示确切的一行存在并且将服务于我们的目的。
回答by dpbradley
Use the "for row in cursor" form of a loop and the loop will just not process if there is no data:
使用循环的“for row in cursor”形式，如果没有数据，循环将不会处理：
declare cursor
t1Cur is
select ... from table1;
t2Cur is
select ... from table2;
t3Cur is
select ... from table3;
t1Flag boolean FALSE;
t2Flag boolean FALSE;
t3Flag boolean FALSE;
begin
for t1Row in t1Cur loop
... processing, set t1Flag = TRUE
end loop;
for t2Row in t2Cur loop
... processing, set t2Flag = TRUE
end loop;
for t3Row in t3Cur loop
... processing, set t3Flag = TRUE
end loop;
... conditional processing based on flags
end;
