iT邦幫忙

2025 iThome 鐵人賽
DAY 8
0
自我挑戰組

基於 Oracle 資料庫的醫院電子病歷系統設計與建置系列 第 8 篇

Day08：資料表轉SQL、資料插入(1/3)

16th鐵人賽
22 瀏覽

  • 分享至 

  • xImage
    •  

今天要分享的是資料表轉SQL語句以及資料插入！

首先先展示實體關聯圖轉SQL的程式碼以及命令檔輸出...
1. 實體關聯圖轉SQL的程式碼

CREATE TABLE AllergyRecords ( 
     " AllergyID "        NUMBER (15)  NOT NULL , 
     " PatientID "        NUMBER (15)  NOT NULL , 
     " Allergen "         VARCHAR2 (50) , 
     " ReactionSymptoms " VARCHAR2 (100) , 
     " OccurrenceDate "   DATE , 
     " Severity "         VARCHAR2 (20) , 
     " AllergyNotes "     VARCHAR2 (150) ) ;
CREATE TABLE Appointments ( 
     " AppointmentID "     NUMBER (15)  NOT NULL , 
     " PatientID "         NUMBER (15)  NOT NULL , 
     " DepartmentID "      NUMBER (15)  NOT NULL , 
     " StaffID "           NUMBER (15)  NOT NULL , 
     " AppointmentTime "   DATE , 
     " AppointmentStatus " VARCHAR2 (20) , 
     " ChiefComplaint "    VARCHAR2 (50) , 
     " VisitDate "         DATE , 
     " VisitStatus "       VARCHAR2 (20) ) ;
CREATE TABLE Departments ( 
     " DepartmentID "          NUMBER (15)  NOT NULL , 
     " DepartmentName "        VARCHAR2 (20) , 
     " DepartmentDescription " VARCHAR2 (100) ) ;
CREATE TABLE Diagnoses ( 
     " DiagnosisID "    NUMBER (15)  NOT NULL , 
     " RecordID "       NUMBER (15)  NOT NULL , 
     " StaffID "        NUMBER (15)  NOT NULL , 
     " RecordType "     VARCHAR2 (10) , 
     " DiagnosisDate "  DATE , 
     " Subjective "     VARCHAR2 (50) , 
     " Objective "      VARCHAR2 (50) , 
     " Assessment "     VARCHAR2 (100) , 
     " Plan "           VARCHAR2 (100) , 
     " ICDCode "        VARCHAR2 (20) , 
     " DiagnosisNotes " VARCHAR2 (100) ) ;
CREATE TABLE HospitalizationRecords ( 
     " HospitalizationID "     NUMBER (15)  NOT NULL , 
     " AppointmentID "         NUMBER (15)  NOT NULL , 
     " DepartmentID "          NUMBER (15)  NOT NULL , 
     " StaffID "               NUMBER (15)  NOT NULL , 
     " AdmissionDate "         DATE , 
     " LengthOfStay "          INTEGER , 
     " ExpectedDischargeDate " DATE , 
     " ActualDischargeDate "   DATE , 
     " WardArea "              VARCHAR2 (20) , 
     " RoomNum "               VARCHAR2 (10) , 
     " BedNum "                VARCHAR2 (20) , 
     " AdmissionDiagnosis "    VARCHAR2 (100) , 
     " DischargeDiagnosis "    VARCHAR2 (100) , 
     " HospitalizationStatus " VARCHAR2 (20) , 
     " CreateTime "            DATE , 
     " DischargeSummary "      VARCHAR2 (100) , 
     " HospitalizationNotes "  VARCHAR2 (100) ) ;
CREATE TABLE InsuranceBilling ( 
     " BillingID "       NUMBER (15)  NOT NULL , 
     " PatientID "       NUMBER (15)  NOT NULL , 
     " AppointmentID "   NUMBER (15)  NOT NULL , 
     " BillingDate "     DATE , 
     " ItemDescription " VARCHAR2 (100) , 
     " Amount "          NUMBER (10,2) , 
     " NHI "             CHAR (1) , 
     " NHIAmount "       NUMBER (10,2) , 
     " PatientPay "      NUMBER (10,2) ) ;
