MySql存储过程的使用

一 . 存储过程的优点

1、通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 
  但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 
  在编译存储过程之后，MySQL将其放入缓存中。MySQL为每个连接维护自己的存储过程高速缓存。 
  如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。

2、存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

3、存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

4、存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

二 . 存储过程的缺点

1、如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 
  此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。

2、存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。

3、很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。而且，MySQL不提供调试存储过程的功能。

4、开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。
  这可能会导致应用程序开发和维护阶段的问题。

三 . 使用例子

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, '陈艳兰', '云南');

1.不带参数的存储过程的使用

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

调用存储过程

call select_students_count();

2.带参数的存储过程的使用

-- 根据城市查询总数
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('北京');

mysql存储过程在传递中文的时候注意字符集问题。处理的方式有两种，我们先做个简单的测试：

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

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 存储过程名;

存储过程至此理清楚