mysql存储过程、触发器、视图

例子1

触发器

-- 删除笔记 级联删除点赞
DELIMITER $$

$$
DROP TRIGGER IF EXISTS `notes_info_delete`
$$
CREATE
    TRIGGER `notes_info_delete` BEFORE DELETE ON `notes_info` 
    FOR EACH ROW BEGIN
 DELETE FROM `praise_info` WHERE notesId not in (select id from notes_info);
    END;
$$
DELIMITER ; 

-- 删除食材 级联删除点赞
DELIMITER $$

$$
DROP TRIGGER IF EXISTS `foods_menu_info_delete`
$$
CREATE
    TRIGGER `foods_menu_info_delete` BEFORE DELETE ON `foods_menu_info` 
    FOR EACH ROW BEGIN
 DELETE FROM `praise_info` WHERE foodsId not in (select id from foods_menu_info);
    END;
$$
DELIMITER ; 


-- 删除用户 级联删除点赞
DELIMITER $$

$$
DROP TRIGGER IF EXISTS `user_info_delete`
$$
CREATE
    TRIGGER `user_info_delete` AFTER DELETE ON `user_info` 
    FOR EACH ROW BEGIN
 DELETE FROM `praise_info` WHERE userId not in (select id from user_info);
    END;
$$
DELIMITER ; 

-- 演示 删除用户数据,触发点赞数据删除
SELECT userId,COUNT(*) FROM `praise_info` GROUP BY userId ORDER BY 1;
delete from user_info where id = 13;
SELECT userId,COUNT(*) FROM `praise_info` GROUP BY userId ORDER BY 1;

视图

-- 创建点赞视图
DROP VIEW IF EXISTS view_praise_info;
CREATE VIEW view_praise_info AS 
SELECT p.name praiseName,f.name foodsName,u.name userName,n.name notesName,p.time createTime FROM 
praise_info p 
LEFT JOIN foods_menu_info f
ON p.foodsId = f.id
LEFT JOIN user_info u
ON p.userId = u.id
LEFT JOIN notes_info n
ON p.notesId = n.id
;
-- 查看点赞视图
SELECT * FROM view_praise_info;

-- 文件被引用视图(方便删除无用的文件)
DROP VIEW IF EXISTS view_nx_system_file_info;
CREATE VIEW view_nx_system_file_info AS 
SELECT f.id,f.originName,f.fileName,COUNT(f1.id)+COUNT(f2.id) reference FROM nx_system_file_info f
LEFT JOIN `foods_material_info` f1 ON f.id = f1.fileId
LEFT JOIN `foods_menu_info` f2 ON f.id = f2.fileId
GROUP BY f.id,f.originName,f.fileName

-- 文件被引用视图
SELECT * FROM view_nx_system_file_info;


-- 食物被点赞排行
DROP VIEW IF EXISTS view_foods_menu_info;
CREATE VIEW view_foods_menu_info AS 
SELECT f.id,s.name subName,f.name foodsName,f.tips,f.userName,COUNT(p.id) pv,COUNT(DISTINCT p.userId) uv 
FROM foods_menu_info f
LEFT OUTER JOIN `sub_classify_info` s ON f.subId = s.id
LEFT OUTER JOIN `praise_info` p ON p.foodsId = f.id
GROUP BY f.id,s.name,f.name,f.tips,f.userName;

-- 食物被点赞排行
SELECT * FROM view_foods_menu_info;

存储过程


-- 插入点赞数据
DELIMITER $$

DROP PROCEDURE IF EXISTS deliciousfoods.insert_praise_info;
$$
CREATE
    PROCEDURE `deliciousfoods`.`insert_praise_info`(OUT p_id BIGINT,IN p_name VARCHAR(255),IN p_notesId BIGINT,IN p_foodsId BIGINT,IN p_userId BIGINT,IN p_level INTEGER)
    BEGIN
INSERT INTO praise_info (NAME,TIME,notesId,foodsId,userId,LEVEL) VALUES (p_name,NOW(),p_notesId,p_foodsId,p_userId,p_level);
SET p_id=LAST_INSERT_ID();
    END$$

DELIMITER ;

-- 调用存储过程插入点赞数据 并将最新的数据查询出来
CALL insert_praise_info(@id,'可乐鸡翅',NULL,NULL,NULL,2);
SELECT * FROM `praise_info` WHERE id = @id;


-- 插入食材信息
DELIMITER $$

DROP PROCEDURE IF EXISTS deliciousfoods.insert_foods_material_info;
$$
CREATE
    PROCEDURE deliciousfoods.insert_foods_material_info(OUT p_id BIGINT,IN p_name VARCHAR(255),IN p_description VARCHAR(255)
    ,IN p_fileId BIGINT,IN p_userId BIGINT,IN p_level INTEGER)
    BEGIN
    
