MySQL：存储过程

🕗 发布于 2024-03-25 07:19 mysql 数据库

1. 概念

MySQL中的存储过程指的是存储在数据库中的SQL语句集合。当创建好存储过程后，在运行时提供所需参数，存储过程就可以以代码指定的方式使用参数执行并返回值。

存储过程的特点包括：

封装与复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可，减少了网络交互，提升了通信速率。
接收参数与返回数据：在存储过程中，可以传递参数，也可以接收返回值。
编译与执行效率：存储过程只在创建时进行编译，以后每次执行存储过程都不需要在重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

2. 基本语法

2.1 创建

-- 创建存储过程
create procedure 存储过程名 (参数没有就不传)
begin
    SQL语句
end;

-- 调用存储过程
call 存储过程名（传的参数，没有就不传）;

栗子

-- 创建存储过程
create procedure p1 ()
begin
    select * from emp;
    select count(*) from emp;
end;

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

2.2 查看

-- 查看存储过程
-- select 字段名 from information_schema.ROUTINES where ROUTINE_SCHENA = 存储过程名
    -- 查询指定数据库存的储过程及状态信息
-- show create procedure 存储过程名
    -- 查询存储过程的定义

栗子

select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'study';
show create procedure p1;

2.3 删除

-- 删除存储过程
-- drop procedure (if exists) 存储过程名; 括号内容可省略

drop procedure if exists p1;

2.4 命令行注意事项：

-- 注意：在命令行中执行存储过程需修改SQL语句结束符号，修改结束符用关键字delimiter
-- delimiter 符号

delimiter $$

3. 变量

概念

系统变量：系统变量是MySQL服务器定义的，用户只能修改其值，不能创建新的系统变量。系统变量分为全局变量和会话变量。全局变量对所有会话都有效，修改全局变量会影响所有新会话（但已存在的会话不受影响），直到MySQL服务器重启。会话变量则是在当前会话级别有效，其变更只会影响到当前会话。需要注意的是，对系统变量的修改在MySQL服务器重启后会恢复为默认值，要想永久修改，需要更改配置文件。
用户变量：用户变量是用户根据需要自己定义的变量，用户变量不用提前声明，在使用的时候直接用“@变量名”就可以。其作用域为当前连接。
局部变量：局部变量是根据需要在局部生效的变量，访问之前，需要DECLARE声明。在存储过程和函数中都可以定义和使用局部变量。

3.1 系统变量

-- 系统变量
global全局系统变量 ：所有会话生效
session当前会话变量：当前会话生效

-- 查看系统变量（括号二选一）
-- show (global/session) variables;  查看所有的系统变量

show session variables;
show global variables;

-- show (global/session) variables like ''; 模糊匹配系统变量

show global variables like 'auto%';

-- select @@系统变量名 查看指定系统变量

select @@autocommit;

-- 设置系统变量（不加global或者session会默认修改当前会话的变量）
    -- 当服务器重启会自动回复到初始值，若要永久修改可在配置文件 /etc/my.cnf 中修改
-- set (session/global) 系统变量名 = '值'; 设置指定系统变量的值

set @@autocommit = 1;

3.2 用户变量

-- 用户变量
        -- 用户变量不需要提取声明或者初始化，未初始化的用户变量默认赋值null；
-- 设置用户变量
-- set @用户变量名1 = 值， @用户变量名2 = 值， ...;
-- set @用户变量名1 := 值, @用户变量名2 := 值, ...;
    -- SQL中= 和 := 都能赋值

select @niubi;
set @niubi := 666;

-- select @用户变量名1 := 值, @用户变量名2 := 值, ...;
select @niuhai := 999;
select @niuhai;

-- select count(*) into @用户变量名 from 表名;
    -- 将查询表统计返回的结果赋值给用户变量
select count(*) into @赋值结果 from emp;
select @赋值结果;

3.3 局部变量

    -- 作用域只要begin和end之间生效，需要declare提前声明
    -- declare 变量名 变量类型 default 默认值
    -- 赋值 变量名 = 值

