需要Oracle自定义排序

Question

我有一个接受参数的PLSQL过程。数据如下所示：

11035,15005,17060,17005,17010,15030,15035,11040,15040,15045,14010

我需要按照与传入参数相同的顺序对结果进行排序。因此，我创建了一些代码来创建动态字符串，如下所示：

0:11035,1:15005,2:17060,3:17005,4:17010,5:15030,6:15035,7:11040,8:15040,9:15045,10:14010

我认为我可以按每个参数值之前的序列号进行排序，但它不起作用。

有什么建议吗？谢谢!

Answer 1

select LISTAGG(xxx, ',') WITHIN GROUP (ORDER BY xxx)
from
(
select regexp_substr('11035,15005,17060,17005,17010,15030,15035,11040,15040,15045,14010','[^,]+', 1, level) as xxx 
from dual
connect by regexp_substr('11035,15005,17060,17005,17010,15030,15035,11040,15040,15045,14010', '[^,]+', 1, level) is not null
)
;

Answer 2

select LISTAGG(xxx, ',')  WITHIN GROUP (ORDER BY ROWNUM) as v_data
from
(
select rownum-1 || ':' || regexp_substr('11035,15005,17060,17005,17010,15030,15035,11040,15040,15045,14010','[^,]+', 1, level) as xxx 
from dual
connect by regexp_substr('11035,15005,17060,17005,17010,15030,15035,11040,15040,15045,14010', '[^,]+', 1, level) is not null
)
;

结果V_DATA: 0:11035,1:15005,2:17060,3:17005,4:17010,5:15030,6:15035,7:11040,8:15040,9:15045,10:14010

Answer 3

试试下面的

with datum as (select '0:11035,1:1500,2:17060,3:17005,4:17010,5:15030,10:15035,7:11040,8:15040,9:15045,6:14010' as str from dual),
cols as (SELECT LEVEL AS id, REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) AS data
         FROM datum
         CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) IS NOT NULL),
output as (select REGEXP_SUBSTR(data, '[^:]+', 1, 2) param_col, 
                  cast(REGEXP_SUBSTR(data, '[^:]+', 1, 1) as number) order_col
                  from cols)
select param_col
from output
order by order_col

理想情况下，将其映射到Oracle中的Type，可能是Params Type，它存储行号和param-value，然后在Procs之间传递。

对于API，让Java或任何API在层中创建类型时使用LinkedHashMap，以保持插入顺序。