PL/SQL Control Structures (Practice) 🚀

Mentor's Note: In the PL/SQL Control Flow Tutorial, we learned the theory. Now, let's solve the 5 core problems from your college assignments! 💡

---

🌟 The Scenario: The Office Manager 🏢

Imagine you are managing an office database. You need to automate decisions based on employee performance and salary.

• The Logic:
  • IF an employee earns a lot, mark them as "High Salary" 💎.
  • LOOP through a list of IDs to print a batch of reports 📋.
• The Result: Instead of manual checks, your database makes decisions automatically! ✅

---

💻 Solved Assignment Problems

1. Calculate Annual Salary

Problem: Accept monthly employee salary and display their total annual salary.

DECLARE
    v_monthly_salary NUMBER(10, 2);
    v_annual_salary  NUMBER(12, 2);
BEGIN
    v_monthly_salary := &Enter_Monthly_Salary;
    v_annual_salary := v_monthly_salary * 12;

    DBMS_OUTPUT.PUT_LINE('Monthly Salary: ' || v_monthly_salary);
    DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || v_annual_salary || ' 💰');
END;
/

2. High or Low Salary Check

Problem: Check whether an employee’s salary is greater than 25,000 and print "High Salary" or "Low Salary".

DECLARE
    v_salary NUMBER(10, 2);
BEGIN
    v_salary := &Enter_Salary;

    IF v_salary > 25000 THEN
        DBMS_OUTPUT.PUT_LINE('Result: High Salary 💎');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Result: Low Salary 🛒');
    END IF;
END;
/

3. Salary Grade Calculation

Problem: Display a grade (A, B, or C) based on an employee's salary range.

DECLARE
    v_salary NUMBER(10, 2);
BEGIN
    v_salary := &Enter_Salary;

    IF v_salary >= 50000 THEN
        DBMS_OUTPUT.PUT_LINE('Grade: A 🏆');
    ELSIF v_salary >= 30000 THEN
        DBMS_OUTPUT.PUT_LINE('Grade: B ✨');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Grade: C 👍');
    END IF;
END;
/

4. Simple Loop (1 to 10)

Problem: Print numbers from 1 to 10 using a simple LOOP.

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Counting: ' || v_counter);
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10; -- 🏁 Exit Condition
    END LOOP;
END;
/

5. WHILE Loop (Even Numbers)

Problem: Display the first 10 even numbers using a WHILE loop.

DECLARE
    v_counter NUMBER := 1;
    v_num     NUMBER := 2;
BEGIN
    WHILE v_counter <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Even Number ' || v_counter || ': ' || v_num);
        v_num := v_num + 2;
        v_counter := v_counter + 1;
    END LOOP;
END;
/

---

🎨 Visual Logic: The Loop Assembly Line

---

📊 Sample Dry Run (WHILE Loop)

Goal: Print first 3 even numbers

Turn	Condition (count <= 3)	Number to Print	Next Count
1	1 <= 3 (True) ✅	2	2
2	2 <= 3 (True) ✅	4	3
3	3 <= 3 (True) ✅	6	4
4	4 <= 3 (False) ❌	--	Stop! 🏁

---

💡 Interview Tip 👔

"Interviewers often ask why we use ELSIF instead of multiple IF statements. Answer: ELSIF is more efficient because once a match is found, the computer skips all the other checks!"

---

💡 Pro Tip: "The art of programming is the art of organizing complexity." - Edsger W. Dijkstra

---

📈 Learning Path

• ← Back to RDBMS Overview
• Next: Data Handling & Cursors →