MySQL——存储过程详解及实例分析

 

目录

一、储存过程简介

1、什么是存储过程

2、存储过程优缺点

3、存储过程入门程序

4、在idea中如何调用储存过程？

 二、存储过程编程

1、存储过程的变量

 2、存储过程中的参数

 3、选择结构if

4、分支结构case

5、3个循环结构

6、存储过程的异常处理

 7、MySQL游标

三、存储过程实例讲解

1、案例1：取消订单

2、案例2：根据商品分类获取商品详情

---

一、储存过程简介

1、什么是存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
          存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、存储过程优缺点

• 优点

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而 一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.存储过程可以重复使用,可减少数据库开发人员的工作量(可以忽略) 

3.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

4. 减少网络通信量、 Java与mysql的IO交互。调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL 语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。 

• 缺点

1、不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时最好不要用存储过程。
2、学习成本高,没有java代码debug方便
3、业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。业务逻辑集中管理会更易于维护与调试，你很难做到业务逻辑都放在存储过程里，比如关于一些客户输入数当据的简单校验，会话数据的校验，应用服务器缓存数据的校验。
4、 运行速度：大多数高级的数据库系统都有statement   cache的，所以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多（检查权限等），所以对于很简单的sql，存储过程没有什么优势。
5、存储占物理内存，如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。

3、存储过程入门程序

DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER 

对上述存储过程的详解：

• 第一个命令是DELIMITER //，它与存储过程语法无关。 DELIMITER语句将标准分隔符 - 分号(;)更改为：//。 在这种情况下，分隔符从分号(;)更改为双斜杠//。为什么我们必须更改分隔符？ 因为我们想将存储过程作为整体传递给服务器，而不是让mysql工具一次解释每个语句。 在END关键字之后，使用分隔符//来指示存储过程的结束。 最后一个命令(DELIMITER;)将分隔符更改回分号(;)。
• 使用CREATE PROCEDURE语句创建一个新的存储过程。在CREATE PROCEDURE语句之后指定存储过程的名称。在这个示例中，存储过程的名称为：GetAllProducts，并把括号放在存储过程的名字之后。
• BEGIN和END之间的部分称为存储过程的主体。将声明性SQL语句放在主体中以处理业务逻辑。 在这个存储过程中，我们使用一个简单的SELECT语句来查询products表中的数据。

要调用存储过程，可以使用以下SQL命令：

 CALL  GetAllProducts();

4、在idea中如何调用储存过程？

