Oracle表表达式的收集和排序 [英] Oracle table expression on collection and order

问题描述

我正在分析一些现有的PL \ SQL代码，并且可以看到在使用表表达式的查询中没有嵌套Oracle集合的情况.

I am analyzing some existing PL\SQL code and I can see cases where Oracle collections are unnested in queries using table expressions.

没有order子句，代码显然假定这些项将按顺序返回，因为它们存储在集合中. 我知道，如果没有"order by"，则不能假设select返回的项的顺序，但是在这种情况下，它似乎可以工作.

There is no order clause and the code obviously assumes that the items will be returned in the order as they are stored in the collection. I know that you cannot assume order of items returned by select if there is no "order by", but in this case it seems to work.

示例代码:

create type s_table as table of varchar2(100);

select rownum, t.* from table(s_table('TERM1', 'TERM2', 'TERM3')) t

我应该担心并更改代码吗?

Should I worry and change the code ?

推荐答案

无ORDER BY =无订单保证

No ORDER BY = no order guarantee

这是一个证明事实的示例，即集合中元素的顺序不一定是元素出现的顺序 在其他基于给定集合对象的查询中.

And here is an example that proves the fact, that the order of the elements in the collection is not necessarily the order according to which the elements could appear in other queries based on the given collection object.

 SQL> CREATE TYPE str_nestab_ty AS TABLE OF VARCHAR2(20);
 2  /

 Type created.

 SQL> SELECT COLUMN_VALUE
 2  FROM TABLE(
 3                CAST
 4                (
 5                    MULTISET
 6                    (
 7                        SELECT 'e10' AS strval FROM DUAL UNION
 8                        SELECT 'e07' AS strval FROM DUAL UNION
 9                        SELECT 'e04' AS strval FROM DUAL UNION
10                        SELECT 'e20' AS strval FROM DUAL UNION
11                        SELECT 'e14' AS strval FROM DUAL
12                    )
13                    AS str_nestab_ty
14                )
15             );

   COLUMN_VALUE
--------------------
e04
e07
e10
e14
e20

SQL> 

这篇关于Oracle表表达式的收集和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！