POS系统教学例子的开发过程
一、数据库及管理工具的安装
1、安装MySQL8.0
2、安装SQLyog
i. SQLyog连接MySQL8.0之前需要先修改root的认证方式,否则会出2058的错误
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ii. 创建函数或触发器前要先修变量的值
SET GLOBAL log_bin_trust_function_creators=1;
否则会出 错误代码: 1418
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
创建spmcb和spkcb
CREATE TABLE `spmcb` (
`spid` int NOT NULL AUTO_INCREMENT COMMENT '商品名称表',
`spmc` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '货品名称',
`sptm` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '货品条码',
`dw` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '计量单位',
`dj` decimal(9,2) DEFAULT NULL COMMENT '零售价',
`spzt` tinyint DEFAULT '0' COMMENT '商品状态(0 正常, 1 下架)',
`mcsx` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名称缩写(由触发器自行维护)',
PRIMARY KEY (`spid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
CREATE TABLE `spkcb` (
`kcid` int NOT NULL AUTO_INCREMENT COMMENT '库存id',
`spid` int DEFAULT NULL COMMENT '商品id(用来代表货品)',
`kcsl` decimal(18,3) DEFAULT '0.000' COMMENT '(当前)库存数量',
`zrkc` decimal(18,3) DEFAULT '0.000' COMMENT '昨日库存(用于日清)',
`yckc` decimal(18,3) DEFAULT '0.000' COMMENT '月初库存(用于月结)',
PRIMARY KEY (`kcid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
3、创建PysxCx()函数
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER $$
CREATE FUNCTION `lx1`.`PysxCx`(zw VARCHAR(20))
RETURNS VARCHAR(10)
BEGIN
SET @pysx='';
SET @l=CHAR_LENGTH(zw);
SET @i=1;
WHILE (@i<=@l) DO
SELECT jp INTO @jp FROM hzpyb WHERE hz=SUBSTR(zw,@i,1);
SET @pysx=CONCAT(@PYSX,@JP);
SET @I=@I+1;
END WHILE;
RETURN @PYSX;
END$$
DELIMITER ;
4、测试PysxCx()函数
SELECT pysxcx('张三丰');
5、编写存储过程SpxxWh
DELIMITER $$
CREATE PROCEDURE `lx1`.`SpxxWh`(spid INT,spmc VARCHAR(50),sptm VARCHAR(20),dw VARCHAR(10),dj NUMERIC(9,2),spzt TINYINT)
BEGIN
IF spid=0 THEN
INSERT INTO spmcb (spmc,sptm,dw,dj) VALUE (spmc,sptm,dw,dj);
ELSEIF spid>0 THEN
UPDATE spmcb SET `spmcb`.`spmc`=spmc,`spmcb`.`sptm`=sptm,`spmcb`.`dw`=dw,`spmcb`.`dj`=dj,`spmcb`.`spzt`=spzt WHERE `spmcb`.`spid`=spid;
ELSE
DELETE FROM spmcb WHERE `spmcb`.`spid`=-spid;
END IF;
END$$
DELIMITER ;
6、测试存储过程SpxxWh
i. 新增
CALL spxxwh(0,'百事可乐','101','瓶',3,0);
SELECT * FROM spmcb;
ii.修改
CALL spxxwh(4,'百事可乐','101','瓶',3.5,0);
SELECT * FROM spmcb;
iii.删除
CALL spxxwh(-4,'百事可乐','101','瓶',3.5,0);
SELECT * FROM spmcb;
6、编写spmcb上的触发器
i. insert before
DELIMITER $$
CREATE TRIGGER `lx1`.`Clmcsx_before_insert_spmcb` BEFORE INSERT ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.spmc);
END$$
DELIMITER ;
ii. insert after
DELIMITER $$
CREATE TRIGGER `lx1`.`Insert_Spkcb_after_insert` AFTER INSERT ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
INSERT INTO spkcb (spid) VALUE (new.spid);
END$$
DELIMITER ;
iii update before
DELIMITER $$
CREATE TRIGGER `lx1`.`Update_mcsx_before_update_spmc` BEFORE UPDATE ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
IF (new.spmc<>old.spmc) THEN
SET new.mcsx=pysxcx(new.spmc);
END IF;
END$$
DELIMITER ;
iv delete after
DELIMITER $$
CREATE TRIGGER `lx1`.`Delete_spkcb_after_delete_spmcb` AFTER DELETE ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
DELETE FROM spkcb WHERE spid=old.spid;
END$$
DELIMITER ;