oracle存储过程写动态sql,【讨论】存储过程里动态order by 能否不用动态sql实现

WITH COLS AS (

SELECT 1 col_id,'A' col_name FROM DUAL

UNION ALL SELECT 2 col_id,'B' col_name FROM DUAL

UNION ALL SELECT 3 col_id,'C' col_name FROM DUAL

)

SELECT SYS_CONNECT_BY_PATH(col_name||DECODE(n,2,' DESC'),',')

FROM cols,(SELECT 1 n FROM DUAL UNION ALL SELECT 2 FROM DUAL)

CONNECT BY NOCYCLE col_id<>PRIOR col_id

,A

,A,B

,A,B,C

,A,B,C DESC

,A,C

,A,C,B

,A,C,B DESC

,A,B DESC

,A,B DESC,C

,A,B DESC,C DESC

,A,C DESC

,A,C DESC,B

,A,C DESC,B DESC

,B

,B,A

,B,A,C

,B,A,C DESC

,B,C

,B,C,A

,B,C,A DESC

,B,A DESC

,B,A DESC,C

,B,A DESC,C DESC

,B,C DESC

,B,C DESC,A

,B,C DESC,A DESC

,C

,C,A

,C,A,B

,C,A,B DESC

,C,B

,C,B,A

,C,B,A DESC

,C,A DESC

,C,A DESC,B

,C,A DESC,B DESC

,C,B DESC

,C,B DESC,A

,C,B DESC,A DESC

,A DESC

,A DESC,B

,A DESC,B,C

,A DESC,B,C DESC

,A DESC,C

,A DESC,C,B

,A DESC,C,B DESC

,A DESC,B DESC

,A DESC,B DESC,C

,A DESC,B DESC,C DESC

,A DESC,C DESC

,A DESC,C DESC,B

,A DESC,C DESC,B DESC

,B DESC

,B DESC,A

,B DESC,A,C

,B DESC,A,C DESC

,B DESC,C

,B DESC,C,A

,B DESC,C,A DESC

,B DESC,A DESC

,B DESC,A DESC,C

,B DESC,A DESC,C DESC

,B DESC,C DESC

,B DESC,C DESC,A

,B DESC,C DESC,A DESC

,C DESC

,C DESC,A

,C DESC,A,B

,C DESC,A,B DESC

,C DESC,B

,C DESC,B,A

,C DESC,B,A DESC

,C DESC,A DESC

,C DESC,A DESC,B

,C DESC,A DESC,B DESC

,C DESC,B DESC

,C DESC,B DESC,A

,C DESC,B DESC,A DESC

78 rows selected.