mysql数据库之存储过程入门
引用：百度百科
存储过程
存储过程（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来代替
存储过程参数的类型以及使用方法：
create or replace procedure add_stu(in sname VARCHAR(30), in sage int) -- mysql中这边不能使用replace is -- 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 if exists pr_param_in; Query OK, 0 rows affected, 1 warning (0.01 sec) 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.03 sec) mysql> delimiter ; mysql> set @id=10; Query OK, 0 rows affected (0.00 sec) mysql> call pr_param_in(@id); +----------+ | id_inner | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select @id as id_out; +--------+ | id_out | +--------+ | 10 | +--------+ 1 row in set (0.00 sec) ## 可以看到用户变量@id传入值为10，执行存储过程后，在过程内部值为：11（id_inner）, ## 但外部变量值依旧为：10（id_out）
## OUT OUT参数只用来从过程传回信息。 ## MySQL存储过程"out"参数：从存储过程内部传值给调用者。 ## 在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值。 mysql> drop procedure if exists pr_param_out; Query OK, 0 rows affected, 1 warning (0.01 sec) 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 1 into id; -> end if; -> select id as id_inner_3; -> end; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> set @id=10; Query OK, 0 rows affected (0.00 sec) mysql> call pr_param_out(@id); +------------+ | id_inner_1 | +------------+ | NULL | +------------+ 1 row in set (0.01 sec) +------------+ | id_inner_3 | +------------+ | 1 | +------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> select @id as id_out; +--------+ | id_out | +--------+ | 1 | +--------+ 1 row in set (0.00 sec) ## 可以看出，虽然我们设置了用户定义变量@id为10，传递@id给存储过程后，在存储过程内部， ## id的初始值总是 null（id_inner_1）。最后id值（id_out=1）传回给调用者。
## INOUT INOUT参数可以向过程传递信息，如果值改变，则可再从过程外调用。 ## MySQL存储过程"inout"参数跟out类似，都可以从存储过程内部传值给调用者。 ## 不同的是：调用者还可以通过inout参数传递至给存储过程。 mysql> drop procedure if exists pr_param_inout; Query OK, 0 rows affected, 1 warning (0.01 sec) 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 1 into id; -> end if; -> select id as id_inner_3; -> end; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> set @id=10; Query OK, 0 rows affected (0.00 sec) mysql> call pr_param_inout(@id); +------------+ | id_inner_1 | +------------+ | 10 | +------------+ 1 row in set (0.00 sec) +------------+ | id_inner_2 | +------------+ | 11 | +------------+ 1 row in set (0.00 sec) +------------+ | id_inner_3 | +------------+ | 11 | +------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql> select @id as id_out; +--------+ | id_out | +--------+ | 11 | +--------+ 1 row in set (0.00 sec) ## 从结果可以看出：我们把 @id(10）传给存储过程后，存储过程最后又把计算结果值11(id_inner_3) ## 传回给调用者。MySQL存储过程inout参数的行为跟C语言函数中的引用传值类似。
=========================================================================================
通过以上例子：
1) 如果仅仅想把数据传给MySQL存储过程，那就用in类型参数；
2) 如果仅仅从MySQL存储过程返回值，那就用out类型参数；
3) 如果需要把数据传给MySQL存储过程经过计算再传回给我们，那就用inout类型参数。
存储过程中使用的其他关键字和流程控制语句：
1、delimiter
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 if exists test_case; create procedure test_case(a int) BEGIN declare i int default 0; case a when a > 0 and a < 2 then set i = 1; when a > 0 and a < 3 then set i = 2; else set i = 3; end case; select i as result; end; call test_case(7);
5、if...else...
当IF中条件成立时，执行THEN后的语句，否则判断ELSEIF中的条件，成立则执行其后的t语句，否则继续判断其他分支。当所有分支的条件均不成立时，执行ELSE分支。
条件表达式可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。（只会执行一个分支，当执行完后，以及退出if语句）
例如：
drop procedure if exists test_first; create procedure test_first(id int) BEGIN if id > 10 and id < 20 THEN select '10-20' as 'result'; ELSEIF id > 20 and id < 30 THEN select '20-30' as 'result'; ELSEIF id > 30 and id < 40 THEN select '30-40' as 'result'; ELSE select 'we can`t find it' as 'result'; end if; END; call test_first(55);
IF作为一条语句，在END IF后需要加上分号";"以表示语句结束。
6、循环语句
1)while...do...end while循环
当while中的条件成立时，执行do后面的语句，否则结束循环。条件表达式可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。
例如：
drop procedure if exists test_while; create procedure test_while() BEGIN declare i , a int DEFAULT 0; set i=1; select a as 'before'; while i < 11 do set a = a + 1; set i = i + 1; end while; select a as 'after'; END; call test_while();
2)repeat...until...end repeat循环
先执行一次循环体，再判断until中的条件是否满足，如果满足，退出循环。
例如：
drop procedure if exists test_repeat; create procedure test_repeat() BEGIN set @i = 1; repeat set @i = @i + 1; until @i>5 end repeat; select @i as result; end; call test_repeat();
3)loop...end loop循环
loop循环需要配合标号，end 标号和if...else...语句来使用
例如：
drop procedure if exists test_loop; create procedure test_loop() BEGIN declare i int default 0; my_loop:LOOP set i = i + 1; if i > 5 then leave my_loop; end if; end loop; select i as result; end; call test_loop();
mysql中goto是保留关键字，所以暂时不能使用goto来完成循环。
7、其他关键字和语句块
1）Labels 标号和 END Labels 结束标号
2）iterate语句
