his.sql

Delphi6开发的HIS程序

    SOOMaterialDetailID    NUMBER(38, 0)    NOT NULL,
    Price                  NUMBER(16, 4)    NOT NULL,
    Amount                 NUMBER(16, 4)    NOT NULL,
    Total                  NUMBER(16, 4)    NOT NULL,
    StockOutOrderID        NUMBER(38, 0)    NOT NULL,
    MaterialStockID        NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSOOMaterialDetail PRIMARY KEY (SOOMaterialDetailID)
) 
;



-- 
-- TABLE: SOOMedicineDetail 
--

CREATE TABLE SOOMedicineDetail(
    SOOMedicineDetailID    NUMBER(38, 0)    NOT NULL,
    IsApprove              NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price                  NUMBER(16, 4)    NOT NULL,
    Amount                 NUMBER(16, 4)    NOT NULL,
    Total                  NUMBER(16, 4)    NOT NULL,
    StockOutOrderID        NUMBER(38, 0)    NOT NULL,
    MedicineStockID        NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKSOOMedicineDetail PRIMARY KEY (SOOMedicineDetailID)
) 
;



-- 
-- TABLE: StockInOrder 
--

CREATE TABLE StockInOrder(
    StockInOrderID     NUMBER(38, 0)    NOT NULL,
    CheckInDate        DATE,
    Total              NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Memo               VARCHAR2(64),
    IsApprove          NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate        DATE,
    Created            DATE              DEFAULT SYSDATE NOT NULL,
    Updated            DATE              DEFAULT SYSDATE NOT NULL,
    CheckInMethodID    NUMBER(38, 0)    NOT NULL,
    ApproveBy          NUMBER(38, 0),
    CreateBy           NUMBER(38, 0)    NOT NULL,
    UpdateBy           NUMBER(38, 0),
    CONSTRAINT PKStockInOrder PRIMARY KEY (StockInOrderID)
) 
;



-- 
-- TABLE: StockOutOrder 
--

CREATE TABLE StockOutOrder(
    StockOutOrderID     NUMBER(38, 0)    NOT NULL,
    ST                  NUMBER(5, 0)      DEFAULT 1 NOT NULL
                        CHECK (ST in(1, 2, 3, 4, 5)),
    Total               NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Memo                VARCHAR2(64),
    IsApprove           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate         DATE,
    CheckOutDate        DATE,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    CreateBy            NUMBER(38, 0)    NOT NULL,
    UpdateBy            NUMBER(38, 0),
    CheckOutMethodID    NUMBER(38, 0)    NOT NULL,
    ApproveBy           NUMBER(38, 0),
    DepartmentID        NUMBER(38, 0)    NOT NULL,
    SectionOfficeID     NUMBER(38, 0),
    CONSTRAINT PKStockOutOrder PRIMARY KEY (StockOutOrderID)
) 
;



-- 
-- TABLE: TendLevel 
--

CREATE TABLE TendLevel(
    TendLevelID          NUMBER(38, 0)    NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    Charge               NUMBER(16, 4)    NOT NULL,
    ChargeCatalogCode    VARCHAR2(8)      NOT NULL,
    CONSTRAINT PKTendLevel PRIMARY KEY (TendLevelID)
) 
;



-- 
-- TABLE: Times 
--

CREATE TABLE Times(
    TimesID      NUMBER(38, 0)    NOT NULL,
    Caption      VARCHAR2(32)     NOT NULL,
    BeginDate    DATE             NOT NULL,
    EndDate      DATE             NOT NULL,
    Created      DATE              DEFAULT SYSDATE NOT NULL,
    Updated      DATE              DEFAULT SYSDATE NOT NULL,
    CreateBy     NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKTimes PRIMARY KEY (TimesID)
) 
;



-- 
-- TABLE: TollStation 
--

CREATE TABLE TollStation(
    TollStationID      NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    SectionOfficeID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKTollStation PRIMARY KEY (TollStationID)
) 
;



