时间戳

首页 > 解决方案 > oracle循环排序

sql - oracle循环排序

问题描述

我有 pl/sql 的循环脚本，我的脚本按 i asc 返回的顺序，

DECLARE 
    i NUMBER;
BEGIN
    FOR rec IN (
        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
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE (rec.i); 
    END LOOP; 
end;

它回来了

1
2
3
4

但我需要订购

1
4
3
2

我应该使用什么功能来解决问题，谢谢你advence

标签： sqloracleoracle11gsql-order-by

解决方案

我希望这能返回你想要的：

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;

推荐阅读