HIS数据库

最新推荐文章于 2024-12-15 08:21:18 发布

原创最新推荐文章于 2024-12-15 08:21:18 发布 · 3.9k 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

该博客介绍了HIS（医院信息系统）数据库的设计，包括ER图的使用，以及数据库中的核心操作，如创建数据库、存储过程、挂号、缴费、开药、退费、退药和诊断等流程。

ER图
ER图
创建数据库

-- 删除已有的HIS
drop database HIS;
-- 创建数据库
create database HIS;
-- 调用数据库HIS
use HIS;
-- 创建科室表
create table department (
DeptCategory int primary key,
DepartName varchar(10) not null,
DepartType int not null
);
-- 创建医生表
create table doctor (
DID int primary key,
DName varchar(20) not null,
DeptCategory int not null,
DocTitle int not null,
DPosition varchar(20) not null,
foreign key (DeptCategory) references department(DeptCategory)
);
-- 创建排班表
create table schedule (
DID int not null,
WorkTime date not null,
primary key(DID,WorkTime),
constraint schedule_fk foreign key(DID) references doctor(DID)
);
-- 创建挂号表
create table regist (
RID int primary key auto_increment,
PID char(18) not null,
PName varchar(20) not null,
Gender int not null,
Birthday date not null,
Age char(3),
AgeType int,
Address text,
DiaDate DATE not null,
NoonType int not null,
DeptCategory int not null,
DID int not null,
DocTitle int not null,
FeeType int not null,
IfRecord int not null,
PerID char(3) not null,
RCondition int not null,
RegisTime datetime not null
);
-- 创建疾病表 
create table decease (
DecID int primary key auto_increment,
DecName varchar(20) not null,
DecType int not null
);
-- 创建项目表
create table treatment (
TreatID int primary key auto_increment,
TreatPrice float not null,
TreatName varchar(20) not null
);
-- 创建处方表
create table prescription (
PreID int primary key auto_increment,
PreName varchar(20) not null,
RID int not null,
PreTime datetime not null,
BCondition int not null
);
-- 创建病历表
create table record (
RecordID int primary key auto_increment,
PID char(18) not null,
RID int not null,
Result int not null,
DiaTime datetime not null,
PreID int not null,
constraint record_fk foreign key(PreID) references prescription(PreID)
);
-- 创建缴费表
create table bill (
PayID int primary key auto_increment,
PreID int not null,
OperID char(3) not null,
DocTitle int not null,
Total float not null,
FeeType int not null,
BillType char(2) not null
);
-- 创建药品表
create table medicine (
MedID int primary key auto_increment,
MedName varchar(20) not null,
Drugs_Dosage varchar(20) not null,
Drugs_Type char(2) not null,
MedPrice float not null
);
-- 创建药房表
create table medroom (
MedID int primary key,
NumLeft int not null,
foreign key (MedID) references medicine(MedID)
);
-- 创建检查项目表
create table treatlist (
ListID int primary key auto_increment,
TreatID int not null,
PreID int not null,
TCondition int not null,
foreign key (TreatID) references treatment (TreatID),
constraint treatlist_fk foreign key(PreID) references prescription(PreID)
);
-- 创建划价表
create table medlist (
ListID int primary key auto_increment, 
MedID int not null,
MedNum int not null,
PreID int not null,
MCondition int not null,
foreign key (MedID) references medicine (MedID),
constraint medlist_fk foreign key(PreID) references prescription(PreID)
);
-- 输入药品信息
alter table medicine auto_increment= 1;
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('注射用甲氨喋呤','1g×1支','西药',15.73);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('红芪','10g/袋','中药',30.79);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('50%葡萄糖注射液(塑瓶)','10:20ml×1支','西药',25.16);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('磷酸奥司他韦胶囊(达菲)','75mg×10粒/盒','西药',60.96);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('黄连颗粒','0.5g/3g袋','中药',1.07);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('灵芝','1000mg/g','中药',28.67);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('盐酸氨酮戊酸散（外用）','118mg×1瓶','西药',19.51);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('康妇消炎栓','2.000g*9粒/盒','西药',7.63);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('补中益气丸','0.375g*240粒/瓶','中药',44.58);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('逍遥丸','0.375g*240粒/瓶','中药',36.17);
-- 输入科室信息(DepartType 1代表外科,0代表内科)
insert into department values (1,'心血管内科',0);
insert into department values (2,'神经内科',0);
insert into department values (3,'呼吸内科',0);
insert into department values (4,'肛肠外科',1);
insert into department values (5,'皮肤科',1);
-- 输入医生信息(DocTitle 1代表普通号,2代表专家号,3代表急诊号)
insert into doctor values (1,'华佗',1,3,'主任医师');
insert into doctor values (2,'宋慈',1,1,'主治医师');
insert into doctor values (3,'张仲景',2,3,'主任医师');
insert into doctor values (4,'孙思邈',2,2,'副主任医师');
insert into doctor values (5,'皇甫谧',3,1,'主治医师');
insert into doctor values (6,'叶桂',3,1,'主治医师');
insert into doctor values (7,'薛生白',4,1,'主治医师');
insert into doctor values (8,'扁鹊',4,3,'主任医师');
insert into doctor values (9,'葛洪',5,2,'副主任医师');
insert into doctor values (10,'李时珍',5,3,'主任医师');
insert into doctor values (11,'白求恩',1,2,'主任医师');
-- 输入疾病信息
alter table decease auto_increment= 1;
insert into decease (DecName,DecType) values ('冠心病',1);
insert into decease (DecName,DecType) values ('心脏病',1);
insert into decease (DecName,DecType) values ('神经衰弱',2);
insert into decease (DecName,DecType) values ('神经病',2);
insert into decease (DecName,DecType) values ('哮喘',3);
insert into decease (DecName,DecType) values ('肺结核',3);
insert into decease (DecName,DecType) values ('痔疮',4);
insert into decease (DecName,DecType) values ('消化不良',4);
insert into decease (DecName,DecType) values ('脚气',5);
insert into decease (DecName,DecType) values ('荨麻疹',5);
-- 输入挂号信息(Gender 0代表女1代表男,AgeType 分为幼儿儿童少年青年中年老年,NoonType 1代表上午2代表下午3代表夜间,FeeType 1代表自费2代表医保3代表低保,IfRecord 0代表不要1代表要病历本)
alter table regist auto_increment= 1;
insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000005','五娃',1,'1960-04-01','60','6','辽宁省沈阳市浑南区','2020-06-04',3,1,11,2,2,1,'101',1,'2020-06-04 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000005','五娃',1,'1960-04-01','60','6','辽宁省沈阳市浑南区','2020-06-15',1,1,11,2,2,0,'103',1,'2020-06-15 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000005','五娃',1,'1960-04-01','60','6','辽宁省沈阳市浑南区','2020-06-23',2,1,11,2,2,0,'102',1,'2020-06-23 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000006','六娃',0,'2008-04-01','12','3','辽宁省沈阳市浑南区','2020-06-29',1,2,4,2,1,1,'103',1,'2020-06-29 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000007','七娃',1,'1970-04-01','50','5','辽宁省沈阳市浑南区','2020-06-29',1,4,8,3,2,0,'101',1,'2020-06-29 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000004','四娃',0,'2010-04-01','10','2','辽宁省沈阳市浑南区','2020-06-29',2,5,9,2,3,1,'101',1,'2020-06-29 08:08:08');
insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000001','大娃',1,'2000-04-01','20','4','辽宁省沈阳市浑南区','2020-07-02',1,3,5,1,1,0,'101',1,'2020-07-02 08:08:08'); 
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000003','三娃',1,'1980-04-01','40','5','辽宁省沈阳市浑南区','2020-07-02',2,4,7,1,2,0,'103',1,'2020-07-02 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000002','二娃',0,'2018-04-01','2','1','辽宁省沈阳市浑南区','2020-07-04',1,3,6,1,1,1,'102',0,'2020-07-04 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000008','师傅',1,'1960-04-01','60','6','辽宁省沈阳市浑南区','2020-07-04',2,3,6,1,2,1,'102',0,'2020-07-04 08:08:08');
  insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000009','大师兄',1,'1980-04-01','40','5','辽宁省沈阳市浑南区','2020-07-04',2,3,6,1,2,0,'102',0,'2020-07-04 08:08:08');
  insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000010','二师弟',1,'1990-04-01','30','4','辽宁省沈阳市浑南区','2020-07-04',2,3,6,1,2,0,'102',0,'2020-07-04 08:08:08');
  insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000011','三师弟',1,'2010-04-01','10','3','辽宁省沈阳市浑南区','2020-07-04',2,3,6,1,2,0,'102',0,'2020-07-04 08:08:08');
  insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000012','白龙马',0,'2019-04-01','1','1','辽宁省沈阳市浑南区','2020-07-04',2,3,6,1,2,0,'102',0,'2020-07-04 08:08:08');
 insert into regist (PID,PName,Gender,Birthday,Age,AgeType,Address,DiaDate,NoonType,DeptCategory,DID,DocTitle,Feetype,IfRecord,PerID,RCondition,RegisTime)
 values ('100000000000000005','五娃',1,'1960-04-01','60','6','辽宁省沈阳市浑南区','2020-07-04',3,1,11,2,2,0,'102',0,'2020-07-04 08:08:08');