-- 
-- TABLE: TollStationCharge 
--

CREATE TABLE TollStationCharge(
    TollStationID     NUMBER(38, 0)    NOT NULL,
    ChargeItemCode    VARCHAR2(8)      NOT NULL,
    IsNeed            NUMBER(5, 0)      DEFAULT 0 NOT NULL
                      CHECK (IsNeed in(0, 1)),
    CONSTRAINT PKTollStationCharge PRIMARY KEY (TollStationID, ChargeItemCode)
) 
;



-- 
-- TABLE: Units 
--

CREATE TABLE Units(
    UnitsID    NUMBER(38, 0)    NOT NULL,
    PinYin     VARCHAR2(32),
    Caption    VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKUnits PRIMARY KEY (UnitsID)
) 
;



-- 
-- TABLE: UseMethod 
--

CREATE TABLE UseMethod(
    UseMethodID    NUMBER(38, 0)    NOT NULL,
    PinYin         VARCHAR2(32),
    Caption        VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKUseMethod PRIMARY KEY (UseMethodID)
) 
;



-- 
-- TABLE: Wedlock 
--

CREATE TABLE Wedlock(
    WedlockID    NUMBER(38, 0)    NOT NULL,
    Caption      VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKWedlock PRIMARY KEY (WedlockID)
) 
;



-- 
-- TABLE: Approach 
--

ALTER TABLE Approach ADD CONSTRAINT RefFrequencyWeek71 
    FOREIGN KEY (FrequencyWeekID)
    REFERENCES FrequencyWeek(FrequencyWeekID)
;

ALTER TABLE Approach ADD CONSTRAINT RefChargeItem74 
    FOREIGN KEY (ChargeItemCode)
    REFERENCES ChargeItem(ChargeItemCode)
;


-- 
-- TABLE: Area 
--

ALTER TABLE Area ADD CONSTRAINT RefArea90 
    FOREIGN KEY (ParentID)
    REFERENCES Area(AreaID)
;


-- 
-- TABLE: Bed 
--

ALTER TABLE Bed ADD CONSTRAINT RefSickroom192 
    FOREIGN KEY (SickroomID)
    REFERENCES Sickroom(SickroomID)
;

ALTER TABLE Bed ADD CONSTRAINT RefBedCatalog84 
    FOREIGN KEY (BedCatalogID)
    REFERENCES BedCatalog(BedCatalogID)
;


-- 
-- TABLE: BedChargeItem 
--

ALTER TABLE BedChargeItem ADD CONSTRAINT RefChargeItem81 
    FOREIGN KEY (ChargeItemCode)
    REFERENCES ChargeItem(ChargeItemCode)
;

ALTER TABLE BedChargeItem ADD CONSTRAINT RefBedCatalog85 
    FOREIGN KEY (BedCatalogID)
    REFERENCES BedCatalog(BedCatalogID)
;


-- 
-- TABLE: ChargeCatalog 
--

ALTER TABLE ChargeCatalog ADD CONSTRAINT RefChargeCatalog189 
    FOREIGN KEY (ParentCode)
    REFERENCES ChargeCatalog(ChargeCatalogCode)
;


-- 
-- TABLE: ChargeItem 
--

ALTER TABLE ChargeItem ADD CONSTRAINT RefChargeCatalog190 
    FOREIGN KEY (ChargeCatalogCode)
    REFERENCES ChargeCatalog(ChargeCatalogCode)
;


-- 
-- TABLE: DAMaterial 
--

ALTER TABLE DAMaterial ADD CONSTRAINT RefMaterial181 
    FOREIGN KEY (MaterialID)
    REFERENCES Material(MaterialID)
;

ALTER TABLE DAMaterial ADD CONSTRAINT RefEmployee68 
    FOREIGN KEY (StopBy)
    REFERENCES Employee(EmployeeID)
;

