MySql存储过程基本使用

一、数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `sex` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `users`(`id`, `user_name`, `sex`) VALUES (1, '张学友', '男');
INSERT INTO `users`(`id`, `user_name`, `sex`) VALUES (2, '迪丽热巴', '女');
INSERT INTO `users`(`id`, `user_name`, `sex`) VALUES (3, '周杰伦', '男');
INSERT INTO `users`(`id`, `user_name`, `sex`) VALUES (4, '杨幂', '女');

 

二、入门案例

DROP PROCEDURE IF EXISTS proc1;

delimiter $$
create procedure proc1()
begin
   SELECT * from users;
end $$ 
delimiter ;


call proc1();

 三、参数传递in

DROP PROCEDURE IF EXISTS proc1;

delimiter $$
create procedure proc1(in in_username VARCHAR(100))
begin
   SELECT * from users WHERE user_name=in_username;
end $$ 
delimiter ;


call proc1('迪丽热巴');

 

四、参数传递out

DROP PROCEDURE IF EXISTS proc1;

delimiter $$
create procedure proc1(in in_username VARCHAR(100),out out_sex VARCHAR(20))
begin
   SELECT sex into out_sex from users WHERE user_name=in_username;
end $$ 
delimiter ;


call proc1('迪丽热巴',@user1);
select @user1 as '性别';

 

五、参数传递inout

inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值(即使函数执行完)

DROP PROCEDURE IF EXISTS proc1;

delimiter $$
create procedure proc1(inout inout_username VARCHAR(100),inout inout_sex VARCHAR(20))
begin
   SELECT CONCAT(user_name,'_',sex)  into inout_username from users WHERE user_name=inout_username;
     set inout_sex=CONCAT(inout_sex,'人');
end $$ 
delimiter ;

set @inout_username='xiaohemiao';
set @inout_sex1='男';
call proc1(@inout_username,@inout_sex);
select @inout_username;
select @inout_sex;