POS系统开发

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;

   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)

二、数据库及表的创建

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<[email protected]) DO

    SELECT jp INTO @jp FROM hzpyb WHERE hz=SUBSTR(zw,@i,1);

    SET @pysx=CONCAT(@PYSX,@JP);

    SET @[email protected]+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 ;

验证触发器

二、应用系统开发

 安装32位ODBC驱动

1. VC_redist.x86

2. mysql-connector-odbc-8.0.28-win32