ALTER TABLE DAMaterial ADD CONSTRAINT RefUseMethod69 
    FOREIGN KEY (UseMethodID)
    REFERENCES UseMethod(UseMethodID)
;

ALTER TABLE DAMaterial ADD CONSTRAINT RefApproach70 
    FOREIGN KEY (ApproachID)
    REFERENCES Approach(ApproachID)
;

ALTER TABLE DAMaterial ADD CONSTRAINT RefDoctorAdvice175 
    FOREIGN KEY (DoctorAdviceId)
    REFERENCES DoctorAdvice(DoctorAdviceId)
;


-- 
-- TABLE: DAMedicine 
--

ALTER TABLE DAMedicine ADD CONSTRAINT RefUseMethod193 
    FOREIGN KEY (UseMethodID)
    REFERENCES UseMethod(UseMethodID)
;

ALTER TABLE DAMedicine ADD CONSTRAINT RefPStockMedicine199 
    FOREIGN KEY (PStockMedicineID)
    REFERENCES PStockMedicine(PStockMedicineID)
;

ALTER TABLE DAMedicine ADD CONSTRAINT RefApproach52 
    FOREIGN KEY (ApproachID)
    REFERENCES Approach(ApproachID)
;

ALTER TABLE DAMedicine ADD CONSTRAINT RefEmployee67 
    FOREIGN KEY (StopBy)
    REFERENCES Employee(EmployeeID)
;

ALTER TABLE DAMedicine ADD CONSTRAINT RefDoctorAdvice176 
    FOREIGN KEY (DoctorAdviceId)
    REFERENCES DoctorAdvice(DoctorAdviceId)
;


-- 
-- TABLE: DAOperation 
--

ALTER TABLE DAOperation ADD CONSTRAINT RefDoctorAdvice178 
    FOREIGN KEY (DoctorAdviceId)
    REFERENCES DoctorAdvice(DoctorAdviceId)
;

ALTER TABLE DAOperation ADD CONSTRAINT RefOperation179 
    FOREIGN KEY (OperationID)
    REFERENCES Operation(OperationID)
;


-- 
-- TABLE: DAService 
--

ALTER TABLE DAService ADD CONSTRAINT RefService182 
    FOREIGN KEY (ServiceID)
    REFERENCES Service(ServiceID)
;

ALTER TABLE DAService ADD CONSTRAINT RefDoctorAdvice177 
    FOREIGN KEY (DoctorAdviceId)
    REFERENCES DoctorAdvice(DoctorAdviceId)
;


-- 
-- TABLE: DATMaterial 
--

ALTER TABLE DATMaterial ADD CONSTRAINT RefDATemplate53 
    FOREIGN KEY (DATemplateID)
    REFERENCES DATemplate(DATemplateID)
;

ALTER TABLE DATMaterial ADD CONSTRAINT RefMaterial58 
    FOREIGN KEY (MaterialID)
    REFERENCES Material(MaterialID)
;


-- 
-- TABLE: DATMedicine 
--

ALTER TABLE DATMedicine ADD CONSTRAINT RefDATemplate54 
    FOREIGN KEY (DATemplateID)
    REFERENCES DATemplate(DATemplateID)
;

ALTER TABLE DATMedicine ADD CONSTRAINT RefMedicine59 
    FOREIGN KEY (MedicineID)
    REFERENCES Medicine(MedicineID)
;

ALTER TABLE DATMedicine ADD CONSTRAINT RefApproach60 
    FOREIGN KEY (ApproachID)
    REFERENCES Approach(ApproachID)
;

ALTER TABLE DATMedicine ADD CONSTRAINT RefUseMethod61 
    FOREIGN KEY (UseMethodID)
    REFERENCES UseMethod(UseMethodID)
;


-- 
-- TABLE: DATOperation 
--

ALTER TABLE DATOperation ADD CONSTRAINT RefDATemplate56 
    FOREIGN KEY (DATemplateID)
    REFERENCES DATemplate(DATemplateID)
