mysql存储过程的使用

直入正题吧：首先创建一张 students 表

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, '赵四', '杭州');
insert into students values(null, 16, '刘能', '上海');
insert into students values(null, 20, '谢广坤', '深圳');
insert into students values(null, 21, '刘美兰', '北京');
insert into students values(null, 20, '宋晓峰', '湖北');
insert into students values(null, 21, '谢大脚', '江苏');
insert into students values(null, 20, '苏玉红', '天津');
insert into students values(null, 21, '陈艳兰', '云南');

 

不带参数的存储过程

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(25))--  in (表示参数传递给存储过程)
        begin
            select count(id) from students where city = _city;
        end;;
delimiter ;

执行存储过程

call select_students_by_city_count('北京');

啊。这里咋不对呢？数据是存在的，为何返回0？说道这里，mysql存储过程在传递中文的时候注意字符集问题。处理的方式有两种，我们先做个简单的测试：

delimiter ;;
    create procedure sp_test(in _city varchar(25))--  in (表示参数传递给存储过程)
        begin
            select _city;
        end;;
delimiter ;

发现传递在的中文变成乱码了...只要正确设置编码即可，这里有2种方式解决，

解决方式一：

我们修改下上面的储存过程sp_test：

delimiter ;;
    create procedure sp_test(in _city varchar(25) CHARACTER SET utf8)--  in (表示参数传递给存储过程)
        begin
            select _city;
        end;;
delimiter ;

解决方式二：

delimiter ;;
    create procedure sp_test(in _city nvarchar(25))--  in (表示参数传递给存储过程，注意这里是nvarchar)
        begin
            select _city;
        end;;
delimiter ;

总结：在创建存储过程的时候，设计中文的参数的数据类型需为Nvarchar，网上据说传递参数的时候也需要多加一个N（这里我没加貌似也正常输出）。

继续回到上面根据城市查询总数select_students_by_city_count，修改如下：

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

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

-- 根据姓名查询学生信息，返回学生的城市
delimiter ;;
create procedure select_students_by_name(
    in _name nvarchar(255),
    out _city nvarchar(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 = '谢广坤';
call select_students_by_name(@_name, @_city, @_age);
select @_name as name,@_city as city, @_age as age;

带有通配符的存储过程

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

执行存储过程：

call select_students_by_likename('%刘%');

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

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

执行存储过程：

call insert_student(9, '瓶底子', 19, '东北');

执行完后，表中多了一条数据，如上图。
修改：

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

执行存储过程：

call update_student(9, '李大个', 22, '杭州');

删除：

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

执行存储过程：

call delete_student_by_id(9);

查询所有的存储过程：

select name from mysql.proc where db='test';

 查询某个存储过程：

show create procedure 存储过程名;