我要评论1、创建存储过程
存储过程就是一条或者多条 sql 语句的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的 sql。
创建存储过程的语句为 “create procedure”，创建存储函数的语句为 “create function”。
调用存储过程的语句为 “call”。
调用存储函数的形式就像调用 mysql 内部函数一样。
drop table if exists t_student;
create table t_student
(
id int(11) primary key auto_increment,
name varchar(255) not null,
age int(11) not null
);
insert into t_student values(null,'大宇',22),(null,'小宇',20);
如上述，t_student 表中的数据有两条。如果我们要分别查询出来这两条数据，显然就是根据 id 来查询。查询出来了第一条数据后，我们可能会去做其它的操作。等过两天，我们要查询另外一条记录的时候，可能又要再写一次这样的查询语句。
存储过程和存储函数应运而生，这样就可以对某些 sql 语句进行封装，从而实现功能的复用。
定义一个根据 id 查询学生记录的存储过程：
drop procedure if exists getstubyid; delimiter // -- 定义存储过程结束符号为// create procedure getstubyid(in stuid int(11),out stuname varchar(255),out stuage int(11)) -- 定义输入与输出参数 comment 'query students by their id' -- 提示信息 sql security definer -- definer指明只有定义此sql的人才能执行，mysql默认也是这个 begin select name ,age into stuname , stuage from t_student where id = stuid; -- 分号要加 end // -- 结束符要加 delimiter ; -- 重新定义存储过程结束符为分号
语法：“create procedure sp_name(定义输入输出参数) 【存储特性】begin sql语句；end”
in 表示输入参数；out 表示输出参数；inout 表示既可以输入也可以输出的参数；sp_name 为存储过程的名字。
如果此存储过程没有任何输入、输出，其实就没有什么意义了，但是 sp_name() 的括号不能省略。
查看刚才创建的存储过程。
show procedure status like 'g%'
下面是调用储存过程。对于存储过程提供的临时变量而言，mysql 规定要加上 “@” 开头。
#study 是当前数据库名称 call study.getstubyid(1,@name,@age); select @name as stuname,@age as stuage;
call getstubyid(2,@name,@age); select @name as stuname,@age as stuage;
这样的好处是，如果一段较为复杂的 sql 语句，我们可能过了几天再去写它，又费时费力。存储过程可以
封装我们写过的 sql，在下次只需要调用它的时候，直接提供参数并指明查询结果输出到哪些变量中即可。
提示：如果存储过程一次查询出两个记录，将会提示出错。"[err] 1172 - result consisted of more than one row"。
所以需要在存储过程的 sql 后面加上 “limit 1”。从位偏移量为 0 的，即从查询结果的第一条数据开始，查询一条记录。
2、创建存储函数
存储函数与存储过程本质上是一样的，都是封装一系列 sql 语句，简化调用。
我们自己编写的存储函数可以像 mysql 函数那样自由的被调用。
drop function if exists getstunamebyid; delimiter // create function getstunamebyid(stuid int) -- 默认是in，但是不能写上去。stuid视为输入的临时变量 returns varchar(255) -- 指明返回值类型 return (select name from t_student where id = stuid); // -- 指明sql语句,并使用结束标记。注意分号位置 delimiter ;
使用存储函数：
select getstunamebyid(1);
提示：在 return 语句后面，有趣的是，分号在 sql 语句的外面。如果不加分号，查询结果居然查询出两条记录。
从上述存储函数的写法上来看，存储函数有一定的缺点。首先与存储过程一样，只能返回一条结果记录。另外就是存储函数只能指明一列数据作为结果，而存储过程能够指明多列数据作为结果。
3、定义变量
如果希望 mysql 执行批量插入的操作，那么至少要有一个计数器来计算当前插入的是第几次。
这里的变量是用在存储过程中的 sql 语句中的，变量的作用范围在 “begin … end” 中。
没有 default 子句，初始值为 null。
定义变量的操作：
declare name,address varchar; -- 发现了吗，sql中一般都喜欢先定义变量再定义类型，与java是相反的。 declare age int default 20; -- 指定默认值。若没有default子句，初始值为null。
为变量赋值：
set name = 'jay'; -- 为name变量设置值 declare var1,var2,var3 int; set var1 = 10,var2 = 20; -- 其实为了简化记忆其语法，可以分开来写 -- set var1 = 10; -- set var2 = 20; set var3 = var1 + var2;
使用变量实例。如下表，在做了去除主键约束后，我又添加了一条 “id=1” 的数据。现在希望查询出 “id=1” 记录的数量。
drop procedure if exists contstbyid;
delimiter // -- 定义存储过程结束符号为//
create procedure contstbyid(in sid int(11),out result int(11)) -- 定义输入变量
begin
declare scount int;
select count(*) into scount from t_student where id = sid;
set result = scount; -- 用变量为输出结果设值
end // -- 结束符要加
delimiter ; -- 重新定义存储过程结束符为分号
call contstbyid(1,@result);
select @result;
显然，在存储过程中的变量，可以直接与输出变量进行相应的计算。本例直接把 “scount” 这个变量的值赋值到输出中。
4、定义条件与定义处理程序
- 定义条件 condition：指的是在执行存储过程中的 sql 语句时，可能出现的问题；
- 定义处理程序 handler：当遇到了指定问题时应该如何处理，避免存储过程因执行异常而停止。
定义条件和定义处理程序的位置应该在 “begin … end” 之间。
定义条件的语法：“declare condition_name condition for 错误码或错误值;”
错误码可以视为一个错误的引用，比如 404，它代表的就是找不到页面的错误，而它的错误值可能为 null pointer exception。
declare command_not_allowed condition for sqlstate '42000'; -- 错误值 declare command_not_allowed condition for 1148; -- 错误码
定义处理程序语法：“declare handler_type handler for condition_name sp_statement;”
handler_type 的值有三种，其中 mysql支持的有两种： continue 是指遇到错误忽略，继续执行下面的 sql。exit 表示遇到错误退出，默认的策略就是 exit。（undo 表示遇到错误后撤回之前的操作，mysql 目前还不支持）
condition_name 可以是我们自己定义的条件，也可以是 mysql 内置的条件，比如 sql warning。sp_statement 指遇到错误的时候，需要执行饿存储过程或存储函数。
declare continue handler for sqlsatte '42s02' set @info = 'no_such_table'; -- 忽略错误值为42s02的sql异常 declare exit handler for sqlexception set @info = 'error_occur'; -- 捕获sql执行异常并输出信息 declare no_such_table condition for 1146; -- 为错误码为1146的错误定义条件 declare continue handler for no_such_table set @info = 'no_such_table'; -- 为指定的条件设置处理程序
drop table if exists t_student;
create table t_student
(
id int(11) primary key auto_increment,
name varchar(255) not null,
age int(11) not null
);
现在通过存储过程，为这张表插入数据。因为 id 属性有主键约束，所以不能插入相同的 id。
drop procedure if exists insertstu;
delimiter // -- 定义存储过程结束符号为//
create procedure insertstu(out result int) -- 指定输出结果
begin
declare flag int(11) default 0; -- 指定变量为0
declare primary_key_limit condition for sqlstate '23000'; -- 主键约束的错误值
declare continue handler for primary_key_limit set @info = -1; -- 设计如果出现错误，@info将会被设置为 -1
insert into t_student(id,name,age) values(1,'dayu',22); -- 插入值，设置主键为1
set flag = 1; -- 普通变量设值为1
set result = flag; -- 如果下面的sql执行出现异常，那么就退出，只有上面的sql生效。将普通变量的值给输出
insert into t_student(id,name,age) values(1,'dayu',22); -- 插入值，设置主键为1
set flag = 2; -- 如果处理程序是exit，那么就不会执行到这一步了
set result = flag; -- 将普通变量的值给输出
end // -- 结束符要加
delimiter ; -- 重新定义存储过程结束符为分号
continue 是指遇到错误忽略，继续执行下面的 sql。因为是 continue 来处理程序，所以遇到错误后将会继续执行。
另外，第二次插入记录，因为违反了主键约束，所以插入失败，但是存储过程仍然继续执行完毕。
call insertstu(@result); select @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
运行结果：
再次查看 t_student 表，只插入了一条记录，但是所有的存储过程都执行完毕了。
现在，重新执行下面的 sql。先重新建表，再将处理程序的处理策略换位 exit；在执行存储过程中遇到了错误，那么就立即退出。
drop table if exists t_student;
create table t_student
(
id int(11) primary key auto_increment,
name varchar(255) not null,
age int(11) not null
);
drop procedure if exists insertstu;
delimiter // -- 定义存储过程结束符号为//
create procedure insertstu(out result int) -- 指定输出结果
begin
declare flag int(11) default 0; -- 指定变量为0
declare primary_key_limit condition for sqlstate '23000'; -- 主键约束的错误值
declare exit handler for primary_key_limit set @info = -1; -- 使用exit策略，遇到sql错误将会结束这次存储过程
-- 出现sql错误则直接退出存储过程的执行
insert into t_student(id,name,age) values(1,'dayu',22); -- 插入值，设置主键为1
set flag = 1; -- 普通变量设值为1
set result = flag; -- 如果下面的sql执行出现异常，那么就退出，只有上面的sql生效。将普通变量的值给输出
insert into t_student(id,name,age) values(1,'dayu',22); -- 插入值，设置主键为1
set flag = 2; -- 如果处理程序是exit，那么就不会执行到这一步了
set result = flag; -- 将普通变量的值给输出
end // -- 结束符要加
delimiter ; -- 重新定义存储过程结束符为分号
call insertstu(@result);
select @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
@result 的结果为 1，说明执行第二条 sql 的时候，出现了异常。同样，@info 的值为 -1，也提示处理条件中定义的存储过程被触发。
最后，数据库表中的数据也是：
如果都是正确的 sql，会是什么情况呢？
drop table if exists t_student;
create table t_student
(
id int(11) primary key auto_increment,
name varchar(255) not null,
age int(11) not null
);
drop procedure if exists insertstu;
delimiter // -- 定义存储过程结束符号为//
create procedure insertstu(out result int) -- 指定输出结果
begin
declare flag int(11) default 0; -- 指定变量为0
declare primary_key_limit condition for sqlstate '23000'; -- 主键约束的错误值
declare exit handler for primary_key_limit set @info = -1; -- 设计如果出现错误，@info将会被设置为 -1
insert into t_student(id,name,age) values(null,'dayu',22); --
set flag = 1; -- 普通变量设值为1
set result = flag; -- 如果下面的sql执行出现异常，那么就退出，只有上面的sql生效。将普通变量的值给输出
insert into t_student(id,name,age) values(null,'dayu',22); --
set flag = 2; -- 如果处理程序是exit，那么就不会执行到这一步了
set result = flag; -- 将普通变量的值给输出
end // -- 结束符要加
delimiter ; -- 重新定义存储过程结束符为分号
call insertstu(@result);
select @result,@info; -- @info没有申明就能调用到，可能是是全局变量吧
6、流程控制的使用
（1）if 语句的使用
drop procedure if exists testif;
delimiter //
create procedure testif(out result varchar(255))
begin
declare val varchar(255);
set val = 'a';
if val is null
then set result = 'is null';
else set result = 'is not null';
end if;
end //
delimiter ;
call testif(@result);
select @result;
（2）case 语句
drop procedure if exists testcase;
delimiter //
create procedure testcase(out result varchar(255))
begin
declare val varchar(255);
set val = 'a';
case val is null
when 1 then set result = 'val is true';
when 0 then set result = 'val is false';
else select 'else';
end case;
end //
delimiter ;
call testcase(@result);
select @result;
（3）loop
loop 用于重复执行 sql。leave 用于退出循环。
drop procedure if exists testloop;
delimiter //
create procedure testloop(out result varchar(255))
begin
declare id int default 0;
add_loop:loop
set id = id + 1;
if id>10 then leave add_loop; -- 可在此处修改成批量插入
end if;
set result = id;
end loop add_loop;
end //
delimiter ;
call testloop(@result);
select @result;
下面是一个批量插入的例子：
drop table if exists t_student;
create table t_student
(
id int(11) primary key auto_increment,
name varchar(255) not null,
age int(11) not null
);
drop procedure if exists testloop;
delimiter //
create procedure testloop(in columncount int(11))
begin
declare id int default 0;
add_loop:loop
set id = id + 1;
if id>columncount then leave add_loop;
end if;
insert into t_student(id,name,age) values(id,'dayu',22);
end loop add_loop;
end //
delimiter ;
call testloop(15);
（4）while
drop procedure if exists testwhile;
delimiter //
create procedure testwhile(in mycount int(11),out result int(11))
begin
declare i int default 0 ; -- 定义变量
while i < mycount do -- 符合条件就循环
-- 核心循环sql;
set i = i + 1 ; -- 计数器+1
end while; -- 当不满足条件，结束循环 --分号一定要加！
set result = i; -- 将变量赋值到输出
end //
call testwhile(10,@result); select @result as 循环次数;
7、使用 “show status” 查看存储过程或函数的状态
show procedure status like 'c%';
show function status like 'c%';
知道了存储过程，如果希望查看具体的存储过程或者存储函数的定义：
show create procedure study.countstu; -- create procedure 列为核心语句 create definer=`root`@`localhost` procedure `countstu`(in stu_sex char,out num int) begin select count(*) into num from t_student where sex = stu_sex; end
提示：带上数据库的名字，小心查询不到。
查看存储函数有哪些：
show function status like 'c%'
查看具体的存储函数创建语句：
show create function study.countstu2 -- create function 列的语句 create definer=`root`@`localhost` function `countstu2`(stu_sex char) returns int(11) return (select count(*) from t_student where sex = stu_sex)
8、从 information_schema.routines 表中查询存储过程与函数
原来，mysql 中的存储过程与存储函数都存放在 information_schema 数据库下的 routines 表中。
select * from information_schema.routines where routine_name like 'c%'
如果什么时候忘记了存储函数或者存储过程的名字，可以查询这张表的数据。然后确定了是某个存储过程或者是存储函数，就可以使用 “show create procedure/function 数据库.sp_name” 查看指定的创建语句了。
9、修改存储过程
语法：“alter procedure | function sp_name [存储特性]”
修改存储过程，将读写权限改为 modifies sql date 并指明调用者 alter procedure countstu2 modifies sql date -- 表示子程序中包含写数据的语句 sql security invoker -- 表示调用者才能执行
存储过程与存储函数的补充
1、存储过程如何修改代码？
虽然提供了 “alter procedure sp_name [存储特性]”，但是只能修改存储过程的存储特性，不能修改 sql。需要删除并重新创建。
2、存储过程中能调用其它存储过程吗？
可以在存储过程中的 sql 中通过 call 调用其它存储过程，但是不能用 drop 删除其它存储过程。
3、存储过程中的 in 参数可能是中文怎么办？
在定义存储过程的时候，加上 “character set gbk”
delimiter //
create procedure getaddressbyname(in u_name varchar(50) character set gbk , out address varchar(50))
begin
sql;
end//
delimiter ;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论