PL/SQL Control Structures

Control structures in PL/SQL allow you to dictate the flow of execution within your programs. They enable conditional execution, looping, and branching, which are essential for writing complex and efficient code.

1. Conditional Statements

Conditional statements allow you to execute certain parts of your code based on specific conditions.

1.1 IF...THEN Statement

Executes a sequence of statements if a condition is TRUE.

Syntax:

plsql
Copy code
IF condition THEN
   -- Statements to execute when condition is TRUE
END IF;

Example:

plsql
Copy code
DECLARE
   v_age NUMBER := 20;
BEGIN
   IF v_age >= 18 THEN
      DBMS_OUTPUT.PUT_LINE('You are eligible to vote.');
   END IF;
END;
/

Explanation:

• Checks if v_age is greater than or equal to 18.
• If the condition is TRUE, it prints a message.

1.2 IF...THEN...ELSE Statement

Provides an alternative sequence of statements if the condition is FALSE.

Syntax:

plsql
Copy code
IF condition THEN
   -- Statements when condition is TRUE
ELSE
   -- Statements when condition is FALSE
END IF;

Example:

plsql
Copy code
DECLARE
   v_score NUMBER := 45;
BEGIN
   IF v_score >= 50 THEN
      DBMS_OUTPUT.PUT_LINE('Passed the exam.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Failed the exam.');
   END IF;
END;
/

Explanation:

• If v_score is 50 or more, prints 'Passed the exam.'
• Otherwise, prints 'Failed the exam.'

1.3 IF...ELSIF...ELSE Statement

Handles multiple conditions in a sequence.

Syntax:

plsql
Copy code
IF condition1 THEN
   -- Statements when condition1 is TRUE
ELSIF condition2 THEN
   -- Statements when condition2 is TRUE
ELSE
   -- Statements when none of the above conditions are TRUE
END IF;

Example:

plsql
Copy code
DECLARE
   v_marks NUMBER := 85;
BEGIN
   IF v_marks >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: A');
   ELSIF v_marks >= 75 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: B');
   ELSIF v_marks >= 60 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: C');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Grade: D');
   END IF;
END;
/

Explanation:

• Evaluates each condition in order.
• Executes the block corresponding to the first TRUE condition.

1.4 CASE Statement

An alternative to multiple IF...ELSIF statements; more readable when dealing with multiple conditions.

Syntax (Simple CASE):

plsql
Copy code
CASE expression
   WHEN value1 THEN
      -- Statements
   WHEN value2 THEN
      -- Statements
   ELSE
      -- Statements
END CASE;

Syntax (Searched CASE):

plsql
Copy code
CASE
   WHEN condition1 THEN
      -- Statements
   WHEN condition2 THEN
      -- Statements
   ELSE
      -- Statements
END CASE;

Example (Simple CASE):

plsql
Copy code
DECLARE
   v_grade CHAR(1) := 'B';
BEGIN
   CASE v_grade
      WHEN 'A' THEN
         DBMS_OUTPUT.PUT_LINE('Excellent');
      WHEN 'B' THEN
         DBMS_OUTPUT.PUT_LINE('Good');
      WHEN 'C' THEN
         DBMS_OUTPUT.PUT_LINE('Average');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Poor');
   END CASE;
END;
/

Example (Searched CASE):

plsql
Copy code
DECLARE
   v_salary NUMBER := 70000;
BEGIN
   CASE
      WHEN v_salary >= 100000 THEN
         DBMS_OUTPUT.PUT_LINE('High Income');
      WHEN v_salary >= 50000 THEN
         DBMS_OUTPUT.PUT_LINE('Middle Income');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Low Income');
   END CASE;
END;
/

Explanation:

• Simple CASE compares an expression to multiple values.
• Searched CASE evaluates multiple conditions.

2. Loops

Loops allow you to execute a sequence of statements multiple times.

2.1 Simple Loop

Executes the enclosed statements repeatedly until an EXIT condition is met.

Syntax:

plsql
Copy code
LOOP
   -- Statements
   EXIT WHEN condition;
END LOOP;

Example:

plsql
Copy code
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;
/

Explanation:

• Prints the counter value.
• Increments v_counter.
• Exits the loop when v_counter exceeds 5.

2.2 WHILE Loop

Executes the loop as long as the condition is TRUE.

Syntax:

plsql
Copy code
WHILE condition LOOP
   -- Statements
END LOOP;

Example:

