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.