<select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">
  <!--该存储过程请阅读本文最后的案例分析2-->
        call  find_category_id( #{cid,mode=IN,jdbcType=INTEGER}  )
    </select>

<select id="getProductByCid" resultType="com.xmcc.redis01.vo.ProductVo">

        call  find_category_id( #{cid,mode=IN,jdbcType=INTEGER} ，

#{id,mode=OUT,jdbcType=INTEGER} )
    </select>

其他命令： 

查看已有的procedure
show procedure status \G

删除存储过程
drop procedure 名字

 二、存储过程编程

1、存储过程的变量

声明变量：要在存储过程中声明一个变量，可以使用DECLARE语句

DECLARE variable_name datatype(size)  DEFAULT default_value;

• 首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
• 其次，指定变量的数据类型及其大小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。
• 第三，当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。
• 如：DECLARE test INT;

分配变量值： 要为变量分配一个值，可以使用SET语句，或者使用SELECT INTO语句将查询的结果分配给一个变量

DECLARE total_count INT DEFAULT 0;

SET total_count := 10;

或者：

SELECT COUNT(*) INTO total_count  FROM products

 变量范围(作用域)

一个变量有自己的范围(作用域)，它用来定义它的生命周期。 如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

如果您在BEGIN END块内声明一个变量，那么如果达到END，它将超出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。 但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

 :=与=的区别：

1.= 
只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
2.:= 
不只在set和update时时赋值的作用，在select也是赋值的作用。

如果明白了=和:=的区别，那么也就理解了下边的现象。 
@num:=@num+1,:=是赋值的作用，所以，先执行@num+1,然后再赋值给@num，所以能正确实现行号的作用。 
@num=@num+1,此时=是等于的作用，@num不等于@num+1，所以始终返回0，如果改为@num=@num,始终返回1了。mysql数据库中，用1表示真，0表示假。

 2、存储过程中的参数

在MySQL中，参数有三种模式：IN，OUT或INOUT。

• IN - 是默认模式。(可写可不写) 在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
• OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。请注意，存储过程在启动时无法访问OUT参数的初始值。
• INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。  

#需要注意类型是写在后面的

CREATE PROCEDURE my_test5(IN i INT,  OUT message VARCHAR(20))

 3、选择结构if

因为很类似于Java，此处直接放实例了，详解介绍可以参考：MySQL教程——存储过程的if语句

delimiter $$
create procedure p4()
begin
declare age int default 18;
if age >=18 then
select concat('已经成年');
else
select concat('未成年');
end if;#(只是结束if 在存储过程中的if、循环语句等基本都需要结束)

end $$


参数的传递
#求矩形的面积
delimiter $$
create procedure p5(width int,height int)
begin
	select concat('你的面积是',width*height) as area;
	if width>height then
	select '胖';
	elseif width < height then
	select '瘦';
    else
    select '方';
   end if;
end $$

4、分支结构case

case……when ~then ~

delimiter $$
create procedure p9()
begin
declare pos int default 0;
case pos  #相当于switch（）
	when 1 then select 1;#相当于case
	when 2 then select 2;
	when 3 then select 3;
	else select 'ojbk'; #相当于default
end case;
end $$

带参数
delimiter $$
create procedure p10(num int)
begin
case num
when 1 then select 1;
when 2 then select 2;
when 3 then select 3;
else select 'ojbk';
end case;
end $$
注意 参考java中的switch\case语句

5、3个循环结构

• while ~~~do

----》实例1：
#求1-100之和
delimiter $
create procedure p6()
begin
	declare total int default 0;
	declare num int default 0;
while num<=100 do
		set total := total +num;
		set num = num+1;
end while;
select total;
end$

----》实例2：
增强： 求1~N的何 N由用户传入
create  procedure p7(in n int)
begin
	declare total int default 0;
	declare num  int default 0;
while num<=n do	
	set total = total +num;
set num = num+1;
end while;

select total;
end$


----》实例3：
增强：out
create  procedure p8(in n int,out total int)
begin
	#设置初始值 如果不设置初始值 结果为NULL
	declare num  int default 0;
	set total = 0;
while num<=n do	
	set total = total +num;
	set num = num+1;
end while;
end$

#参数一：直接输入 参数二 定义一个变量 接受total的结果
call p8(100,@sum);

#查询接受参数
select @sum$

repeat

语法结构
repeat
		sql1
		sql2
until（直到） 条件 

repeat和until之间就是循环体

create procedure p11()
begin
declare i int default 0;
declare total int default 0;
repeat #开始循环
	set i = i+1;
	set total = total+i;
until i>=100#当i大于等于100就执行下面的 结束循环
end repeat;
select total;
end$

 loop

#loop 用法  
DELIMITER //
CREATE PROCEDURE my_test4()
BEGIN
     DECLARE i INT DEFAULT 100;
     loop_label: LOOP  #相当于java的标记 用于多重循环，loop表示开始循环 也可以不用标记 这儿一起举例了
        INSERT INTO xmcc_product VALUES(CONCAT('1000',i),CONCAT('手机',i),i*10,i*100);
       SET i=i+1;
        if i>=105 THEN  #当i大于105的时候 执行下面的
            leave loop_label; #离开标记处的循环
        END IF; #结束if 再次提示 注意
    END LOOP; #离开循环 语法
    END ;// #简写 delimiter

 #调用 
 CALL my_test4();

6、存储过程的异常处理

语法：

01）当sql出现异常的时候跳出存储过程并设置值为xxx
    Declare exit handler for sqlException set …..

02）当sql出现异常的时候继续存储过程并设置值为xxx
    Declare continue handler for sqlException set …..

-- continue继续  exit 退出  HANDLER执行处理器    SQLEXCEPTION:sql异常

编程实例：（结合事务使用）

#存储过程异常处理
DELIMITER//
CREATE PROCEDURE my_test7()
BEGIN
DECLARE i INT DEFAULT 0;
#发生SQL异常时，程序继续，并设置i=-1；
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i:=-1;
START TRANSACTION;#开启事务
UPDATE xmcc_product SET p_name='苹果' WHERE p_id=10000;
INSERT INTO xmcc_product VALUES(10000,'主键冲突',10,100); 
IF i=-1 THEN
SELECT '出现异常，事务回滚';
ROLLBACK; #回滚
ELSE
COMMIT; #提交
END IF;
END//
DELIMITER ;
#调用 
CALL my_test7();
#查看结果
SELECT * FROM xmcc_product

 7、MySQL游标

游标介绍：（处理存储过程中的结果集）

1）有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。 
2）先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
3）类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个

使用语法：

1）declare 声明；（游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。游标必须始终与SELECT语句相关联。）
    declare 游标名 cursor for  查询语句

2）open 打开；
    open 游标名

3）fetch 取值；
        fetch 游标名 into val1,val2,val3……

4）close 关闭；
        close 游标名

实用实例：（该实例非常重要，请重点理解）

DELIMITER //
CREATE PROCEDURE my_test8()
BEGIN
DECLARE result VARCHAR(100) DEFAULT '';-- 定义一个result 来存储商品名称的拼接
DECLARE product_name VARCHAR(10);-- 定义一个变量来接收每次游标的商品名称
DECLARE done INT DEFAULT FALSE; -- 定义done变量 默认值为false
-- 查询所有的商品名称放入游标中 CURSOR 表示该变量为游标类型
DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;
-- 游标遍历结束 会出现not found 设置done的值为true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
OPEN curl;-- 打开游标
WHILE(NOT done) DO
FETCH curl INTO product_name; -- 弹出游标的一条数据赋值给product_name
SET result:=CONCAT(result,',',product_name);-- 拼接字符串
END WHILE;
CLOSE curl;-- 关闭游标 就像java中需要关闭resultset一样
SELECT result;-- 打印出结果
END;//
#调用
CALL my_test8();

三、存储过程实例讲解

1、案例1：取消订单

案例:当取消订单的时候
    1.修改订单状态为取消(1)
    2.对订单项里面的商品增加库存

 存储过程编写及分析（分析请看注解）

#编写取消订单的存储过程
DELIMITER $$
#输入参数为订单order_id 输出参数为result 1 代表存储过程成功 -1代表存储过程出现异常
CREATE PROCEDURE cancel_order(IN order_id VARCHAR(30),OUT result INT)
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result:=-1;#如果出现异常设置返回值为-1
START TRANSACTION;#开启事务
SET result:=1;#设置初始值 这里设置为1
UPDATE xmcc_order SET o_statu=1 WHERE o_id=order_id; #修改订单状态
     BEGIN 
	  DECLARE done INT DEFAULT TRUE;#定义变量done用来判断
	  DECLARE product_id VARCHAR(30); #定义变量 来接收游标的商品id
	  DECLARE quantity_1 INT; #定义变量来接收游标的商品数量
	  #定义游标 存储根据订单id查询到订单项中的商品id与数量
	  DECLARE cur CURSOR FOR SELECT p_id,quantity FROM xmcc_orderdetail WHERE o_id=order_id;
	  #当游标循环结束 设置done的值为false
	  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=FALSE;
	  OPEN cur;#打开游标
	  WHILE done DO #循环
	  FETCH cur INTO product_id,quantity_1;#将游标的值设置到变量中
	  IF done THEN #判断 不然会多修改一次
	  UPDATE xmcc_product SET p_stock=p_stock+quantity_1 WHERE p_id=product_id;
	  END IF;
	  END WHILE;
	  CLOSE cur;
     END ;
 IF result=1 THEN #没有出现异常就提交
 COMMIT;
 ELSE 
 ROLLBACK;
 END IF;
 END ; $$
 #测试
CALL cancel_order('20001',@result);
#查看结果
SELECT @result

2、案例2：根据商品分类获取商品详情

项目资料背景：

数据库资料：
    1、产品分类表：xmcc_category。数据内容结构（父—子结构）：A（a1,a2,a3）B（b1,b2,b3）
    2、产品详情表：xmcc_product 有对应的category_id

项目需求：
    根据category_id获取到产品详情的集合。

逻辑分析：
    如：查询的category_id=A    
    1）根据category_id=A  从表xmcc_category获取到所有的分类id集合list1（A，a1,a2,a3）
    2）根据list1从表xmcc_product 获取对应的产品详情list2<xmcc_product >

存储过程编程： 

DELIMITER //
#传入参数category_id 分类id
CREATE PROCEDURE find_category_id(IN category_id INT)
BEGIN
DROP TABLE IF EXISTS tmp_id;
#创建临时表 用来存储所有的id 临时表在链接结束会自动删除
CREATE TEMPORARY TABLE  tmp_id(id INT);
#清空该表
TRUNCATE TABLE tmp_id;
#临时表 用来存储所有的商品信息;
#根据分类查询商品的时候，商品展示列表只需要这几个字段就可以了，详情的时候才是所有字段 
DROP TABLE IF EXISTS tmp_product;
CREATE TEMPORARY TABLE  tmp_product(SELECT id,NAME,subtitle,main_image,price FROM xmcc_product WHERE 1=2);
TRUNCATE TABLE tmp_id;
#调用另外的存储过程
CALL recursive_find_id(category_id);
  BEGIN
  #设置循环条件
   DECLARE done INT DEFAULT '0';
   #设置cid来接收游标中弹出的id
   DECLARE cid INT DEFAULT '0';
   #递归过程将所有的id放在临时表中 现在查出放在游标ids中
   DECLARE ids CURSOR FOR SELECT id FROM tmp_id;
   #当游标遍历结束 done=1
   DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=1;
   #打开游标
   OPEN ids;
   #弹出游标一个值赋给cid
   FETCH ids INTO cid;
   WHILE (done=0) DO
   #根据分类查找到商品 ，插入数据到商品临时表中
   INSERT INTO tmp_product  SELECT id,NAME,subtitle,main_image,price FROM xmcc_product xp WHERE xp.category_id=cid and xp.status=1;
   FETCH ids INTO cid;
   END WHILE;
  END;
  #返回查询到的商品结果集
SELECT * FROM tmp_product;
END ;//


DELIMITER //
CREATE PROCEDURE recursive_find_id(IN category_id INT)
BEGIN 
#定义id接收每次查询到的分类id
DECLARE cid INT;
#定义done为0TRUE继续循环
DECLARE done INT DEFAULT TRUE;
#定义ids游标来获得parent_id为传入的id的集合
DECLARE ids CURSOR FOR SELECT id FROM xmcc_category WHERE parent_id=category_id;
#游标循环结束 出现not found  done=1循环结束
DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=FALSE;
#mysql 递归的时候 需要设置深度
SET @@max_sp_recursion_depth = 10;
#将id插入临时表中
INSERT INTO tmp_id VALUES(category_id);
OPEN ids;#打开游标
#将查询到的游标结果 弹出赋值给id 每次弹出一个
FETCH ids INTO cid;
WHILE done DO
#递归调用当前存储过程
CALL recursive_find_id(cid);
FETCH ids INTO cid;
END WHILE;
CLOSE ids;
END;//