我刚刚发现，如果你有一个引发TOO_MANY_ROWS异常的SELECT INTO，那么变量仍会被分配来自查询检索到的第一条记录的值。 这是预期的行为吗？
这是我的例子：
for co in my_cursor loop l_sco_db_id := null; begin select db_id into l_sco_db_id from objects_tab where object_name = co.object_name; exception when no_data_found then dbms_output.put_line('No objects_tab record found for Object ' || co.object_name); when too_many_rows then dbms_output.put_line('Multiple objects_tab records found for Object ' || co.object_name); l_sco_db_id := null; end; end loop;这是在一个循环中，所以我在开头设置变量null以确保它是空白的，但是我必须在WHEN TOO_MANY_ROWS异常中再次明确地执行它，这是我没想到的。 我的同事（至少是那些立即听到的人）都不希望这个变量有价值。
I just discovered that if you have a SELECT INTO that raises a TOO_MANY_ROWS exception, the variable still gets assigned the value from the first record the query retrieved. Is that expected behavior?
Here's my example:
for co in my_cursor loop l_sco_db_id := null; begin select db_id into l_sco_db_id from objects_tab where object_name = co.object_name; exception when no_data_found then dbms_output.put_line('No objects_tab record found for Object ' || co.object_name); when too_many_rows then dbms_output.put_line('Multiple objects_tab records found for Object ' || co.object_name); l_sco_db_id := null; end; end loop;This is inside a loop, so I set the variable null at the beginning to ensure it's blank, but I had to explicitly do it again in the WHEN TOO_MANY_ROWS exception, which I didn't expect. None of my coworkers (at least, those in immediate earshot) expected the variable to have a value, either.
最满意答案
这是预期的行为，因为当你了解幕后发生的事情时，它是有道理的。 但是，当你第一次看到它时，这绝对是一种看似奇怪的行为。 从技术上讲，记录的行为是未定义的，因此不应该依赖它，并且可能在将来发生变化。
在封面下， select into只是语法糖
打开光标 从游标中获取一行到目标变量 如果未提取任何行，则抛出no_data_found异常 尝试从游标中获取第二行，如果第二次获取成功则抛出too_many_rows异常。鉴于此，有意义的是目标变量将由第一次获取写入。 但是， select into语句的Oracle文档说明了这一点
PL / SQL引发预定义的异常TOO_MANY_ROWS，并且INTO子句中的变量值未定义。
因此，Oracle可以自由地保持值不变，或者让变量具有第一行的值或第二行的值，或者实际上是其他任何值。 而且你不应该编写依赖于任何特定行为的代码。
例如，如果你看看Jeff Kemp的这篇博客文章 ，变量将从获取的第一行中获取值。 但是如果你对Jeff的代码进行一些小的调整，那么你就可以获取一个局部变量
CREATE or replace PROCEDURE proc2 (v OUT NUMBER) IS l_v integer; BEGIN SELECT 1 INTO l_v FROM all_objects; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line ('TOO MANY ROWS: v=' || l_v); v := l_v; END; /然后行为发生变化，并且值似乎没有被覆盖。
DECLARE v NUMBER; BEGIN proc2(v); dbms_output.put_line('AFTER: v=' || v); END; /It's expected behavior in that when you understand what is going on under the covers it makes some sense. But it's definitely a behavior that seems rather odd when you see it the first time. Technically, the behavior is documented to be undefined so it shouldn't be relied upon and could change in the future.
Under the covers, a select into is just syntactic sugar for
Open cursor Fetch a row from the cursor into the target variable Throw a no_data_found exception if no row was fetched Attempt to fetch a second row from the cursor, throw a too_many_rows exception if that second fetch succeeded.Given that, it sort of makes sense that the target variable would get written by the first fetch. The Oracle documentation for the select into statement, however, states that
PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined.
So Oracle is free to either leave the value unchanged or to let the variable have the value of the first row fetched or the second row or, realistically, anything else. And you shouldn't write code that depends on any particular behavior.
As an example, if you look at this blog post from Jeff Kemp, the variable takes on the value from the first row that was fetched. But if you make a small tweak to Jeff's code so that you fetch into a local variable
CREATE or replace PROCEDURE proc2 (v OUT NUMBER) IS l_v integer; BEGIN SELECT 1 INTO l_v FROM all_objects; EXCEPTION WHEN TOO_MANY_ROWS THEN dbms_output.put_line ('TOO MANY ROWS: v=' || l_v); v := l_v; END; /then the behavior changes and the value doesn't appear to be overwritten.
DECLARE v NUMBER; BEGIN proc2(v); dbms_output.put_line('AFTER: v=' || v); END; /更多推荐
发布评论