木木触发器存储过程

触发器操作

（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 ;