进销存业务一: 商品信息维护的数据库端代码
一、基础表:
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 ;