CREATE TABLE MedicalResourceUsage ( 
     " UsageID "       NUMBER (15)  NOT NULL , 
     " PatientID "     NUMBER (15)  NOT NULL , 
     " StaffID "       NUMBER (15)  NOT NULL , 
     " DepartmentID "  NUMBER (15)  NOT NULL , 
     " ResourceType "  VARCHAR2 (50) , 
     " ResourceName "  VARCHAR2 (50) , 
     " UsageDate "     DATE , 
     " UsageDuration " VARCHAR2 (15) , 
     " UsageNotes "    VARCHAR2 (100) ) ;
CREATE TABLE Patients ( 
     " PatientID "             NUMBER (15)  NOT NULL , 
     " PatientName "           VARCHAR2 (25) , 
     " PatientGender "         CHAR (10) , 
     " PatientBirth "          DATE , 
     " BloodType "             VARCHAR2 (10) , 
     " PatientPhone "          VARCHAR2 (20) , 
     " PatientAddress "        VARCHAR2 (50) , 
     " PatientIdentityNumber " VARCHAR2 (15) , 
     " EmergencyRelation "     VARCHAR2 (10) , 
     " EmergencyName "         VARCHAR2 (10) , 
     " EmergencyPhone "        VARCHAR2 (20) ) ;
CREATE TABLE Prescriptions ( 
     " PrescriptionID "        NUMBER (15)  NOT NULL , 
     " DiagnosisID "           NUMBER (15)  NOT NULL , 
     " MedicationName "        VARCHAR2 (50) , 
     " MedicationInstruction " VARCHAR2 (20) , 
     " MedicationDays "        INTEGER , 
     " MedicationCode "        VARCHAR2 (20) , 
     " MedicationType "        VARCHAR2 (20) , 
     " MedicationStatus "      CHAR (1) ) ;
CREATE TABLE Records ( 
     " RecordID "          NUMBER (15)  NOT NULL , 
     " PatientID "         NUMBER (15)  NOT NULL , 
     " AppointmentID "     NUMBER (15)  NOT NULL , 
     " StaffID "           NUMBER (15)  NOT NULL , 
     " FamilyHistory "     VARCHAR2 (50) , 
     " Habits "            VARCHAR2 (50) , 
     " SurgicalHistory "   VARCHAR2 (100) , 
     " HospitalHistory "   VARCHAR2 (100) , 
     " AllergyHistory "    VARCHAR2 (100) , 
     " VaccinationRecord " VARCHAR2 (50) , 
     " CreateDate "        DATE , 
     " UpdatedDate "       DATE ) ;
CREATE TABLE SatisfactionSurveys ( 
     " SurveyID "      NUMBER (15)  NOT NULL , 
     " AppointmentID " NUMBER (15)  NOT NULL , 
     " SurveyDate "    DATE , 
     " TargetRole "    VARCHAR2 (20) , 
     " Rating "        INTEGER , 
     " Comments "      VARCHAR2 (150) ) ;
CREATE TABLE Schedules ( 
     " ScheduleID "   NUMBER (15)  NOT NULL , 
     " StaffID "      NUMBER (15)  NOT NULL , 
     " ScheduleDate " DATE , 
     " DepartmentID " NUMBER (15)  NOT NULL , 
     " TimeSlot "     VARCHAR2 (10) ) ;
CREATE TABLE Staff ( 
     " StaffID "           NUMBER (15)  NOT NULL , 
     " DepartmentID "      NUMBER (15)  NOT NULL , 
     " StaffName "         VARCHAR2 (20) , 
     " StaffGender "       VARCHAR2 (20) , 
     " StaffRole "         VARCHAR2 (20) , 
     " StaffLicneseNum "   VARCHAR2 (30) , 
     " StaffPhone "        VARCHAR2 (20) , 
     " YearsOfExperience " INTEGER ) ;
