MySQL之存储过程

存储过程就是一组预编译好的sql语句，通过案例来学习如何创建，调用，删除，查看存储过程。

CREATE TABLE `stusent` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1.创建无参的存储过程

DELIMITER $
DROP PROCEDURE IF EXISTS procedure1;
CREATE PROCEDURE procedure1()
  BEGIN
    INSERT INTO stusent VALUES (1,'张三',20);
    INSERT INTO stusent VALUES (2,'李四',10);
  END $
DELIMITER ;

2.调用存储过程

call procedure1();

3.查看结果

4.查看存储过程

show create procedure procedure1

 

5.删除存储过程

DROP PROCEDURE IF EXISTS procedure1;

 6.再查看存储过程

show create procedure procedure1

7.创建入参存储过程

DELIMITER $
DROP PROCEDURE IF EXISTS procedure2;
CREATE PROCEDURE procedure2(in id int,in name varchar(30),in age int)
  BEGIN
    INSERT INTO stusent VALUES (id,name,age);
  END $
DELIMITER ;

8.调用存储‘

CALL procedure2(3,'wangwu',18);

9.查询

10.创建，调用，查看出参的存储过程

 

DROP PROCEDURE IF EXISTS procedure3;
DELIMITER $
CREATE PROCEDURE procedure3(out count_id int,out max_id INT)
  BEGIN
      INSERT INTO stusent VALUES (4,'zhaoliu',20);
      SELECT COUNT(*),max(id) into count_id,max_id from stusent;
  END $
DELIMITER ;

CALL procedure3(@count_id,@max_id);

select @count_id,@max_id

11.创建入参出参存储过程

DROP PROCEDURE IF EXISTS procedure4;
DELIMITER $
CREATE PROCEDURE procedure4(INOUT a int,INOUT b int)
  BEGIN
   set a =a+1;
     select b*4 into b;
  END $
DELIMITER ;

-- 创建了2个自定义变量
set @a=1,@b:=2;

CALL procedure4(@a,@b);

select @a,@b