-- 输入处方信息
alter table prescription auto_increment= 1;
insert into prescription (PreName,RID,PreTime,BCondition)
values (1,1,'2020-06-04 21:21:21',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (1,2,'2020-06-15 10:10:10',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (1,3,'2020-06-23 16:16:16',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (4,4,'2020-06-29 08:08:08',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (7,5,'2020-06-29 09:09:09',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (10,6,'2020-06-29 15:15:15',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (5,7,'2020-07-02 11:11:11',1);
insert into prescription (PreName,RID,PreTime,BCondition)
values (8,8,'2020-07-02 14:14:14',0);
-- 输入病历表
alter table record auto_increment= 1;
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000005',1,1,'2020-06-04 21:21:21',1);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000005',2,1,'2020-06-15 10:10:10',2);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000005',3,1,'2020-06-23 16:16:16',3);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000006',4,4,'2020-06-29 08:08:08',4);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000007',5,7,'2020-06-29 09:09:09',5);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000004',6,10,'2020-06-29 15:15:15',6);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000001',7,5,'2020-07-02 11:11:11',7);
insert into record (PID,RID,Result,DiaTime,PreID) values ('100000000000000003',8,8,'2020-07-02 14:14:14',8);
-- 输入项目表
alter table treatment auto_increment= 1;
insert into treatment (TreatPrice,TreatName) values (200,'脑CT');
insert into treatment (TreatPrice,TreatName) values (100,'B超');
insert into treatment (TreatPrice,TreatName) values (200,'彩超');
insert into treatment (TreatPrice,TreatName) values (200,'心电图');
insert into treatment (TreatPrice,TreatName) values (5,'验血');
insert into treatment (TreatPrice,TreatName) values (10,'验尿');
insert into treatment (TreatPrice,TreatName) values (10,'打针');
-- 输入药房表
insert into medroom values (1,1000);
insert into medroom values (2,1000);
insert into medroom values (3,1000);
insert into medroom values (4,1000);
insert into medroom values (5,1000);
insert into medroom values (6,1000);
insert into medroom values (7,1000);
insert into medroom values (8,1000);
insert into medroom values (9,1000);
insert into medroom values (10,1000);
-- 输入划价表
alter table medlist auto_increment= 1;
insert into medlist(MedID,MedNum,PreID,MCondition) values (5,2,1,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (5,2,2,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (5,2,3,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (2,1,4,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (8,3,5,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (3,1,6,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (4,2,7,1);
insert into medlist(MedID,MedNum,PreID,MCondition) values (6,1,8,1);
--  输入检测项目表
alter table treatlist auto_increment= 1;
insert into treatlist(TreatID,PreID,TCondition) values (4,1,1);
insert into treatlist(TreatID,PreID,TCondition) values (4,2,1);
insert into treatlist(TreatID,PreID,TCondition) values (4,3,1);
insert into treatlist(TreatID,PreID,TCondition) values (1,4,1);
insert into treatlist(TreatID,PreID,TCondition) values (5,6,1);
insert into treatlist(TreatID,PreID,TCondition) values (7,7,1);
insert into treatlist(TreatID,PreID,TCondition) values (5,8,1);
-- 缴费表
alter table bill auto_increment= 1;
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (1,'001',2,202.14,2,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (2,'002',2,202.14,2,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (3,'001',2,202.14,2,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (4,'002',2,230.79,1,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (5,'001',3,22.89,2,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (5,'001',3,22.89,2,'退费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (6,'002',2,30.16,3,'缴费');
insert into bill (PreID,OperID,DocTitle,Total,FeeType,BillType)  values (7,'001',1,131.92,1,'缴费');
-- 排班表
insert into schedule values (1,'2020-04-01');
insert into schedule values (3,'2020-04-04');
insert into schedule values (1,'2020-04-04');
insert into schedule values (2,'2020-04-06');
insert into schedule values (1,'2020-04-07');
insert into schedule values (2,'2020-04-08');
insert into schedule values (3,'2020-04-10');
insert into schedule values (1,'2020-04-11');
insert into schedule values (1,'2020-04-14');
insert into schedule values (3,'2020-04-16');
insert into schedule values (1,'2020-04-17');
insert into schedule values (1,'2020-04-20');
insert into schedule values (2,'2020-04-23');
insert into schedule values (2,'2020-04-25');
insert into schedule values (1,'2020-04-27');
insert into schedule values (11,'2020-06-04');
insert into schedule values (11,'2020-06-15');
insert into schedule values (11,'2020-06-23');
insert into schedule values (4,'2020-06-29');
insert into schedule values (8,'2020-06-29');
insert into schedule values (9,'2020-06-29');
insert into schedule values (5,'2020-07-02');
insert into schedule values (7,'2020-07-02');
insert into schedule values (6,'2020-07-04');
insert into schedule values (11,'2020-07-04');

存储过程
出药

delimiter $$
CREATE PROCEDURE medroom (in p_RecordID INT)
BEGIN
DECLARE t_PreID INT;
DECLARE t_ListID INT DEFAULT 1;
DECLARE t_NumLeft INT;
SET t_PreID =(SELECT PreID FROM record WHERE RecordID=p_RecordID);
increment: LOOP
IF ((SELECT MCondition FROM medlist WHERE ListID=t_ListID)=0 AND (SELECT PreID FROM medlist WHERE ListID=t_ListID)=t_PreID) THEN 
UPDATE medlist SET MCondition=1 WHERE ListID=t_ListID;
SET t_NumLeft=(SELECT NumLeft FROM medroom WHERE MedID=(SELECT MedID FROM medlist WHERE ListID=t_ListID))-(SELECT MedNum FROM medlist WHERE ListID=t_ListID);
UPDATE medroom SET NumLeft=t_NumLeft WHERE MedID=(SELECT MedID FROM medlist WHERE ListID=t_ListID) ;
SELECT '操作成功';
SET t_ListID = t_ListID + 1;
IF t_ListID=(SELECT MAX(ListID) FROM medlist) AND (SELECT MCondition FROM medlist WHERE ListID=t_ListID)=0 THEN 
SELECT '操作失败';
LEAVE increment;
END IF;
END IF;
END LOOP increment;
END$$

挂号

delimiter $$
CREATE PROCEDURE regist (in p_PID CHAR(18),in p_PName VARCHAR(20),in p_Gender CHAR(1),in p_Birthday CHAR(10),
in p_Address TEXT,in p_DiaDate CHAR(10), in p_NoonType CHAR(2),
in p_DeptCategory INT,in p_DID INT,in p_DocTitle INT,in p_FeeType INT,in p_IfRecord CHAR(1),in p_PerID CHAR(3))
BEGIN
DECLARE t_Gender INT ;
DECLARE t_Age INT;
DECLARE t_AgeType INT ;
DECLARE t_NoonType INT ;
DECLARE t_IfRecord INT ;
DECLARE t_RID INT;
-- 性别转换
IF p_Gender='男性' THEN SET t_Gender=1;
ELSE SET t_Gender=0;
END IF; 
-- 计算年龄
SET t_Age=TIMESTAMPDIFF(YEAR,p_Birthday, CURDATE()) ;
-- 年龄类型转换
IF t_Age<2 THEN SET t_AgeType=1;
ELSEIF t_Age BETWEEN 2 AND 12 THEN SET t_AgeType =2;
ELSEIF t_Age BETWEEN 13 AND 18 THEN SET t_AgeType =3;
ELSEIF t_Age BETWEEN 19 AND 40 THEN SET t_AgeType =4;
ELSEIF t_Age BETWEEN 41 AND 59 THEN SET t_AgeType =5;
ELSE SET t_AgeType =6;
END IF ;
-- 午别转换
IF p_NoonType='上午' THEN SET t_NoonType =1;
ELSEIF p_NoonType='下午' THEN SET t_NoonType =2;
ELSE SET t_NoonType =3;
END IF;
-- 要病历本转换
IF p_IfRecord='要' THEN SET t_IfRecord =1;
ELSE SET t_IfRecord =0;
END IF;
IF p_PID=NULL OR p_PName=NULL OR t_Gender=NULL OR p_Birthday=NULL OR p_Address=NULL OR p_Diadate=NULL OR t_NoonType=NULL OR p_DeptCategory=NULL OR p_DID=NULL OR p_DocTitle=NULL OR p_FeeType=NULL OR t_IfRecord=NULL OR p_PerID=NULL
THEN
SELECT '操作失败';
ELSE SELECT '操作成功';
SET t_RID=(SELECT MAX(RID) FROM regist )+1;
INSERT INTO regist 
VALUES (t_RID,p_PID,p_PName,t_Gender,STR_TO_DATE(p_Birthday,'%Y-%m-%d'),t_Age,t_AgeType,p_Address,STR_TO_DATE(p_Diadate,'%Y-%m-%d'),t_NoonType,p_DeptCategory,p_DID,p_DocTitle,p_FeeType,t_IfRecord,p_PerID,0,CURRENT_TIMESTAMP);
END IF;
END$$

缴费

delimiter $$
CREATE PROCEDURE pay (in p_PreID INT,in p_OperID INT)
BEGIN
DECLARE t_DocTitle INT; 
DECLARE t_Total FLOAT DEFAULT 0;
DECLARE t_FeeType INT;
SET t_DocTitle = (SELECT DocTitle FROM regist WHERE RID=(SELECT RID FROM prescription WHERE PreID=p_PreID));
SET t_Total=(SELECT TRUNCATE(sum(MedPrice*(SELECT  MedNum FROM medlist WHERE PreID=p_PreID)),2) FROM medicine WHERE MedID=(SELECT MedID FROM medlist WHERE PreID=p_PreID));
SET t_FeeType=(SELECT FeeType FROM regist WHERE RID=(SELECT RID FROM prescription WHERE PreID=p_PreID));
IF (SELECT BCondition FROM prescription WHERE PreID=p_PreID)=0 THEN
INSERT INTO bill(PreID,OperID,DocTitle,Total,FeeType,BillType) VALUES(p_PreID,p_OperID,t_DocTitle,t_Total,t_FeeType,'缴费');
UPDATE prescription SET BCondition=1 WHERE PreID=p_PreID;
SELECT '操作成功';
ELSE SELECT '操作失败';
END IF;
END$$

开药

delimiter $$
CREATE PROCEDURE medicinelist (in p_RID INT, in p_MedName VARCHAR(20) ,in p_MedNum INT)
BEGIN
DECLARE t_MedID INT; 
DECLARE t_PreID INT;
SET t_PreID =(SELECT PreID FROM prescription WHERE RID=p_RID);
SET t_MedID =(SELECT MedID FROM medicine WHERE MedName=p_MedName);
IF t_PreID in (SELECT PreID FROM prescription )
THEN
INSERT INTO medlist(MedID,MedNum,PreID,MCondition) VALUES (t_MedID,p_MedNum,t_PreID,0);
SELECT '操作成功';
ELSE SELECT '操作失败';
END IF;
END$$

退费

delimiter $$
CREATE PROCEDURE payback (in p_PreID INT,in p_OperID INT)
BEGIN
DECLARE t_DocTitle INT;
DECLARE t_Total FLOAT;
DECLARE t_FeeType INT;
IF (SELECT BCondition FROM prescription WHERE PreID=p_PreID)=1 THEN
SET t_DocTitle=(SELECT DocTitle FROM bill WHERE PreID=p_PreID);
SET t_Total=(SELECT Total FROM bill WHERE PreID=p_PreID);
SET t_FeeType=(SELECT FeeType FROM bill WHERE PreID=p_PreID);
INSERT INTO bill(PreID,OperID,DocTitle,Total,FeeType,BillType) VALUES(p_PreID,p_OperID,t_DocTitle,t_Total,t_FeeType,'退费');
UPDATE prescription SET BCondition=-1 WHERE PreID=p_PreID;
SELECT '操作成功';
ELSE SELECT '操作失败';
END IF;
END$$

退药

delimiter $$
CREATE PROCEDURE unregist (in p_RID INT )
BEGIN
IF (SELECT RCondition FROM regist WHERE RID=p_RID)=0
THEN SELECT '操作成功';
UPDATE regist SET RCondition=-1 WHERE RID=p_RID; 
ELSE SELECT '操作失败';
END IF;
END$$

诊断

delimiter $$
CREATE PROCEDURE prescription (in p_RID INT ,in p_PreName VARCHAR(20))
BEGIN
DECLARE t_decease INT;
DECLARE t_PID CHAR(18);
DECLARE t_PreID INT;
SET t_decease =(SELECT DecID FROM decease WHERE DecName=p_PreName);
SET t_PID=(SELECT PID FROM regist WHERE RID=p_RID);
IF (SELECT RCondition FROM regist WHERE RID=p_RID)=0
THEN
INSERT INTO prescription (PreName,RID,PreTime,BCondition) VALUES (t_decease,p_RID,CURRENT_TIMESTAMP,0);
SET t_PreID=(SELECT MAX(PreID) FROM prescription);
INSERT INTO record (PID,RID,Result,DiaTime,PreID) VALUES (t_PID,p_RID,t_decease,CURRENT_TIMESTAMP,t_PreID);
UPDATE regist SET RCondition=1 WHERE RID=p_RID;
SELECT '操作成功';
ELSE SELECT '操作失败';
END IF;
END$$