In Oracle PL/SQL, a FOR LOOP is used to execute a block of code repeatedly for a fixed number of times. The syntax for a FOR LOOP is as follows:
FOR loop_counter IN start_value..end_value LOOP -- code to be executed END LOOP;
The loop_counter is a variable that is used to control the loop and is incremented or decremented on each iteration. The start_value and end_value are the range of values that the loop_counter will take on during the execution of the loop. The code inside the loop is executed for each value of the loop_counter within the specified range.
It’s also possible to use a FOR LOOP with a cursor, which allows you to process each row returned by a SELECT statement. The syntax for this type of loop is as follows:
FOR cursor_name IN (SELECT statement) LOOP -- code to be executed END LOOP;
The cursor_name is a variable that represents the cursor, and the SELECT statement is used to retrieve the data that will be processed in the loop.
For example, the following code uses a FOR LOOP to print the numbers from 4 to 6:
FOR LOOP example
DECLARE v_num NUMBER:=3; BEGIN FOR i IN 1..v_num LOOP v_num:=v_num+1; DBMS_OUTPUT.put_line(v_num); END LOOP; END;
It’s also possible to use a FOR LOOP with a cursor to process each row returned by a SELECT statement.
FOR emp_rec IN (SELECT * FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name); END LOOP;
This code will retrieve all rows from the employees table, and for each row, the first_name and last_name columns are concatenated and printed.