CREATE TABLE SurgeryRecords ( 
     " SurgeryID "                 NUMBER (15)  NOT NULL , 
     " AppointmentID "             NUMBER (15)  NOT NULL , 
     " StaffID "                   NUMBER (15)  NOT NULL , 
     " SurgeryDate "               DATE , 
     " SurgeryName "               VARCHAR2 (50) , 
     " SurgerySite "               VARCHAR2 (20) , 
     " SurgeryType "               VARCHAR2 (20) , 
     " AnesthesiaType "            VARCHAR2 (20) , 
     " DurationMinutes "           INTEGER , 
     " Complications "             VARCHAR2 (150) , 
     " PostOperativeInstructions " VARCHAR2 (150) , 
     " StaffNotes "                VARCHAR2 (100) ) ;
CREATE TABLE Tests ( 
     " TestID "         NUMBER (15)  NOT NULL , 
     " AppointmentID "  NUMBER (15)  NOT NULL , 
     " StaffID "        NUMBER (15)  NOT NULL , 
     " TestName "       VARCHAR2 (50) , 
     " TestCode "       VARCHAR2 (20) , 
     " TestDate "       DATE , 
     " TestResult "     VARCHAR2 (100) , 
     " TestStatus "     VARCHAR2 (20) , 
     " TestNotes "      VARCHAR2 (100) ) ;
CREATE TABLE VaccinationRecords ( 
     " VaccinationID "              NUMBER (15)  NOT NULL , 
     " PatientID "                  NUMBER (15)  NOT NULL , 
     " StaffID "                    NUMBER (15)  NOT NULL , 
     " VaccineName "                VARCHAR2 (50) , 
     " DoseNum "                    INTEGER , 
     " VaccinationDate "            DATE , 
     " VaccinationLocation "        VARCHAR2 (50) , 
     " HasPostVaccinationSymptoms " CHAR (1) , 
     " PostVaccinationReaction "    VARCHAR2 (150) ) ;
ALTER TABLE AllergyRecords ADD CONSTRAINT AllergyRecords_PK PRIMARY KEY ( AllergyID ) ;
ALTER TABLE Appointments ADD CONSTRAINT Appointments_PK PRIMARY KEY ( AppointmentID ) ;
ALTER TABLE Departments ADD CONSTRAINT Departments_PK PRIMARY KEY ( DepartmentID ) ;
ALTER TABLE Diagnoses ADD CONSTRAINT Diagnoses_PK PRIMARY KEY ( DiagnosisID ) ;
ALTER TABLE HospitalizationRecords ADD CONSTRAINT HospitalizationRecords_PK PRIMARY KEY ( HospitalizationID ) ;
ALTER TABLE InsuranceBilling ADD CONSTRAINT InsuranceBilling_PK PRIMARY KEY ( BillingID ) ;
ALTER TABLE MedicalResourceUsage ADD CONSTRAINT MedicalResourceUsage_PK PRIMARY KEY ( UsageID ) ;
ALTER TABLE Patients ADD CONSTRAINT Patients_PK PRIMARY KEY ( PatientID ) ;
ALTER TABLE Prescriptions ADD CONSTRAINT Prescriptions_PK PRIMARY KEY ( PrescriptionID ) ;
ALTER TABLE Records ADD CONSTRAINT Records_PK PRIMARY KEY ( RecordID ) ;
ALTER TABLE SatisfactionSurveys ADD CONSTRAINT SatisfactionSurveys_PK PRIMARY KEY ( SurveyID ) ;
ALTER TABLE Schedules ADD CONSTRAINT Schedule_PK PRIMARY KEY ( ScheduleID ) ;
ALTER TABLE Staff ADD CONSTRAINT Staff_PK PRIMARY KEY ( StaffID ) ;
ALTER TABLE SurgeryRecords ADD CONSTRAINT SurgeryRecords_PK PRIMARY KEY ( SurgeryID ) ;
ALTER TABLE Tests ADD CONSTRAINT Tests_PK PRIMARY KEY ( TestID ) ;
ALTER TABLE VaccinationRecords ADD CONSTRAINT VaccinationRecords_PK PRIMARY KEY ( VaccinationID ) ;
ALTER TABLE AllergyRecords 
    ADD CONSTRAINT AllergyRecords_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE Appointments 
    ADD CONSTRAINT Appointments_Departments_FK FOREIGN KEY 
    (DepartmentID) REFERENCES Departments (DepartmentID) ;
