数据库-存储过程 （mysql数据库）

一.什么是存储过程？
存储过程就是将 一组预先编译好的SQL语句与流程控制语句（过程）放进数据库（存储）;用户可以通过指定 存储过程的名字并给出参数来执行它——存储过程可以接收和输出参数、返回执行存储过程的状态，也可以嵌套调用。

优点：
1.减少网络通信量（调用一个行数不多的存储过程与直接用SQL语句网络通信量可能不会有太大差别，但是如果存储过程包含上百行SQL，性能会高很多）
2.执行速度快。
3.更强安全性。（譬如防止sql注入）

缺点：
1.可移植性差，一种数据库到另一种数据库，存储过程需要修改
2.存储过程需要花时间去学习，如语法等。

二.编写存储过程(mysql数据库)
1.例子
delimiter //
create procedure p1()
language SQL
deterministic
sql security DEFINER
comment 'a simple procedure'  
begin
SELECT 'Hello Word!';
END//
详解：
· 在存储过程中，通常要输入很多SQL语句，而SQL语句中每个语句以分号来结束，因此要告诉存储过程，什么位置是意味着整个存储过程结束，所以我们在编写存储过程前，先定义分隔符，我们这里定义//为分隔符，我们使用DELIMITER //这样的语法，就可以定义结束符了。
· LANGUAGE选项指定了使用的语言，这里默认是使用SQL。
· DETERMINISTIC关键词的作用是，当确定每次的存储过程的输入和输出都是相同的内容时，可以使用该关键词，否则默认为NOT DETERMINISTIC。
· SQL SECURITY关键词，是表示调用时检查用户的权限。当值为INVOKER时，表示是用户·调用该存储过程时检查，默认为DEFINER,即创建存储过程时检查。
· COMMENT部分是存储过程的注释说明部分。
· 在BEGIN END部分中，是存储过程的主体部分。

2.调用
call 存储过程名字(param1,param2,...)
3.修改存储过程
· 用ALTER的语法去修改存储过程的主要特征和参数，要修改其存储过程的主体部分的话，必须要先删除然后再重建。
· 比如下面修改存储过程num_from_employee的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。
alert procedure num_from_employee
MODIFIES SQL DATA sql security INVOKER ;

4.删除
drop procedure if exists p2;

三. 存储过程的参数
create procedure proc1() 这个存储过程中是空的参数列表
create procedure proc1(in varname DATA-TYPE)这个存储过程中有一个输出参数，名称为varname，后面跟数据类型。in 参数默认，可省略不写。
create procedure proc1(out varname DATA-TYPE) varname为输出参数
create procedure proc1(inout varname DATA-TYPE) varname即为输入，也为输出
例一：
DELIMITER //
CREATE PROCEDURE proc_in (IN var1 INT)
BEGIN 
SELECT var1 + 2 AS result;
END //
例二：
DELIMITER //
CREATE PROCEDURE proc_out (OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END //
例三：
DELIMITER //
drop procedure if exists proc_inout;
CREATE PROCEDURE proc_inout (inout var1 int)
BEGIN
SET var1 = var1 *2;
END //
ps. out输出参数调用时需要加"@"; call proc_inout(@5)//
四. 定义变量
必须显式地在存储过程的一开始声明变量，并指出它们的数据类型，一但声明了变量后，就可以在存储过程中使用。
定义变量语法：
declare varname DATA-TYPE default defaultvalue
例一：
declare a,b INT default 5;
declare str varchar(50);
declare today TIMESTAMP default CURRENT_DATE;
declare v1,v2,v3 tinyint;

例二：
delimiter //
create procedure var_proc (IN param VARCHAR(20))
begin
declare a,b int default 5;
declare str varchar(50);
declare today timestamp default current_date;
insert into table1 values(a);
set str = 'a string';
select concat(str,param),today from table2 where b>=5;
end //

五. mysql存储过程的语法结构
支持： if / case / iterate / leave loop/ while / repeat
1. IF
if ... then
...
end if;
例一：
delimiter //
create procedure proc_if(in param int)
begin
declare var1 int;
set var1 = param + 1;
if var1 = 0 then
SELECT var1;
end if;
if param = 0 then
SELECT 'param value = 0';
ELSE
SELECT 'param value <=0';
end if;
end //
2.  CASE
两种写法：
法一：
BEGIN
　　DECLARE variable1 INT;
　　SET variable1 = param1 + 1;
　　CASE variable1
　　WHEN 0 THEN
　　    INSERT INTO table1 VALUES (param1);
　　WHEN 1 THEN
　　    INSERT INTO table1 VALUES (variable1);
　　ELSE
　　    INSERT INTO table1 VALUES (99);
　　END CASE;
END //
法二：
CASE
　　WHEN variable1 = 0 THEN
　　    INSERT INTO table1 VALUES (param1);
　　WHEN variable1 = 1 THEN
　　    INSERT INTO table1 VALUES (variable1);
　　ELSE
　　    INSERT INTO table1 VALUES (99);
　　END CASE;
3. WHILE
　 WHILE variable1
　　    INSERT INTO table1 VALUES (param1);
　　    SELECT COUNT(*) INTO variable2 FROM table1;
　　    SET variable1 = variable1 + 1;
　　END WHILE;

六. mysql存储过程中的游标
游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
declare 游标名 cursor for select ... ;
declare continue handler for not found; /*指定当遍历完结果集后，游标如何继续处理*/
open 游标名; /*打开游标*/
fetch 游标名 into variable [,variable]; /*讲变量赋值给游标*/
close cursor-name; /*使用后关闭游标*/

例：
delimiter //
create procedure proc_cursor (out param int)
begin 
declare a,b,c int;
declare cur1 cursor for select col1 from table1;
declare continue handler for not found set b = 1;
open cur1;
set b = 0;
set c = 0;
while b=0 do
fetch cur1 into a;
if b=0 then
set c=c+a;
end if;
end while;
close cur1;
set param = c;
end //

其中，DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
表示将从table1表中选取col1列的内容放到游标curl中，即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。