PL/SQL Control Structures (Practice) 🚀¶

BCA Semester 2 RDBMSPL/SQL Control Structures

Recommended order: Semester 2 overview -> RDBMS lab -> PL/SQL control structures -> PL/SQL data handling

Best for: BCA students preparing SQL and PL/SQL practical work

Prerequisites: PL/SQL Basics

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¶

graph TD
    A[Start Loop 🏁] --> B[i = 1]
    B --> C{i <= 10?}
    C -- Yes --> D[Print Number 📤]
    D --> E[i = i + 1 ⚙️]
    E --> C
    C -- No --> F[End Loop 🏁]

📊 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