一：存储过程简介
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程(Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数）来调用执行它。 一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。 存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0 终于开始已经支持存储过程，这样即
可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
存储过程优点:
（1）存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
（2）存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（3）存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。（4）存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。 （5）存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现为相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。存储过程缺点：
（1）过程化编程，复杂业务处理的维护成本高。
（2）调试不便。
（3）不同数据库之间可移植性差。--不同数据库语法不一致!
二：MySQL存储过程变量及赋值
1. 局部变量
（1）用户自定义，在begin...end块中有效。
（2）变量声明语法：
declare var_ name type [default var_value]; -- 变量声明
declare nicknane varchar (32); -- 例子
（3）使用set赋值：
一般用来对于变量的赋值； 语法格式：set 变量名 = 变量值;
create procedure sp_01()
begin
-- 声明变量name，并赋初始值为zl
declare name varchar(25) default 'zl';
-- 利用set对变量进行赋值
set name = 'lisi';
-- 查询（相当于打印）
select name; -- lisi
end;
-- 调用存储过程（相当于调用方法）
call sp_01();
（4）使用into赋值
一般用来把一个SQL语句的结果，存到存储过程的变量中 例如：先声明一个变量：name，然后把查询的结果放到这个变量当中 select e.ename into name from emp e where e.empno = 7902;
create procedure sp_02()
begin
declare name varchar(32) default 'unknow';
select e.ename into name from emp e where e.empno = 7902;
select name; -- FORD
end;
call sp_02();
select @name; -- NULL 局部变量，出来就不起作用了
2. 用户变量
用户变量就相当于Java中的成员变量，用户自定义，当前会话（连接）有效
变量声明语法：
@var_name
-- 不需要提前声明，使用即声明
create procedure sp_03()
begin
-- 不需要提前用declare声明，使用即声明
set @var_name = 'lisi';
end;
call sp_03();
select @var_name; -- lisi
3. 会话变量
由系统提供，当前会话（连接）有效
变量声明语法：
@@session.var_name
（1）查看会话变量
show session variables;
（2）查看某会话变量
-- auto_increment_increment是一个会话变量
select @@session.auto_increment_increment
（3）修改会话变量
set @@session.auto_increment_increment = 0;
4. 全局变量
由系统提供，整个mysql服务器有效
变量声明语法：
@@global.var_name;
（1）查看全局变量中变量名有 "char" 的记录
show global variables like '%char%';
（2）查看全局变量 character_set_client的值
select @@global.character_set_client;
三：MySQL存储过程的创建
1. 语法格式
（1）delimiter // 声明结束符为// 。因为MySQL默认使用" ;"作为结束符，而在存储过程中，会使用" ;"作为一段语句的结束，导致" ;"使用冲突
（2）MySQL存储过程创建的格式：
create procedure 过程名 ([过程参数[,...]]) [特性 ...] 过程体（3）MySQL存储过程调用的格式：
call 过程名 ([过程参数[,...]])（4）这里先举个例子:
delimiter // --声明分隔符为 //
create procedure hello_procedure()
begin
select 'hello procedure';
end;
// --结束符
call hello_procedure(); --调用存储过程（就相当于Java中调用方法）
// --结束符
delimiter ; --恢复分隔符为分号;
** 注意：**
（1）这里需要注意的是 delimiter// 和 delimiter; 两句，delimiter是分割符的意思，MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用delimiter关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
** 例如：**
1、使用默认的分隔符分号 ；是SQL语句的结束标志
select count(*) from emp；--程序看到 分号; 就会执行
2、修改默认的分隔符为双斜杠 // 作为SQL语句的结束标志
delimiter // --修改分隔符为//
select count(*) from emp; --不会执行
// --看到双斜杠才会执行
（2）存储过程根据需要可能会有输入、输出、输入输出参数，对应着：IN、OUT、INOUT
（3）过程体的开始与结束使用begin与end进行标识。这样，我们的一个MySQL存储过程就完成了。
2. 使用navicat创建一个存储过程
**（1）连接数据库 **
（2）新建数据库并导入数据脚本
右击鼠标选择新建数据库，并输入数据库名bjpowernode和字符集utf-8
再次右击选择运行SQL文件
把SQL脚本导进去，生成表
（3）选择查询右键，新建一个查询，在里面编写SQL语句；值得注意的是使用navicat工具就不需要修改分隔符了，可以直接进行存储过程的创建
全选，然后运行已选择的
（4）找到函数，右击然后刷新，就可以找到我们创建的PROC1
3. 存储过程中的参数：IN OUT INOUT
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN,OUT,INOUT，分别代表输入、输出、输入输出；
CREATE PROCEDURE([IN | OUT | INOUT] 参数名 数据类形...])
IN输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认类型
OUT输出参数：该值可在存储过程内部被改变，并可返回
INOUT输入输出参数：调用时指定，并且可被改变和返回
MySQL 存储过程的调用：用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。
例题1：IN参数
IN类型是把存储过程之外的数据带入给存储过程，但在存储过程中即使把IN类型参数改变了，最终结果也不会带回给调用环境的；入参！
-- P_IN参数没有声明输入输出类型，则默认的就是IN类型
-- 声明存储过程
CREATE PROCEDURE PROC1(P_IN INT)
BEGIN
SELECT P_IN; -- 10 P_IN的值能传过来
SET P_IN = 3; -- 设置P_IN参数的值
SELECT P_IN ; -- 3 再次查询P_IN 参数的值
END;
-- 开始调用存储过程
SET @P_IN = 10; -- 声明一个变量P_IN，并且赋初始值，声明变量采用@XXX格式
CALL PROC1(@P_IN); -- 调用存储过程并且传递P_IN参数
SELECT @P_IN; -- 10 查询P_IN的结果，IN类型不会把结果带出来，最终还是10，而不是3
语句运行结果是：10 3 10
例题2：OUT参数
OUT类型参数，不能把数据代入存储过程，但存储过程中的数据可以带回给调用环境；出参！
CREATE PROCEDURE PROC2(OUT S INT)
BEGIN
SELECT S; -- NULL，OUT类型数据是不能把数据代入给存储过程的
SELECT COUNT(*) INTO S FROM emp; -- 14条数据
SELECT S; -- 14
SET S = 1000; -- 修改数据
SELECT S; -- 1000，再次查询
END;
-- 开始调用存储过程
SET @S = 10; -- 并没有初始化值，OUT类型数据是不能把数据代入给存储过程的
CALL PROC2(@S); -- 调用存储过程
SELECT @S; -- 1000，存储过程中的数据可以带回给调用环境
语句运行结果是：NULL 14 1000 1000
例题3：IN，OUT联合使用
IN是入参，OUT是出参；利用IN入参传dept表中的dname，再利用OUT出参，把dept表中的loc显示出来
create procedure pro_in_out(IN dept_dname varchar(32), out dept_loc varchar(32))
begin
select d.loc into dept_loc from dept d where d.dname = dept_dname;
end;
call pro_in_out('ACCOUNTING',@dept_loc);
select @dept_loc; -- NEW YORK
例题4：INOUT参数
INOUT类型参数，既可以把数据代入存储过程，也可以把存储过程中的数据带回给调用环境；既能入也能出！
CREATE PROCEDURE PROC3(INOUT P_IN_OUT INT)
BEGIN
SELECT P_IN_OUT; -- 10，可以把外界的值带入存储过程
SET P_IN_OUT = 66; -- 修改数据
SELECT P_IN_OUT; -- 66，再次查询
END;
-- 开始调用存储过程
SET @P_IN_OUT = 10; -- 初始化值，可以把外界的值带乳存储过程
CALL PROC3(@P_IN_OUT); -- 调用存储过程
SELECT @P_IN_OUT; -- 66，存储过程中的数据可以带回给调用环境
语句运行结果是：10 66 66
四：MySQL存储过程的管理
1. MySQL存储过程的查询
我们想知道一个数据库下面有那些表，我们一般采用show tables; 进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢? 答案是，我们可以查看某个数据库下面的存储过程，但是是令一种方式：show procedure status where db = “数据库名”;
例如：查看bjpowernode数据库下面的存储过程
SHOW PROCEDURE STATUS WHERE DB = "bjpowernode";
如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用 desc 表名 进行查看呢? 答：可以查看存储过程的详细，语法：SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。
例如：查看PROC2存储过程的详细信息
SHOW CREATE PROCEDURE bjpowernode.PROC2;
2. MySQL存储过程的修改
目前，MySQL 还不提供对已存在的存储过程的代码修改，如果一定要修改存储过程的代码，必须，先将存储过程删除之后，在重新编写代码；或者直接创建一个新的存储过程
3. MySQL存储过程的删除
删除一个存储过程比较简单，和删除表一样；语法：
DROP PROCEDURE [IF EXISTS] 存储过程名称
例如：从MySQL的表格中删除一个或多个存储过程。
DROP PROCEDURE IF EXISTS PROC1;
运行成功界面：
删除成功结果界面：
五：MySQL存储过程的控制语句
1. 变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储过程外再也不能找到这个声明的变量，但是我们可以通过out参数或者将其值指派给会话变量来保存其值。
CREATE PROCEDURE PROC4()
BEGIN
DECLARE X1 VARCHAR(5) DEFAULT "OUTER" ;
-- 嵌套块，在嵌套块(内部块)中也定义一个变量X1
-- 在内部块中和外部块中有相同名称的X1，在内部块中优先使用内部块中定义的X1
BEGIN
-- 如果下面定义的变量X1，注释掉，最终运行的结果全是OUTER
DECLARE X1 VARCHAR(5) DEFAULT "INNER" ;
DECLARE X2 VARCHAR(5) DEFAULT "INNER2" ;
SELECT X1; -- 结果是INNER
END;
SELECT X1; -- 结果是OUTER
SELECT X2; -- 不能访问X2，因为X2已经超出了使用范围
END;
CALL PROC4();
运行结果如下：INNER OUTER
2. 条件语句
2.1 if-then-else语句
语法格式：
if 条件语句 then SQL语句 end if; 或者 if 条件语句 then SQL语句 else SQL语句 end if;
-- 创建一个表
CREATE TABLE TEMP(ID INT(4));
-- 创建存储过程
CREATE PROCEDURE PROC5(IN P_IN INT)
BEGIN
DECLARE VAR INT;
SET VAR = P_IN + 1;
-- if --------------------
-- 注意：在MySQL中判断变量是否相等没使用 =
IF VAR = 1 THEN
INSERT INTO TEMP VALUES(100);
END IF;
-- if else --------------------
IF P_IN = 0 THEN
INSERT INTO TEMP VALUES (-1);
ELSE
INSERT INTO TEMP VALUES (1);
END IF;
END;
CALL PROC5(0);
SELECT *FROM TEMP;
先插入了一个100，后又插入了一个-1
2.2 case语句
语法格式：
case 变量 when 值 then SQL语句或者：
case when 变量和值的关系式 then SQL语句
-- 删除表中的数据
DELETE FROM TEMP;
-- 创建存储过程
CREATE PROCEDURE PROC6(IN P_IN INT)
BEGIN
DECLARE VAR INT;
SET VAR = P_IN + 1;
CASE VAR
WHEN 1 THEN
INSERT INTO TEMP VALUES (1);
WHEN 2 THEN
INSERT INTO TEMP VALUES (2);
ELSE
INSERT INTO TEMP VALUES (3);
END CASE;
END;
CALL PROC6(0);
SELECT *FROM TEMP;
运行结果：1
3. 循环语句
3.1 while...end while
例题语法格式：
while 变量 do SQL语句 变量自增 end while
-- 清空TEMP表中的记录
DELETE FROM TEMP:
-- 创建存储过程
CREATE PROCEDURE PROC7 ()
BEGIN
-- 设置一个int类型的var变量，初始值为0
DECLARE VAR INT DEFAULT 0;
WHILE VAR < 10 DO
INSERT INTO TEMP VALUES (VAR) ;
SET VAR = VAR + 1;
END WHILE;
END;
CALL PROC7();
SELECT * FROM TEMP;
运行结果：插入10个数据到表中
3.2 repeat...end repeat
例题语法格式：
repeat SQL语句 变量自增 UNTIL 循环结束条件 end repeatrepeat是在执行操作后检查结果，而while则是执行前进行检查!
-- 清空TEMP表中的记录
DELETE FROM TEMP ;
-- 创建存储过程
CREATE PROCEDURE PROC8()
BEGIN
DECLARE VAR INT DEFAULT 0;
REPEAT
INSERT INTO TEMP VALUES (VAR) ;
SET VAR = VAR + 1;
-- 增加退出循环
-- 退出循环的语句，末尾没有分号
UNTIL VAR > 10 -- until直到...才
END REPEAT;
END;
CALL PROC8();
SELECT * FROM TEMP;
运行结果：插入0-10的数据到表中
3.3 loop...end loop
loop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件。
loop是死循环需要手动退出循环，我们可以使用leave来退出。可以把leave看成我们java中的break;与之对应的就有iterate (继续循环)，类比java的continue。
例题语法格式：
别名:LOOP SQL语句 变量自增 if 循环结束条件 then leave 别名 end if END LOOP
-- 清空TEMP表中的记录
DELETE FROM TEMP ;
-- 创建存储过程
CREATE PROCEDURE PROC9()
BEGIN
DECLARE VAR INT DEFAULT 0;
LOOP1:LOOP -- 起一个名字
INSERT INTO TEMP VALUES (VAR) ;
SET VAR = VAR + 1;
-- 退出循环的条件
IF VAR > 6 THEN -- 终止循环的条件
leave LOOP1; -- 退出loop1循环
END IF;
-- 上面也可以写成
IF VAR <=6 THEN
iterate LOOP1; -- 继续循环
END IF;
leave LOOP1;
END LOOP LOOP1;
END;
CALL PROC9();
SELECT * FROM TEMP;
运行结果：插入0-6的数据到表中
六：游标
（1）游标：用游标得到一个结果集，逐行处理数据。
（2）语法格式：
-- 声明语法
declare cursor_name cursor for select_statement; -- 把查询结果集放到cursor_name
-- 打开语法
open cursor_name; -- 打开这个结果集
-- 取值语法
fetch cursor_name into var_name[,var_name...]; -- 一行一行的取值
-- 关闭语法
close cursor_name;
（3）在语法中，变量声明、游标声明、handle声明是必须按照先后顺序书写的，否则创建存储过程出错。
（4）例：按照部门名称查询，通过select查看员工的编号、姓名、薪资
-- 删除存储过程
drop procedure if exists sp_show_emp;
-- 创建存储过程
create procedure sp_show_emp (in dept_name varchar(32))
begin
-- 变量声明，用来接收数据
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2); -- 总共7位，2位是小数
-- 先定义一个标记，后面handler句柄用
declare flag boolean default true;
-- 游标声明
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler句柄声明
-- 1329是错误码，还可以使用02000，或者NOT FOUND（02开头的都可以）
declare continue handler for 1329 set flag = false;
-- 打开游标
open emp_cursor;
-- 取值
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal; -- 数据取完，会报错，对应有一个错误码
if flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_flag = 'end'
-- 关闭游标
close emp_cursor;
end;
call sp_show_emp('ACCOUNTING');
-- handler使用continue，这里就会执行到@end_flag赋值；如果使用exit就不会执行
select @end_flag;
版权归原作者 @每天都要敲代码 所有， 如有侵权，请联系我们删除。