FB

PL/SQL Tutorial: Chapter 3

Last updated: September 17, 2022

In this chapter, you’ll learn about:

  • Conditions (If, Then, Else)
  • Loops
  • Commenting Code

These are common features of other programming languages, and PL/SQL lets you use these features as well.

What Are Conditions?

In the code we have written so far, the database executes every line of code from top to bottom. This is acceptable for simple programs. But as you learn more and write more code, you’ll eventually want the code to do different things based on different conditions, such as:

  • Display a message if a user’s account balance is below a certain number
  • Perform a different action depending on the account type
  • Display a different value depending on the location of a user

Each of these can be done by using PL/SQL keywords that allow conditions. Conditions are where you run different lines of code based on a specific condition. You can specify the condition to check, and this check will return true or false. If it’s true, then a set of code is run.

A Simple IF Statement

An IF statement is where a condition is checked, and if it is true, a set of code is run.

This can be represented in PL/SQL code in this way:

IF (condition) THEN
  your_code;
END IF;

This looks similar in many programming languages, and the only differences are usually the keywords and symbols used.

Let’s take a look at an example. We want to find the length and width of a rectangle, and if they are the same, we will display a message saying it is a square.

DECLARE
  l_width NUMBER(5) := 20;
  l_length NUMBER(5) := 20;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  END IF;
END;

We have two variables: one for the length and one for the width. Inside the BEGIN block, we have an IF statement.

Inside the brackets of the IF statement is the condition. The condition checks that the width equals the length, which is done using the = sign. If this calculation is true, then the code inside runs, which is the PUT_LINE function.

The IF statements ends with the END IF. We then finish the code with the END statement. Also notice the lines that have a semicolon: the put_line function, the END IF, and the END statement.

If we run this code, this is output:

Statement processed.
This is a square.

This message is shown because the length and width are equal. We can change the values of the variables and see if the output changes.

DECLARE
  l_width NUMBER(5) := 18;
  l_length NUMBER(5) := 20;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  END IF;
END;

The output shows:

Statement processed.

Notice that the output does not say it is a square. This is because the IF statement does not return a true value, because the length and width are different (18 and 20).

That’s a simple IF statement in PL/SQL: code that runs if a condition is true.

Running Code When False with ELSE

In the earlier example, we had some PL/SQL code that displayed a message if the values were the same, and therefore the shape is a square. Nothing was displayed if the values were not the same.

What if we wanted to do something if these values were not the same, or if the condition was false?

We can do that using the ELSE statement. The ELSE statement runs code if the condition is false. In a diagram, it looks like this.

This can be represented in PL/SQL code in this way:

IF (condition) THEN
  your_code;
ELSE
  your_other_code;
END IF;

You simply add the ELSE keyword after the code you run if the condition is true, then add code you want to run if the condition is false.

Let’s say we wanted to expand on the earlier example to show a message if the length and width is not equal. We will display a message saying the shape is a rectangle.

Our code would look like this:

DECLARE
  l_width NUMBER(5) := 20;
  l_length NUMBER(5) := 20;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The only changes to this code are adding the ELSE statement and a put_line function to say “This is a rectangle”.

The output of this code is:

Statement processed.
This is a square.

This output is still showing the message of “square” because both the length and width variables are the same. We can change one and the output will change.

DECLARE
  l_width NUMBER(5) := 17;
  l_length NUMBER(5) := 20;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The output shows:

Statement processed.
This is a rectangle.

The code has determined that the length and width (17 and 20) are different, so it has run the code after the ELSE statement and not the IF statement.

So that’s how you can perform a simple IF statement and run code if the condition is true and different code if the condition is false.

Checking Multiple Conditions with ELSIF

Another feature of PL/SQL IF statements is the ability to have multiple conditions inside a single IF statement.

Using our earlier example, we check if the length and width are the same, display “square” if they are and “rectangle” if they are not. What if we want to add another check to see if the width is less than 0?