SELECT fileName INTO @fileName FROM nx_system_file_info WHERE id = p_fileId;
SELECT NAME INTO @userName FROM user_info WHERE id = p_userId;
INSERT INTO foods_material_info (NAME,description,fileId,fileName,userName,LEVEL,uploadUserId) VALUES 
(p_name,p_description,p_fileId,@fileName,@userName,p_level,p_userId);
SET p_id=LAST_INSERT_ID();
    END$$

DELIMITER ;

-- 调用存储过程插入食材信息 并将最新的数据查询出来
CALL insert_foods_material_info(@id,'鲍鱼1','鱼类',25,1,1);
SELECT * FROM foods_material_info WHERE id = @id;


-- 插入菜谱信息
DELIMITER $$

DROP PROCEDURE IF EXISTS deliciousfoods.insert_foods_menu_info;
$$
CREATE
    PROCEDURE deliciousfoods.insert_foods_menu_info(OUT p_id BIGINT,IN p_name VARCHAR(255),IN p_description VARCHAR(255)
    ,IN p_tips VARCHAR(255)
    ,IN p_subId BIGINT
    ,IN p_fileId BIGINT,IN p_userId BIGINT,IN p_level INTEGER)
    BEGIN
    
SELECT fileName INTO @fileName FROM nx_system_file_info WHERE id = p_fileId;
SELECT NAME INTO @userName FROM user_info WHERE id = p_userId;
INSERT INTO foods_menu_info (NAME,description,tips,subId,fileId,fileName,userName,LEVEL,uploadUserId) VALUES 
(p_name,p_description,p_tips,p_subId,p_fileId,@fileName,@userName,p_level,p_userId);
SET p_id=LAST_INSERT_ID();
    END$$

DELIMITER ;

-- 调用存储过程插入菜谱信息 并将最新的数据查询出来
CALL insert_foods_menu_info(@id,'可乐鸡翅1','每一道菜都是一台时光机','家常菜',13,13,1,2);
SELECT * FROM foods_menu_info WHERE id = @id;

例子2

触发器

-- 点菜时自动修改订单价格
drop TRIGGER if exists trigger_add_dinner_notice;
-- 重新定义分割符,防止和默认分隔符;冲突
DELIMITER :::
CREATE
    TRIGGER `trigger_add_dinner_notice` AFTER INSERT ON `dinner_notice` 
    FOR EACH ROW BEGIN
UPDATE `dinner_order` SET price = IFNULL(price,0) + NEW.menuprice*NEW.menunum WHERE id = NEW.order_id;
    END
:::
DELIMITER ;

-- 删除菜品时自动修改订单价格
DROP TRIGGER IF EXISTS trigger_delete_dinner_notice;
DELIMITER :::
CREATE
    TRIGGER `trigger_delete_dinner_notice` AFTER DELETE ON `dinner_notice` 
    FOR EACH ROW BEGIN
UPDATE `dinner_order` SET price = IFNULL(price,0) - OLD.menuprice*OLD.menunum WHERE id = OLD.order_id;
    END
:::
DELIMITER ;

-- 修改菜品时自动修改订单价格
DROP TRIGGER IF EXISTS trigger_update_dinner_notice;
DELIMITER :::
CREATE
    TRIGGER `trigger_update_dinner_notice` AFTER update ON `dinner_notice` 
    FOR EACH ROW BEGIN
UPDATE `dinner_order` SET price = IFNULL(price,0) - OLD.menuprice*OLD.menunum + NEW.menuprice*NEW.menunum  WHERE id = NEW.order_id;
    END
:::
DELIMITER ;

SELECT * FROM dinner_notice WHERE order_id = 48;
-- 总价 165
select * from `dinner_order` where  id = 48;
-- 删除价格为 13 的菜品
delete from dinner_notice where id = 81;
-- 总价 152 = 165 - 13
SELECT * FROM `dinner_order` WHERE  id = 48;
-- 添加两盘红烧肉
insert into dinner_notice (menu_name,order_id,menunum,remark,menuprice) values ('红烧肉',48,2,"无",38);
-- 总价 228 = 152 + 38 *2
SELECT * FROM `dinner_order` WHERE  id = 48;
-- 修改红烧肉为1盘
update dinner_notice set menunum = 1 where order_id = 48 and menu_name = '红烧肉' ;
-- 总价 190 = 228 - 38
SELECT * FROM `dinner_order` WHERE  id = 48;

存储过程-支付订单

-- 支付订单
DROP PROCEDURE IF EXISTS proc_pay_order;
DELIMITER :::
CREATE
    PROCEDURE proc_pay_order(IN p_id integer,IN p_price double,OUT p_message varchar(255))
    BEGIN
DECLARE v_price DOUBLE ;
DECLARE v_ispay INT  ;
SELECT price,ispay into v_price,v_ispay FROM dinner_order WHERE id = p_id;
IF v_price IS NULL THEN 
   SET p_message = '找不到对应订单';
