存储过程实现创建表和外键等

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$$

DELIMITER ;