你怎么知道我什么都不会

阿巴阿巴高可用

阿玛阿玛碰运气

文心一言

查询的存储过程

创建货品信息查询HpxxCx存储过程,能够实现对货品信息的查询操作

DROP PROCEDURE IF EXISTS `HpxxCx`;
delimiter ;;
CREATE PROCEDURE `HpxxCx`(name char(50))READS SQL DATA 
BEGINselect * from hpmcb where hpmc=name;
END
;;
delimiter ;

增删改的存储过程

创建货品信息维护HpxxWh存储过程,能够实现对货品信息的增删改操作

DROP PROCEDURE IF EXISTS `HpxxWh`;
delimiter ;;
CREATE PROCEDURE `HpxxWh`(xhpid INT,NAME CHAR(50))
BEGINdeclare len INT;SELECT COUNT(*) INTO len FROM hpkcb where hpid=xhpid;IF len>0 THENupdate hpkcb set kcsl = kcsl+1 where hpid = xhpid;elseif len=0 thendelete from hpkcb where hpid = xhpid;else insert into hpmcb(hpid,hpmc) values (xhpid,`name`);END IF;
END
;;
delimiter ;

返回拼音缩写

创建PysxCx函数，能够根据输入的汉字，返回其拼音首字母缩写

DROP FUNCTION IF EXISTS `PysxCx`;
delimiter ;;
CREATE FUNCTION `PysxCx`(NAME CHAR(50))RETURNS char(50) CHARSET utf8mb3READS SQL DATA 
BEGINDECLARE sx CHAR(50) DEFAULT "";DECLARE jp1 CHAR(3);DECLARE hz1 CHAR(3)DEFAULT "";DECLARE i INT DEFAULT 1;SET hz1=SUBSTRING(NAME,i,1);WHILE(hz1 <> '') DOSELECT jp INTO jp1 FROM hzpyb WHERE hz = hz1;SET sx=CONCAT(sx,jp1) ;SET i=i+1;SET hz1=SUBSTRING(NAME,i,1);END WHILE;RETURN sx;
END
;;
delimiter ;

自动填充拼音缩写的触发器

对于货品信息表创建触发器，自动维护名称缩写列
1）Update_mcsx_before_insert_hpmcb

DROP TRIGGER 
IF EXISTS `Update_mcsx_before_insert_hpmcb`;
delimiter ;;
CREATE TRIGGER `Update_mcsx_before_insert_hpmcb` BEFORE 
INSERT ON `hpmcb` FOR EACH ROW 
SET new.mcsx = PysxCx(new.hpmc)
;;
delimiter ;

2）Update_mcsx_before_update_hpmcb_hpmc

DROP TRIGGER IF EXISTS `Update_mcsx_before_update_hpmcb_hpmc`;
delimiter ;;
CREATE TRIGGER `Update_mcsx_before_update_hpmcb_hpmc` BEFORE 
UPDATE ON `hpmcb` FOR EACH ROW 
IF old.hpmc <> new.hpmc THEN SET new.mcsx = PysxCx(new.hpmc);
END IF
;;
delimiter ;

销售

把字符串8,1,9,2,10,3,11,1,还原为表。（注意该字符串两个数字为一组，前者代表hpid(货品id),后者代表xssl（销售数量））