Our code so far hasn’t included any logic for this. If both numbers are negative, then the code will still run:

DECLARE
  l_width NUMBER(5) := -4;
  l_length NUMBER(5) := -3;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The output shows:

Statement processed.
This is a rectangle.

We want to do something if the width value is negative. We would need to add another check inside this statement. We can do that using the ELSIF keyword, which is short for “else if”.

In PL/SQL code, the code looks like this:

IF (condition) THEN
  your_code;
ELSIF (condition2) THEN
  your_second_code
ELSE
  your_other_code;
END IF;

The ELSIF goes after the code that is run for the IF statement, and before the ELSE statement. If the first IF condition is false, then the condition inside the ELSIF is checked. If that is true, then the code inside the ELSIF is run, otherwise the ELSE statement is run.

Let’s expand our example to check if the width is negative and display a message.

DECLARE
  l_width NUMBER(5) := -4;
  l_length NUMBER(5) := -3;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSIF (l_width < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width is negative.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The check of l_width < 0 will return a value of true if the l_width is negative, and the message below will be displayed. This code will only be run if the width and length are not equal.

The output is:

Statement processed.
The width is negative.

You can use the ELSIF keyword to add extra conditions to your IF statement.

What if both the width and length are negative? Which condition will be checked? They are equal and negative so they meet both conditions. Let’s test this out.

DECLARE
  l_width NUMBER(5) := -4;
  l_length NUMBER(5) := -3;
BEGIN
  IF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSIF (l_width < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width is negative.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The output shows:

Statement processed.
This is a square.

The output says it is a square. This is because the first IF statement ran, found that the values are equal, displayed the message, and then ended the IF statement. If one condition is true, the rest are skipped: they aren’t run. This is an important point to keep in mind.

This could be a problem with your application. If you would prefer to display a message about the number being negative rather than it being a square, you can move your conditions around, and place the negative check above the square check:

DECLARE
  l_width NUMBER(5) := -4;
  l_length NUMBER(5) := -4;
BEGIN
  IF (l_width < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width is negative.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The check for the width being negative comes first, and then if that is false, the length and width are checked to see if they are equal.

The output of this code is shown below:

Statement processed.
The width is negative.

We can see the output makes more sense now. The values are equal, but because the width is negative, it displays the negative message.

Multiple Criteria In A Condition

There was another issue in our code above. We have checked the width to see if it is negative, but what if the width is positive and the length is negative?

DECLARE
  l_width NUMBER(5) := 6;
  l_length NUMBER(5) := -4;
BEGIN
  IF (l_width < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width is negative.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

This code shows the following output:

Statement processed.
This is a rectangle.

We don’t want the message to say it is a rectangle. We would rather a message to say that one of the numbers is negative.

Rather than adding another IF condition, we can adjust the one we already have. PL/SQL allows you to add multiple criteria inside an IF statement condition.

For example, we could check if either the length or width is negative, and if so, display a message saying that one of the values is negative. Our code would look like this:

DECLARE
  l_width NUMBER(5) := 6;
  l_length NUMBER(5) := -4;
BEGIN
  IF (l_width < 0 OR l_length < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

Notice that our first IF statement includes this code:

l_width < 0 OR l_length < 0

The two conditions have been separated by the OR keyword. This means that if either the width < 0 or the length < 0 then the IF statement will return true, and the code underneath will run. This will mean that our example should show the right message:

Statement processed.
The width or length is negative.

This will handle situations where one of the values is negative and the other is positive.

You can also use the AND keyword to check that both conditions are true. For example, let’s say you wanted to display a separate message for “big squares” and a big square is where the length is greater than 50 and the length and width are the same. Your code would look like this:

DECLARE
  l_width NUMBER(5) := 55;
  l_length NUMBER(5) := 55;
BEGIN
  IF (l_width < 0 OR l_length < 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
  ELSIF (l_width = l_length AND l_length > 50 ) THEN
    DBMS_OUTPUT.PUT_LINE('This is a big square.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The code shows this output:

Statement processed.
This is a big square.

This code shows that the specified values make a big square.

Reversing a Condition

So far we’ve looked at code that checks that conditions are true. What if we want to check if conditions are false?

Generally, it’s better practice to check if conditions are true, and it makes for easier reading of code. However, sometimes you may need to check if something is false. This can be done with the NOT keyword.

Let’s take our earlier example and change the condition from checking if the width and length are less than zero to checking if the width and length are not greater than zero

DECLARE
  l_width NUMBER(5) := -3;
  l_length NUMBER(5) := -2;
BEGIN
  IF NOT (l_width > 0 AND l_length > 0) THEN
    DBMS_OUTPUT.PUT_LINE('The width or length is negative.');
  ELSIF (l_width = l_length AND l_length > 50 ) THEN
    DBMS_OUTPUT.PUT_LINE('This is a big square.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

The changed line of code is this one:

IF NOT (l_width > 0 AND l_length > 0) THEN

We have added the word NOT outside the brackets, which means the condition inside the brackets needs to be false for the IF statement to be true. The NOT keyword has reversed the logic.

Why did we also change the OR to AND? Because of how they work.

We could have left it like this:

IF NOT (l_width > 0 OR l_length > 0) THEN

If we did, then it will only return TRUE if both of the values are false. It can be explained by this table:

l_width l_length Length > 0 Width > 0 Using OR Using AND
-3 -2 FALSE FALSE FALSE FALSE
4 -2 TRUE FALSE TRUE FALSE
-3 5 FALSE TRUE TRUE FALSE
4 5 TRUE TRUE TRUE TRUE

The only situation where both of the numbers are positive is where AND returns true. If we use OR, the statement returns TRUE if either of the numbers is positive. This can result in the wrong message being shown in our code.

Nested IF Statements

We’ve just seen how you can use IF statements in PL/SQL. This allows you to check conditions and run different code depending on these conditions.

PL/SQL allows for IF statements to be nested inside other IF statements if that’s something you need to do to implement the logic you want.

The code would look like this:

IF (condition) THEN
  IF (condition) THEN
    your_code;
  ELSE
    more_code;
  END IF;
ELSIF (condition2) THEN
  your_second_code
ELSE
  your_other_code;
END IF;

You add in another IF statement where your code would normally go. You can use ELSE and ELSIF statements there as well.

PL/SQL supports a large number of nested IF statements, so you can nest as many as you need. However, if you start getting considering the maximum number of nesting while you’re programming, you probably need to rewrite your code. Nesting more than 4 or 5 levels deep is usually not the best way to structure your code.

Let’s see an example of a nested IF statement. We’ll adjust our earlier code to write a message that lets us know which of the values is negative.

DECLARE
  l_width NUMBER(5) := -3;
  l_length NUMBER(5) := -2;
BEGIN
  IF (l_width < 0 OR l_length < 0) THEN
    IF (l_width < 0) THEN
      DBMS_OUTPUT.PUT_LINE('The width is negative.');
    END IF;
    IF (l_length < 0) THEN
      DBMS_OUTPUT.PUT_LINE('The length is negative.');
    END IF;
  ELSIF (l_width = l_length AND l_length > 50 ) THEN
    DBMS_OUTPUT.PUT_LINE('This is a big square.');
  ELSIF (l_width = l_length) THEN
    DBMS_OUTPUT.PUT_LINE('This is a square.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('This is a rectangle.');
  END IF;
END;

This code has some nested IF statements inside the IF statement that checks for the width or length being less than 0.

This shows the following output:

Statement processed.
The width is negative.
The length is negative.

Two lines of output have been written because both of the nested IF statements are true. This shows how you can add an IF statement inside another IF statement.

The CASE Statement

IF statements are powerful. They let you check for different conditions and run different code. Your program might get to a point where there are several IF and ELSIF statements. If so, it can start to look a little messy.

You might be wondering, is there a better way to do this?

Yes, there is. It’s called a CASE statement.

A CASE statement lets you specify one or more conditions, and if the condition is true, then some code is run. It’s easier to write and read than a long IF statement.

The CASE statement looks like this:

CASE condition
WHEN value THEN code;
WHEN value THEN code;
…
ELSE default_code;
END CASE;

It looks and operates the same as a CASE statement in regular SQL.

There are a few things in this CASE statement:

  • A condition: this is the condition that is checked.
  • WHEN value: this defines the value of the condition that is used in the following code.
  • THEN code: this code is run if the value is equal to the condition.
  • ELSE default_code: this code is run if none of the specified values match the condition.

Let’s see an example. Let’s say that we wanted to show specific messages if the length is one of a few different values.

DECLARE
  l_width NUMBER(5) := 5;
  l_length NUMBER(5) := 10;
BEGIN
  CASE l_length
    WHEN 10 THEN
      DBMS_OUTPUT.PUT_LINE('The length is ten.');
    WHEN 20 THEN
      DBMS_OUTPUT.PUT_LINE('The length is twenty.');
    WHEN 30 THEN
      DBMS_OUTPUT.PUT_LINE('The length is thirty.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('The length is another value.');
  END CASE;
END;

This code will check the l_length value, and display a specific output if it is equal to 10, 20, or 30. If it’s different, it displays a different message.

If we run the code, this is what we see:

Statement processed.
The length is ten.

The output shows the message “The length is ten”. If we change the length to 30, this is what we see.

Statement processed.
The length is thirty.

If the length is not equal to any of the values in the CASE statement, we see the other message.

DECLARE
  l_width NUMBER(5) := 5;
  l_length NUMBER(5) := 10;
BEGIN
  CASE l_length
    WHEN 10 THEN
      DBMS_OUTPUT.PUT_LINE('The length is ten.');
    WHEN 20 THEN
      DBMS_OUTPUT.PUT_LINE('The length is twenty.');
    WHEN 30 THEN
      DBMS_OUTPUT.PUT_LINE('The length is thirty.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('The length is another value.');
  END CASE;
END;

The output is:

Statement processed.
The length is another value.

Only one of the conditions in the CASE statement is calculated. Unlike other programming languages, we don’t need to add a break statement to exit this code. As soon as the first criteria is met, it exits the case statement. If no matches are found, the ELSE statement is run.

What is a Loop?

We’ve just learned about IF statements and CASE statements, which let you run different pieces of code depending on different conditions.

Another useful feature of PL/SQL is a loop.

What is a loop?

A loop is a feature of programming languages that lets you run the same piece of code multiple times. You can specify the conditions that ensure the loop is run, such as the maximum number of iterations of the loop, or until a certain condition is met.

There are several ways to write loops in PL/SQL, which we will explain in this guide.

A Basic Loop

The most basic kind of loop in PL/SQL will start a loop (a set of code that is executed multiple times). It will only exit when you tell it to. It uses the following keywords:

  • LOOP to start the loop
  • END LOOP to define the end of the code that’s run as part of the loop
  • EXIT to stop running the loop

The code would look something like this:

BEGIN
  LOOP
    your_code
    IF (condition) THEN
      EXIT;
    END IF;
  END LOOP;
END;

The LOOP and END LOOP indicate that all code inside it should be run multiple times. The code inside the loop is executed, line by line. When the END LOOP is reached, the code starts again from the LOOP statement.

If an EXIT statement is reached, the loop exits and any code after the loop is then run.

Let’s see an example, where we want to display an output five times on the screen.

Our code would look like this:

BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('A message here.');
  END LOOP;
END;

If we run this code, it will keep running without stopping unless you force it to stop in your IDE (or reset your session in LiveSQL).

This is called an infinite loop. It happens because the code is looping and you haven’t told it to stop. This is an issue that happens occasionally in code, and is something to be avoided.

So how do we stop this loop from running forever? We need to tell it to exit at some point.

BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('A message here.');
    IF (condition) THEN
      EXIT;
    END IF;
  END LOOP;
END;

But what’s our condition? We want to run the loop and display the message five times, so we need to do two things:

  • Keep track of how many times the loop has run
  • Check if the loop has run five times each time it runs

We can do this using a variable:

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('A message here.');
    IF (condition) THEN
      EXIT;
    END IF;
  END LOOP;
END;

We have declared the variable l_loop_counter and assigned it a value of 0, which indicates the number of times the loop has been run. We now need to increase the value by 1 each time we run the loop.

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
  LOOP
    l_loop_counter := l_loop_counter + 1;
    DBMS_OUTPUT.PUT_LINE('A message here.');
    IF (condition) THEN
      EXIT;
    END IF;
  END LOOP;
END;

This code will increase that variable by 1 each time the loop is run. We then need to add this to our condition, so the loop exits when we want it to.

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
  LOOP
    l_loop_counter := l_loop_counter + 1;
    DBMS_OUTPUT.PUT_LINE('A message here.');
    IF (l_loop_counter = 5) THEN
      EXIT;
    END IF;
  END LOOP;
END;

This code now says the loop will exit when the l_loop_counter value equals 5. Let’s run this code:

Statement processed.
A message here.
A message here.
A message here.
A message here.
A message here.

The output shows the line “A message here” five times, which meets the criteria of our code. The loop counter starts at 0, is incremented by 1 each time it is run, and once it gets to 5 the loop exits.

You can change the value inside the condition from 5 to 10 or another number to change the number of times the loop is run.

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
  LOOP
    l_loop_counter := l_loop_counter + 1;
    DBMS_OUTPUT.PUT_LINE('A message here.');
    IF (l_loop_counter = 10) THEN
      EXIT;
    END IF;
  END LOOP;
END;

The output would then be:

Statement processed.
A message here.
A message here.
A message here.
A message here.
A message here.
A message here.
A message here.
A message here.
A message here.
A message here.

What if you wanted to show the number of the loop in each message? You can do that using concatenation which we learned about earlier. You can concatenate the loop counter variable to the message:

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
  LOOP
    l_loop_counter := l_loop_counter + 1;
    DBMS_OUTPUT.PUT_LINE('A message here: ' || l_loop_counter);
    IF (l_loop_counter = 5) THEN
      EXIT;
    END IF;
  END LOOP;
END;

This code shows the loop counter after the message:

Statement processed.
A message here: 1
A message here: 2
A message here: 3
A message here: 4
A message here: 5

Using different features of PL/SQL means you can create the programs you really need.

The For Loop

PL/SQL offers another type of loop called the FOR loop. This FOR loop allows you to define the criteria of the loop at the start, which makes it easier to see how the loop runs and easier to avoid “infinite loop” issues.

The syntax of a FOR loop looks like this:

FOR counter IN start_value ..  end_value LOOP
  your_code
END LOOP;

This includes a few things:

  • The FOR keyword starts the loop
  • The counter variable is the variable that is used as a counter for the loop. It is incremented/increased by 1 each time the loop is run.
  • The start_value is the value that the counter is initially set to.
  • The end_value is the value that the counter ends on. When the counter value is greater than the end_value, the loop exits and the code after the loop is run.

An example of the PL/SQL FOR loop is shown here.

DECLARE
  l_loop_counter NUMBER(3);
BEGIN
  FOR l_loop_counter IN 1 .. 5 LOOP
    DBMS_OUTPUT.PUT_LINE('Loop number: ' || l_loop_counter);
  END LOOP;
END;

This code is much shorter. There is no need for an IF statement to check if the counter is still in range. There is no need for a separate line to increase the value of the loop counter, as it’s done as part of the FOR loop.

Also, notice the syntax of the start and end value:

1 .. 5

It’s a number, then a space, then two periods, then a space, then another number. If it’s not written in this way, you’ll get a syntax error. I’ve done this many times – forgotten the space, only put in one period – and wondered why the code didn’t run. So make sure you check that when you write your code.

If we run this code, we get this result:

Statement processed.
Loop number: 1
Loop number: 2
Loop number: 3
Loop number: 4
Loop number: 5

We can see the output is similar, and the code is much shorter. FOR loops are great in this way: they include a lot of the parts of a normal LOOP in the syntax, which means less code.

You can change the start and ending values of the loop counter to whatever numbers you want. But the increment on each loop can only go up or down by 1:

DECLARE
  l_loop_counter NUMBER(3);
BEGIN
  FOR l_loop_counter IN 12 .. 20 LOOP
    DBMS_OUTPUT.PUT_LINE('Loop number: ' || l_loop_counter);
  END LOOP;
END;

The output shows:

Statement processed.
Loop number: 12
Loop number: 13
Loop number: 14
Loop number: 15
Loop number: 16
Loop number: 17
Loop number: 18
Loop number: 19
Loop number: 20

As you can see, the FOR loop is very useful. I generally prefer using it to the regular LOOP syntax.

The While Loop

Another type of loop offered in PL/SQL is the WHILE loop.

What is the WHILE loop?

It’s a type of loop that runs until the specified condition is met. It’s similar to the basic LOOP but includes a condition when you define it:

WHILE (condition) LOOP
  your_code
END LOOP;

This loop syntax includes a condition, which means the code inside the loop only runs if the condition is true. The code inside the loop must ensure that the condition eventually results in FALSE, otherwise, you’ll end up with an infinite loop.

For example, this code will cause an infinite loop.

DECLARE
  l_loop_counter NUMBER(3);
BEGIN
   WHILE (l_loop_counter < 5) LOOP
    DBMS_OUTPUT.PUT_LINE('Loop number: ' || l_loop_counter);
  END LOOP;
END;

This code will cause an infinite loop because there is no code that increases the l_loop_counter or that initialises it with a value. We need to add this code manually.

DECLARE
  l_loop_counter NUMBER(3) := 0;
BEGIN
   WHILE (l_loop_counter < 5) LOOP
    DBMS_OUTPUT.PUT_LINE('Loop number: ' || l_loop_counter);
    l_loop_counter := l_loop_counter + 1;
  END LOOP;
END;

The output is:

Statement processed.
Loop number: 0
Loop number: 1
Loop number: 2
Loop number: 3
Loop number: 4

The output shows 5 rows as expected. The values are 0 to 4 because the loop counter starts at zero and keeps going until it is not less than 5.

Differences Between Loop Types

As you can see, there are three different types of loops in PL/SQL. There are some slight differences between each of them though, which are summarised in this table:

Criteria Basic LOOP FOR WHILE
Includes the counter definition No Yes No
Includes the exit criteria No Yes Yes
Increments a counter No Yes No
Runs the first line of the loop Yes Yes Not always

One of the main differences between the WHILE and FOR loops is that the WHILE loop may not always run the code inside the loop. If the condition used in the WHILE loop is false when it is first checked, the code inside the loop is not run.

Conclusion

The PL/SQL language includes powerful features for running certain pieces of code in certain conditions. This includes IF THEN ELSE logic, CASE statements, and looping. There are three types of loops (Basic, FOR, and WHILE), each of them are slightly different.

Chapter Quiz

Welcome to your PLSQL Chapter 3 Quiz

1. 
What is the keyword used as part of an IF statement that runs code if none of the provided conditions are met?

2. 
What does the FOR statement do?

3. 
Which of the following pieces of code will correctly check that two conditions are true in an IF statement?

4. 
What is wrong with this code? 

DECLARE   
 l_loop_counter NUMBER(3) := 8;
BEGIN
  WHILE (l_loop_counter < 5) LOOP
 DBMS_OUTPUT.PUT_LINE('Loop number: ' || l_loop_counter);
 l_loop_counter := l_loop_counter + 1;
  END LOOP;
END;

 

< Back to Tutorial Index

Next Chapter >