触发器操作
(1)在goods表上创建触发器,实现货品名称缩写的自动维护
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`Update_mcsx_before_insert_goods`
BEFORE INSERT
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
SET new.`abbreviations`=`pysxcx`(new.gname);
END$$
DELIMITER ;
(2)添加货品时,自动在库存中添加一条对应数据
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`Insert_into_stock_after_insert_name` AFTER INSERT
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
INSERT INTO stock(gid) VALUES(new.gid);
END$$
DELIMITER ;
(3)删除货品时,自动在库存中删除对应库存数据
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `jxcglxt`.`delete_from_stock_after_delete_goods` AFTER DELETE
ON `jxcglxt`.`goods`
FOR EACH ROW BEGIN
DELETE FROM stock WHERE gid=old.gid;
END$$
DELIMITER ;
5、存储过程操作截图
(1)创建货品信息维护goodsWh存储过程,能够实现对货品信息的增删改操作
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`goodsWh`(
gid INT,gname VARCHAR(20),
unit VARCHAR(20),barcode VARCHAR(20),
retail_Price DECIMAL(10,2),
promotional_Price DECIMAL(10,2),
STATUS TINYINT
)
BEGIN
IF gid=0 THEN
INSERT INTO goods(`gname`,`unit``barcode`,
`retail_Price`,`promotional_Price`,
`Status`)VALUES
(gname,unit,barcode,retai_Price,STATUS);
ELSEIF gid>0 THEN
UPDATE goods SET `goods`.`gname`=gname,
`goods`.`unit`=unit,`goods`.`barcode`=barcode,
`goods`.`retail_Price`=retail_Price,
`goods`.`promotional_Price`=promotional_Price,
`goods`.`Status`=`Status`
WHERE goods.`gid`=gid;
ELSE
DELETE FROM goods WHERE goods.gid=-gid;
END IF;
END$$
DELIMITER ;
(2)创建货品信息查询goodsCx存储过程,能够实现对货品信息的查询操作
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`goodsCx`(gid INT)
BEGIN
IF gid=0 THEN
SELECT * FROM goods;
ELSE
SELECT * FROM goods WHERE goods.`gid`=gid;
END IF;
END$$
DELIMITER ;
6、使用存储过程处理进销存系统中的简单业务逻辑操作截图
3)参照goodsWh、goodsCx存储过程分别创建2个存储过程,实现对user表信息的增删改及查询操作。
1.userwh存储过程:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`userwh`(uid INT,uname VARCHAR(20),
sex VARCHAR(20),card_number VARCHAR(20),phone VARCHAR(20),
card_balance DECIMAL(10,2),points INT,last_purchase_date DATE)
BEGIN
IF uid=0 THEN
INSERT INTO `user`(`uname`,`sex`,`card_number`,
`phone`,`card_balance`,`points`,`last_purchase_date`)
VALUES(uname,sex,card_number,phone,
card_balance,points,last_purchase_date);
ELSEIF uid>0 THEN
UPDATE USER SET `user`.`uname`=uname,`user`.`sex`=sex,
`user`.`card_number`=card_number,`user`.`phone`=phone,
`user`.`card_balance`=card_balance,`user`.`points`=points,
`user`.`last_purchase_date`=last_purchase_date
WHERE `user`.`uid`=uid;
ELSE
DELETE FROM USER WHERE `user`.`uid`=-uid;
END IF;
END$$
DELIMITER ;
2.usercx存储过程:
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `jxcglxt`.`usercx`(uid INT)
BEGIN
IF uid=0 THEN
SELECT * FROM `user`;
ELSE
SELECT * FROM `user` WHERE `user`.`uid`=uid;
END IF;
END$$
DELIMITER ;
创建PysxCx函数,能够根据输入的汉字,返回其拼音首字母缩写,操作截图
DELIMITER $$
CREATE
FUNCTION `jxcglxt`.`pysxcx`(zw CHAR(50))
RETURNS CHAR(50)
READS SQL DATA
BEGIN
SET @l=CHAR_LENGTH(zw);
SET @pysx='';
SET @i=1;
WHILE @i<=@l DO
SET @hz=SUBSTR(zw,@i,1);
SELECT jp INTO @jp
FROM`hzpyb`
WHERE hz=@hz;
SET @pysx=CONCAT(@pysx,@jp);
SET @i=@i+1;
END WHILE;
RETURN @pysx;
END$$
DELIMITER ;