MySQL（七）存储过程

最新推荐文章于 2024-07-09 07:43:41 发布

Dim_Jerry

最新推荐文章于 2024-07-09 07:43:41 发布

阅读量563

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Dim_Jerry/article/details/110203466

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库存储过程

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

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，它可以封装成SQL语句集，以便完成一些较为复杂的业务逻辑，并且可以想 Java 等高级编程语言一样输入参数。

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

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

问题来了？

为什么wo们不直接使用 SQL来查询业务数据，Java 来处理业务逻辑，这样子做不行吗？为什么要费尽心思去特意学SQL的存储过程来处理业务逻辑？

因为我们在实际的生产当中，我们可以通过修改存储过程的方式修改业务逻辑，而不用启动服务器。另一方面，存储过程是期间京每一条SQL语句都进行编译，并保存在数据库当中，那么我们通过Java程序调用SQL语句时可以不同一条条的进行编译，而是直接调用存储过程，这样的效率更高。

优点
- 存储过程可封装，并隐藏复杂的商业逻辑。
- 存储过程可以回传值，并可以接受参数。
- 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
- 存储过程可以用在数据检验，强制实行商业逻辑等。
缺点
- 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
- 存储过程的性能调校与撰写，受限于各种数据库系统。

1. 语法结构

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
   
-- proc_parameter参数部分(先写参数名，再写参数类型):      [ IN | OUT | INOUT ] param_name type  
                                                                                                      -- type 是 MySQL 支持的所有类型
                                                                                                      
-- routine_body(程序体)部分 ，可以书写合法的SQL语句：  BEGIN ...... END$$

声明语句结束符，可以自定义:
```
DELIMITER $$
```

声明存储过程:

CREATE PROCEDURE hello_procedurer(IN 参数名 参数类型)

存储过程开始和结束符号:
```
BEGIN .... END   
```
演示示例：

-- 声明语句结束符，可以自定义:
delimiter $$
-- 创建存储过程，名为  hello_procedure， 参数为空， 执行输出 'hello world'
create procedure hello_procedure ()
begin
    select 'hello world';
end $$
-- 声明语句结束符
delimiter ;
-- 调用春初过程
call hello_procedure();
-- 删除存储过程
drop procedure hello_procedure;

2. 变量结构

MySQL变量一共分为两大类：用户自定义变量和系统变量。如下：

用户自定义变量（重点）
- 局部变量
- 会话变量
系统变量（理解）
- 会话变量
- 全局变量

1. 局部变量

局部变量就是用户自定义，在begin … end 代码块中定义的，也在代码块中有效。

语法：
       declare  变量名  变量类型  [default 默认值]
举例：  declare name varchar(32) '张三'

-- set 赋值
delimiter $$

drop procedure if exists var01$$

create procedure var01()
begin
    -- 声明变量
    declare username varchar(32) default '张三';
    -- 为变量赋值
    set username = '李四';
    select username;
end $$

delimiter ;

call var01();

2. 会话变量

会话变量即为服务器为每个客户端连接维护的变量。在客户端连接时，使用相应全局变量的当前值对客户端的回话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量。其作用域与生命周期均限于当前客户端连接。


delimiter $$
drop procedure if exists var02$$

-- 创建存储过程
create procedure var02()
begin
    set @username='张三';
end $$
delimiter ;

-- 调用存储过程
call var02();
-- 查询会话变量的值
select @username;

@username 会话变量可以在 begin … end 代码块中定义，也可以在外部定义，定义后同一变量名在内存中只有一份，且当前会话有效。

3. 入参变量和出参变量

IN 输入参数

表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

仅需要将数据传入存储过程，并不需要返回计算后的该值。

只能当做传入参数
OUT 输出参数

该值可在存储过程内部被改变，并可返回

不接受外部传入的数据，仅返回计算之后的值。

只能当做转出参数
INOUT 输入输出参数

调用时指定，并且可被改变和返回

需要数据传入存储过程经过调用计算后，再传出返回值

可当做传入转出参数

详细演示

声明语句结束符，可以自定义:

delimiter $$
创建一个会话变量，用于测试out对会话变量的影响

set @username=‘wangwu’;
如果存在该存储过程，则删除

drop procedure if exists var03$$
创建一个存储过程，命名为var03。该存储过程是传入两个变量，第一个为输入in变量，第二个为是输出out变量。当输入用户id 时，返回用户名。
```
create procedure var03(in id int,out username varchar(32))
begin
    select id,username;
    -- select 'procedure into username' into username;
end $$
```
声明语句结束符为；

delimiter ;
查询会话变量用户名

select @username;

在还未调用存储过程时，我们发现此时的会话变量@username的值依然为 wangwu
调用存储过程，查看会话变量值是否正确

call var03(35,@username);

😤我们会发现，此时查询到的username会为空。？？？？？？？？这是为什么呢？😤

这里就是 in 和 out 参数的区别了。也有区别与Java等高级语言中的参数。