ELSEIF v_ispay = 1 THEN
   SET p_message = '订单已支付,无需再次支付';
ELSEIF p_price < v_price THEN
   SET p_message = '对不起,你的支付金额不足';
ELSE
   UPDATE dinner_order SET ispay = 1 WHERE id = p_id;
   SET p_message = concat('支付成功,支付金额:',v_price,',找零:',p_price-v_price);
END IF;
    END
:::
DELIMITER ;

-- 支付订单 测试
select * from `dinner_order` where id = 49;
CALL proc_pay_order(49,89.00,@message);
SELECT @message;
update dinner_order set ispay = 0 WHERE id = 49;
CALL proc_pay_order(49,89.00,@message);
SELECT @message;

存储过程-添加积分


-- 添加会员积分

DROP PROCEDURE IF EXISTS proc_add_member_score;
DELIMITER :::
CREATE
    PROCEDURE proc_add_member_score(IN p_id INTEGER,IN p_mcode INTEGER,OUT p_message VARCHAR(255))
    BEGIN
DECLARE v_id INTEGER ;
DECLARE v_mcode INTEGER ;
DECLARE v_type_id INTEGER ;
SELECT id,mcode INTO v_id,v_mcode FROM member WHERE id = p_id;
IF v_id IS NULL THEN 
   SET p_message = '找不到会员';
ELSE
   IF v_mcode < 1000 THEN
     SET v_type_id = 1;
   ELSEIF v_mcode < 2000 THEN
     SET v_type_id = 2;
   ELSEIF v_mcode < 3000 THEN
     SET v_type_id = 3;
   ELSE
     SET v_type_id = 4;
   END IF;
   UPDATE member SET type_id = v_type_id, mcode = IFNULL(v_mcode,0) + p_mcode WHERE id = p_id;
   SET p_message = CONCAT('添加会员积分成功 ',IFNULL(v_mcode,0),' => ',IFNULL(v_mcode,0) + p_mcode);
END IF;
    END
:::
DELIMITER ;

-- 添加会员积分 测试
SELECT * FROM `member` WHERE id = 1;
CALL proc_add_member_score(1,89,@message);
SELECT @message;
CALL proc_add_member_score(1,-89,@message);
SELECT @message;

存储过程-点菜

-- 点菜
DROP PROCEDURE IF EXISTS proc_add_menu;
DELIMITER :::
CREATE
    PROCEDURE proc_add_menu(IN p_order_id INTEGER,IN p_menu_id INTEGER,IN p_num INTEGER,OUT p_message VARCHAR(255))
    BEGIN
DECLARE v_name VARCHAR(20) ;
DECLARE v_price INTEGER ;
DECLARE v_id    INTEGER ;
DECLARE v_num    INTEGER ;
SELECT m_name,m_price INTO v_name,v_price FROM menu WHERE id = p_menu_id;
IF v_name IS NULL OR v_price IS NULL THEN 
   SET p_message = '找不到菜品';
ELSEIF p_num = 0 THEN
   SET p_message = '菜品数量不可为0';
ELSEIF NOT EXISTS(SELECT * FROM dinner_order WHERE id = p_order_id) THEN
   SET p_message = '找不到订单';
ELSE
   SELECT id,menunum INTO v_id,v_num FROM dinner_notice WHERE order_id = p_order_id AND menu_name = v_name;
   SET v_num = IFNULL(v_num,0) + p_num;
   IF v_num <= 0 AND v_id IS NULL THEN 
       SET p_message = '无需添加菜品';
   ELSEIF v_num <=0 AND v_id IS NOT NULL THEN
       DELETE FROM dinner_notice WHERE id = v_id;
       SET p_message = '删除菜品';
   ELSEIF v_id IS NULL THEN
       INSERT INTO dinner_notice (menu_name,order_id,menunum,remark,menuprice) VALUES (v_name,p_order_id,v_num,'无',v_price);
       SET p_message = CONCAT('添加菜品(',v_name,')');
   ELSE
       UPDATE dinner_notice SET menunum = v_num WHERE id = v_id;
       SET p_message = CONCAT('修改菜品(',v_name,')数量为',v_num);
   END IF;
END IF;
    END
:::
DELIMITER ;

-- 点菜 测试
SELECT * FROM `dinner_notice` WHERE order_id = 48;
CALL proc_add_menu(48,14,1,@message);
SELECT @message;

如果觉得文章对你有用，请随意赞赏

mysql存储过程、触发器、视图

http://burna.cn/archives/mysql-cun-chu-guo-cheng--chu-fa-qi--shi-tu

作者

飞飞幻想

发布于

2022-09-08

更新于

2022-09-10

许可协议

CC BY 4.0

mysql存储过程、触发器、视图

例子1

触发器

视图

存储过程

例子2

触发器

存储过程-支付订单

存储过程-添加积分

存储过程-点菜

作者

发布于

更新于

许可协议

评论