create procedure p1()
begin
    declare emp_count int default 0;
    select count(*) into emp_count from emp;
    select emp_count;
end;

call p1;

4. 流程控制语句

两类语句

条件控制语句：if、case语句

循环控制语句：

4.1 if

-- if判断条件的使用
    -- if 条件1 then
        -- 满足条件1执行的语句
    -- elseif 条件2 then
        -- 满足条件2执行的语句
    -- else
        -- 不满足以上所有条件执行的语句
    -- end if;

参数

-- 创建过程
-- 传递变量进行判断
create procedure p3(in source int)
begin
    -- 声明局部变量
    declare result varchar(10);

    if source >= 80 then
        set result = '优秀';
    elseif source >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;

    -- 查询结果
    select result;
end;

-- 调用过程
call p3(99);

练习

-- 练习2：将传入的200分制的分数换算成100分制并返回
create procedure p5(inout score double)
    -- inout修饰的变量可以输入和输出参数
begin
    set score := score * 0.5;
end;

-- 调用
set @score = 91;
call p5(@score);
select @score;

4.2 case

- 使用规则
-- case开始
 case
     when 条件1 then 满足条件1执行的语句；
     when 条件2 then 满足条件2执行的语句；
     when 条件3 then 满足条件3执行的语句；
     else 不满足以上条件执行的语句；
-- case结束
end case;

-- 输入季度，并进行判断
create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when 1 <= month and month <= 3 then set result = '第一季度';
        when 4 <= month and month <= 6 then set result = '第二季度';
        when 7 <= month and month <= 9 then set result = '第三季度';
        when 10 <= month and month <= 12 then set result = '第四季度';
        else set result = '非法参数';
        end case;

    select concat('您输入的月份为：', month, '，属于', result);
end;

call p6(19);

4.3 while

-- 使用规则
while 条件 do
         满足条件执行的SQL语句
         不满足条件退出循环
end while；

-- 练习：累加n的值
create procedure p7(in n int)
begin
    declare sum int default 0;
    while n > 0
        do
            set sum = sum + n;
            set n = n - 1;
        end while;
    select sum;
end;

-- 调用
call p7(10);

4.4 repeat

-- 使用规则
repeat
    要执行的sql语句
    until 满足条件就退出循环
    end repeat;
end repeat;

-- 练习：计算前n个数的值
create procedure p8(in n int)
begin
    declare sum int default 0;

    repeat
        set sum = sum + n;
        set n = n - 1;
    until n <= 0
        end repeat;

    select sum;
end;

-- 调用
call p8(10);

4.5 loop

-- 使用规则
-- leaver 跳出循环
-- iterate 中断本次循环，进行下一次循环

create procedure p10(in n int)
begin
    -- 定义变量统计总和
    declare sum int default 0;
    -- 循环
    fun: loop
        -- 满足以下条件跳出循环
        if n <= 0 then
            leave fun;
        end if;
        -- 满足以下条件不进行累加
        if n % 2 = 0 then
            set n = n - 1;
            iterate fun;
        end if;
        -- 赋值
        set sum = sum + n;
        set n = n - 1;
    end loop fun;
    -- 查看最终结果
    select sum;
end;

call p10(5);

5. 游标（cursor）

-- 用来存储查询结果集的数据类型，在存储过程和函数中可以用游标对结果集进行数据处理
-- 使用规则：声明，open、fetch、close
-- 声明游标 declare 游标名 cursor for 查询语句（查询到的内容将会存储到游标内）;
-- open 游标名;
-- fetch 游标名 into 变量1，变量2，...;（游标内的每一条记录都会存储到对应的变量，变量接收的类型要和游标记录的数据类型保持一致）
-- close 游标名;

-- 条件处理程序（handler）：处理程序出现问题时该如何执行（如当游标内的记录为空时如何退出循环）
-- 使用规则：declare handler_action handler for condition_value, condition_value,... statement;
-- 声明一个处理异常程序的语句即：遇到上面的状态码（如：02000）时进行什么程序操作（如：exit），中止程序后该游标什么状态（如：关闭游标）