如果是以Java的思想理解调用存储过程的话，我们可能会认为说 @username变量的值为wangwu 传入到存储过程函数中，所以我们也理所应当的认为 username的值应该为wangwu。

所以这就是错误的地方了。 out函数它只负责变量的接收，不负责传入值。也就是说调用call var03(35,@username); 的时候，@username只是将它的内存地址传进去，告诉存储过程说，等等你把out变量username的值给我（我是@username）。然后在存储过程中一直都没有给username进行赋值的操作，所以此时的username的值为null，最终就将null赋值给了 @username。
查询会话变量用户名

select @username;

看完了步骤7，也就大概了解了为什么@username经过存储过程后就变为空了。
删除之前的存储过程，打开存储过程中的注释，再次运行查看结果。

-- 测试in入参 out出参

set @username = 'wangwu';

delimiter $$
drop procedure if exists var03$$

create procedure var03(in id int, out username varchar(32))
begin
    select id, username;
    -- select 'procedure into username' into username;
    -- set username='procedure into username'
end $$

delimiter ;

select @username;

call var03(35, @username);
select @username;

3. 流程控制

在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程。MySQL 中流程控制语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句等。

下面将详细讲解这些流程控制语句。

1. 判断（IF 语句）

IF 语句用来进行条件判断，根据是否满足条件（可包含多个条件），来执行不同的语句，是流程控制中最常用的判断语句。其语法的基本形式如下：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF
------------------------------------------------------------------------------------------
IF  判断条件  THEN 执行语句
	[ELSELF  判断条件  THEN  执行语句]
	[ELSE  执行语句]
END IF

其中，search_condition 参数表示条件判断语句，如果返回值为 TRUE ，相应的 SQL 语句列表（statement_list）被执行；如果返回值为 FALSE，则 ELSE 子句的语句列表被执行。statement_list 可以包括一个或多个语句。

注意：MySQL 中的 IF( ) 函数不同于这里的 IF 语句。

🐱‍🏍剑来

set @username = '张三风';
delimiter $$
drop procedure if exists if01$$

create procedure if01()
begin

    IF @username = '张三' THEN
        select concat('我是',@username);
    ELSEIF  @username = '张三风' THEN
        select '张三是我徒弟';

    ELSE
        select '我是栗四,不认识张三';
    END IF;
end $$

delimiter ;xx
call if01;

该例子主要演示的是根据 username的值显示不一样的结果。结果很容易理解。

2. 判断（CASE语句）

CASE 语句也是用来进行条件判断的，它提供了多个条件进行选择，可以实现比 IF 语句更复杂的条件判断。CASE 语句的基本形式如下：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE
------------------------------------------------------------------
CASE  参数变量
    WHEN  参数变量1  THEN  执行语句]
    [WHEN  参数变量2  THEN  执行语句]
    [ELSE  执行语句]
END  CASE

其中：

case_value 参数表示条件判断的变量，决定了哪一个 WHEN 子句会被执行；
when_value 参数表示变量的取值，如果某个 when_value 表达式与 case_value 变量的值相同，则执行对应的 THEN 关键字后的 statement_list 中的语句；
statement_list 参数表示 when_value 值没有与 case_value 相同值时的执行语句。
CASE 语句都要使用 END CASE 结束。
这个就有点类似与 Java 中的 switch 语句的使用。 与之区别就是执行时，只要进入其中的一个判断语句之后，就不会在执行其他的，也不需要使用过break语句进行跳出。

3. 循环（loop）

语法：

[begin_label:] LOOP 
    statement_list ;
END LOOP [begin_label];
------------------------------------------------------------------
循环名: loop
	-- 循环体
	执行语句;
end loop 循环名;

反复执行循环体中的语句，直到循环结束。如果没有leave等关键字，此时的loop为死循环。

🐱‍🏍剑来

-- -----------关于loop的示例-------------------
-- 需求： 打印1-10，输出结果为 1，2，3，4......9,10
-- 如果存在loop_test，则先删除
drop procedure if exists loop_test;
delimiter $$
-- 创建存储过程loop_test
create procedure loop_test()
begin
    declare c_index int default 1;
    declare result_str varchar(256) default '1';
    cnt: loop
        -- 循环10次，则跳出循环
        if c_index >= 10 then
            leave cnt;
        end if;
  	  -- 自增
        set c_index = c_index + 1;
        -- 拼接
        select concat(result_str, ',', c_index) into result_str;
    end loop cnt;
    -- 查询结果
    select result_str;
end $$

delimiter ;
call loop_test();

leave 就相当于Java中的break语句，用于跳出当前循环

上述例子，类比Java代码如下：

    public void loop_test() {
        String result_str = "1";
        int c_index = 1;
        while (true) {
            if (c_index >= 10) {
                break;
            }
            c_index = c_index + 1;
            result_str = result_str + "," + c_index;
        }
        System.out.println(result_str);
    }