.jpg)
Introduction:
Control statements play a crucial role in any programming language, and PL/SQL
is no exception. These statements provide developers with the power to control
the flow of execution, make decisions based on certain conditions, and loop
through a set of instructions. In this blog, we will dive deep into the world
of control statements in PL/SQL, exploring their different types and
understanding how they can be effectively used to enhance the functionality
and flexibility of your code.
Conditional Selection Statements:
Decision Making Structure require that the programmer specify one or more
condition to be evaluated or tested by the programmer along with a statement
or statement to be executed if the condition is determined to be true and
optionally other statements to be executed if the condition is determined to
be false.
|
| Decision Making Structure |
PL/SQL Programming Language provides following type of decision making
statements:
1) IF - THEN
It is the simplest form of if control statement frequently used in decision
making and changing the control flow of the program execution.
Syntax:
IF condition THEN Statement: {It is executed when condition is true} END IF;
If the Boolean Expression condition evaluates to true then the block of code
inside the if statement will be executed, if the Boolean Expression evaluates
to be false then the first set of code after the end of the if statement will
be executed.
Example:
DECLARE age NUMBER := 25; BEGIN IF age >= 18 THEN DBMS_OUTPUT.PUT_LINE('You are eligible to vote.'); END IF; DBMS_OUTPUT.PUT_LINE('End of the program.'); END;
After the IF statement, the program continues executing the next line, which is DBMS_OUTPUT.PUT_LINE('End of the program.');. This line will always be executed, regardless of the outcome of the IF condition.
If age is greater than or equal to 18, the output will be:
You are eligible to vote. End of the program.
End of the program.
2) IF - THEN - ELSE
A sequence of IF-THEN Statement can be follow by optional sequence of ELSE
statement which executes when the condition is false.
|
| Decision Making Structure ( IF-THEN-ELSE ) |
Syntax:
IF condition THEN {...statements to execute when condition is TRUE...} ELSE {...statements to execute when condition is FALSE...} END IF;
- The Statement 1 and Statement 2 are different sequence of statements.
- In IF-THEN-ELSE when the test condition is true then S1 is executed and S2 is skipped if when test condition is false then S1 is bypassed and S2 is executed.
Example:
DECLARE age NUMBER := 15; BEGIN IF age >= 18 THEN DBMS_OUTPUT.PUT_LINE('You are eligible to vote.'); ELSE DBMS_OUTPUT.PUT_LINE('You are not eligible to vote yet.'); END IF; DBMS_OUTPUT.PUT_LINE('End of the program.'); END;
After the IF-ELSE statement, the program continues executing the next line, which is DBMS_OUTPUT.PUT_LINE('End of the program.');. This line will always be executed, regardless of the outcome of the IF condition.
Since age is less than 18 in this example, the output will be:
You are not eligible to vote yet. End of the program.
LOOP
There may be a situation when you need to execute a block of code several
number of times in general statements are executed sequentially.
The first statement in a function is executed first follow by the second and
so on.
|
| Decision Making Structure ( LOOP ) |
Syntax for a basic loop:
LOOP Sequence of statements; END LOOP;
Types of PL/SQL Loops
There are 4 types of PL/SQL Loops.1) Basic Loop / Exit Loop
2) While Loop
3) For Loop
2) While Loop
3) For Loop
PL/SQL Exit Loop (Basic Loop)
In PL/SQL, the EXIT statement is used to terminate a loop prematurely. When
the EXIT statement is encountered within a loop, the control immediately
exits the loop and continues with the next statement after the loop. This
allows you to control the flow of execution and exit the loop based on
certain conditions.
Syntax:
LOOP statements; EXIT; {or EXIT WHEN condition;} END LOOP;
Here's an example of using the EXIT statement in a basic loop (also known as
a loop without a specific termination condition):
DECLARE counter NUMBER := 1; BEGIN LOOP -- Print the value of the counter DBMS_OUTPUT.PUT_LINE('Counter: ' || counter); -- Check if the counter reaches 5 IF counter = 5 THEN EXIT; -- Exit the loop if counter = 5 END IF; -- Increment the counter counter := counter + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('End of the loop.'); END;
In this example, we have a basic loop that iterates indefinitely. Within the
loop, we print the value of the `counter` variable using the
`DBMS_OUTPUT.PUT_LINE` statement. Then, we check if the
`counter` reaches 5 using an IF statement. If the condition evaluates
to true, the EXIT statement is executed, and the loop is terminated.
After the EXIT statement, the program continues executing the next line,
which is `DBMS_OUTPUT.PUT_LINE('End of the loop.');`.
The output of this example will be:
Counter: 1 Counter: 2 Counter: 3 Counter: 4 Counter: 5 End of the loop.
As you can see, the loop terminates when the `counter` reaches 5 due
to the EXIT statement.
The EXIT statement is useful when you need to prematurely exit a loop based
on a specific condition. It allows you to control the flow of execution and
optimize the loop's behavior according to your requirements.
PL/SQL WHILE Loop
In PL/SQL, the WHILE loop is used to repeatedly execute a block of code as
long as a specified condition remains true. It provides a way to iterate
based on a condition, allowing you to control the flow of execution. The
condition is checked before each iteration, and if it evaluates to true,
the loop body is executed. If the condition evaluates to false initially,
the loop body is skipped entirely.
Here's the syntax for the WHILE loop in PL/SQL:
WHILE condition LOOP -- Statements to be executed statement1; statement2; ... END LOOP;
The condition is a Boolean expression that determines whether the loop
should continue or terminate. If the condition evaluates to true, the
statements within the loop body are executed. After each iteration, the
condition is checked again, and the loop continues as long as the
condition remains true. Once the condition evaluates to false, the control
moves to the next statement after the loop.
Here's an example that demonstrates the usage of the WHILE loop in PL/SQL:
DECLARE counter NUMBER := 1; BEGIN WHILE counter <= 5 LOOP -- Print the value of the counter DBMS_OUTPUT.PUT_LINE('Counter: ' || counter); -- Increment the counter counter := counter + 1; END LOOP; DBMS_OUTPUT.PUT_LINE('End of the loop.'); END;
In this example, we initialize a variable `counter` to 1. The WHILE
loop checks if the `counter` is less than or equal to 5. If the
condition is true, the statements within the loop body are executed, which
includes printing the value of `counter` and incrementing it by 1.
The loop continues as long as the `counter` is less than or equal
to 5.
The output of this example will be:
Counter: 1 Counter: 2 Counter: 3 Counter: 4 Counter: 5 End of the loop.
As you can see, the loop iterates five times, printing the value of
`counter` from 1 to 5.
The WHILE loop provides flexibility when you need to repeat a set of
statements based on a condition. It allows you to control the iteration
process and execute code dynamically as long as the condition remains
true.
PL/SQL FOR Loop
In PL/SQL, the FOR loop is used to iterate over a specified range of
values or a collection. It provides a convenient way to execute a block of
code for each iteration, eliminating the need for maintaining a loop
counter manually. The FOR loop is commonly used when you know the number
of iterations in advance.
Here's the syntax for the FOR loop in PL/SQL:
FOR loop_counter IN lower_bound..upper_bound LOOP -- Statements to be executed statement1; statement2; ... END LOOP;
In this syntax:
- `loop_counter` is the loop counter variable that takes on the
values from the lower_bound to the upper_bound.
- `lower_bound` and `upper_bound` define the range of values
for the loop counter.
Here's an example that demonstrates the usage of the FOR loop in PL/SQL:
BEGIN FOR i IN 1..5 LOOP -- Print the value of i DBMS_OUTPUT.PUT_LINE('Value of i: ' || i); END LOOP; DBMS_OUTPUT.PUT_LINE('End of the loop.'); END;
In this example, the FOR loop iterates from 1 to 5. In each iteration, the
value of `i` is printed using the `DBMS_OUTPUT.PUT_LINE`
statement. After the loop completes, the statement "End of the loop." is
printed.
The output of this example will be:
Value of i: 1 Value of i: 2 Value of i: 3 Value of i: 4 Value of i: 5 End of the loop.
As you can see, the loop iterates five times, with the value of `i`
ranging from 1 to 5.
The FOR loop simplifies the process of iterating over a range of values.
It automatically handles the loop counter and eliminates the need for
manual counter management. It is especially useful when you know the exact
number of iterations required.
PL/SQL Continue Statement
In PL/SQL, the CONTINUE statement is used within a loop to skip the
current iteration and proceed with the next iteration. It provides a way
to control the flow of execution within the loop based on certain
conditions. When the CONTINUE statement is encountered, the control jumps
to the next iteration of the loop, bypassing the remaining statements
within the current iteration.
Here's the syntax for the CONTINUE statement in PL/SQL:
LOOP -- Statements IF condition THEN CONTINUE; END IF; -- More statements END LOOP;
In this syntax:
- The `LOOP` keyword indicates the beginning of the loop.
- Within the loop, you can place the CONTINUE statement where you want to
skip the current iteration and move to the next iteration.
- The `IF` statement is used to check a condition. If the condition
evaluates to true, the CONTINUE statement is executed, and the control
moves to the next iteration.
- The `END IF;` statement marks the end of the IF condition.
Here's an example that demonstrates the usage of the CONTINUE statement in
PL/SQL:
BEGIN FOR i IN 1..5 LOOP -- Skip the iteration if i is an even number IF i MOD 2 = 0 THEN CONTINUE; END IF; -- Print the value of i for odd numbers only DBMS_OUTPUT.PUT_LINE('Odd number: ' || i); END LOOP; DBMS_OUTPUT.PUT_LINE('End of the loop.'); END;
In this example, the FOR loop iterates from 1 to 5. In each iteration, the
IF statement checks if `i` is an even number using the `MOD`
operator. If the condition is true, the CONTINUE statement is executed,
and the control moves to the next iteration, skipping the remaining
statements within the loop body. For odd numbers, the value of `i`
is printed using the `DBMS_OUTPUT.PUT_LINE` statement.
The output of this example will be:
Odd number: 1 Odd number: 3 Odd number: 5 End of the loop.
As you can see, the even numbers (2 and 4) are skipped due to the CONTINUE
statement, and only the odd numbers are printed.
The CONTINUE statement allows you to control the flow of execution within
a loop. It provides the flexibility to skip certain iterations based on
specific conditions, helping you customize the behavior of your loop as
per your requirements.
PL/SQL GOTO Statement
In PL/SQL, the GOTO statement is used to transfer control to a specified
label within a block of code. It allows you to jump to a specific point
in your code, bypassing any statements in between. The GOTO statement
can be useful in certain scenarios but should be used with caution, as
it can make code harder to understand and maintain.
Here's the syntax for the GOTO statement in PL/SQL:
GOTO label_name;
In this syntax:
- `label_name` is the identifier assigned to a specific point in
the code where you want to jump.
Here's an example that demonstrates the usage of the GOTO statement in
PL/SQL:
DECLARE age NUMBER := 15; BEGIN IF age < 18 THEN -- Jump to the label if age is less than 18 GOTO underage; END IF; DBMS_OUTPUT.PUT_LINE('You are eligible to vote.'); <<underage>> DBMS_OUTPUT.PUT_LINE('You are not eligible to vote yet.'); DBMS_OUTPUT.PUT_LINE('End of the program.'); END;
In this example, we have a variable `age` set to 15. The IF
statement checks if `age` is less than 18. If the condition is
true, the GOTO statement is executed, and the control jumps to the
`underage` label. The statements following the label are
executed, which in this case prints the message "You are not eligible to
vote yet."
After the execution of the statements following the label, the control
continues with the next statement after the GOTO statement, which in
this case is the line `DBMS_OUTPUT.PUT_LINE('End of the program.');`.
The output of this example will be:
You are not eligible to vote yet. End of the program.
As you can see, the message "You are eligible to vote." is not printed
because the GOTO statement transfers the control to the
`underage` label.
The GOTO statement should be used sparingly and only when necessary, as
it can make code harder to understand and maintain. Overusing the GOTO
statement can lead to complex and error-prone code. It is generally
recommended to use structured control flow constructs like IF-THEN-ELSE
and loops instead of GOTO statements, as they provide more readable and
maintainable code.
NULL Statement:
The NULL statement in PL/SQL is a placeholder statement that does
nothing. It is used when you need a statement syntactically but do not
require any action to be performed. The NULL statement is often used in
conditional branches where a statement is not needed, or in situations
where you want to indicate that no action is required at a particular
point in the code.
The NULL statement has the following syntax:
NULL;
The NULL statement is typically used in control structures like
IF-THEN-ELSE or CASE statements when you need to include a branch that
does not require any action. It serves as a placeholder to maintain the
syntactic structure of the code.
Here's an example that demonstrates the usage of the NULL statement:
DECLARE age NUMBER := 20; BEGIN IF age < 18 THEN NULL; -- No action needed for this branch ELSE DBMS_OUTPUT.PUT_LINE('You are eligible to vote.'); END IF; DBMS_OUTPUT.PUT_LINE('End of the program.'); END;
In this example, we have a variable `age` set to 20. The IF-THEN-ELSE
statement checks if `age` is less than 18. If the condition is true, the
NULL statement is encountered, indicating that no action is required for
that branch. If the condition is false, the code inside the ELSE branch
is executed, which includes printing the message "You are eligible to
vote."
The output of this example will be:
You are eligible to vote. End of the program.
As you can see, the NULL statement serves as a placeholder in the
IF-THEN-ELSE structure to maintain the syntactic structure of the code,
even though no action is needed in that particular branch.
The NULL statement is useful in cases where you need a statement
syntactically but do not require any action to be performed. It helps
maintain the structure and readability of the code by indicating that no
specific action is needed at a particular point.
Conclusion:
Control statements are an integral part of any programming language, and PL/SQL provides a rich set of control statements to help you shape the flow of execution in your code. In this blog, we explored the different types of control statements in PL/SQL, including conditional selection statements, loop statements, and sequential control statements. By mastering these control statements, you can effectively handle complex decision-making scenarios, repetitive tasks, and alter the flow of execution as per your requirements.
Remember, control statements are powerful tools, and it's essential to use them judiciously and maintain code readability. As you continue your PL/SQL programming journey, experiment with different control statements and leverage their flexibility to write efficient and robust code.
So, dive in, explore, and unlock the full potential of control statements in PL/SQL!
Happy coding!
Remember, control statements are powerful tools, and it's essential to use them judiciously and maintain code readability. As you continue your PL/SQL programming journey, experiment with different control statements and leverage their flexibility to write efficient and robust code.
So, dive in, explore, and unlock the full potential of control statements in PL/SQL!
Happy coding!
.jpg)
