互联网数据库Mysql高级——存储过程

存储过程

一、存储过程简介

** 为什么要用存储过程：

       MySQL5.0 版本开始支持存储过程，大多数 SQL 语句都是针对一个或多个表的单条语句。并非所有的操作都那么简单。经常会有一个完整的操作需要多条语句才能完成。存储过程简单来说，就是为以后的使用而保存的一条或多条 MySQL 语句的集合。可将其视为批处理文件，虽然他们的作用不仅限于批处理。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

** 存储过程的优点：

       1)通过把处理封装在容易使用的单元中，简化复杂的操作；

       2)简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码；

       3)通常存储过程有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。

       4)存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只用发送存储过程的名称和参数；

       5)存储的程序对任何应用程序都是可重用的和透明的。

       6)存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

** 存储过程的缺点：

       1)如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。

       2)存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难；

       3)很难调试存储过程。

       4)开发和维护存储过程并不容易。

二、MySQL 中的存储过程

** 创建与调用过程：

创建存储过程（语法）：

create procedure mypro(in a int,in b int,out sum int)
begin
   set sum = a+b;
end;

运行效果：

** 调用存储过程（语法）：

call mypro(1,2,@s);-- 调用存储过程

select @s;-- 显示过程输出结果

运行效果：

** 存储过程语法解析：
    create procedure 用来创建过程；

    mypro 用来定义过程名称；

    (in a int,in b int,out sum int)表示过程的参数，其中 in 表示输入参数，out 表示输出参数。类似于 Java 定义方法时的形参和返回值；

    begin 与 end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；

    call 用来调用过程，@s 是用来接收过程输出参数的变量。

** 存储过程的参数：
    MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型：

    IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；

    OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；

    INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

** 变量：
       MySQL 中的存储过程类似 java 中的方法。既然如此，在存储过程中也同样可以使用变量。java 中的局部变量作用域是变量所在的方法，而 MySQL 中的局部变量作用域是所在的存储过程。

案例：

use mysqladv; -- 使用 mysqladv 数据库
create PROCEDURE mypro1() -- 创建存储过程
begin
  declare name varchar(16);-- declare声明变量
  set name='张';-- 变量赋值
  select * from mytable where username=name;
end;
call mypro1();-- 调用过程

运行效果：

** 流程控制语句：

    1)if 条件语句
    IF 语句包含多个条件判断，根据结果为 TRUE、FALSE 执行语句，与编程语言中的 if、else if、else 语法类似。

   案例：

-- 输入一个整数，使用 if 语句判断是正数还是负数
create PROCEDURE mypro2(in num int)
BEGIN
    if num<0 THEN -- 条件开始
     select '负数';
    elseif num=0 THEN 
     select '不是正数也不是负数';
    else 
     select '正数';
    end if;-- 条件结束
end;
call mypro2(0);-- 赋值 调用过程

运行结果：

    2)case 条件语句:
    case 是另一个条件判断的语句，类似于编程语言中的 choose、when 语法。MySQL 中的 case 语句有两种语法格式。

   案例：

-- 定义存储过程，输入一个整数，使用 case 语句判断是正数还是负数，代码如下所示：
-- 创建过程
create procedure mypro3(in num int)
begin
  case -- 条件开始
    when num<0 then select '负数';
    when num=0 then select '不是正数也不是负数';
               else select '正数';
  end case; -- 条件结束
end;
-- 调用过程
call mypro3(1);

运行结果：

    3)while 循环语句:
    while 语句的用法和 java 中的 while 循环类似。

   案例：

-- 用while语句计算出1+2+3+...+10的累加和
-- 创建过程
create PROCEDURE mypro4(out sum int)
begin
  declare i int default 0;
   set sum=0;
  while i<10 DO -- 循环开始
   set i=i+1;
   set sum=sum+i;
  end while;-- 循环结束
end;
-- 调用过程
call mypro4(@sum);
-- 查询变量值
select @sum;

运行结果：

   拓展案例：
计算1-100既能被3整除，又能被5整除的数字之和：

-- 计算1-100既能被3整除，又能被5整除的数字之和
create PROCEDURE myprodb(out sum int)
begin
  declare i int default 0;
     set sum=0;
  while i<=100 DO -- 循环开始
  if i%5=0 and i%3=0 then 
     set sum=sum+i;
  end if;
     set i=i+1;
  end while;-- 循环结束
end;
-- 调用过程
call myprodb(@u);
-- 查询变量值
select @u;

运行结果：

    4)repeat 循环语句:
    repeat 语句的用法和 java 中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是 repeat 表达式值为 false 时才执行循环操作，直到表达式值为 true 停止。

   案例：

-- 定义存储过程，使用 repeat 循环输出 1 到 10 的累加和
-- 创建过程
create procedure mypro5(out sum int)
begin
declare num int default 0;
   set sum = 0;
   repeat-- 循环开始
     set num = num+1;
     set sum = sum+num;
   until num>=10
   end repeat; -- 循环结束
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;

运行结果：

    5)loop 循环语句:

    循环语句，用来重复执行某些语句。执行过程中可使用 leave 语句或 iterate 跳出循环，也可以嵌套 IF 等判断语句。

    leave 语句效果相当于 java 中的 break，用来终止循环；

    iterate 语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

   案例：

-- 定义存储过程，使用 loop 循环输出 1 到 10 的累加和
-- 创建过程
create procedure mypro6(out sum int)
begin
  declare num int default 0;
  set sum = 0;
	  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;

运行结果：

三、存储过程的管理

** 显示存储过程

  ①显示存储过程的语法：

SHOW PROCEDURE STATUS;-- status是状态

  ②显示特定数据库的存储过程语法：

SHOW PROCEDURE status where db = 'mysqladv';
-- mysqladv是数据库

  ③显示特定模式的存储过程语法：

SHOW PROCEDURE status where name like '%my%';
-- 要求显示名称中包含“my”的存储过程

** 显示存储过程源码

  显示某一个存储过程的源代码语法：

SHOW CREATE PROCEDURE mypro;-- mypro为过程的名字

** 删除存储过程

  删除存储过程"mypro1"语法：

drop PROCEDURE mypro1;-- mypro1为过程的名字