;

ALTER TABLE DATOperation ADD CONSTRAINT RefOperation57 
    FOREIGN KEY (OperationID)
    REFERENCES Operation(OperationID)
;


-- 
-- TABLE: DATService 
--

ALTER TABLE DATService ADD CONSTRAINT RefDATemplate55 
    FOREIGN KEY (DATemplateID)
    REFERENCES DATemplate(DATemplateID)
;

ALTER TABLE DATService ADD CONSTRAINT RefService62 
    FOREIGN KEY (ServiceID)
    REFERENCES Service(ServiceID)
;


-- 
-- TABLE: Doctor 
--

ALTER TABLE Doctor ADD CONSTRAINT RefSectionOffice195 
    FOREIGN KEY (SectionOfficeID)
    REFERENCES SectionOffice(SectionOfficeID)
;

ALTER TABLE Doctor ADD CONSTRAINT RefEmployee196 
    FOREIGN KEY (EmployeeID)
    REFERENCES Employee(EmployeeID)
;

ALTER TABLE Doctor ADD CONSTRAINT RefDoctorCatalog34 
    FOREIGN KEY (DoctorCatalogID)
    REFERENCES DoctorCatalog(DoctorCatalogID)
;


-- 
-- TABLE: DoctorAdvice 
--

ALTER TABLE DoctorAdvice ADD CONSTRAINT RefEmployee187 
    FOREIGN KEY (CreateBy)
    REFERENCES Employee(EmployeeID)
;

ALTER TABLE DoctorAdvice ADD CONSTRAINT RefEmployee188 
    FOREIGN KEY (UpdateBy)
    REFERENCES Employee(EmployeeID)
;

ALTER TABLE DoctorAdvice ADD CONSTRAINT RefDoctor41 
    FOREIGN KEY (DoctorID)
    REFERENCES Doctor(DoctorID)
;

ALTER TABLE DoctorAdvice ADD CONSTRAINT RefInpatient64 
    FOREIGN KEY (InpatientID)
    REFERENCES Inpatient(InpatientID)
;

ALTER TABLE DoctorAdvice ADD CONSTRAINT RefDoctorAdviceType73 
    FOREIGN KEY (DoctorAdviceTypeID)
    REFERENCES DoctorAdviceType(DoctorAdviceTypeID)
;


-- 
-- TABLE: DoctorAdviceType 
--

ALTER TABLE DoctorAdviceType ADD CONSTRAINT RefChargeCatalog72 
    FOREIGN KEY (ChargeCatalogCode)
    REFERENCES ChargeCatalog(ChargeCatalogCode)
;


-- 
-- TABLE: Employee 
--

ALTER TABLE Employee ADD CONSTRAINT RefDepartment194 
    FOREIGN KEY (DepartmentID)
    REFERENCES Department(DepartmentID)
;

ALTER TABLE Employee ADD CONSTRAINT RefJob33 
    FOREIGN KEY (JobID)
    REFERENCES Job(JobID)
;


-- 
-- TABLE: Inpatient 
--

ALTER TABLE Inpatient ADD CONSTRAINT RefFood66 
    FOREIGN KEY (FoodID)
    REFERENCES Food(FoodID)
;

ALTER TABLE Inpatient ADD CONSTRAINT RefSicknessLevel75 
    FOREIGN KEY (SicknessLevelID)
    REFERENCES SicknessLevel(SicknessLevelID)
;

ALTER TABLE Inpatient ADD CONSTRAINT RefBed76 
    FOREIGN KEY (BedID)
    REFERENCES Bed(BedID)
;

ALTER TABLE Inpatient ADD CONSTRAINT RefSickType105 
    FOREIGN KEY (SickTypeID)
    REFERENCES SickType(SickTypeID)
;

ALTER TABLE Inpatient ADD CONSTRAINT RefCountry106 
    FOREIGN KEY (CountryID)