FOR r in(选择...进入...) [英] FOR r in (SELECT ... INTO ...)

问题描述

今天，我遇到了一段有趣的代码，我认为不应该编译.它在FOR r IN ... LOOP中使用SELECT ... INTO子句.这是在Oracle 11i上编译的脚本.该脚本是在程序包中编译的实际PL/SQL代码的简化版本，可以在生产环境中运行.

Today, I came across a funny piece of code that I think should not compile. It uses an SELECT ... INTO clause within a FOR r IN ... LOOP. Here's a script that compiles on Oracle 11i. The script is a shortened version of actual PL/SQL code compiled in a package, runing in production.

create table tq84_foo (
   i number,
   t varchar2(10)
);

insert into tq84_foo values (1, 'abc');
insert into tq84_foo values (2, 'def');

declare

  rec tq84_foo%rowtype;

begin

  for r in (
     select     i,     t 
       into rec.i, rec.t -- Hmm???
       from tq84_foo
  )
  loop

    dbms_output.put_line('rec: i= ' || rec.i || ', t=' || rec.t);

  end loop;

end;
/


drop table tq84_foo purge;

运行时的输出为:

rec: i= , t=
rec: i= , t=

我相信 1)我可以安全地删除select语句的INTO部分，并且2)该构造应该无效或至少表现出未定义的行为.

I believe 1) I can safely remove the INTO part of the select statement and 2) that this construct should either be invalid or exhibits at least undefined behaviour.

我的两个假设对吗?

推荐答案

您的假设部分正确:

1)是，您可以安全地删除SELECT语句的INTO部分.但是您应该将循环中的行更改为以下格式:

1) Yes, you can safely remove the INTO part of the SELECT statement. But you should change the line in the loop to this format:

dbms_output.put_line('rec: i= ' || r.i || ', t=' || r.t);

这样，它将从r变量中获取数据

That way it will get the data out of the r variable

2)此代码的问题是，如果查询返回多行，则SELECT ... INTO的语法将失败.如果它没有失败，则可能是错误，并且将具有意外的行为.

2) The problem with this code is that the syntax of the SELECT ... INTO should fail if the query return more than one row. If it does not fail so it might be a bug and will have unexpected behaviour.

这篇关于FOR r in(选择...进入...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！