mysql存储过程

MySQL存储过程

什么是存储过程

就是为以后的使用而保存的一条或多条 MySQL 语句的集合；

类似于Java中封装的无参有参方法

存储过程的优点

1、简化多条sql的操作；

2、简化对变动的管理；

3、有助于提高应用程序的性能；

4、有助于减少应用程序和数据库服务器之间的流量；

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

6、存储的程序是安全的；权限分配。

存储过程的缺点

1、大量使用存储过程，内存和cpu使用量会大大增加；

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

3、很难调试存储过程，mysql数据库不支持调试存储过程；

4、开发和维护存储过程并不容易；

如何使用存储过程

创建存储过程

-- 创建存储过程 
create procedure mypro(in a int,in b int,out sum int) 
begin
  set sum = a+b; 
end;

语法解析：procedure 存储过程关键字; mypro 存储过程名称; in 输入类型; a 参数名字; int 参数类型; out 输出参数; sum 输出参数名字;

​ begin end 类似于Java方法中的{ }; set 赋值;

调用存储过程

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

关键字：call 调用; mypro 存储过程的名字; (1,2,@s) 创建存储过程时创建的输入输出参数，@s表示创建一个变量接受输出参数; select 查询；

存储过程的参数

in 输入参数；out输出参数；inout输入输出参数

注意事项：in能输入字面量或变量，out只能输出变量,inout 输入输出在使用输出的时候只能输出变量

存储过程的类别

1、没有参数的过程;

2、只有输入参数的过程;

3、只有输出参数的类型;

4、包含输入和输出参数的过程;

变量

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

定义变量

DECLARE variable_name [,variable_name...] datatype [DEFAULT value]

语法解析：DECLARE 用于声明变量；variable_name 表示变量名称；datatype 为 MySQL 的数据类型；DEFAULT用于声明默认值;

变量赋值

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

案例

create PROCEDURE mypro1()
BEGIN
	DECLARE name1 varchar(20);
	set name1='张三';
	select * from student where Sname=name1;
END;
call mypro1;

注意：申明一个变量只能在begin和end中使用，类似于Java中的局部变量

流程控制语句

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

案例

-- 创建过程 
create procedure mypro2(in num int) 
begin
 if num<0 then -- 条件开始 
   select '负数'; 
 elseif num=0 then 
   select '不是正数也不是负数'; 
 else 
   select '正数'; 
 end if;-- 条件结束 
end;
-- 调用过程 
call mypro2(-1);

case 条件语句

case 是另一个条件判断的语句，类似于编程语言中的 choose、when 语法。MySQL 中的 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);

案例2

-- 创建过程 
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);

注意：两种 case 语法都可以实现条件判断，但第一种适合范围值判断，而第二种适合确定值判断。

while 循环语句

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

-- 创建过程 
create procedure mypro5(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 mypro5(@sum); 
-- 查询变量值 
select @sum;

repeat 循环语句

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

案例

-- 创建过程 
create procedure mypro6(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 mypro6(@sum); 
-- 查询变量值 
select @sum;

loop 循环语句

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

案例

-- 创建过程 
create procedure mypro7(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 mypro7(@sum); 
-- 查询变量值 
select @sum;

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

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

存储过程的管理

显示存储过程、显示存储过程源码、删除存储过程；

显示所有的存储过程

SHOW PROCEDURE STATUS;

显示特定数据库的存储过程

SHOW PROCEDURE status where db = 'schooldb';

显示特定名字的存储过程

SHOW PROCEDURE status where name like '%my%';

显示存储过程源码

SHOW CREATE PROCEDURE mypro1;

删除存储过程

drop PROCEDURE mypro1;

注意：在使用Navicat学习时彩色版12无法创建存储过程，建议更换其他版本