Oracle Ref Cursor Vs选择进入异常处理

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

如果对于给定的标准存在记录,则从table1读取数据,否则从表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:

目前我正在用游标重新实现这个,所以我有这样的东西:

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,另一个是选择变量并且有三个no_data_found异常块。我不想使用我们目前的两阶段查询过程。

6 个解决方案

#1

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异常有多糟糕?我在这里证明使用异常比计算更好,看看是否有任何数据。

#2

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引擎,第二个选项只执行一个上下文切换。

#3

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:

您可以使用另一个“技巧”来避免编写多个无数据的处理程序:

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

#4

An enhanced version of "Dave Costa"'s MIN option...

增强版“Dave Costa”的MIN选项......

SELECT COUNT(1), MIN(data) INTO v_rowcount, v_data FROM table2;

Now v_rowcount can be checked for values 0, >1 (greater than 1) where normal select query will throw NO_DATA_FOUND or TOO_MANY_ROWS exception. 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”表示确实存在一行并且将满足我们的目的。

#5

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;
/

#6

Use the "for row in cursor" form of a loop and the loop will just not process if there is no data:

使用循环中的“for cursor 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;

#1