MySQL 存储过程的使用方法

存储过程是在数据库中，为了完成特定功能的一组 SQL 语句集，它存储在数据库中，一次编译后永久有效，用户可以通过指定存储过程的名字并给出参数来执行。

创建 students 表

SQL脚本如下：

create table students(
    id int primary key auto_increment,
    age int,
    name varchar(20),
    city varchar(20)
) character set utf8;

insert into students values(null, 22, 'lisa', '杭州');
insert into students values(null, 16, 'rock', '上海');
insert into students values(null, 20, 'jack', '深圳');
insert into students values(null, 21, 'rose', '北京');

不带参数的存储过程

-- 查询学生个数
drop procedure if exists select_students_count;

delimiter ;; -- 替换分隔符
	create procedure select_students_count() 
		begin 
			select count(id) from students; 
		end ;;
delimiter ;

执行存储过程：

call select_students_count();

带参数的存储过程

-- 根据城市查询总数
delimiter ;;
	create procedure select_students_by_city_count(in _city varchar(255))
		begin
			select count(id) from students where city = _city;
		end;;
delimiter ;

执行存储过程：

call select_students_by_city_count('上海');

带有输出参数的存储过程

MySQL 支持 in (传递给存储过程)，out (从存储过程传出) 和 inout (对存储过程传入和传出) 类型的参数。存储过程的代码位于 begin 和 end 语句内，它们是一系列 select 语句，用来检索值，然后保存到相应的变量 (通过 into 关键字)

-- 根据姓名查询学生信息，返回学生的城市
delimiter ;;
create procedure select_students_by_name(
    in _name varchar(255),
    out _city varchar(255), -- 输出参数
    inout _age int(11)
)
    begin 
    	select city from students where name = _name and age = _age into _city;
    end ;;
delimiter ;

执行存储过程：

set @_age = 20;
set @_name = 'jack';
call select_students_by_name(@_name, @_city, @_age);
select @_city as city, @_age as age;

带有通配符的存储过程

delimiter ;;
create procedure select_students_by_likename(
    in _likename varchar(255)
)
    begin
    	select * from students where name like _likename;
    end ;;
delimiter ;

执行存储过程：

call select_students_by_likename('%s%');
call select_students_by_likename('%j%');

使用存储过程进行增加、修改、删除

增加存储过程

delimiter ;;
create procedure insert_student(
    _id int,
    _name varchar(255),
    _age int,
    _city varchar(255)
)
    begin
    	insert into students(id,name,age,city) values(_id,_name,_age,_city);
    end ;;
delimiter ;

执行存储过程：

call insert_student(5, '张三', 19, '上海');

执行完后，表中多了一条数据，如下图：

修改存储过程

delimiter ;;
create procedure update_student(
    _id int,
    _name varchar(255),
    _age int,
    _city varchar(255)
)
    begin
    	update students set name = _name, age = _age, city = _city where id = _id;
    end ;;
delimiter ;

执行存储过程：

call update_student(5, 'amy', 22, '杭州');

删除存储过程

delimiter ;;
create procedure delete_student_by_id(
    _id int
)
    begin
    	delete from students where id=_id;
    end ;;
delimiter ;

执行存储过程：

call delete_student_by_id(5);

students 表中 id 为5的那条记录成功删除。如下图：

查询存储过程

查询所有的存储过程：

select name from mysql.proc where db='数据库名';

查询某个存储过程：

show create procedure 存储过程名;

mysql server 使用了 5.7 以上版本，在 mysql 客户端修改 long_query_time 全局变量由 10 修改为 1，但重新查询 long_query_t ...