oracle – SELECT * FROM TABLE(流水线函数)：我可以确定结果中

在下面的示例中,我将始终获得“1,2”,或者是否可以获得“2,1”并且您能告诉我您在文档中的哪个位置可以保证它是否存在？

如果答案是肯定的,则意味着没有ORDER BY和ORDER SIBLINGS,就可以确定SELECT语句中的结果集顺序.

CREATE TYPE temp_row IS OBJECT(x number);
/

CREATE TYPE temp_table IS TABLE OF temp_row;
/

CREATE FUNCTION temp_func
RETURN temp_table PIPELINED
IS
BEGIN
    PIPE ROW(temp_row(1));
    PIPE ROW(temp_row(2));
END;
/

SELECT * FROM table(temp_func());

谢谢.

解决方法

2003年有一个旧的Tom Kyte thread(因此可能已经过时),它表明依赖隐式顺序是不可取的,原因与您不依赖普通SQL中的顺序相同.

1st: is the order of rows returned from the table function within a
SQL statement the exact same order in which the entries were “piped”
into the internal collection (so that no order by clause is needed)?

…

Followup May 18,2003 – 10am UTC:

1) maybe,maybe not,I would not count on it. You should not count
on the order of rows in a result set without having an order by. If
you join or do something more complex then simply “select * from
table( f(x) )”,the rows could well come back in some other order.

empirically — they appear to come back as they are piped. I do not
believe it is documented that this is so.

In fact,collections of type NESTED TABLE are documented to explicitly
not have the ability to preserve order.

为了安全起见,如果要对查询结果进行排序,则应该像查询中一样,按照显式ORDER BY进行操作.

说过我已经完成了你的功能并运行了1000万次迭代,以检查隐式顺序是否曾被破坏;事实并非如此.

SQL> begin
  2    for i in 1 .. 10000000 loop
  3      for j in ( SELECT a.*,rownum as rnum FROM table(temp_func()) a ) loop
  4
  5         if j.x <> j.rnum then
  6            raise_application_error(-20000,'It broke');
  7         end if;
  8      end loop;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!