MySql高级（二）

二. 存储过程

1.存储过程简介

• 就是为以后使用而保存的一条或多条MySQL语句，可以视为批处理文件.
• 思想上是sql层面的代码封装，重用.

2.存储优缺点

1. 简化复杂的操作，简化对变动的管理.
2. 通常存储过程有助于提高应用程序的性能，减少流量.
3. 存储的程序是安全，可重用高，透明的.

• 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难
• 很难调试存储过程
• 开发和维护存储过程难
• CPU 使用率也会增加

3.创造与调用过程

1. create procedure 用来创建过程；
2. mypro 用来定义过程名称；
3. (in a int,in b int,out sum int)表示过程的参数，其中 in 表示输入参数（常量或变量），out 表示输出参数（变量）inout表示输入输出参数（变量）。类似于 Java 定义方法时的形参和返回值；
4. begin 与 end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；
5. call 用来调用过程，@s 是用来接收过程输出参数的变量。

创建：

-- 创建存储过程 
create procedure mypro(in a int,in b int,out sum int) 
-- create创建 关键词procedure 名称自定义 ()
begin-- 关键词
	set sum = a+b; 
end; -- 关键词

调用：

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果

3.1 变量

1. 定义变量

   • declare 用于声明变量；
   • variable_name 表示变量名称；
   • datatype 为 MySQL 的数据类型；
   • default 用于声明默认值;

   DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
   -- 如 定义一个name 数据类型 默认值 'jack'
   declare name varchar(20) default ‘jack’;
   

2. 变量赋值

   SET 变量名 = 表达式值 [,variable_name = expression ...]
   

   create procedure mypro1()-- 无参查询语句
   BEGIN
   DECLARE belece int;
   SET belece = 200 ;-- 已有数据
   SELECT * FROM t_act WHERE balance=belece;
   END;
   call mypro1();
   

3. 流程控制语句

   if条件语句

   ​ 关键词：if，then，else

   create procedure mypro2(in n INT)-- 不加；号
   BEGIN
   IF n<0 THEN -- if 条件 then 开始
   SELECT '负数';-- 当n<o时发生
   ELSEIF n=0 then -- 否则 n=0 
   SELECT '0'; -- 时发生
   ELSE SELECT '正数';-- 否则
   END IF;-- 结束
   end;
   CALL mypro2(-1);
   

   case 条件语句

   ​ 判断区间：

   ​ 关键词：case，when

   create PROCEDURE mypro3(in n INT)
   begin
   	CASE-- 开始
   		when n<0 THEN SELECT '负数';-- 满足条件时
   		when n=0 then select '0'; -- 满足条件时
   		ELSE SELECT '正数';-- 都不满足时
   	END CASE;-- 结束
   end
   CALL mypro3(5);
   

   ​ 判断准确数值

   create procedure mypro4(in num int) 
   begin
   case num -- 条件开始传入参数 
   when 1 then select '数值是 1';
   when 2 then select '数值是 2';
   else select '不是 1 也不是 2'; 
   end case; -- 条件结束 
   end; -- 调用过程 
   call mypro4(3);
   

   while 循环语句

   ​ 关键词 ：while，do，set

   CREATE PROCEDURE mypro4(OUT sum int)-- 输出一个参数
   BEGIN
   -- 初始化一个变量默认为零
   DECLARE num int DEFAULT 0;
   -- 初始化输出元素
   set sum=0;
    while num<10 DO-- 开始循环
     set num=num+1;
     set sum=sum+num;
    END while; -- 循环结束
   END;
   -- 应用
   CALL mypro4(@sum)
   -- 查询
   SELECT @sum
   

   repeat 循环语句

   ​ 关键词：repeat，until

   CREATE PROCEDURE mypro5(OUT sum INT)
   BEGIN
   -- 初始化变量
   DECLARE num int DEFAULT 0;
   SET sum =0;
   	REPEAT -- 类似于do while循环
   		SET num=num+1;
   		set sum =sum +num;
   	UNTIL num>=10
   	END REPEAT;
   
   end;
   call mypro5(@sum)
   select @sum
   

   loop 循环语句

   1. leave 语句效果相当于 java 中的 break，用来终止循环；
   2. iterate 语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

   ​ 关键词：loop，leave，iterate

   CREATE procedure mypro6(out sum INT)
   BEGIN
   -- 初始化变量
   DECLARE num INT DEFAULT 0;
   	SET sum=0;
   -- 循环开始 别名：loop
   	LOOP_sum:LOOP
   -- 循环
   		SET num=num+1;
   		set sum =sum+num;
   -- 判断
   		if num>=10 THEN
   -- 满足条件运行
   -- 跳出循环
   			LEAVE loop_sum;
   		end if;
   -- 结束循环
   	END loop loop_sum;
   end;
   CALL mypro6(@sum)
   SELECT @sum
   

   • 输出1-100被5整除

   -- 设置过程
   CREATE PROCEDURE method6(out sum int )
   BEGIN
   -- 初始化i将其默认为1
   DECLARE i INT DEFAULT 1;
   -- 定义一个容器
   set sum =0; 
   -- 小于最大值
   WHILE i<=100 DO
   -- i除与5=0进入if语句
   	IF i%5=0 THEN
   	-- 将其放入容器
   	SET sum=sum+i;
   	END IF;-- 结束if
   	-- i+1继续循环
   	SET i=i+1;
   	-- 不满足条件结束循环
   end WHILE;
   END;
   CALL method6(@t)
   SELECT @t
   
   

4.存储过程管理

1. 显示存储过程

   SHOW PROCEDURE STATUS;
   -- 显示特定数据库的存储过程
   SHOW PROCEDURE status where db = 'schooldb';
   -- 显示特定模式的存储过程，要求显示名称中包含“my”的存储过程
   SHOW PROCEDURE status where name like '%my%';
   

2. 显示存储源码

   SHOW CREATE PROCEDURE mypro1;
   

3. 删除存储过程

   drop PROCEDURE mypro1;