引用：百度百科
存储过程
存储过程(Stored Procedure)是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
注意：本文主要简述mysql中存储过程的使用，其他数据库可能有所出入。
存储过程的好处：1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。
编写存储过程时用到的注释：
-- 这是一个存储过程(--后至少一个空格)
#这是一个存储过程(#后无需空格)
查看数据库中的存储过程：
show procedure status;
show procedure status where db = 'mianshi'; -- 查询数据库中的存储过程的信息
select name from MySQL.proc where db = 'mianshi' and type = 'PROCEDURE'; -- 查询数据库中所有的存储过程的名字
show create procedure testusually; -- 查看存储过程或函数的创建代码
删除一个存储过程：
drop procedure stu_add; -- 这边stu_add是存储过程的名字
drop procedure if exists stu_add;
创建一个简单的存储过程：
create procedure hello() select 'hello'; -- 注意存储过程名字后面的()必须加上，即使没有参数也需要
调用存储过程：
call hello(); -- mysql中只能使用call来调用存储过程
call add_stu('lisi', null); -- mysql中的存储过程没有默认值，所以必须指定参数，如果没有值可以使用null来代替
存储过程参数的类型以及使用方法：
createor replace procedure add_stu(in sname VARCHAR(30),in sage int)-- mysql中这边不能使用replaceis-- mysql中这边不能使用is或者as
BEGIN
set @sname = sname;-- @sname和@sage都是临时变量，将参数的值赋值给它们
set @sage =sage;
insert into student(sname, sage) values(@sname, @sage);
return sname; -- mysql中不能使用return
select * from student where sid =(select max(sid) from student);
end;
如果存储过程中有多条sql语句，那么必须使用begin end关键字，并且每条sql语句后面都要使用；号隔开。
至于参数的字段类型(上例中的varcher和int)和数据库中的字段类型一致，但是某些类型需要指明其具体长度，否则出错。
IN
OUT
IN OUT
默认
必须指定
必须指定
值被：传递给子程序。子程序不能改变参数值。
值被：返回调用环境。子程序会改变参数值。
值被：传递给子程序，返回调用环境。子程序可以改变参数值。
可以是表达式，常量、或者是有值的变量。
必须是一个变量，这个变量是否有值不重要。值不会被传进去。
必须是一个变量，而且变量应该有值。
这三个类型的具体区别与使用方法见下面的代码：引用：http://blog.csdn.net/cao478208248/article/details/28122819
## IN IN参数只用来向过程传递信息，为默认值。
## MySQL存储过程"in"参数：跟C语言的函数参数的值传递类似，MySQL存储过程内部可能会修改此参数，
## 但in类型参数的修改对调用者(caller)来说是不可见的(not visible)
mysql>use test;
mysql> drop procedure ifexists pr_param_in;
Query OK,0 rows affected, 1 warning (0.01sec)
mysql> delimiter //
mysql> create procedure pr_param_in(in id int)->begin-> if (id is not null) then-> set id=id+1;-> end if;->select id as id_inner;->end;-> //
Query OK, 0 rows affected (0.03sec)
mysql>delimiter ;
mysql> set @id=10;
Query OK,0 rows affected (0.00sec)
mysql>call pr_param_in(@id);+----------+
| id_inner |
+----------+
| 11 |
+----------+
1 row in set (0.00sec)
Query OK,0 rows affected (0.00sec)
mysql>select @id as id_out;+--------+
| id_out |
+--------+
| 10 |
+--------+
1 row in set (0.00sec)
## 可以看到用户变量@id传入值为10，执行存储过程后，在过程内部值为：11(id_inner),
## 但外部变量值依旧为：10(id_out)
参数IN
## OUT OUT参数只用来从过程传回信息。
## MySQL存储过程"out"参数：从存储过程内部传值给调用者。
## 在存储过程内部，该参数初始值为null，无论调用者是否给存储过程参数设置值。
mysql> drop procedure ifexists pr_param_out;
Query OK,0 rows affected, 1 warning (0.01sec)
mysql> delimiter //
mysql> create procedure pr_param_out(out id int)->begin->select id as id_inner_1;-> if (id is not null) then-> set id=id+1;->select id as id_inner_2;-> else
-> select 1into id;-> end if;->select id as id_inner_3;->end;-> //
Query OK, 0 rows affected (0.01sec)
mysql>delimiter ;
mysql> set @id=10;
Query OK,0 rows affected (0.00sec)
mysql>call pr_param_out(@id);+------------+
| id_inner_1 |
+------------+
| NULL |
+------------+
1 row in set (0.01sec)+------------+
| id_inner_3 |
+------------+
| 1 |
+------------+
1 row in set (0.01sec)
Query OK,0 rows affected (0.01sec)
mysql>select @id as id_out;+--------+
| id_out |
+--------+
| 1 |
+--------+
1 row in set (0.00sec)
## 可以看出，虽然我们设置了用户定义变量@id为10，传递@id给存储过程后，在存储过程内部，
## id的初始值总是null(id_inner_1)。最后id值(id_out=1)传回给调用者。
参数OUT
## INOUT INOUT参数可以向过程传递信息，如果值改变，则可再从过程外调用。
## MySQL存储过程"inout"参数跟out类似，都可以从存储过程内部传值给调用者。
## 不同的是：调用者还可以通过inout参数传递至给存储过程。
mysql> drop procedure ifexists pr_param_inout;
Query OK,0 rows affected, 1 warning (0.01sec)
mysql> delimiter //
mysql> create procedure pr_param_inout(inout id int)->begin->select id as id_inner_1;-> if (id is not null) then-> set id=id+1;->select id as id_inner_2;-> else
-> select 1into id;-> end if;->select id as id_inner_3;->end;-> //
Query OK, 0 rows affected (0.01sec)
mysql>delimiter ;
mysql> set @id=10;
Query OK,0 rows affected (0.00sec)
mysql>call pr_param_inout(@id);+------------+
| id_inner_1 |
+------------+
| 10 |
+------------+
1 row in set (0.00sec)+------------+
| id_inner_2 |
+------------+
| 11 |
+------------+
1 row in set (0.00sec)+------------+
| id_inner_3 |
+------------+
| 11 |
+------------+
1 row in set (0.01sec)
Query OK,0 rows affected (0.01sec)
mysql>select @id as id_out;+--------+
| id_out |
+--------+
| 11 |
+--------+
1 row in set (0.00sec)
## 从结果可以看出：我们把 @id(10)传给存储过程后，存储过程最后又把计算结果值11(id_inner_3)
## 传回给调用者。MySQL存储过程inout参数的行为跟C语言函数中的引用传值类似。
参数IN OUT
=========================================================================================
通过以上例子：
1) 如果仅仅想把数据传给MySQL存储过程，那就用in类型参数；
2) 如果仅仅从MySQL存储过程返回值，那就用out类型参数；
3) 如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数。
存储过程中使用的其他关键字和流程控制语句：
1、delimiter
用来改变mysql的输入结束符，delimiter默认是";",但是如果在命令行客户端中，存储过程中可能使用BEGIN END，其中可能包括;,所以可以使用这个命令改变输入结束符。
例如：delimiter //; 运行成功后，命令结束符变为//，之后这样使用select * from student//
2、declare
一般用来在存储过程中声明变量，mysql中只能在begin后面使用，否则出错。可以给变量设置默认值。
例如：declare a, b int default 0; 或者 declare a int;
3、set
SET用于设置不同类型的变量。这些变量会影响服务器或客户端的操作。SET可以用于向用户变量或系统变量赋值。如果要同时声明变量并且给变量赋值，需要在变量名称前面加上@，如果变量已经被声明(declare)，则可以直接赋值。
例如：SET @var_name =12; select @var_name;
set和declare的区别：
1)作用范围
set可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量。
declare只能在存储过程中使用，称为存储过程变量。
2)初始化
在调用存储过程时，以declare声明的变量都会被初始化为 null或者默认值。
set定义的会话变量(即@开头的变量)不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。
4、case...when...then...else
case后面为一个需要判断的表达式，when后面可以是一个变量或者条件语句(条件语句应该可以，可以自己测试一下)，成立则执行其后的then语句，否则继续判断其他分支。当所有分支的条件都不成立时，执行else分支。条件表达式可以由“=、、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。(只会执行一个分支，当执行完后，以及退出case语句)
例如：
drop procedure ifexists test_case;
create procedure test_case(aint)
BEGIN
declare iint default 0;casea
when a> 0 and a < 2then set i= 1;
when a> 0 and a < 3then set i= 2;elseset i= 3;
endcase;
select i as result;
end;
call test_case(7);
View Code
5、if...else...
当IF中条件成立时，执行THEN后的语句，否则判断ELSEIF中的条件，成立则执行其后的t语句，否则继续判断其他分支。当所有分支的条件均不成立时，执行ELSE分支。
条件表达式可以由“=、、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。(只会执行一个分支，当执行完后，以及退出if语句)
例如：
drop procedure ifexists test_first;
create procedure test_first(idint)
BEGINif id > 10 and id < 20THEN
select'10-20' as 'result';
ELSEIF id> 20 and id < 30THEN
select'20-30' as 'result';
ELSEIF id> 30 and id < 40THEN
select'30-40' as 'result';
ELSE
select'we can`t find it' as 'result';
endif;
END;
call test_first(55);
View Code
IF作为一条语句，在END IF后需要加上分号";"以表示语句结束。
6、循环语句
1)while...do...end while循环
当while中的条件成立时，执行do后面的语句，否则结束循环。条件表达式可以由“=、、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。
例如：
drop procedure ifexists test_while;
create procedure test_while()
BEGIN
declare i , aint DEFAULT 0;set i=1;select a as 'before';while i < 11 do
set a = a + 1;set i = i + 1;
endwhile;select a as 'after';
END;
call test_while();
View Code
2)repeat...until...end repeat循环
先执行一次循环体，再判断until中的条件是否满足，如果满足，退出循环。
例如：
drop procedure ifexists test_repeat;
create procedure test_repeat()
BEGIN
set @i= 1;
repeat
set @i= @i + 1;
until
@i>5end repeat;
select @i as result;
end;
call test_repeat();
View Code
3)loop...end loop循环
loop循环需要配合标号，end 标号和if...else...语句来使用
例如：
drop procedure ifexists test_loop;
create procedure test_loop()
BEGIN
declare iint default 0;
my_loop:LOOP
set i= i + 1;ifi> 5then
leave my_loop;
endif;
end loop;
select i as result;
end;
call test_loop();
View Code
mysql中goto是保留关键字，所以暂时不能使用goto来完成循环。
7、其他关键字和语句块
1)Labels 标号和 END Labels 结束标号
2)iterate语句
763
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
