Skip to content

PL/SQL Cursors & Exception Handling (Practice) 🚀

Prerequisites: PL/SQL Control Structures

Mentor's Note: In our PL/SQL Cursors Tutorial, we learned the theory. Now, let's solve the 7 specific problems from your RDBMS Lab assignment! 💡

🌟 The Scenario: The Patient Records 🏥

Imagine you are a doctor reviewing a list of patients.

  • Implicit Cursor: You look up One specific patient by their ID. 🔍
  • Explicit Cursor: You have a Stack of Files (The Movie Reel 🎞️) and you go through them one by one. 📦
  • Exception: You search for a file, but it's Missing 🚫. You don't panic; you just handle the error safely. ✅

💻 Solved Assignment Problems

🔹 Single Row Retrieval (Implicit Cursors)

1. Count Employees

Problem: Count the total number of employees in the table.

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM EMPLOYEE;
    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);
END;
/

2. Fetch Employee by ID

Problem: Fetch name and salary using emp_id.

DECLARE
    v_name   EMPLOYEE.emp_name%TYPE;
    v_salary EMPLOYEE.salary%TYPE;
BEGIN
    SELECT emp_name, salary INTO v_name, v_salary
    FROM EMPLOYEE WHERE emp_id = &Enter_Employee_ID;

    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ' 👤');
    DBMS_OUTPUT.PUT_LINE('Salary: ₹' || v_salary);
END;
/

3. Handling NO_DATA_FOUND

Problem: Handle the error if a searched ID doesn't exist.

DECLARE
    v_name EMPLOYEE.emp_name%TYPE;
BEGIN
    SELECT emp_name INTO v_name FROM EMPLOYEE WHERE emp_id = &ID;
    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ Error: No employee found with that ID.');
END;
/

🔹 Multiple Row Retrieval (Explicit Cursors)

4. Display All Names (Cursor Loop)

DECLARE
    CURSOR c_emp IS SELECT emp_name FROM EMPLOYEE;
    v_name EMPLOYEE.emp_name%TYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_name;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ' ✅');
    END LOOP;
    CLOSE c_emp;
END;
/

5. Filtered Cursor (Salary > 30,000)

DECLARE
    CURSOR c_high IS SELECT emp_name, salary FROM EMPLOYEE WHERE salary > 30000;
BEGIN
    FOR r IN c_high LOOP
        DBMS_OUTPUT.PUT_LINE(r.emp_name || ' earns ₹' || r.salary);
    END LOOP;
END;
/

6. Join Cursor (Employee & Dept)

DECLARE
    CURSOR c_join IS 
        SELECT E.emp_name, D.dept_name 
        FROM EMPLOYEE E JOIN DEPARTMENT D ON E.dept_id = D.dept_id;
BEGIN
    FOR r IN c_join LOOP
        DBMS_OUTPUT.PUT_LINE(r.emp_name || ' works in ' || r.dept_name);
    END LOOP;
END;
/

7. Parameterized Cursor (By Dept ID)

DECLARE
    CURSOR c_dept (p_id NUMBER) IS 
        SELECT emp_name FROM EMPLOYEE WHERE dept_id = p_id;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employees in Dept &Dept_ID:');
    FOR r IN c_dept(&Dept_ID) LOOP
        DBMS_OUTPUT.PUT_LINE('- ' || r.emp_name);
    END LOOP;
END;
/

🎨 Visual Logic: The Exception Parachute

graph TD
    A[Start Block 🏁] --> B[Run Query 🔍]
    B --> C{Record Found?}
    C -- Yes --> D[Show Data 📤]
    C -- No --> E[Deploy Parachute: EXCEPTION 🪂]
    E --> F[Show Friendly Error 💬]
    D --> G[End ✅]
    F --> G

💡 Interview Tip 👔

"Interviewers love to ask: 'What happens if a SELECT INTO returns more than one row?' Answer: It throws a TOO_MANY_ROWS exception! Always use a cursor if you expect multiple results."

💡 Pro Tip: "Success is not final, failure is not fatal: it is the courage to continue that counts." - Winston Churchill

📈 Learning Path