Patient Information Retrieval 🚀

Mentor's Note: In a hospital, doctors need patient data instantly. A slow or buggy system can be dangerous. This lab shows you how to build a safe, reliable lookup tool! 💡

---

🌟 The Scenario: The Doctor's Search 🩺

Imagine you are a doctor at a clinic in Surat.

• The Logic: A patient arrives and gives you their Patient ID. 📦
• The Search: You enter the ID into the system. It looks in the Patient Master table. 🔍
• The Result:
  • Found: The system displays their Name, DOB, and Phone. ✅
  • Not Found: The system shows a friendly "Record not found" instead of crashing. 🕸️

---

📖 Concept Explanation

1. Using %TYPE 🏷️

Instead of guessing if a name is VARCHAR2(50) or (100), we use Table.Column%TYPE. This makes the code "Smart" because it automatically copies the correct size from the table.

2. Error Trapping

We use the EXCEPTION block to catch NO_DATA_FOUND. This is the "Parachute" that ensures the program exits safely if an ID is wrong. 🪂

---

🎨 Visual Logic: The Lookup Flow

---

💻 Implementation: The Hospital Lab

Oracle SQL

-- 🛒 Scenario: Looking up a patient's medical file
-- 🚀 Action: Using SELECT INTO with exception handling

DECLARE
    -- 📦 Smart variables using %TYPE
    v_id      Patientmaster.pat_id%TYPE;
    v_name    Patientmaster.pat_name%TYPE;
    v_dob     Patientmaster.DOB%TYPE;
    v_phone   Patientmaster.phoneno%TYPE;
BEGIN
    -- 📥 1. Accept ID from user
    v_id := &Enter_Patient_ID;

    -- 🔍 2. Try to fetch details
    SELECT pat_name, DOB, phoneno
    INTO v_name, v_dob, v_phone
    FROM Patientmaster
    WHERE pat_id = v_id;

    -- 📤 3. Show Result
    DBMS_OUTPUT.PUT_LINE('--- Patient Profile ---');
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ' 👤');
    DBMS_OUTPUT.PUT_LINE('DOB: ' || TO_CHAR(v_dob, 'DD-Mon-YYYY'));
    DBMS_OUTPUT.PUT_LINE('Contact: ' || v_phone || ' 📞');

EXCEPTION
    -- 🕸️ 4. Safety Net
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('⚠️ Alert: Patient ID ' || v_id || ' does not exist.');
END;
/

---

📊 Sample Dry Run

Goal: Find ID 501 (Not in table)

Step	Action	Status	Result
1	Input 501	Variable set	Waiting...
2	Query	Search pat_id=501	No rows found! ❌
3	Jump	Go to EXCEPTION	Handler reached 🪂
4	Print	Show Alert	"ID 501 does not exist"

---

📉 Complexity Analysis

• Time Complexity: $O(1)$ - This is a Primary Key Search, which is the fastest way to find data in a database. ⚡

---

🎯 Practice Problems

Easy Level 🟢

• Add a line to print the current system date (SYSDATE) at the top of the report.

Medium Level 🟡

• If the patient is found, also display their Doctor's Name by joining with the Doctormaster table.

---

💡 Interview Tip 👔

"Interviewers often ask: 'Can SELECT INTO return 0 rows?' Answer: No, if it returns 0 rows, it immediately stops and triggers the NO_DATA_FOUND exception. Your code must handle it!"

---

📈 Learning Path

• ← Back: Advanced Calculations
• Next: Product Sales Update →

---