如何向子表DOCTOR插入数据并关联父表EMPLOYEE？

Hey there! Let's break down how to handle inserting into the DOCTOR table while ensuring it’s properly linked to the EMPLOYEE table—whether you need to create a new employee record at the same time or link to an existing one.

1. Insert a Doctor with a New Employee Record #

If you need to add both a new employee and their corresponding doctor record, you’ll want to ensure the operations are atomic (so if one fails, both roll back). Use an anonymous PL/SQL block to capture the generated Emp_Id from the sequence and reuse it for the DOCTOR insert:

DECLARE
    v_emp_id VARCHAR2(3 CHAR);
BEGIN
    -- First insert into EMPLOYEE and capture the auto-generated Emp_Id
    INSERT INTO EMPLOYEE (Emp_Id, Emp_Fname, Emp_Lname, Emp_DOB, Emp_Address, Emp_Phone, Emp_Email, Emp_Type)
    VALUES (emp_id_no_seq.NEXTVAL, 'John', 'Le Grange', TO_DATE('15-MAY-65', 'DD-MON-RR'), '15 Maple Ave.', '0112562314', 'jlg@rockhealth.com', 'D')
    RETURNING Emp_Id INTO v_emp_id;

    -- Insert into DOCTOR using the captured Emp_Id
    INSERT INTO DOCTOR (Doc_Id, Emp_Id, Doc_Spec, Doc_Med_Lic, Doc_Fee, Doc_Avail)
    VALUES ('D01', v_emp_id, 'Cardiology', 'MED12345', 150.00, 'Yes');

    COMMIT; -- Finalize both inserts
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- Undo all changes if any step fails
        RAISE; -- Re-throw the error for debugging
END;
/

2. Insert a Doctor Linked to an Existing Employee #

If the employee already exists in the EMPLOYEE table, you can directly reference their Emp_Id in the DOCTOR insert. Oracle’s foreign key constraint will automatically block inserts if the Emp_Id doesn’t exist in the parent table:

-- Ensure the Emp_Id 'E01' exists in EMPLOYEE first (Oracle will enforce this via foreign key)
INSERT INTO DOCTOR (Doc_Id, Emp_Id, Doc_Spec, Doc_Med_Lic, Doc_Fee, Doc_Avail)
VALUES ('D02', 'E01', 'Neurology', 'MED67890', 200.00, 'No');

3. Automate with a Stored Procedure (Reusable Solution) #

For cleaner, repeatable code, wrap the logic in a stored procedure. This lets you either pass an existing Emp_Id or let the procedure create a new employee automatically:

CREATE OR REPLACE PROCEDURE insert_doctor(
    p_doc_id IN VARCHAR2,
    p_emp_id IN VARCHAR2 DEFAULT NULL,
    p_emp_fname IN VARCHAR2,
    p_emp_lname IN VARCHAR2,
    p_emp_dob IN DATE,
    p_emp_address IN VARCHAR2,
    p_emp_phone IN VARCHAR2,
    p_emp_email IN VARCHAR2,
    p_doc_spec IN VARCHAR2,
    p_doc_med_lic IN VARCHAR2,
    p_doc_fee IN NUMBER,
    p_doc_avail IN VARCHAR2
)
IS
    v_emp_id VARCHAR2(3 CHAR);
BEGIN
    IF p_emp_id IS NULL THEN
        -- Create a new employee and capture their ID
        INSERT INTO EMPLOYEE (Emp_Id, Emp_Fname, Emp_Lname, Emp_DOB, Emp_Address, Emp_Phone, Emp_Email, Emp_Type)
        VALUES (emp_id_no_seq.NEXTVAL, p_emp_fname, p_emp_lname, p_emp_dob, p_emp_address, p_emp_phone, p_emp_email, 'D')
        RETURNING Emp_Id INTO v_emp_id;
    ELSE
        -- Verify the existing employee is a doctor (Emp_Type = 'D')
        SELECT Emp_Id INTO v_emp_id
        FROM EMPLOYEE
        WHERE Emp_Id = p_emp_id
        AND Emp_Type = 'D';
    END IF;

    -- Insert the doctor record
    INSERT INTO DOCTOR (Doc_Id, Emp_Id, Doc_Spec, Doc_Med_Lic, Doc_Fee, Doc_Avail)
    VALUES (p_doc_id, v_emp_id, p_doc_spec, p_doc_med_lic, p_doc_fee, p_doc_avail);

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20001, 'Employee ID ' || p_emp_id || ' does not exist or is not a doctor.');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

How to Call the Procedure: #

• Create new employee + doctor:

EXEC insert_doctor(
    p_doc_id => 'D03',
    p_emp_fname => 'Jane',
    p_emp_lname => 'Smith',
    p_emp_dob => TO_DATE('20-JUN-70', 'DD-MON-RR'),
    p_emp_address => '20 Oak St.',
    p_emp_phone => '0113456789',
    p_emp_email => 'jane.smith@rockhealth.com',
    p_doc_spec => 'Pediatrics',
    p_doc_med_lic => 'MED54321',
    p_doc_fee => 120.00,
    p_doc_avail => 'Yes'
);

• Link to existing employee:

EXEC insert_doctor(
    p_doc_id => 'D04',
    p_emp_id => 'E01',
    p_emp_fname => NULL, -- These params can be NULL when using an existing employee
    p_emp_lname => NULL,
    p_emp_dob => NULL,
    p_emp_address => NULL,
    p_emp_phone => NULL,
    p_emp_email => NULL,
    p_doc_spec => 'Orthopedics',
    p_doc_med_lic => 'MED98765',
    p_doc_fee => 180.00,
    p_doc_avail => 'No'
);

内容的提问来源于stack exchange，提问作者user8291121