Q.4 Create following tables
SOLUTION.
CREATE TABLE Doctormaster (
doct_id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
address VARCHAR2(100),
specialization VARCHAR2(50)
);
CREATE TABLE Patientmaster (
pat_id NUMBER PRIMARY KEY,
pat_name VARCHAR2(50) NOT NULL,
DOB DATE,
address VARCHAR2(100),
phoneno VARCHAR2(15),
doct_id NUMBER,
CONSTRAINT fk_doctor FOREIGN KEY (doct_id) REFERENCES Doctormaster(doct_id) ON DELETE SET NULL
);
DECLARE
v_pat_id Patientmaster.pat_id%TYPE;
v_pat_name Patientmaster.pat_name%TYPE;
v_dob Patientmaster.DOB%TYPE;
v_address Patientmaster.address%TYPE;
v_phoneno Patientmaster.phoneno%TYPE;
v_doct_id Patientmaster.doct_id%TYPE;
BEGIN
-- Accept patient ID from user
v_pat_id := &pat_id;
-- Retrieve patient details
SELECT pat_name, DOB, address, phoneno, doct_id
INTO v_pat_name, v_dob, v_address, v_phoneno, v_doct_id
FROM Patientmaster
WHERE pat_id = v_pat_id;
-- Display patient details
DBMS_OUTPUT.PUT_LINE('Patient Details:');
DBMS_OUTPUT.PUT_LINE('Name : ' || v_pat_name);
DBMS_OUTPUT.PUT_LINE('DOB : ' || TO_CHAR(v_dob, 'DD-MON-YYYY'));
DBMS_OUTPUT.PUT_LINE('Address : ' || v_address);
DBMS_OUTPUT.PUT_LINE('Phone No : ' || v_phoneno);
-- Display doctor ID if assigned
IF v_doct_id IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Doctor ID : ' || v_doct_id);
ELSE
DBMS_OUTPUT.PUT_LINE('No doctor assigned.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Patient not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/