-- handler_action
-- continue：继续执行当前程序;
-- exit：退出程序;

-- condition_value
-- sqlstate sqlstate_value：状态码（如：02000）
-- sqlwarning：所有以01开头的状态码的简写
-- not found：所有以02开头的状态码的简写
-- sqlexception：所有没有被sqlwarning、not found捕抓到的状态码的简写

create procedure p11(in source varchar(10))
begin
    -- 声明游标内数据的类型
    declare uname varchar(10) default null;
    declare uid int;
    declare uage int;
    declare udept_id int;
    declare uaddress varchar(10);

    -- 声明游标
    declare emp_cursor cursor for select * from emp where address = source;
    -- 声明条件处理程序，当游标满足什么条件自动退出程序（当游标记录的数据为空时退出程序，并关闭游标）
    declare exit handler for not found close emp_cursor;
    -- 或者使用状态码退出
    -- declare exit handler for SQ
    -- declare exit handler for sqlstate '02000' close emp_cursor;

    -- 创建新表存储游表的记录
    drop table if exists tb_emp;
    create table tb_emp(
        id int primary key auto_increment,
        name varchar(10) not null,
        address varchar(10)
    );
    -- 打开游标
    open emp_cursor;
    -- 循环遍历存储游标内的记录
    while true do
        -- 获取游标内的数据存储到定义的临时变量中
        fetch emp_cursor into uid, uname, uage, udept_id,  uaddress;
        -- 将数据存入新表
        insert into tb_emp value (null, uname, uaddress);
        end while;
    -- 关闭游标
    close emp_cursor;
end;

-- 调用存储过程
call p11('广州');

6. 存储函数

-- 使用规则：函数必须要有返回值
create function 函数名称(参数列表)
returns type（函数返回值类型） character（函数特征）
begin
    函数内部要执行的语句
    return 返回值
end;

-- 使用规则：函数必须要有返回值
create function 函数名称(参数列表)
returns type（函数返回值类型） character（函数特征）
begin
    函数内部要执行的语句
    return 返回值
end;

7. 触发器

触发器的主要特点和功能包括：

自动化业务逻辑和操作：触发器能够自动执行存储过程，响应特定事件，如insert、update和delete语句，从而实现约束、默认值或处理业务逻辑的功能。
响应特定事件：每个触发器都有一个触发事件和响应事件。触发事件通常是数据表上的INSERT、UPDATE或DELETE语句，而响应事件则是在触发事件后MySQL服务器执行的操作。
处理多种操作：触发器可以处理多种操作，比如设置默认值、展示错误信息、实现复杂的业务逻辑等。

需要注意的是，在MySQL中，只有执行INSERT、UPDATE和DELETE操作时才能激活触发器，其他SQL语句则不会激活触发器。

通过创建和使用触发器，开发人员可以更有效地管理数据库中的数据，确保数据的完整性和一致性，同时减少手动干预的需要，提高应用程序的自动化程度。

创建触发器语法

create trigger 触发器名称

after/before insert on 触发器关联的表名 for each row

begin

insert into 触发器的记录插入的哪张表的名称 (插入内容);

end;

-- 创建触发器
-- 插入数据触发器
-- 创建触发器（trigger） 触发器名字
create trigger tb_emp_insert_trigger
    -- 触发器在emp表操作之后触发
    after insert on emp for each row
-- 触发器内部结构
begin
    insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
                         (null, 'insert', now(), new.id,
                          concat('插入数据内容为：id=',new.id,'name=',new.name,'age=',new.age,'address=',new.address )
                          );
end;

create trigger 触发器名称

after/before update on 触发器关联的表名 for each row

begin

insert into 触发器的记录插入的哪张表的名称 (插入内容);

end;

-- 创建修改数据触发器
create trigger tb_emp_update_trigger
    -- 触发器在emp表操作之后触发
    after update on emp for each row
