- DELIMITER $$
- DROP PROCEDURE IF EXISTS `mytest`.`newtest`$$
- CREATE PROCEDURE `newtest`(in bzxxinfo varchar(20),in swxxinfo varchar(20),in swyycfinfo varchar(20),
- in swajsinfo varchar(20),in swzfsinfo varchar(20),in swxtsczsinfo varchar(20),in cksrlinfo varchar(20),
- in shjclxinfo varchar(20),in shjcxminfo varchar(20),in brdainfo varchar(20),
- in brybinfo varchar(20),in brjtqjinfo varchar(20),in jwbsinfo varchar(20),
- in bryypcinfo varchar(20),in brpcjginfo varchar(20))
- BEGIN
- /*1、病症信息表 (BZXXINFO)*/
- SET @csql=concat("create table if not exists ",bzxxinfo, " (bzxxid int(11) NOT NULL auto_increment,
- bzxxfid int(4) NOT NULL default '0',
- bzxxmc varchar(100) NOT NULL default '',
- bzxxpym varchar(100) NOT NULL default '',
- bzms text,
- bzbj varchar(1) NOT NULL default '',
- PRIMARY KEY (`bzxxid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*2、食物信息表 (SWXXINFO)*/
- SET @csql=concat("create table if not exists ",swxxinfo, " ( swxxid int(11) NOT NULL auto_increment,
- swxxfid int(4) NOT NULL default '-100',
- swxxmc varchar(100) NOT NULL default '',
- swxxpym varchar(100) NOT NULL default '',
- lxbj varchar(1) NOT NULL default '',
- swxxbz varchar(300) default NULL,
- PRIMARY KEY (`swxxid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*3、食物一般营养成份信息表 (SWYYCFINFO)*/
- SET @csql=concat("create table if not exists ",swyycfinfo, " ( swxxybid int(4) NOT NULL default '0',
- edible int(4) default NULL,
- water float default NULL,
- energy_kcal float default NULL,
- energy_kj float default NULL,
- protein float default NULL,
- fat float default NULL,
- cho float default NULL,
- dietary_fiber_t float default NULL,
- dietary_fiber_s float default NULL,
- dietary_fiber_i float default NULL,
- cholesterol float default NULL,
- ash float default NULL,
- vita float default NULL,
- carotene float default NULL,
- retinol float default NULL,
- thiamin float default NULL,
- riboflavin float default NULL,
- vitb6 float default NULL,
- vitb12 float default NULL,
- pa float(9,2) default NULL,
- folate float default NULL,
- niacin float default NULL,
- vitc float default NULL,
- vitd float default NULL,
- vite_t float default NULL,
- vite_ate float default NULL,
- vitk float default NULL,
- ca float default NULL,
- p float default NULL,
- k float default NULL,
- na float default NULL,
- mg float default NULL,
- fe float default NULL,
- zn float default NULL,
- se float default NULL,
- cu float default NULL,
- mn float default NULL,
- i float default NULL,
- f float(9,2) default NULL,
- cr float(9,2) default NULL,
- mo float(9,2) default NULL,
- choline float(9,2) default NULL,
- biotin float(9,2) default NULL,
- yycfbz varchar(300) default NULL,
- PRIMARY KEY (`swxxybid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*4、食物氨基酸含量信息表 (SWAJSINFO)*/
- SET @csql=concat("create table if not exists ",swajsinfo, " (swxxajsid int(11) NOT NULL default '0',
- water_a float default NULL,
- protein_a float default NULL,
- iie float default NULL,
- leu float default NULL,
- lys float default NULL,
- saa float default NULL,
- met float default NULL,
- cys float default NULL,
- aaa float default NULL,
- phe float default NULL,
- tyr float default NULL,
- thr float default NULL,
- trp float default NULL,
- val float default NULL,
- arg float default NULL,
- his float default NULL,
- ala float default NULL,
- asp float default NULL,
- glu float default NULL,
- gly float default NULL,
- pro float default NULL,
- ser float default NULL,
- swajsbz varchar(300) default NULL,
- PRIMARY KEY (`swxxajsid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*5、食物脂肪酸含量信息表 (SWZFSINFO)*/
- SET @csql=concat("create table if not exists ",swzfsinfo, " (swxxzfsid int(4) NOT NULL default '0',
- fatt float default NULL,
- sfa float default NULL,
- mufa float default NULL,
- pufa float default NULL,
- un_k float default NULL,
- swzfsbz varchar(300) default NULL,
- PRIMARY KEY (`swxxzfsid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*6、食物血糖生成指数信息表 (SWXTSCZSINFO)*/
- SET @csql=concat("create table if not exists ",swxtsczsinfo, " ( swxxxtid int(4) NOT NULL default '0',
- gi float default NULL,
- sjly varchar(1) default NULL,
- swqthlbz varchar(300) default NULL,
- PRIMARY KEY (`swxxxtid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*7、居民膳食营养素参考摄入量信息表 (CKSRLINFO)*/
- SET @csql=concat("create table if not exists ",cksrlinfo, " (cksrlid int(11) NOT NULL auto_increment,
- dris_lx varchar(1) NOT NULL,
- nlfw varchar(10) NOT NULL,
- xbid varchar(1) NOT NULL,
- sjly varchar(1) NOT NULL,
- tlhdspid varchar(1) NOT NULL,
- energymj decimal(9,2) default NULL,
- energykcal decimal(9,2) default NULL,
- protein decimal(9,2) default NULL,
- fat decimal(9,2) default NULL,
- ca decimal(9,2) default NULL,
- p decimal(9,2) default NULL,
- k decimal(9,2) default NULL,
- na decimal(9,2) default NULL,
- mg decimal(9,2) default NULL,
- fe decimal(9,2) default NULL,
- i decimal(9,2) default NULL,
- zn decimal(9,2) default NULL,
- se decimal(9,2) default NULL,
- cu decimal(9,2) default NULL,
- f decimal(9,2) default NULL,
- cr decimal(9,2) default NULL,
- mn decimal(9,2) default NULL,
- mo decimal(9,2) default NULL,
- vita decimal(9,2) default NULL,
- vitd decimal(9,2) default NULL,
- vite decimal(9,2) default NULL,
- vitb1 decimal(9,2) default NULL,
- vitb2 decimal(9,2) default NULL,
- vitb6 decimal(9,2) default NULL,
- vitb12 decimal(9,2) default NULL,
- vitc decimal(9,2) default NULL,
- pa decimal(9,2) default NULL,
- fa decimal(9,2) default NULL,
- niacin decimal(9,2) default NULL,
- choline decimal(9,2) default NULL,
- biotin decimal(9,2) default NULL,
- cksrlbz varchar(300) default NULL,
- PRIMARY KEY (`cksrlid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*8、生化检查类型信息表 (SHJCLXINFO)*/
- SET @csql=concat("create table if not exists ",shjclxinfo, " (shjclxid int(11) NOT NULL auto_increment,
- shjclxbh int(4) NOT NULL default '0',
- shjclxmc varchar(100) NOT NULL default '',
- shjclxpym varchar(100) NOT NULL default '',
- PRIMARY KEY (`shjclxid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*9、生化检查项目信息表 (SHJCXMINFO)*/
- SET @csql=concat("create table if not exists ",shjcxminfo, " ( shjcxmid int(11) NOT NULL auto_increment,
- shjclxid int(4) NOT NULL default '0',
- shjcxmbh int(4) NOT NULL default '0',
- shjcxmmc varchar(100) NOT NULL default '',
- shjcxmpym varchar(100) NOT NULL default '',
- PRIMARY KEY (`shjcxmid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*10.病人基本档案信息表 (BRDAINFO)*/
- SET @csql=concat("create table if not exists ",brdainfo, " ( brdaid int(11) NOT NULL auto_increment,
- brdabh varchar(20) NOT NULL,
- brxm varchar(50) NOT NULL,
- pym varchar(50) NOT NULL,
- xbid varchar(1) NOT NULL,
- csrg varchar(10) NOT NULL,
- nl varchar(3) NOT NULL,
- brlx varchar(2) NOT NULL,
- brsg varchar(3) NOT NULL,
- brtz varchar(3) NOT NULL,
- tzzs varchar(10) NOT NULL,
- fpcdid varchar(1) NOT NULL,
- tlhdid varchar(1) NOT NULL,
- czrq varchar(10) NOT NULL,
- zyzdid int(4) NOT NULL,
- zljg varchar(2000) NOT NULL,
- ysxg varchar(50) NOT NULL,
- yzbm varchar(10) NOT NULL,
- lxdz varchar(100) NOT NULL,
- lxdh varchar(30) NOT NULL,
- zxsj varchar(30) NOT NULL,
- PRIMARY KEY (`brdaid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*11.病人一般信息表 (BRYBINFO) */
- SET @csql=concat("create table if not exists ",brybinfo, " (
- brybid int(11) NOT NULL auto_increment,
- brdaid int(4) NOT NULL,
- mrwy varchar(10) NOT NULL,
- cstz varchar(3) NOT NULL,
- tl varchar(3) NOT NULL,
- cjpm varchar(3) NOT NULL,
- kds varchar(2) NOT NULL,
- hwhd varchar(2) NOT NULL,
- pftx varchar(10) NOT NULL,
- pzhd varchar(3) NOT NULL,
- sbzw varchar(3) NOT NULL,
- yw varchar(3) NOT NULL,
- tw varchar(3) NOT NULL,
- hd varchar(10) NOT NULL,
- dh varchar(10) NOT NULL,
- db varchar(50) NOT NULL,
- fat varchar(10) NOT NULL,
- fatb varchar(10) NOT NULL,
- smm varchar(50) NOT NULL,
- obb varchar(10) NOT NULL,
- bmr varchar(50) NOT NULL,
- scnl varchar(10) NOT NULL,
- sczf varchar(10) NOT NULL,
- scsf varchar(10) NOT NULL,
- zxsj varchar(30) NOT NULL,
- PRIMARY KEY (`brybid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*12 病人既往病史信息表 (JWBSINFO) */
- SET @csql=concat("create table if not exists ",jwbsinfo, " (
- `jwbsid` int(11) NOT NULL auto_increment,
- `brdaid` int(4) NOT NULL,
- `bzxxid` int(4) NOT NULL,
- `zxsj` varchar(30) NOT NULL,
- PRIMARY KEY (`jwbsid`))");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*13.病人家庭、起居和饮食习惯信息表 (BRJTQJINFO)*/
- SET @csql=concat("create table if not exists ",brjtqjinfo, " (
- `brdaid` varchar(4) NOT NULL,
- `jtptz` varchar(20) NOT NULL,
- `yxhl` varchar(10) NOT NULL,
- `ztxh` varchar(10) NOT NULL,
- `wcyc` varchar(10) NOT NULL,
- `zccs` varchar(10) NOT NULL,
- `xccs` varchar(10) NOT NULL,
- `pjxf` varchar(10) NOT NULL,
- `jtsr` varchar(10) NOT NULL,
- `fqnl` varchar(3) NOT NULL,
- `fqsg` varchar(3) NOT NULL,
- `fqtz` varchar(3) NOT NULL,
- `fqbmi` varchar(10) NOT NULL,
- `fqjb` varchar(50) NOT NULL,
- `mqnl` varchar(3) NOT NULL,
- `mqsg` varchar(3) NOT NULL,
- `mqtz` varchar(3) NOT NULL,
- `mqbmi` varchar(10) NOT NULL,
- `mqjb` varchar(50) NOT NULL,
- `jcsd` varchar(10) NOT NULL,
- `sqys` varchar(50) NOT NULL,
- `shsw` varchar(100) NOT NULL,
- `qtxx` varchar(1000) NOT NULL,
- `zxsj` varchar(30) NOT NULL,
- PRIMARY KEY (`brdaid`)
- )");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*14 病人营养评测信息表 (BRYYPCINFO)*/
- SET @csql=concat("create table if not exists ",bryypcinfo, " (
- `bryypcid` int(11) NOT NULL auto_increment,
- `brdaid` int(4) NOT NULL,
- `yypcff` varchar(50) NOT NULL,
- `yypczk` varchar(200) NOT NULL,
- `zdxx` varchar(200) NOT NULL,
- `bzxxid` int(4) NOT NULL,
- `zlxgpj` varchar(1000) NOT NULL,
- `zxsj` varchar(30) NOT NULL,
- PRIMARY KEY (`bryypcid`)
- )");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*15 病人营养评测选项结果信息表 (BRPCJGINFO)*/
- SET @csql=concat("create table if not exists ",brpcjginfo, " (
- `brpcjgid` int(11) NOT NULL auto_increment,
- `brdaid` int(4) NOT NULL,
- `yypcff` varchar(50) NOT NULL,
- `yypczk` varchar(200) NOT NULL,
- `zdxx` varchar(200) NOT NULL,
- `zxsj` varchar(30) NOT NULL,
- PRIMARY KEY (`brpcjgid`)
- )");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /***********************************表之间的主外键关系*************************************************************/
- /*病人基本档案信息表 (BRDAINFO)与病人一般信息表 (BRYBINFO)的关联关系*/
- /*SET @csql= concat("alter table ",brybinfo," add constraint FKaaa foreign key (brdaid) references ",brdainfo,"(brdaid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /*病人基本档案信息表 (BRDAINFO)与病人家庭、起居和饮食习惯信息表 (BRJTQJINFO)的关联关系1111*/
- /*SET @csql= concat("alter table ", brjtqjinfo ," add constraint FKaaa foreign key (brdaid) references ",brdainfo,"(brdaid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /* 病人基本档案信息表 (BRDAINFO)与病人既往病史信息表 (JWBSINFO)的关联关系*/
- /*SET @csql= concat("alter table ", jwbsinfo ," add constraint FKaaa foreign key (brdaid) references ",brdainfo,"(brdaid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /* 病症信息表 (BZXXINFO)与病人既往病史信息表 (JWBSINFO)的关联关系*/
- /*SET @csql= concat("alter table ", jwbsinfo ," add constraint FKaaa foreign key (bzxxid) references ",bzxxinfo,"(bzxxid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /* 病人基本档案信息表 (BRDAINFO)与病人营养评测信息表 (BRYYPCINFO)的关联关系111*/
- /*SET @csql= concat("alter table ", bryypcinfo ," add constraint FKaaa foreign key (brdaid) references ",brdainfo,"(brdaid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /* 病症信息表 (BZXXINFO)与病人营养评测信息表 (BRYYPCINFO)的关联关系*/
- /*SET @csql= concat("alter table ", bryypcinfo ," add constraint FKaaa foreign key (bzxxid) references ",bzxxinfo,"(bzxxid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;
- /* 病人基本档案信息表 (BRDAINFO)与病人营养评测选项结果信息表 (BRPCJGINFO)的关联关系*/
- /*SET @csql= concat("alter table ", brpcjginfo ," add constraint FKaaa foreign key (brdaid) references ",brdainfo,"(brdaid)");
- PREPARE create_stmt from @csql;
- EXECUTE create_stmt;*/
- END$$