Oracle FOR LOOP REVERSE Example

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.

Syntax

FOR n IN REVERSE start_number .. end_number LOOP
-- statement to execute in every iteration
END LOOP;

Oracle FOR LOOP REVERSE Examples

1. Print Number in Reverse Order

In the following example, it will print the number from 1 to 5 in reverse order.

SET SERVEROUTPUT ON;
BEGIN
FOR i IN REVERSE 1 .. 5
LOOP
DBMS_OUTPUT.put_line (i);
END LOOP;
END;
/

Output:

5
4
3
2
1
PL/SQL procedure successfully completed.

2. Print Date in Reverse Order

In the following example, it will print date from 1st Jan 2018 to 5th Jan 2018 in reverse order. Here is the little tricky part, because FOR LOOP only increment or decrement the numbers, so first I am converting the date to a number and then converting back to the date when printing.

SET SERVEROUTPUT ON;
DECLARE
start_date DATE := '01jan2018';
end_date DATE := '05jan2018';
BEGIN
FOR i IN REVERSE TO_CHAR (start_date, 'yyyymmdd') .. TO_CHAR (end_date,
'yyyymmdd')
LOOP
DBMS_OUTPUT.put_line (TO_CHAR (TO_DATE (i, 'yyyymmdd'), 'dd/mm/yyyy'));
END LOOP;
END;
/

Output:

05/01/2018
04/01/2018
03/01/2018
02/01/2018
01/01/2018
PL/SQL procedure successfully completed.

See also:

• Oracle TO_CHAR(number) Examples
• Oracle TO_CHAR(date) Examples
• Oracle TO_DATE Function Examples