Control structures are fundamental to any programming language, and PL/SQL is no exception. They allow you to control the flow of execution in your PL/SQL programs. In this section, we will cover the following control structures:
- Conditional Control: IF Statements
- Iterative Control: LOOP Statements
- Sequential Control: GOTO Statements
- Conditional Control: IF Statements
The IF statement is used to execute a block of code based on a condition. PL/SQL supports several forms of the IF statement:
Simple IF Statement
Example
DECLARE
v_salary NUMBER := 3000;
BEGIN
IF v_salary > 2500 THEN
DBMS_OUTPUT.PUT_LINE('Salary is above 2500');
END IF;
END;
/IF-THEN-ELSE Statement
IF condition THEN
-- statements to execute if condition is true
ELSE
-- statements to execute if condition is false
END IF;Example
DECLARE
v_salary NUMBER := 2000;
BEGIN
IF v_salary > 2500 THEN
DBMS_OUTPUT.PUT_LINE('Salary is above 2500');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is 2500 or below');
END IF;
END;
/IF-THEN-ELSIF-ELSE Statement
IF condition1 THEN
-- statements to execute if condition1 is true
ELSIF condition2 THEN
-- statements to execute if condition2 is true
ELSE
-- statements to execute if none of the above conditions are true
END IF;Example
DECLARE
v_salary NUMBER := 2500;
BEGIN
IF v_salary > 3000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is above 3000');
ELSIF v_salary = 3000 THEN
DBMS_OUTPUT.PUT_LINE('Salary is exactly 3000');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary is below 3000');
END IF;
END;
/
- Iterative Control: LOOP Statements
PL/SQL provides several types of loops to execute a block of code multiple times:
Basic LOOP
Example
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 5;
END LOOP;
END;
/WHILE LOOP
Example
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/FOR LOOP
Example
- Sequential Control: GOTO Statements
The GOTO statement transfers control to a labeled statement. It is generally discouraged due to its potential to create complex and hard-to-read code.
Syntax
Example
DECLARE
v_counter NUMBER := 1;
BEGIN
<<start_loop>>
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
v_counter := v_counter + 1;
IF v_counter <= 5 THEN
GOTO start_loop;
END IF;
END;
/Practical Exercises
Exercise 1: Using IF Statements
Write a PL/SQL block that checks if a number is positive, negative, or zero and prints an appropriate message.
DECLARE
v_number NUMBER := -5;
BEGIN
IF v_number > 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is positive');
ELSIF v_number < 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is negative');
ELSE
DBMS_OUTPUT.PUT_LINE('The number is zero');
END IF;
END;
/Exercise 2: Using LOOP Statements
Write a PL/SQL block that prints the first 10 natural numbers using a FOR loop.
Exercise 3: Using GOTO Statements
Write a PL/SQL block that uses a GOTO statement to print numbers from 1 to 5.
DECLARE
v_counter NUMBER := 1;
BEGIN
<<start_loop>>
DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
v_counter := v_counter + 1;
IF v_counter <= 5 THEN
GOTO start_loop;
END IF;
END;
/Common Mistakes and Tips
- Overusing GOTO: Avoid using
GOTOstatements as they can make your code difficult to read and maintain. - Infinite Loops: Ensure that your loops have a proper exit condition to avoid infinite loops.
- Condition Order: In
IF-THEN-ELSIF-ELSEstatements, order your conditions from most specific to most general to ensure correct execution.
Conclusion
In this section, we covered the essential control structures in PL/SQL, including conditional control with IF statements, iterative control with various LOOP statements, and sequential control with GOTO statements. Understanding and using these control structures effectively will allow you to write more dynamic and flexible PL/SQL programs. In the next section, we will delve into cursors, which are crucial for handling query results in PL/SQL.