oracle loop ordering

for loop in oracle sql query
oracle for loop select
oracle for loop cursor
for loop with select statement in oracle example
oracle while loop example
pl/sql while loop multiple conditions
select statement inside for loop in oracle
oracle for loop list of values

I have loop script by pl/sql, my script returned order by 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;

and it returned

1
2
3
4

but I need ordering

1
4
3
2

what function should I use to solve the problem, thank you advence

As Main starting reference point of interest for indexing may be i-1 in the order by part of the sql statement.

So, the following cursor may be prefered :

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

FOR LOOP Statement, With each iteration of the FOR LOOP statement, its statements run, its index is either incremented or decremented, and control returns to the top of the loop. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows were processed.

I would expect this to return what you want:

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;

However, that is just because the data is small. Oracle doesn't guarantee the ordering of result sets without an ORDER BY. So a safer method is:

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;

LOOP Statements, If and only if the value of this expression is TRUE , the statements after LOOP execute. cursor_for_loop_statement. Issues a SQL query and loops through the rows  The loop can be made to work in the reverse order by adding the keyword 'REVERSE' before lower_limit. Example 1 : In this example, we are going to print number from 1 to 5 using FOR loop statement. For that, we will execute the following code.

Instead of using UNION operator use UNION ALL operator because UNION operator return sorted distinct result. On the other hand UNION ALL operator return duplicate unsorted result

try the below query:- 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; /

Using PL/SQL Control Structures, Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements) structure simply executes a sequence of statements in the order in which they occur. In Oracle PL/SQL, FOR LOOP with REVERSE clause is used to repeat loop iteration in reverse order. The following are the syntax and examples for REVERSE FOR LOOP.

Script: Using REVERSE in FOR Loops, Script Name Using REVERSE in FOR Loops; Description Need to 6 ORDER BY LEVEL DESC) LOOP DBMS_OUTPUT.put_line (indx. Oracle / PLSQL: Loops and Conditional Statements The following is a list of topics that explain how to use Loops and Conditional Statements in Oracle/PLSQL:

On Looping, First, and Last, If I try to use a FOR loop to iterate from FIRST to LAST and my collection is empty, PL/SQL raises a VALUE_ERROR exception. What's the best way to avoid  Note: If you pass a composite variable as a parameter to a remote subprogram, then you must create a redundant loop-back DATABASE LINK, so that when the remote subprogram compiles, the type checker that verifies the source uses the same definition of the user-defined composite variable type as the invoker uses.

On Cursor FOR Loops, But there's a problem with using a cursor FOR loop for a single-row fetch: the The resulting performance improvement can be an order of magnitude or greater. Note that Oracle Database automatically optimizes a cursor FOR LOOP to work similarly to a BULK COLLECT query. Although your code looks as if it fetched one row at a time, Oracle Database fetches multiple rows at a time and allows you to process each row individually. PL/SQL cursor FOR LOOP examples

Comments
  • It's the SQL part that controls the ordering - the loop just processes the output.
  • I want to change ordering in loop, but not select statment
  • for example DBMS_OUTPUT.PUT_LINE (mod(rec.i,4)-1 returned 2,3,4,1 - 4 is count rows
  • I want to change ordering in loop, but not select statment