mysql存储过程学习记录-一篇极速掌握存储过程

存储过程其实相对学起来我认为还是比较容易的。由于各个数据库的方言有些出入导致如何系统在开发过程中使用存储过程导致数据库迁移的难度是比较大的。

一、存储过程简介

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有 SQL
语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的
函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以
看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
(1).存储过程增强了 SQL 语言的功能和灵活性。存储过程可以用流控制语句编
写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调
用，而不必重新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对
存储过程进行修改，对应用程序源代码毫无影响。
(3). 存 储 过 程 能 实 现 较 快 的 执 行 速 度 。 如 果 某 一 操 作 包 含 大 量 的
Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度
快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对
其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的
Transaction-SQL 语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），
如果这一操作所涉及的 Transaction-SQL 语句被组织程存储过程，那么当在客
户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了
网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权
用户对数据的访问，保证了数据的安全

二、mysql存储过程的命令（注意：mysql5.0之后才支持存储过程）

1、 delimiter // , 设置分隔符 或者说成是结束符比较贴切。在mysql语法中一个命令的结束我们一般是以分号结尾“；”但是在存储过程中可以包含sql语句，各个sql语句之间可能也是分号结尾导致出现歧义。所以我们可以预先换成另外自己定义的一个符合来结尾。注意最好我们带上换成delimiter ；分号结尾。

2、创建一个存储过程。有点类型与我们函数/方法
create procedure proc(in p_in int)
begin
要执行的sql…
end//

【例：】

CREATE PROCEDURE PROC2(P_IN INT)
BEGIN
SELECT P_IN; -- 查询 P_IN 参数的值
SET P_IN = 3; -- 设置 P_IN 参数的值
SELECT P_IN ; -- 查询 P_IN 参数的值
END;

【调用：】

SET @P_IN = 10; -- 设置@P_IN =10 
CALL PROC2(@P_IN); -- 调用存储过程,并且传递@P_IN 的参数
SELECT @P_IN; -- 查询@P_IN 的结果

2.1 参数类型 in

存储过程可以携带参数 in out inout 三种
表示我们外部环境 与存储过程内部的数据交换方式
in 表示 外部 --> 存储过程 ，参数只能传入存储过程进行处理 但是不能传出

2.2 参数类型 out

out 表示 外部 <-- 存储过程 ，参数只能有存储过程传出不能接受到外部的环境参数。

2.3 参数类型 inout

inout     外部 <--> 存储过程 既可以传入也可以传出。

【例：】

CREATE PROCEDURE PROC2(P_IN INT)
BEGIN
SELECT P_IN; -- 查询 P_IN 参数的值
SET P_IN = 3; -- 设置 P_IN 参数的值
SELECT P_IN ; -- 查询 P_IN 参数的值
END;

SET @P_IN = 10; -- 设置@P_IN =10 
CALL PROC2(@P_IN); -- 调用存储过程,并且传递@P_IN 的参数
SELECT @P_IN; -- 查询@P_IN 的结果

---

CREATE PROCEDURE PROC3(OUT P_OUT INT)
BEGIN
 SELECT P_OUT;
 SET P_OUT = 10;
 SELECT P_OUT;
END;

SET @P_OUT = 1000; -- 设置@P_OUT 变量为 1000
CALL PROC3(@P_OUT); -- 调用存储过程并且传递@P_OUT 参数
SELECT @P_OUT; -- 查询 P_OUT 的结构

---

-- 注意:INOUT 之间没有空格
CREATE PROCEDURE PROC4(INOUT P_IN_OUT INT) BEGIN
 SELECT P_IN_OUT;
 SET P_IN_OUT = 10;
 SELECT P_IN_OUT;
END;

SET @P_IN_OUT = 50;
CALL PROC3(@P_IN_OUT);
SELECT @P_IN_OUT;

三、变量

3.1定义变量

DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
其中，datatype 为 MySQL 的数据类型，如:int, float, date, varchar(length)

变量赋值
SET 变量名 = 表达式值

SET @S = "Hello World"; 
SELECT @S;
SET @RES = 1+2+3; 
SELECT @RES;
--声明存储过程
CREATE PROCEDURE PROC5()
BEGIN 
 SELECT CONCAT(@STR1,"World"); 
END;
-- 首先定义一个变量,然后调用存储过程
SET @STR1 = "Hello";
CALL PROC5();

3.2存储过程间传递用户变量

-- 声明存储过程 P1,在 P1 中定义变量@LAST_PROC; 
CREATE PROCEDURE P1() 
BEGIN 
 SET @LAST_PROC = "P1"; 