plsql
Copy code
DECLARE
   v_number NUMBER := 1;
BEGIN
   WHILE v_number <= 5 LOOP
      DBMS_OUTPUT.PUT_LINE('Number: ' || v_number);
      v_number := v_number + 1;
   END LOOP;
END;
/

Explanation:

• Checks the condition before each iteration.
• Continues looping while v_number is less than or equal to 5.

2.3 FOR Loop

Automatically manages the loop counter; runs for a specified range.

Syntax:

plsql
Copy code
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
   -- Statements
END LOOP;

Example (Incrementing):

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

Example (Decrementing with REVERSE):

plsql
Copy code
BEGIN
   FOR i IN REVERSE 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Value of i: ' || i);
   END LOOP;
END;
/

Explanation:

• Incrementing Loop: i goes from 1 to 5.
• Decrementing Loop: i goes from 5 down to 1 using REVERSE.

3. Example: Calculating Factorial Using Loops

Calculating the factorial of a number using different types of loops.

3.1 Using a FOR Loop

plsql
Copy code
DECLARE
   v_num NUMBER := 5;
   v_factorial NUMBER := 1;
BEGIN
   FOR i IN 1..v_num LOOP
      v_factorial := v_factorial * i;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_num || ' is ' || v_factorial);
END;
/

Explanation:

• Initializes v_factorial to 1.
• Multiplies v_factorial by each number from 1 to v_num.

3.2 Using a WHILE Loop

plsql
Copy code
DECLARE
   v_num NUMBER := 5;
   v_factorial NUMBER := 1;
   v_counter NUMBER := 1;
BEGIN
   WHILE v_counter <= v_num LOOP
      v_factorial := v_factorial * v_counter;
      v_counter := v_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_num || ' is ' || v_factorial);
END;
/

Explanation:

• Uses v_counter to control the loop.
• Multiplies v_factorial by v_counter in each iteration.

3.3 Using a Simple Loop

plsql
Copy code
DECLARE
   v_num NUMBER := 5;
   v_factorial NUMBER := 1;
   v_counter NUMBER := 1;
BEGIN
   LOOP
      EXIT WHEN v_counter > v_num;
      v_factorial := v_factorial * v_counter;
      v_counter := v_counter + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_num || ' is ' || v_factorial);
END;
/

Explanation:

• Uses a LOOP with an EXIT WHEN condition.
• Similar logic to the WHILE loop but checks the exit condition inside the loop.

Putting It All Together

Control structures are essential for building logic in PL/SQL programs. By mastering conditional statements and loops, you can create dynamic and efficient database applications.

Additional Examples

Nested IF Statements:

plsql
Copy code
DECLARE
   v_age NUMBER := 25;
   v_country VARCHAR2(10) := 'USA';
BEGIN
   IF v_country = 'USA' THEN
      IF v_age >= 21 THEN
         DBMS_OUTPUT.PUT_LINE('You are legally an adult in the USA.');
      ELSE
         DBMS_OUTPUT.PUT_LINE('You are a minor in the USA.');
      END IF;
   ELSE
      DBMS_OUTPUT.PUT_LINE('Country not recognized.');
   END IF;
END;
/

Nested Loops:

plsql
Copy code
BEGIN
   FOR i IN 1..3 LOOP
      FOR j IN 1..3 LOOP
         DBMS_OUTPUT.PUT_LINE('i=' || i || ', j=' || j);
      END LOOP;
   END LOOP;
END;
/

Using CASE in SELECT Statements (Advanced Use):

plsql
Copy code
SELECT employee_id,
       first_name,
       last_name,
       CASE
          WHEN salary >= 10000 THEN 'High'
          WHEN salary >= 5000 THEN 'Medium'
          ELSE 'Low'
       END AS salary_level
FROM employees;

Summary

• Conditional Statements: Control the flow based on conditions.
  • IF...THEN: Executes code when a condition is TRUE.
  • IF...THEN...ELSE: Provides alternative execution paths.
  • IF...ELSIF...ELSE: Handles multiple conditions.
  • CASE Statement: Simplifies complex conditional logic.
• Loops: Execute code multiple times.
  • Simple Loop: Repeats until an EXIT condition is met.
  • WHILE Loop: Continues while a condition is TRUE.
  • FOR Loop: Iterates over a specified range.
• Example Application: Calculating factorial demonstrates how loops can perform iterative calculations.

By understanding and utilizing these control structures, you can enhance the functionality and efficiency of your PL/SQL programs.