mysql存储过程和存储函数(1)

mysql存储过程和存储函数

MySql 8.0版本
可参考官方网站：https://dev.mysql.com/doc/refman/8.0/en/faqs-stored-procs.html

概念

存储过程：
是一组可以存储在服务器中的 SQL 语句。完成此操作后，客户端无需继续重新发出单个语句，而是可以引用存储的例程。

存储函数：

函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

​

优缺点

优点：

• 执行效率高：减少编译次数，减少数据库连接次数，在需要处理的数据量很庞大的时候，执行效率大大提高。
• 松耦合：使应用程序和数据库之间形成松耦合，即使改变数据表，也无需修改程序代码，只需要修改存储过程即可。
• 重复性：将大量sql语句集成，大大减少重复代码的编写。
• 减少网络流量：存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。
• 语言多样化：当应用程序使用的是多种语言进行开发，但需要对数据库进行相同操作时，非常有必要使用。
• 安全性：例如，银行将存储过程和函数用于所有常见操作。这提供了一致且安全的环境，并且例程可以确保正确记录每个操作。在这样的设置中，应用程序和用户将无法直接访问数据库表，而只能执行特定的存储例程。

缺点：

• 可移植性差：由于存储过程将业务逻辑放到了数据库层面，如果需要换数据库或者数据迁移，就要重新再写存储过程，毕竟每个数据库提供的函数都不一样。
• 更改比较繁琐：如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等。
• 商业适应能力差：只能适应数据库表修改较少，或者较为稳定的商业模式。不适应经常升级，迁移等的商业项目。
• 编写难度大：没有像java等开发语言有这么其他的纠错工具等，遇到问题比较难以发现，编写的难度较大。

通过以下命令可以查看你需要的数据库的所有存储过程和存储函数

SELECT ROUTINE_TYPE, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA='dbname';

• IN,OUT,INOUT

存储函数只有且默认是in，存储过程可以有in，out，inout

举例吧

delimiter $$
create procedure fixSum(in a int,out b int,inout c int)
begin
	set b = a + c;
	set c = c + 10;
END$$

set @c = 10;
call fixSum(5,@b,@c);
-- in只能输入值，而out和inout可以输出值
select @c,@b;

• 调用

call [存储过程名];
select [函数名];

变量

(此处不细说区别)

• 系统变量（@@）
• 全局变量（重启系统会回复默认值）
• 会话变量
• 用户自定义变量

set @a = 1;
set @b = 2;

select @a + @b;

• 局部变量

作用域：一般在begin …… end复合语句当中

declare 变量名 变量类型 default 值
-- set 赋值

---

使用案例

基本结构（以下以列举存储函数用例）

• 简单函数

create function getSum(a int, b int) returns int
return a + b;
-- 构建好函数

select getSum(5,6);
-- 输出的结果是11

存储过程没有返回值

• 修改结尾界定符

delimiter $$

• BEGIN … END复合语句

create function [方法名(形参列表)] returns [返回参数类型]
begin
[函数体]
[返回值: return 类型(指定数据类型);]
end;

简单示例

delimiter $$
create function selectName(a int) returns varchar(255)
begin
	return (select name from student_info where id = a);
end$$
-- 构建好函数

select selectName(1);
-- 输出的结果是a

• 修改语句

alter procedure [存储过程名];
alter function [函数名];

• 删除语句

drop function [函数名];
-- 不需要写形参
drop procedure [存储过程名];

• return语句

return 变量；

此语句不用于存储过程、触发器或事件。该LEAVE语句可用于退出这些类型的存储程序。

• leave语句

leave

---

流控制构造

只写例子，具体可以去官网查看

• if语句

简单例子

delimiter $$
create function selectIf(a int) returns int
begin
	if a > 10 then return 0;
	elseif a < 10 then return 100;
	else return 1;
	end if;
end$$

• case语句

简单例子

delimiter $$
create function selectCase(a int) returns int
begin
	case a
		when 1 then return 2;
		when 2 then return 3;
		when 3 then return 4;
	end case;
end$$

• loop语句

简单例子

delimiter $$
CREATE function doiterate(p1 INT) returns int
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
	return @x;
END$$
-- 官方例子

其中，ITERATE 循环名 表示再次调用这个循环，LEAVE 循环名 跳出循环

• repeat语句

delimiter $$
CREATE function dorepeat(p1 INT)
BEGIN
 SET @x = 0;
 xuhuan1: REPEAT
	 SET @x = @x + 1;
 UNTIL @x > p1 END REPEAT xuhuan1;
 return @x;
END$$
-- 官方例子

至少进入一次循环

• while语句

delimiter $$
CREATE function dowhile(p1 INT)
BEGIN
	set @x = 1;
	xunhuan2: while @x < p1 do
	set @x = @x + 2;
	end while xunhuan2;
	return @x;
END$$