There are several ways to loop through data in Oracle, here are a few common ones:
FOR LOOP Statement:
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;In this example, the FOR loop will iterate 10 times and print the value of i each time. The loop variable i is initialized to 1 and incremented by 1 each iteration until it reaches the end value of 10.
WHILE LOOP Statement:
DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;
In this example, the WHILE loop will iterate 10 times and print the value of i each time. The loop variable i is initialized to 1 and incremented by 1 each iteration until the condition i <= 10 is no longer true.
FOR LOOP over a Cursor:
DECLARE
CURSOR c_employees IS
SELECT first_name, last_name
FROM employees;
BEGIN
FOR r_employees IN c_employees LOOP
DBMS_OUTPUT.PUT_LINE(r_employees.first_name || ' ' || r_employees.last_name);
END LOOP;
END;
In this example, the FOR loop will iterate over each row returned by the cursor c_employees. The loop variable r_employees is a record that holds the values of the current row, and the values can be accessed by field name (e.g., r_employees.first_name). The loop will continue until all rows have been processed.
You can use the BULK COLLECT statement to retrieve multiple rows of data into a PL/SQL collection in a single fetch, which can greatly improve performance compared to fetching the data one row at a time.
BULK COLLECT in a FOR loop in Oracle:
DECLARE
TYPE t_employees IS TABLE OF employees%ROWTYPE;
employees_table t_employees;
BEGIN
SELECT * BULK COLLECT INTO employees_table
FROM employees;
FOR i IN employees_table.FIRST..employees_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(employees_table(i).first_name || ' ' || employees_table(i).last_name);
END LOOP;
END;
In this example, the TYPE statement declares a PL/SQL collection t_employees of employees%ROWTYPE, which is a record type that holds the columns of the employees table. The SELECT statement retrieves all rows from the employees table into the collection employees_table using the BULK COLLECT clause.
The FOR loop then iterates over the elements of the collection, and the loop variable i is used to access each element of the collection. In this example, the first name and last name of each employee are concatenated and printed using the DBMS_OUTPUT.PUT_LINE statement.
The BULK COLLECT statement can be much more efficient than fetching the data one row at a time, as it reduces the number of context switches between PL/SQL and the SQL engine.
