MySQL复习资料(七)——MySQL-存储过程

delimiter $$
create procedure test_fun(in in_age int)
begin
  select * from users where age=in_age;
end
$$
delimiter ;
#查询存储过程是否创建成功
show procedure status like 'test_fun';

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `createDate` datetime(0) NOT NULL,
  `userName` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `passWord` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(3) NOT NULL,
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `introduce` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `userName_index`(`userName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, '2022-06-26 13:43:11', 'admin', '123456', 22, '15912345678', '喜欢学习.');
INSERT INTO `users` VALUES (2, '2022-06-26 13:43:11', 'zhangsan', '123456', 32, '15912345678', '喜欢做饭.');
INSERT INTO `users` VALUES (3, '2022-06-26 13:43:11', 'lisi', '45451', 42, '15912345678', '喜欢化妆.');
INSERT INTO `users` VALUES (4, '2022-06-26 13:43:11', 'zhaoliu', '2222', 52, '15912345678', '喜欢武术.');
INSERT INTO `users` VALUES (5, '2022-06-26 13:43:11', 'zhaoliu', '11111', 16, '15912345678', '喜欢舞蹈.');
INSERT INTO `users` VALUES (6, '2022-06-26 13:43:11', 'zhaoliu', '123787', 27, '15912345678', '喜欢泡妞.');

call fahai(1,'法海你不懂爱，雷峰塔你掉下来');
delimiter $$ #开启边界
create procedure fahai(in new_id int,intro varchar(50))
BEGIN
update users set introduce=intro where id=new_id;
END
$$
delimiter ;#结束边界