原神数据库实训存储过程

drop procedure if exists insert_user;
DELIMITER $$
CREATE PROCEDURE insert_user(
    in userName VARCHAR(255), 
    IN weapon_type INT,
    IN dendro VARCHAR(255),   
		in rarity int,
		in user_level int
)
BEGIN
if user_level>90 OR user_level<0 then
	signal sqlstate '45000' set message_text='等级错误';
end if;
    INSERT INTO user (userName, weapon_type_id, dendro,rarity,user_level) VALUES (userName, weapon_type, dendro,rarity,user_level);
END$$

DELIMITER ;

call p('胡桃',1,'火',4,70);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

weapon武器表存储过程

drop procedure if exists insert_weapon;
DELIMITER $$
CREATE PROCEDURE insert_weapon(
    in weapon_type_id int,  
    IN rarity INT,        
    IN weapon_name VARCHAR(255),    
		in weapon_details varchar(255)
)
BEGIN
if	(weapon_type_id < 0 OR weapon_type_id > 5) OR (rarity < 0 OR rarity > 5) then
	signal sqlstate '45000' set message_text='超出类型id限制(1-5)';
end if;
    INSERT INTO weapon (weapon_type_id,rarity,weapon_name,weapon_details) VALUES (weapon_type_id,rarity,weapon_name,weapon_details);
END$$

DELIMITER ;

call insert_weapon(1,4,'错误测试1','错误测试1-1');
call insert_weapon(1,7,'错误测试2','错误测试2-1');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

圣遗物表存储过程

drop procedure if exists insert_artifacts;
DELIMITER $$
CREATE PROCEDURE insert_artifacts(        
    IN artifacts_name VARCHAR(255),    
		in set_bonus varchar(255)
)
BEGIN
    INSERT INTO artifacts (artifacts_name,set_bonus) VALUES (artifacts_name,set_bonus);
END$$

DELIMITER ;

call insert_artifacts('测试1','测试1');
1
2
3
4
5
6
7
8
9
10
11
12
13

等级更新限制

drop trigger if exists before_update_user;
DELIMITER $$
CREATE TRIGGER before_update_user
BEFORE UPDATE ON `user`
FOR EACH ROW
BEGIN
	IF new.user_level>90 OR new.user_level<0 THEN
		SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = '更新失败：用户等级不能大于90或小于0';
	END IF;
END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

武器更新限制

drop trigger if exists before_update_weapon;
DELIMITER $$
CREATE TRIGGER before_update_weapon
BEFORE UPDATE ON `weapon`
FOR EACH ROW
BEGIN
		IF new.weapon_level>90 OR new.weapon_level<0 THEN
				-- 如果大于100，则抛出错误
				SIGNAL SQLSTATE '45000'
				SET MESSAGE_TEXT = '更新失败：武器等级不能大于100或小于0';
		END IF;
END$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13