IT虾米网

mysql存储过程的使用详解

wyy 2021年02月23日 数据库 173 0

直入正题吧:首先创建一张 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 存储过程名;

 

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Mysql锁详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。