首先创建一张 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 存储过程名;
来源: https://www.cnblogs.com/nnngu/p/8463399.html