oracle循环排序

我希望这能回报你想要的:

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        SELECT 1 as i from dual
        UNION ALL
        SELECT 4 as i from dual
        UNION ALL
        SELECT 3 as i from dual
        UNION ALL
        SELECT 2 as i from dual
     ) LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

然而,这只是因为数据很小。Oracle不保证在没有 ORDER BY 因此,更安全的方法是:

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        SELECT i
        FROM (SELECT 1 as i, 1 as ord from dual UNION ALL
              SELECT 4 as i, 2 from dual UNION ALL
              SELECT 3 as i, 3 from dual UNION ALL
              SELECT 2 as i, 4 from dual
             ) x
        ORDER BY ord
     ) LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

索引的主要起始参考点可能是 i-1 在sql语句的order by部分中。

因此,可能首选以下光标:

SELECT * FROM
(
 SELECT 1 as i from dual
 UNION
 SELECT 2 as i from dual
 UNION
 SELECT 3 as i from dual
 UNION
 SELECT 4 as i from dual
)
ORDER BY sign(i-1), (i-1) desc;

SQL Fiddle Demo

而不是使用 协会 操作员使用 联合所有 操作员,因为 协会 操作员返回 已排序的非重复 后果另一方面 联合所有 操作员返回 复制未排序 后果

尝试以下查询:- SET SERVEROUTPUT ON; DECLARE i NUMBER; BEGIN FOR rec IN ( SELECT 1 as i from dual UNION ALL SELECT 4 as i from dual UNION ALL SELECT 3 as i from dual UNION ALL SELECT 2 as i from dual ) LOOP DBMS_OUTPUT.PUT_LINE (rec.i); END LOOP; end; /