数据库实验

进销存业务一: 商品信息维护的数据库端代码
一、基础表:
1、商品信息表
CREATE TABLE `spxxb` (
  `spid` intྫ) NOT NULL AUTO_INCREMENT,
  `spmc` varchar࿒) DEFAULT NULL,
  `sptm` varcharྴ) DEFAULT NULL,
  `jldw` varcharྪ) DEFAULT NULL,
  `spzt` intྫ) DEFAULT ';0';,
  `lsj` decimalƙ,2) DEFAULT NULL,
  `mcsx` varcharྪ) DEFAULT NULL,
  PRIMARY KEY (`spid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2、商品库存表
CREATE TABLE `spkcb` (
  `kcid` intྫ) NOT NULL AUTO_INCREMENT,
  `spid` intྫ) DEFAULT NULL,
  `kcsl` intྫ) DEFAULT ';0';,
  PRIMARY KEY (`kcid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

二、自定义函数
DELIMITER $$
CREATE FUNCTION `PysxCx`(zw varchar࿒)) RETURNS varcharྪ) CHARSET utf8mb4
BEGIN
  set @pysx=';';;
  set @jp=';';;
  set @l=char_length(zw);
  set @i=1;
  while (@i<=@l) do
    select jp into @jp from hzpyb where hz=substring(zw,@i,1);
    set @pysx=concat(@pysx,@jp);
    set @i=@i+1;
  end while;
  return @pysx;
END */$$
DELIMITER ;

三、存储过程
DELIMITER $$

CREATE PROCEDURE `SpxxWh`(whfs int,spid int,spmc varchar࿒),sptm varcharྴ),jldw varcharྪ),spzt smallint,lsj numericƙ,2))
BEGIN
  if whfs=0 then
    insert into spxxb (spmc,sptm,jldw,lsj) value (spmc,sptm,jldw,lsj);
  elseif whfs=1 then
    update spxxb set `spxxb`.`spmc`=spmc,`spxxb`.`sptm`=sptm,`spxxb`.`jldw`=jldw,`spxxb`.`spzt`=spzt,`spxxb`.`lsj`=lsj where `spxxb`.`spid`=spid;
  else
    delete from spxxb where `spxxb`.`spid`=spid;
  end if;
END */$$
DELIMITER ;

四、触发器
DELIMITER $$
CREATE  TRIGGER `Update_spxxb_mcsx_before_insert_spxxb` BEFORE INSERT ON `spxxb` FOR EACH ROW BEGIN
  set new.mcsx=pysxcx(new.spmc);
END */$$
DELIMITER ;

DELIMITER $$
CREATE  TRIGGER `Update_mcsx_before_update_spxxb_spmc` BEFORE UPDATE ON `spxxb` FOR EACH ROW BEGIN
  if new.spmc<>old.spmc then
    set new.mcsx=pysxcx(new.spmc);
  end if;
END */$$

DELIMITER $$
CREATE  TRIGGER `Delete_spkcb_before_delete_spxxb` BEFORE DELETE ON `spxxb` FOR EACH ROW BEGIN
  delete from spkcb where spid=old.spid;
END */$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `Delete_spkcb_before_delete_spxxb` BEFORE DELETE ON `spxxb` FOR EACH ROW BEGIN
  DELETE FROM spkcb WHERE spid=old.spid;
END;$$
DELIMITER ;