如果答案是肯定的,则意味着没有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.
