Skip to content

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! 💡

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

graph TD
    A[Input ID 🆔] --> B{Search in Table 🔍}
    B -- Found ✅ --> C[Display Full Record 📤]
    B -- Not Found ❌ --> D[Trigger NO_DATA_FOUND 🪂]
    D --> E[Show Error Message 💬]
    C --> F[End 🏁]
    E --> F

💻 Implementation: The Hospital Lab

-- 🛒 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