PL/SQL, the procedural language for the Oracle Database, offers several types of loops that can be used to perform repetitive tasks. In this tutorial, we will explore the different types of loops available in PL/SQL, including the basic loop structure, the FOR loop, the WHILE loop, the FOR loop with a CURSOR, as well as the EXIT and CONTINUE statements. We will also look at examples of nested loops and how they can be used to process data in a certain way. By the end of this tutorial, you will have a solid understanding of how to use loops in PL/SQL to streamline your code and improve your database application's performance.

Loop in PL/SQL Examples

1. Basic Loop Structure

The basic structure of a loop in PL/SQL is as follows:

BEGIN
   LOOP
      -- code to be executed
   END LOOP;
END;

2. FOR Loop

A FOR loop is used to execute a set of statements a specific number of times. The syntax of a FOR loop is as follows:

FOR counter IN start_value..end_value
LOOP
   -- code to be executed
END LOOP;

Example:

BEGIN
   FOR i IN 1..5
   LOOP
      DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
   END LOOP;
END;

This will output:

Value of i: 1
Value of i: 2
Value of i: 3
Value of i: 4
Value of i: 5

3. WHILE Loop

A WHILE loop is used to execute a set of statements as long as a given condition is true. The syntax of a WHILE loop is as follows:

WHILE condition
LOOP
   -- code to be executed
END LOOP;

Example:

BEGIN
   i := 1;
   WHILE i <= 5
   LOOP
      DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
      i := i + 1;
   END LOOP;
END;

This will output:

Value of i: 1
Value of i: 2
Value of i: 3
Value of i: 4
Value of i: 5

4. FOR Loop with CURSOR

A FOR loop can also be used with a CURSOR to fetch and process rows returned by a SELECT statement. The syntax of a FOR loop with a CURSOR is as follows:

FOR record IN (SELECT columns FROM table)
LOOP
   -- code to process record
END LOOP;

Example:

DECLARE
   CURSOR employees_cur IS SELECT first_name, last_name FROM employees;
   employee_rec employees_cur%ROWTYPE;
BEGIN
   FOR employee_rec IN employees_cur
   LOOP
      DBMS_OUTPUT.PUT_LINE(employee_rec.first_name || ' ' || employee_rec.last_name);
   END LOOP;
END;

This will output the first_name and last_name of all the employees in the table employees

5. EXIT and CONTINUE Statement

The EXIT statement is used to exit a loop prematurely and the CONTINUE statement is used to skip an iteration of a loop.

Example:

BEGIN
   FOR i IN 1..5
   LOOP
      IF i = 3 THEN
         DBMS_OUTPUT.PUT_LINE('Skipping iteration ' || i);
         CONTINUE;
      END IF;
      DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Exiting loop');
END;

This will output:

Value of i: 1
Value of i: 2
Skipping iteration 3
Value of i: 4
Value of i: 5
Exiting loop

Notice that the iteration 3 is skipped and it continues to the next iteration and at the end it exits the loop.

6. Nested Loop

A loop can be nested within another loop. This can be useful when you need to process data in a certain way.

Example:

BEGIN
   FOR i IN 1..3
   LOOP
      FOR j IN 1..3
      LOOP
         DBMS_OUTPUT.PUT_LINE('Value of i: ' || i || ', Value of j: ' || j);
      END LOOP;
   END LOOP;
END;

This will output:

Value of i: 1, Value of j: 1
Value of i: 1, Value of j: 2
Value of i: 1, Value of j: 3
Value of i: 2, Value of j: 1
Value of i: 2, Value of j: 2
Value of i: 2, Value of j: 3
Value of i: 3, Value of j: 1
Value of i: 3, Value of j: 2
Value of i: 3, Value of j: 3

In this example, the outer loop iterates 3 times, and for each iteration of the outer loop, the inner loop iterates 3 times. Therefore, this code will output 9 lines in total.

7. Conclusion

In this tutorial, we've covered the basics of looping in PL/SQL. We've discussed the syntax and usage of the FOR, WHILE, and FOR with CURSOR loops, as well as the EXIT and CONTINUE statements. Additionally, we also looked at nested loops and how they can be used to process data in a certain way.

It's important to note that you should use the appropriate loop type for the task at hand, and also to be mindful of the performance impact of your loops.