END; 
-- 声明存储过程 P2,在 P2 中使用 P1 中定义的变量@LAST_PROC 
CREATE PROCEDURE P2() 
BEGIN 
 SELECT CONCAT("最后一个存储过程是",@LAST_PROC); 
END; 
-- 调用存储过程 P1(),P2(); 
CALL P1(); 
CALL P2();

3.3存储过程之间的注释

MySQL 存储过程可使用两种风格的注释
双模杠：–
该风格一般用于单行注释
c 风格： /一般用于多行注释/
例如：

3.4存储过程的查询

show procedure status where db='数据库名';
SHOW CREATE PROCEDURE test.P1;
DROP PROCEDURE IF EXISTS P1;

3.5存储过程变量作用域

内部变量享有最高优先权。执行完end语句，内部变量消失。

-- 创建存储过程 
CREATE PROCEDURE PROC6() 
BEGIN 
 DECLARE X1 VARCHAR(5) DEFAULT "OUTER"; 
 -- ----------嵌套块------------------------- 
 BEGIN 
 DECLARE X1 VARCHAR(5) DEFAULT "INNER"; 
 SELECT X1; -- 结果是"INNER" 
 END; 
 -- ----------------------------------------- 
 SELECT X1; -- 结果是"OUTER" 
END; 
-- 调用存储过程 
CALL PROC6();

3.6存储过程条件语句

便于测试我们创建一张表 CREATE TABLE TEMP (ID INT(4));

1、 if-then -else 语句

CREATE PROCEDURE PROC7(IN PARAM INT)
BEGIN
 DECLARE VAR INT;
 SET VAR = PARAM + 1;
 -- ----------IF 语句---------------
 -- 注意:判断 var 是否等于 1,要使用=,而不是==;
 IF VAR = 1 THEN
 	INSERT INTO TEMP VALUES (100);
 END IF;
 -- ----------IF ELSE 语句---------------
 IF PARAM = 0 THEN 
 	UPDATE TEMP SET ID = ID + 1;
 ELSE 
 	UPDATE TEMP SET ID = ID + 2;
 END IF;
END;

调用存储过程
CALL PROC7(0);

2、case 语句

DELETE FROM TEMP;

创建存储过程
CREATE PROCEDURE PROC8(IN PARAM INT)
BEGIN
 DECLARE VAR INT;
 SET VAR = PARAM + 1;
 CASE VAR 
 	WHEN 1 THEN 
 		INSERT INTO TEMP VALUES (1);
 	WHEN 2 THEN
 		INSERT INTO TEMP VALUES (2);
 	ELSE 
 		INSERT INTO TEMP VALUES (100);
 END CASE; 
END;
调用存储过程
CALL PROC8(10);
查询结果:
SELECT * FROM TEMP;

提一点： case when then 其实在我们写sql语句是常有的。不只是说存储过程用到。平时写sql用起来也是比较好的。建议不会的可以熟悉一下。

3、while ··do语句

DELETE FROM TEMP;

CREATE PROCEDURE PROC9()
BEGIN
 DECLARE VAR INT DEFAULT 0; 
 WHILE VAR < 10 DO
 INSERT INTO TEMP VALUES (VAR);
 SET VAR = VAR + 1;
 END WHILE;
END;
 
调用存储过程
CALL PROC9();
查询结果:
SELECT * FROM TEMP;

4、repeat 循环语句 （有点类似 do…while）这个是先执行在判断

创建存储过程
CREATE PROCEDURE PROC10()
BEGIN
 DECLARE VAR INT DEFAULT 0;
 REPEAT 
 INSERT INTO TEMP VALUES (VAR);
 SET VAR = VAR + 1;
 UNTIL VAR >= 10 -- 末尾没有分号
 END REPEAT;
END;
调用存储过程
CALL PROC10();
查询结果:
SELECT * FROM TEMP;

5、loop ·····end loop 循环语句

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样
不需要结束条件, leave 语句的意义是离开循环。

CREATE PROCEDURE PROC11()
BEGIN
 DECLARE VAR INT DEFAULT 0;
 LOOP1:LOOP
 INSERT INTO TEMP VALUES (VAR);
 SET VAR = VAR + 1;
 IF VAR > 10 THEN -- 循环终止条件
 LEAVE LOOP1;
 END IF;
 END LOOP;
END;
CALL PROC11(); -- 调用存储过程
SELECT * FROM TEMP; -- 查询结果

文章挺长的。说起来存储过程确实基本不用了，如果确实需要可以来学习一下。

下次再聊！！！