FOR r in (SELECT ... INTO ...)

FOR r in (SELECT ... INTO ...)(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.

我的两个假设是否正确?

Are my two assumptions right?

推荐答案

您的假设部分正确:

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 (SELECT ... INTO ...)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！