ALTER TABLE Appointments 
    ADD CONSTRAINT Appointments_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE Appointments 
    ADD CONSTRAINT Appointments_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE Diagnoses 
    ADD CONSTRAINT Diagnoses_Records_FK FOREIGN KEY 
    (RecordID) REFERENCES Records (RecordID) ;
ALTER TABLE Diagnoses 
    ADD CONSTRAINT Diagnoses_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE HospitalizationRecords 
    ADD CONSTRAINT HospitalRecord_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE HospitalizationRecords 
    ADD CONSTRAINT HospitalRecord_Departments_FK FOREIGN KEY 
    (DepartmentID) REFERENCES Departments (DepartmentID) ;
ALTER TABLE HospitalizationRecords 
    ADD CONSTRAINT HospitalRecord_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE InsuranceBilling 
    ADD CONSTRAINT InsuranceBilling_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE InsuranceBilling 
    ADD CONSTRAINT InsureBill_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE MedicalResourceUsage 
    ADD CONSTRAINT MRU_Departments_FK FOREIGN KEY 
    (DepartmentID) REFERENCES Departments (DepartmentID) ;
ALTER TABLE MedicalResourceUsage 
    ADD CONSTRAINT MRU_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE MedicalResourceUsage 
    ADD CONSTRAINT MRU_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE Prescriptions 
    ADD CONSTRAINT Prescriptions_Diagnoses_FK FOREIGN KEY 
    (DiagnosisID) REFERENCES Diagnoses (DiagnosisID) ;
ALTER TABLE Records 
    ADD CONSTRAINT Records_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE Records 
    ADD CONSTRAINT Records_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE Records 
    ADD CONSTRAINT Records_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE SatisfactionSurveys 
    ADD CONSTRAINT SatisfySurvey_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE Schedules 
    ADD CONSTRAINT Schedules_Departments_FK FOREIGN KEY 
    (DepartmentID) REFERENCES Departments (DepartmentID) ;
ALTER TABLE Schedules 
    ADD CONSTRAINT Schedules_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE Staff 
    ADD CONSTRAINT Staff_Departments_FK FOREIGN KEY 
    (DepartmentID) REFERENCES Departments (DepartmentID) ;
ALTER TABLE SurgeryRecords 
    ADD CONSTRAINT SurgeryRecords_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE SurgeryRecords 
    ADD CONSTRAINT SurgeryRecords_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE Tests 
    ADD CONSTRAINT Tests_Appointments_FK FOREIGN KEY 
    (AppointmentID) REFERENCES Appointments (AppointmentID) ;
ALTER TABLE Tests 
    ADD CONSTRAINT Tests_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;
ALTER TABLE VaccinationRecords 
    ADD CONSTRAINT VaccinationRecords_Patients_FK FOREIGN KEY 
    (PatientID) REFERENCES Patients (PatientID) ;
ALTER TABLE VaccinationRecords 
    ADD CONSTRAINT VaccinationRecords_Staff_FK FOREIGN KEY 
    (StaffID) REFERENCES Staff (StaffID) ;

2.命令檔輸出
https://ithelp.ithome.com.tw/upload/images/20250920/20167787fgXIt88Uje.jpg

再來要分享的是資料蒐集、輸入的部分！
(目前正在趕工中，怕時間來不及所以先上傳)

以上是我今天的分享，明天還會繼續進行資料輸入(每個資料表要有太多欄位了><)，希望大家喜歡我今天的分享 ~ 謝謝大家的觀看！/images/emoticon/emoticon02.gif


上一篇
Day07：實體關聯圖繪製、資料表間關聯的補充說明
下一篇
Day09：資料插入(2/3)
系列文
基於 Oracle 資料庫的醫院電子病歷系統設計與建置 共 10 篇
  1. 6
    Day06：實體關聯圖初步設計
  2. 7
    Day07：實體關聯圖繪製、資料表間關聯的補充說明
  3. 8
    Day08：資料表轉SQL、資料插入(1/3)
  4. 9
    Day09：資料插入(2/3)
  5. 10
    Day10：資料插入(3/3)
完整目錄
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
iThome鐵人賽
參賽組數
902 組
團體組數
37 組
累計文章數
10152 篇
完賽人數
98 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 17th鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react
熱門問題
熱門回答
熱門文章
IT邦幫忙