mysql存储过程 | 学习整理

mysql存储过程

1、什么是存储过程？

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

MySQL 5.0 版本开始支持存储过程。

类似开发语言中的函数，方法。

2、为什么是用它？直接sql语句不香吗？

前面说了，存储过程就像开发语言中的函数（mysql中的函数必须有返回值），这就好比面向对象编程和面向过程编程的区别，体现封装的重要性，相对复杂的逻辑处理，避免重复造轮子。

存储过程优点：

• 封装处理，简化复杂的操作，简化对变动的管理

• 保证数据一致性

• 灵活，可编程性强，并且能完成较复杂的判断和运算

• 可以回传值，并可以接受参数。

• 提高性能，减少网络之间的数据传输，节省开销

• 权限划分

相对应的缺点：

• 编写比SQL语句复杂

• 性能调校与撰写，受限于各种数据库系统

• 移植性很差，切换数据库时，需要重新编写

3、创建一个简单的存储过程

3.1 基础语法

其中 $$ 可以替换成 //

-- 判断如果存在则删除，可以根据实际情况选择使用
drop procedure if exists `存储过程名称`;

-- 创建存储过程
delimiter $$
create procedure 存储过程名称()
begin
	//具体sql语句
end $$

-- 调用存储过程
call 存储过程名称();

3.2 创建一个简单的存储过程

-- 判断如果存在则删除，可以根据实际情况选择使用
drop procedure if exists `test`;

-- 创建存储过程
delimiter $$
create procedure test()
begin
	 select 'joker say hello world';
end $$

-- 调用存储过程
call test();

返回结果：

4、变量

4.1 什么是变量？

变量是一个命名数据对象，变量的值可以在存储过程执行期间更改。我们通常使用存储过程中的变量来保存直接/间接结果。 这些变量是存储过程的本地变量。

变量必须先声明后，才能使用它。

-- 变量声明及赋值语法
DECLARE variable_name datatype(size) DEFAULT default_value;
SET variable_name = value 

-- 举例
DECLARE age int DEFAULT 0;
SET age = 18;

4.2 变量的作用域

如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

简单理解就是，作用范围在begin和end块之间，超过end就会失效。

示列1:

-- 创建存储过程
delimiter $$
create procedure test2()
begin
    begin 
    	 -- 定义一个 年龄变量
			 DECLARE age int DEFAULT 0;
    end;
    begin 
    	 -- 查询年龄变量
			 select age;
    end;  
end $$

-- 调用存储过程
call test2();

示列1 结果：

很显然，报错了，在第二个begin end的作用域里面，没有age这个变量

示列2： 修改示列1中的语句，将age设置为全局变量

-- 创建存储过程
delimiter $$
create procedure test3()
begin
		-- 定义一个 年龄变量
		DECLARE age int DEFAULT 0;
    begin 
			set age = 18;
    end;
    begin 
    	 -- 查询年龄变量
			 select age;
    end;  
end $$

-- 调用存储过程
call test3();

示列2 结果：

5、参数

在现实应用中，开发的存储过程几乎都需要参数。这些参数使存储过程更加灵活和有用。 在MySQL中，参数有三种模式：IN，OUT或INOUT。

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

示列:

drop procedure if exists `test4`;
-- 创建存储过程
delimiter $$
create procedure test4(in numadd int, out numa int, inout numb int)
begin
		set numa = numadd + 1;
		set numb = numb + numadd;
end $$

-- 调用存储过程
set @a = 0;
set @b = 10;
call test4(1,@a,@b);
select @a, @b;

---

下次更新 循环相关的语法