Conditional and Iterative Control in PL/SQL

Conditional Control Statements

PL/SQL offers several conditional control statements to control the flow of execution based on specific conditions.

1. IF Statement:

   SQL

   IF condition THEN statement1; ELSIF condition2 THEN statement2; ELSE statement3; END IF;

2. CASE Statement:

   SQL

   CASE expression WHEN value1 THEN statement1; WHEN value2 THEN statement2; ELSE statement3; END CASE;

Iterative Control Statements

PL/SQL provides several iterative control statements to repeat a block of code multiple times.

1. LOOP Statement:

   SQL

   LOOP statement1; statement2; EXIT WHEN condition; END LOOP;

2. WHILE LOOP:

   SQL

   WHILE condition LOOP statement1; statement2; END LOOP;

3. FOR LOOP:

   SQL

   FOR counter_variable IN initial_value .. final_value LOOP statement1; statement2; END LOOP;

Example:

DECLARE
  v_salary NUMBER(10,2) := 50000;
BEGIN
  IF v_salary > 40000 THEN
    DBMS_OUTPUT.PUT_LINE('High Salary');
  ELSIF v_salary > 30000 THEN
    DBMS_OUTPUT.PUT_LINE('Medium Salary');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Low Salary');
  END IF;

  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
  END LOOP;
END;
/

Key Points:

• Indentation: Use proper indentation to improve code readability.

• Semicolons: Terminate each PL/SQL statement with a semicolon.

• Comments: Use comments to explain the purpose of code sections.

• Error Handling: Use exception handling to gracefully handle errors and avoid program crashes.

By effectively using conditional and iterative control statements, you can create dynamic and flexible PL/SQL applications.