SQLize Online / PHPize Online  /  SQLtest Online

-- Таблица пациентов CREATE TABLE t_tpatient ( tpatient_id NUMBER NOT NULL, patient_id NUMBER NOT NULL, firstname CLOB, lastname CLOB, secondname CLOB, databirth DATE, adress CLOB, pol CLOB, discounts NUMBER, valid_from TIMESTAMP, valid_to TIMESTAMP, transaction_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT t_tpatient_pk PRIMARY KEY (tpatient_id, valid_from), CONSTRAINT t_tpatient_patientid_un UNIQUE (patient_id) ); -- Таблица врачей CREATE TABLE t_tdoctor ( tdoctor_id NUMBER NOT NULL, doctor_id NUMBER NOT NULL, firstname CLOB, lastname CLOB, secondname CLOB, speciality CLOB, category CLOB, valid_from TIMESTAMP, valid_to TIMESTAMP, transaction_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT t_tdoctor_pk PRIMARY KEY (tdoctor_id, valid_from), CONSTRAINT t_tdoctor_doctorid_un UNIQUE (doctor_id) ); -- Таблица диагнозов CREATE TABLE t_diagnosis ( diagnosis_id NUMBER NOT NULL, iddiagnos NUMBER, namediagnos CLOB, diagnosiscode NUMBER, valid_from TIMESTAMP, valid_to TIMESTAMP, transaction_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT t_diagnosis_pk PRIMARY KEY (diagnosis_id, valid_from), CONSTRAINT t_diagnosis_iddiagnos_un UNIQUE (iddiagnos) ); -- Таблица истории пациентов CREATE TABLE t_patient_history ( history_id NUMBER NOT NULL, tpatient_id NUMBER NOT NULL, idcart NUMBER, firstname CLOB, lastname CLOB, secondname CLOB, databirth DATE, adress CLOB, pol CLOB, discounts NUMBER, valid_from TIMESTAMP, valid_to TIMESTAMP, transaction_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT t_patient_history_pk PRIMARY KEY (history_id, valid_from), CONSTRAINT t_patient_history_tpatient_fk FOREIGN KEY (tpatient_id, valid_from) REFERENCES t_tpatient (tpatient_id, valid_from) ); -- Таблица лечения CREATE TABLE t_treatment ( treatment_id NUMBER NOT NULL, tpatient_id NUMBER NOT NULL, tdoctor_id NUMBER NOT NULL, start_date DATE, end_date DATE, treatment_details CLOB, valid_from TIMESTAMP, valid_to TIMESTAMP, transaction_time TIMESTAMP DEFAULT SYSTIMESTAMP, CONSTRAINT t_treatment_pk PRIMARY KEY (treatment_id, valid_from), CONSTRAINT fk_t_treatment_patient FOREIGN KEY (tpatient_id, valid_from) REFERENCES t_tpatient (tpatient_id, valid_from), CONSTRAINT fk_t_treatment_doctor FOREIGN KEY (tdoctor_id, valid_from) REFERENCES t_tdoctor (tdoctor_id, valid_from) ); CREATE OR REPLACE PACKAGE treatment_pkg AS PROCEDURE check_existing_treatment(p_tpatient_id IN NUMBER); END treatment_pkg; / CREATE OR REPLACE PACKAGE BODY treatment_pkg AS PROCEDURE check_existing_treatment(p_tpatient_id IN NUMBER) IS l_count INTEGER; BEGIN SELECT COUNT(*) INTO l_count FROM t_treatment WHERE tpatient_id = p_tpatient_id AND valid_to > SYSDATE; IF l_count > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Existing treatment is still ongoing. Cannot add new treatment.'); END IF; END check_existing_treatment; END treatment_pkg; / CREATE OR REPLACE TRIGGER check_treatment_before_insert BEFORE INSERT ON t_treatment FOR EACH ROW BEGIN treatment_pkg.check_existing_treatment(:NEW.tpatient_id); END; /