-- 触发器内部结构
begin
    insert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values
        (null,'update',now(),new.id,
        concat('更新之前数据：id=', old.id, 'name=', old.name, 'age=', old.age, 'address=', old.address,
               '更新之后数据：id=', new.id, 'name=', new.name, 'age=', new.age, 'address=', new.address                                                                         )
        );
end;

删除同理

原文地址：https://blog.csdn.net/2301_79526467/article/details/136802763

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：【Nginx】反向代理解决跨域问题
下一篇：2024最新华为OD机试试题库全 -【局域网中的服务器个数】- C卷

C#绑定窗口句柄，获取后台窗口的图片的实现与分析
通过使用C#结合Windows API，我们可以实现对后台窗口的图像获取。尽管过程中涉及许多底层操作，但通过合理使用这些API，能够有效地获取我们需要的数据。希望本文的代码示例和解释对实现此功能有所帮
阅读更多2024-11-08
1. pytorch 中冻结模型参数后参数仍会被调整
起初设置 requires_grad(False) 优化器也没有添加sgfnet的模型参数。但是在pylightning框架中，每次推理完模型的参数都会被改变，经过仔细排查发现问题。
阅读更多2024-11-08
Maven
项目构建是指将源代码、配置文件、资源文件等转化为能够运行或部署的应用程序或库的过程, Maven可以管理项目的编译、测试、打包、部署等构建过程。那么可以将需要的依赖项的信息编写到Maven工程的配置
阅读更多2024-11-08
【K8S问题系列】Kubernetes Pod节点CrashLoopBackOff 状态【已解决】
在 Kubernetes 中，Pod 的状态为 **`CrashLoopBackOff`** 表示某个容器在启动后崩溃，Kubernetes 尝试重启该容器，但由于持续崩溃，重启的间隔时间逐渐增加。
阅读更多2024-11-08
Flutter 中的那些设计模式的写法（持续更新）
我们都知道设计模式是相同的，同一种设计模式的理念不会因为语言不同而会有所改变，但是由于语法的差异，设计模式的写法也有所差异，本文会介绍一些flutter中常用设计模式的写法以及使用场景。创建型模式单例
阅读更多2024-11-08
vue.js组件和传值以及微信小程序组件和传值
记录微信小程序组件与uniapp组件的引用以及传值的问题
阅读更多2024-11-08
React 中 `key` 属性的警告及其解决方案
React 中 `key` 属性的警告及其解决方案
阅读更多2024-11-08
icloud域名邮箱优势分析：烽火邮箱怎么样？
icloud域名邮箱凭借其专业的品牌形象、高级别的安全性、无缝的设备同步和强大的功能。烽火邮箱，媲美iCloud域名邮箱，企业邮箱专业稳定，支持自定义域名，短期邮箱也灵活，打造高效商务沟通！
阅读更多2024-11-08
React 源码学习01 ---- React.Children.map 的实现与应用
目前是第一次看这个函数的实现，知道他大概干了什么，也大概明白每一步要干什么，但是目前还不清除他为什么要这么干。为什么要来看他的实现呢？一个是只有知道他是怎么实现的，才能知道在调用这个方法时，那些情况会
阅读更多2024-11-08
Go-性能调优实战案例
AB实验SDK优化分析基础库的核心逻辑和性能瓶颈压测验证落地验证。单独的benchmark无法满足复杂逻辑分析压测（单机器集群）并且问题还有：cpu占用80%和40%的数据是不一样的都采样
阅读更多2024-11-08

MySQL：存储过程

1. 概念

2. 基本语法

2.1 创建

2.2 查看

2.3 删除

2.4 命令行注意事项：

3. 变量

3.1 系统变量

3.2 用户变量

3.3 局部变量

4. 流程控制语句

4.1 if

参数

4.2 case

4.3 while

4.4 repeat

4.5 loop

5. 游标（cursor）

6. 存储函数

7. 触发器

创建触发器语法

相关文章