DELIMITER $$
USE`mpos1`$$
DROP PROCEDURE IF EXISTS `xsjlcp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `xsjlcp`(xsmx VARCHAR(1000))
BEGINDROP TABLE IF EXISTS lsb;CREATE TEMPORARY TABLE lsb (hpid INT,xssl NUMERIC(18,3));WHILE (xsmx>'')DOSET @K=POSITION(',' IN xsmx);SET (@hpid=LEFT(xsmx,@k-1);SET xsmx=SUBSTR(xsmx.@K+1);SET @k=POSITION(','IN xsmx);SET @xssl=LEFT(xsmx,@k-1);INSERT INTO lsb VALUES((@hpid,@xssl),SET xsmx=SUBSTR(xsmx,@k+1);    END WHILE;SELECT * FROM lsb:SELECT hptm,hpmc,jldw,dj,cxj,xssl,xssl*cxj AS 金额 FROM tb_hpxxb,lsb WHERE tb_hpxxb.hpid=lsb.hpid;
END$$
DELIMITER ;

调用

SET @xsmx='8,1,9,2';
CALL xsjlcp(@xsmx);

完整销售业务

DELIMITER $$
USE`mpos1`$$
DROP PROCEDURE IF EXISTS `xsjicp_new`$$
CREATE DEFINER=`root` @`localhost` PROCEDURE `xsjlcp_new` (syyid INT,hyid INT,ysje DECIMAL(18,2),ssje DECIMAL(18,2),zffs TINYINT,xsmx VARCHAR(1000))
BEGIN/*1.Syib insert syyid gkid ysje ssje zffs 需要从用户界面传过来syyid gkid zffs ysje ssje zffs*/INSERT INTO tb_syib(syyid,gkid,ysje,ssje,zffs) VALUES (syyid,hyid,ysje,ssje,zffs);SET @syid=@@identity;DROP TABLE IF EXISTS lsb;CREATE TEMPORARY TABLE lsb (hpid INT,xssl NUMERIC(18,3));WHILE (xsmx>'') DOSET @k=POSITION(',' IN xsmx);SET @hpid=LEFT(xsmx,@k-1);SET xsmx=SUBSTR(xsmx,@k+1);SET @k=POSITION(',' IN xsmx);SET @xssl=LEFT(xsmx,@k-1);INSERT INTO lsb VALUES(@hpid,@xssl);SET xsmx=SUBSTR(xsmx,@k+1);END WHILE;/*2.Syjlmxb insert syid hpid xssl djlsj 需要从用户界面传过来：hpid xssl*/INSERT INTO tb_symxb(syid,hpid,xssl,dj,lsj) SELECT @syid,lsb hpid,xssl,dj,cxj FROM lsb,tb_hpxxb WHERE lsb.hpid=tb_hpxxb.hpid,/*3.hpkcb update 依据hpid,xssl更新kcsl 需要从用户界面传过来：hpid xssl*/DROP TABLE IF EXISTS lsb1;CREATE TEMPORARY TABLE lsb1 (hpid INT,xssl NUMERIC(18,3));INSERT INTO lsb1 SELECT hpid,SUM(xssl) FROM lsb GROUP BY hpid,UPDATE tb_hpkcb b1,lsb1 b2 SET b1.kcsl=b1.kcsl-b2.xssl WHERE b1.hpid=b2.hpid;/*4.hyxxb update 依据hyid,ssje更新knye,kyjf需要从用户界面传过来：hpid ssje*/IF hyid> 10000 THENUPDATE tb_hyob SET knye=knye-ssje,kyif=kyif+FLOOR(ssie) WHERE `tb_hyob`.`hyid`=hyid;
END$$
DELIMITER ;

调用

CALL  xsjlcp_new(1,11100,11,11,0,'8,1,9,2,10,3,11,1,');

存储过程 实现查询销售记录及销售明细

DROP PROCEDURE IF EXISTS `mypos01`.`SyjlCx`;
DELIMITER $$
CREATEPROCEDURE`mypos01`.`SyjiCx`(cxlb INT,cxcs INT)BEGINIF cxlb=0 THENSELECT syid,syyid,sysj,hyid,ysje,ssje,yhje, CASE zffsWHEN 0 THEN'现金'WHEN 1 THEN'储值卡'WHEN 2 THEN'支付宝'WHEN 3 THEN'微信支付'ENDFROM tb_syjlb WHERE syyid= cxcs AND sysj>=CURDATE() ORDER BY syid DESC;ELSESELECT * FROM v_xsjlmxb WHERE syid=cxcs;END IF;END$$
DELIMITER ;

调用

CALL SyjlCx(0,1)

退货业务实现

CALL thjlcp_new(-11)

营业员对当天销售的扎帐处理存储过程

call ZZjlcp(1,10);

附件：MySQL的内置函数

1、数学函数
abs(x):绝对值
ceil(x)或者ceiling(x)：向上取整
Floor(x):向下取整
pow(x,y)：求指数，x的y次方
rand(),rand(N):返回0-1间的浮点数，使用不同的seed N可以获得不同的随机数
round(x, D)：四舍五入保留D位小数，D默认为0， 可以为负数， 如round(19, -1)返回20

2 字符串函数
char_length(str):返回str所包含的字符数，一个多字节字符算一个字符
length(str): 返回字符串的字节长度，如utf8中，一个汉字3字节，数字和字母算一个字节
concat(s1, s1, …): 返回连接参数产生的字符串
lower(str)或者lcase(str):全部小写
upper(str)或者ucase(str):全部大写
left(s,n):返回字符串s最左边n个字符
right(s,n): 返回字符串最右边n个字符
ltrim(s):删除s左侧空格字符
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len),MID(str,pos,len): 获取特定位置，特定长度的子字符串
INSTR(str,substr): 判断子字符串在目标字符串中是否存在，存在返回其位置，不存在，返回0。

3 日期和时间函数
CURDATE(), CURRENT_DATE, CURRENT_DATE():用于获取当前日期，格式为’YYYY-MM-DD’; 若+0则返回YYYYMMDD
CURTIME([fsp]), CURRENT_TIME, CURRENT_TIME([fsp]): 用于获取当前时间， 格式为’HH:MM:SS’ 若+0则返回 HHMMSS
DATEDIFF(expr1,expr2):返回相差的天数,参数是必须使用字符串格式（用引号）。
TIMEDIFF(expr1,expr2)：返回相隔的时间
ADDDATE(date,INTERVAL expr unit):进行时间的增加。
Unit：day/hour/minute/second
MONTH(date)
MONTHNAME(date)
DAYNAME(date)
DAY(date)，DAYOFMONTH(date)：1-31或者0
DAYOFWEEK(date)：1-7==>星期天-星期六
DAYOFYEAR(date)： 1-365

4 系统信息函数
VERSION():返回mysql服务器的版本，是utf8编码的字符串
DATABASE()，SCHEMA()：显示当前使用的数据库
SESSION_USER(), SYSTEM_USER(), USER(), CURRENT_USER, CURRENT_USER():返回当前的用户名@主机，utf8编码字符串
CHARSET(str)
COLLATION(str)
LAST_INSERT_ID()：自动返回最后一个insert或者update查询， 为auto_increment列设置的第一个发生的值

5 其他函数
MD5(str):对数据进行MD5加密。
UUID：生成一个唯一标识符（自增长），是整数，数据唯一，空间唯一

6条件判断函数
IF(expr1,expr2,expr3)：如果expr1不为0或者NULL,则返回expr2的值，否则返回expr3的值
IFNULL(expr1,expr2)：如果expr1不为NULL,返回expr1,否则返回expr2
NULLIF(expr1,expr2): 如果expr1=expr2则返回NULL, 否则返回expr2
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
当compare_value=value时返回result
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
当condition为TRUE时返回result
SELECT CASE 1 WHEN 1 THEN ‘one’
WHEN 2 THEN ‘two’ ELSE ‘more’ END;##‘one’
SELECT CASE WHEN 1>0 THEN ‘true’ ELSE ‘false’ END;##‘true’
SELECT CASE BINARY ‘B’
WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;##NULL

7 格式或类型转化函数
FORMAT(X,D[,locale])：将数字X转化成’#,###,###.##'格式，D为保留的小数位数
CONV(N,from_base,to_base)：改变数字N的进制，返回值为该进制下的数字构成的字符串
INET_ATON(expr)：ip字符串转数字
INET_NTOA(expr)：数字转ip字符串
CAST(expr AS type)：转换数据类型
CONVERT(expr,type), CONVERT(expr USING transcoding_name)： type可以为BINARY[(N)]，CHAR[(N)]，DATE，DATETIME， DECIMAL[(M[,D])]，DECIMAL[(M[,D])]，TIME，UNSIGNED [INTEGER]等等。transcoding_name如utf8等等

-- ----------------------------
-- Procedure structure for hpxxbcp
-- ----------------------------
DROP PROCEDURE IF EXISTS `hpxxbcp`;
delimiter ;;
CREATE PROCEDURE `hpxxbcp`(hpid int,hpmc VARCHAR(50),hptm VARCHAR(20),jldw VARCHAR(20),hpzt TINYINT,dj DECIMAL(18,3),cxj DECIMAL(18,3))
BEGIN/*增加货品*/if hpid=0 thenINSERT into tb_hpxxb(hpmc,hptm,jldw,dj,cxj,mcsx)VALUES(hpmc,hptm,jldw,dj,cxj,pysxcx(hpmc));set @hpid=@@identity;INSERT INTO tb_hpkcb(hpid,kcsl,zrkc,yckc)VALUES(@hpid,0,0,0);ELSEIF hpid>0 THEN/*修改*/UPDATE tb_hpxxb settb_hpxxb.hpmc=hpmc,tb_hpxxb.hptm=hptm,tb_hpxxb.jldw=jldw,tb_hpxxb.dj=dj,tb_hpxxb.cxj=cxj,tb_hpxxb.mcsx=pysxcx(hpmc),tb_hpxxb.hpzt=hpzt WHERE tb_hpxxb.hpid=hpid;ELSEDELETE FROM tb_hpkcb WHERE tb_hpkcb.hpid=hpid;DELETE FROM tb_hpxxb WHERE tb_hpxxb.hpid=hpid;END IF;
END
;;
delimiter ;-- ----------------------------
-- Procedure structure for hpxxbcx
-- ----------------------------
DROP PROCEDURE IF EXISTS `hpxxbcx`;
delimiter ;;
CREATE PROCEDURE `hpxxbcx`(hpid int)
BEGIN/*查询全部货品*/IF hpid=0 THENSELECT * FROM tb_hpxxb;ELSE/*根据hpid查询货品信息*/SELECT * FROM tb_hpxxb WHERE tb_hpxxb.hpid=hpid;END IF;END
;;
delimiter ;-- ----------------------------
-- Function structure for pysxcx
-- ----------------------------
DROP FUNCTION IF EXISTS `pysxcx`;
delimiter ;;
CREATE FUNCTION `pysxcx`(wz VARCHAR(50))RETURNS varchar(50) CHARSET utf8mb4DETERMINISTIC
BEGINset @i=1;set @mcsx='';set @len=CHAR_LENGTH(wz);WHILE (@hz) DOset @hz=SUBSTR(wz,@i,1);SELECT jp INTO @ip from hzpyb WHERE hz=@hz;set @i=@i+1;
END WHILE;RETURN @mcsx;
END
;;
delimiter ;-- ----------------------------
-- Procedure structure for SyjlCx
-- ----------------------------
DROP PROCEDURE IF EXISTS `SyjlCx`;
delimiter ;;
CREATE PROCEDURE `SyjlCx`(cxlb INT,cxcs INT)
BEGINIF cxlb=0 THENSELECT syid,syyid,sysj,ysje,ssje,yhje,CASE zffsWHEN 0 THEN '现金'WHEN 1 THEN '存储卡'WHEN 2 THEN '支付宝'WHEN 3 THEN '微信支付'ENDFROM tb_syjlb WHERE syyid=cxcs AND sysj>=CURDATE() ORDER BY syid DESC;ELSESELECT *FROM v_xsjlmxb WHERE syid=cxcs;END IF;END
;;
delimiter ;-- ----------------------------
-- Procedure structure for Thjlcp
-- ----------------------------
DROP PROCEDURE IF EXISTS `Thjlcp`;
delimiter ;;
CREATE PROCEDURE `Thjlcp`(syid INT)
BEGINSELECT gkid INTO @gkid FROM tb_syjlb WHERE tb_syjlb.syid=syid;SELECT ssje INTO @ssje FROM tb_syjlb WHERE tb_syjlb.syid=syid;/*变更余额与积分*/IF @gkid>10000 THENUPDATE tb_hyxxb SET knye=knye+@ssje,kyjf=kyjf-FLOOR(@ssje) WHERE tb_hyxxb.hyid=@gkid;/*FLOOR(X)函数是向下取整函数*/END IF;/*还原库存*/DROP TABLE IF EXISTS lsb;CREATE TEMPORARY TABLE lsb(hpid INT,xssl NUMERIC(18,3));INSERT INTO lsb(hpid,xssl) SELECT hpid,SUM(xssl) FROM tb_symxb WHERE tb_symxb.syid=syid GROUP BY hpid;UPDATE tb_hpkcb b1,lsb b2 SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;DELETE FROM tb_symxb WHERE tb_symxb.syid=syid;DELETE FROM tb_syjlb WHERE tb_syjlb.syid=syid;END
;;
delimiter ;-- ----------------------------
-- Procedure structure for thjlcp_new
-- ----------------------------
DROP PROCEDURE IF EXISTS `thjlcp_new`;
delimiter ;;
CREATE PROCEDURE `thjlcp_new`(syid int)
BEGINIF syid>0 THEN /*当天退货*/DELETE FROM tb_syjlb WHERE tb_syjlb.syid=syid;ELSE /*时候退货*//*增加一条负销售*/INSERT tb_syjlb(syyid,gkid,sysj,zffs,ysje,ssje,yhje,xszt,zzid) SELECT syyid,gkid,NOW(),zffs,-ysje,-ssje,-yhje,-1,zzidFROM tb_syjlb WHERE tb_syjlb.syid=-syid;SET @syid_new=@@identity;/*修改销售状态，以避免多次退货*/UPDATE tb_syjlb SET xszt=@syid_new WHERE tb_syjlb.syid=-syid;/*在销售明细表中插入负销售*/INSERT INTO tb_symxb(syid,hpid,xssl,dj,lsj)SELECT @syid_new,hpid,-xssl,dj,lsj FROM tb_symxb WHERE tb_symxb.syid=-syid;/*变更库存*/DROP TABLE IF EXISTS lsb;CREATE TEMPORARY TABLE lsb(hpid INT,xssl NUMERIC(18,3));INSERT INTO lsb (hpid,xssl) SELECT hpid,SUM(xssl) FROM tb_symxb WHERE tb_symxb.syid=-syid GROUP BY hpid;UPDATE tb_hpkcb b1,lsb b2 SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;/*如果是会员，还原积分*/SELECT gkid INTO @gkid FROM tb_syjlb WHERE tb_syjlb.syid=-syid;SELECT ssje INTO @ssje FROM tb_syjlb WHERE tb_syjlb.syid=-syid;IF @hyid> 10000 THENUPDATE tb_hyxxb SET knye=knye+@ssje,kyjf=kyjf-FLOOR(@ssje) WHERE tb_hyxxb.hyid=@gkid;END IF;END IF;
END
;;
delimiter ;-- ----------------------------
-- Procedure structure for xsjlcp
-- ----------------------------
DROP PROCEDURE IF EXISTS `xsjlcp`;
delimiter ;;
CREATE PROCEDURE `xsjlcp`(xsmx VARCHAR(1000))
BEGINDROP TABLE if EXISTS lsb;create TEMPORARY table lsb(hpid int,xssl NUMERIC(18,3));while(xsmx>'') Doset @k=POSITION(','in xsmx);/*position()的意思是检测子字符串在母字符串第一次出现的位置*/set @hpid=LEFT(xsmx,@k-1);/*left()是返回指定位置左边的字符串*/set xsmx=SUBSTR(xsmx,@k+1);				/*SUBSTR()意思是从一个内容中，按照指定条件，「截取」一个字符串。这个内容可以是数值或字符串。*/set @k=POSITION(','in xsmx);set @xssl=LEFT(xsmx,@k-1);INSERT into lsb VALUES(@hpid,@xssl);set xsmx=SUBSTR(xsmx,@k+1);END WHILE;SELECT*FROM lsb;SELECT hptm,hpmc,jldw,dj,cxj,xssl,xssl*cxj AS 金额 FROM tb_hpxxb,lsb where tb_hpxxb.hpid=lsb.hpid;
END
;;
delimiter ;-- ----------------------------
-- Procedure structure for xsjlcp_new
-- ----------------------------
DROP PROCEDURE IF EXISTS `xsjlcp_new`;
delimiter ;;
CREATE PROCEDURE `xsjlcp_new`(syyid INT,hyid INT,ysje DECIMAL(18,2),ssje DECIMAL(18,2),zffs TINYINT, xsmx VARCHAR(1000))
BEGINset autocommit=0; /*取消事务的自动提交*/START TRANSACTION;/*开启事务*//*1.Syjlb insert syyid gkid ysje ssje zffs 需要从用户界面传过来syyid gkid zffs ysje ssje zffs */INSERT INTO tb_syjlb(syyid,gkid,ysje,ssje,zffs) VALUES (syyid, hyid,ysje,ssje,zffs);SET @syid=@@identity;DROP TABLE IF EXISTS lsb;CREATE TEMPORARY TABLE lsb(hpid INT,xssl NUMERIC(18,3));WHILE (xsmx>'')DoSET @k=POSITION(','IN xsmx);SET @hpid= LEFT(xsmx,@k-1);SET xsmx= SUBSTR(xsmx, @k+1);SET @k= POSITION(','IN xsmx);SET @xssl=LEFT(xsmx, @k-1);INSERT INTO lsb VALUES(@hpid,@xssl);SET xsmx= SUBSTR(xsmx,@k+1);END WHILE;SAVEPOINT aa;/*设置保存点*//*2.Syjlmxb insert syid hpid xssl dj Isj 需要从用户界面传过来：hpid xss */INSERT INTO tb_symxb(syid,hpid,xssl,dj,lsj) SELEcT @syid,lsb.hpid,xssl,dj,cxj FROM lsb,tb_hpxxb WHERE lsb.hpid= tb_hpxxb.hpid;ROLLBACK to aa;/*回滚到保持点aa，但是保存点前面执行的sql语句仍然有效*//*3.npkcb update 依据hpid,xssl更新kcsl需要从用户界面传过来：hpid xssl */DROP TABLE IF EXISTS lsb1;CREATE TEMPORARY TABLE lsb1(hpid INT,xssl NUMERIC(18,3));INSERT INTO lsb1 SELECT hpid,SUM(xssl) FROM lsb GROUP BY hpid;UPDATE tb_hpkcb b1,lsb1 b2 SET b1.kcsl= b1.kcsl-b2.xssl WHERE b1.hpid=b2.hpid;SAVEPOINT bb;/*设置保存点*//*4.nyxxb update 依据hyid,ssje更新knye,kyjf需要从用户界面传过来：hpid ssje */IF hyid> 10000 THENUPDATE tb_hyxxb SET knye= knye-ssje,kyjf= kyjf+ FLOOR(ssje) WHERE tb_hyxxb.hyid=hyid;end IF;ROLLBACK TO bb;/*回滚到保持点bb，但是保存点前面执行的sql语句仍然有效*/COMMIT; /*提交事务*/
END
;;
delimiter ;-- ----------------------------
-- Procedure structure for ZzjlCp
-- ----------------------------
DROP PROCEDURE IF EXISTS `ZzjlCp`;
delimiter ;;
CREATE PROCEDURE `ZzjlCp`(syyid INT,ssje DECIMAL(18,2))
BEGIN
-- 计算应收金额
SELECT SUM(tb_syjlb.ssje) INTO @yjje FROM tb_syjlb WHERE tb_syjlb.syyid=syyid AND zffs=0 AND zzid IS NULL;
-- 插入扎帐记录
IF ABS(@yjje-ssje)<0.01 THENINSERT INTO tb_zzjlb(syyid,ysje,ssje,ccyy) VALUE (syyid,@yjje,ssje,'无差错');
ELSEINSERT INTO tb_zzjlb(syyid,ysje,ssje) VALUE (syyid,@yjje,ssje);
END IF;
SET @zzid=@@identity;
-- 更新收银记录表中的zzid;
UPDATE tb_syjlb SET zzid=@zzid WHERE tb_syjlb.syyid=syyid AND zzid IS NULL; 
END
;;
delimiter ;-- ----------------------------
-- Triggers structure for table tb_hpxxb
-- ----------------------------
DROP TRIGGER IF EXISTS `update_mcsx_before_insert_hpxxb`;
delimiter ;;
CREATE TRIGGER `update_mcsx_before_insert_hpxxb` BEFORE INSERT ON `tb_hpxxb` FOR EACH ROW BEGIN
set new.mcsx=pysxcx(new.hpmc);
END
;;
delimiter ;-- ----------------------------
-- Triggers structure for table tb_hpxxb
-- ----------------------------
DROP TRIGGER IF EXISTS `update_mcsx_before_update_hpxxb`;
delimiter ;;
CREATE TRIGGER `update_mcsx_before_update_hpxxb` BEFORE UPDATE ON `tb_hpxxb` FOR EACH ROW IF new.hpmc!=old.hpmc THEN
SET new.mcsx=pysxcx(new.hpmc);     END IF
;;
delimiter ;-- ----------------------------
-- Triggers structure for table tb_syjlb
-- ----------------------------
DROP TRIGGER IF EXISTS `update_hpkcb_hyxxb_delete_symxb_before_delect_syjlb`;
delimiter ;;
CREATE TRIGGER `update_hpkcb_hyxxb_delete_symxb_before_delect_syjlb` BEFORE DELETE ON `tb_syjlb` FOR EACH ROW BEGINIF old.gkid>10000 THENUPDATE tb_hyxxb SET knye=knye+old.ssje,kyjf=kyjf-FLOOR(old.ssje) WHERE tb_hyxxb.hyid=old.gkid;END IF;/*还原库存*/UPDATE tb_hpkcb b1,(SELECT hpid,SUM(xssl) AS xssl FROM tb_symxb WHERE tb_symxb.syid=old.syid GROUP BY hpid) b2SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;DELETE FROM tb_symxb WHERE tb_symxb.syid=old.syid;
END
;;
delimiter ;SET FOREIGN_KEY_CHECKS = 1;