mysql 存储过程和自定义函数 详解

首先创建存储过程或者自定义函数时，都要使用use database 切换到目标数据库，因为存储过程和自定义函数都是属于某个数据库的。

存储过程是一种预编译的 SQL 代码集合，封装在数据库对象中。以下是一些常见的存储过程的关键字：

存储过程

1. 存储过程的定义

  • CREATE PROCEDURE: 用于创建一个新的存储过程。

    CREATE PROCEDURE procedure_name (parameter_list)
BEGIN-- 存储过程体
END;

  • DROP PROCEDURE: 用于删除已存在的存储过程。

    DROP PROCEDURE procedure_name;

2. 参数定义

  • IN: 表示输入参数。

  • OUT: 表示输出参数。

  • INOUT: 表示既可以输入又可以输出的参数。

    CREATE PROCEDURE my_procedure(IN param1 INT, OUT param2 INT)
BEGIN-- 存储过程体
END;

3. 变量声明与操作

  • DECLARE: 定义局部变量、条件和游标。

    DECLARE var1 INT DEFAULT 0;
DECLARE var2 VARCHAR(50);

  • SET: 用于给变量赋值。

    SET var1 = 10;

4. 控制流程

  • BEGIN/END: 标记存储过程块的开始和结束。

    BEGIN-- 存储过程体
END;

  • IF 条件判断

    IF 语句用于简单的条件分支。

    语法格式：
    IF condition THENSQL逻辑
ELSEIF condition THENSQL逻辑
ELSESQL逻辑
END IF;
    案例：

    根据用户 ID 返回不同的信息。

    DELIMITER //
CREATE PROCEDURE CheckUser(IN userId VARCHAR(32))
BEGINDECLARE userName VARCHAR(32);IF userId = 'APP-2016-00494878' THENSELECT username INTO userName FROM users WHERE userid = userId;SELECT userName;ELSEIF userId = 'APP-2016-7777777' THENSELECT userage INTO userName FROM users WHERE userid = userId;SELECT userName;ELSESELECT userId;END IF;
END //
DELIMITER ;

    调用存储过程：

    CALL CheckUser('APP-2016-00494878');

  • CASE: 用于多条件判断。

  • 语法格式：
    CASEWHEN condition1 THENSQL逻辑WHEN condition2 THENSQL逻辑ELSESQL逻辑
END CASE;

  • 根据用户角色返回不同的权限级别。

  • DELIMITER //
CREATE PROCEDURE GetUserRole(IN userId INT)
BEGINDECLARE userRole VARCHAR(20);SELECT role INTO userRole FROM users WHERE id = userId;CASE userRoleWHEN 'admin' THENSELECT 'Administrator';WHEN 'user' THENSELECT 'Standard User';ELSESELECT 'Guest';END CASE;
END //
DELIMITER ;

    调用存储过程：

    CALL GetUserRole(1);

  • LOOP/WHILE/REPEAT: 实现循环操作。

    LOOP_LABEL: LOOP-- 循环体IF condition THENLEAVE LOOP_LABEL;END IF;
END LOOP;

 WHILE 循环

WHILE 循环在条件为真时执行循环体中的 SQL 语句。

语法格式：
WHILE 条件 DOSQL逻辑
END WHILE;
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSum(IN n INT)
BEGINDECLARE total INT DEFAULT 0;WHILE n > 0 DOSET total = total + n;SET n = n - 1;END WHILE;SELECT total;
END //
DELIMITER ;

调用存储过程：

CALL CalculateSum(100);

 

REPEAT 循环

REPEAT 循环至少执行一次循环体中的 SQL 语句，直到条件为真时退出循环。

语法格式：
REPEATSQL逻辑
UNTIL 条件
END REPEAT;
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSumRepeat(IN n INT)
BEGINDECLARE total INT DEFAULT 0;REPEATSET total = total + n;SET n = n - 1;UNTIL n <= 0END REPEAT;SELECT total;
END //
DELIMITER ;

调用存储过程：

CALL CalculateSumRepeat(100);

LOOP 循环

LOOP 循环是无条件循环，通常与 LEAVE 语句结合使用来退出循环。

语法格式：
[loop_label:] LOOPSQL逻辑
END LOOP [loop_label];
案例：

计算从 1 累加到 n 的值，n 为传入的参数值。

DELIMITER //
CREATE PROCEDURE CalculateSumLoop(IN n INT)
BEGINDECLARE total INT DEFAULT 0;sum_loop: LOOPIF n <= 0 THENLEAVE sum_loop;END IF;SET total = total + n;SET n = n - 1;END LOOP sum_loop;SELECT total;
END //
DELIMITER ;

调用存储过程：

CALL CalculateSumLoop(100);

 

5. 条件处理

  • DECLARE HANDLER: 定义异常处理程序。

    DECLARE CONTINUE HANDLER FOR SQL_ERROR_CODE error_code
BEGIN-- 异常处理语句
END;

6. 游标操作

  • DECLARE CURSOR: 声明游标。

    DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table WHERE condition;

  • OPEN CURSOR: 打开游标。

    OPEN cursor_name;

  • FETCH CURSOR: 读取游标数据。

    FETCH cursor_name INTO var1, var2;

  • CLOSE CURSOR: 关闭游标。

    CLOSE cursor_name;

7. 调试与优化

  • SET: 调整优化器参数。

    SET optimizer_switch = 'index_merge=on';

  • SHOW VARIABLES: 查看系统变量。

    SHOW VARIABLES LIKE 'optimizer_switch';

8.案例 

以下是一个包含事务处理的存储过程示例：

DELIMITER //CREATE PROCEDURE transfer_funds(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGINSTART TRANSACTION;IF amount > 0 THEN-- 从源账户扣除金额UPDATE accounts SET balance = balance - amount WHERE id = from_account;-- 向目标账户增加金额UPDATE accounts SET balance = balance + amount WHERE id = to_account;-- 提交事务COMMIT;ELSE-- 回滚事务ROLLBACK;END IF;
END //DELIMITER ;

这个存储过程通过事务确保资金转账操作的原子性。

9.补充知识：游标

注意事项

  • 游标只能在存储过程和函数中使用。

  • 游标是只读的，不能用于修改数据，但可以通过 SELECT 查询结果集。

  • 需要显式地打开和关闭游标，以确保资源的正确释放。

通过使用游标，可以对查询结果集进行逐行处理，这对于需要对每行数据执行复杂操作的场景非常有用

DELIMITER //
CREATE PROCEDURE ProcessOrders()
BEGINDECLARE done INT DEFAULT 0;DECLARE orderId INT;DECLARE orderStatus VARCHAR(20);-- 声明一个名为 cur 的游标。-- 该游标基于查询 SELECT id, status FROM orders，用于存储查询结果集。-- 可以通过游标逐行访问和操作 orders 表中的每一行数据。DECLARE cur CURSOR FOR SELECT id, status FROM orders;-- 定义一个异常处理程序，当游标遍历完所有行（触发 NOT FOUND 异常）时，将变量 done 设置为 1。DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN cur; -- 打开游标order_loop: LOOPFETCH cur INTO orderId, orderStatus; -- 从游标中提取数据IF done THENLEAVE order_loop; -- 如果没有更多数据，退出循环END IF;-- 在这里处理每一行数据UPDATE orders SET status = 'processed' WHERE id = orderId;END LOOP order_loop;CLOSE cur; -- 关闭游标
END //
DELIMITER ;

在这个存储过程中，当游标遍历完所有行时，FETCH cur INTO orderId, orderStatus 会触发 NOT FOUND 异常。此时，CONTINUE HANDLER 将 done 设置为 1，退出循环。

通过这种方式，可以优雅地处理游标遍历完成的情况，确保程序不会因异常而中断。

10.查询当前数据库有哪些存储过程 

SELECT SPECIFIC_NAME AS '存储过程名', ROUTINE_SCHEMA AS '数据库名', CREATED AS '创建时间', LAST_ALTERED AS '最后修改时间'
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'AND ROUTINE_SCHEMA = 'test_mybatis';

自定义函数

1. 定义自定义函数

  • CREATE FUNCTION: 用于创建一个新的自定义函数。

    CREATE FUNCTION function_name (parameter_list)
RETURNS return_type
BEGIN-- 函数体
END;

    • function_name: 函数名称。

    • parameter_list: 参数列表，参数可以是 IN 类型。

    • RETURNS: 指定函数返回值的类型。

2. 参数定义

  • IN: 定义输入参数。MySQL 的自定义函数只支持 IN 类型的参数。

    CREATE FUNCTION add_numbers(IN a INT, IN b INT)
RETURNS INT
BEGINRETURN a + b;
END;

3. 函数体

  • BEGIN/END: 标记函数体的开始和结束。

    BEGIN-- 函数逻辑
END;

4. 返回值

  • RETURN: 用于返回函数的计算结果。

    RETURN value;

5. 属性

  • DETERMINISTIC 或 NOT DETERMINISTIC: 标记函数是否是确定性的。

    • 确定性函数：对于相同的输入参数总是返回相同的结果。

    • 非确定性函数：对于相同的输入参数可能返回不同的结果。

    CREATE FUNCTION function_name (parameter_list)
RETURNS return_type DETERMINISTIC
BEGIN-- 函数逻辑
END;

6. 修改或删除函数

  • ALTER FUNCTION: 修改已存在的自定义函数。

  • DROP FUNCTION: 删除已存在的自定义函数。

    DROP FUNCTION function_name;

7. 调用函数

  • 可以直接在 SQL 语句中调用自定义函数。

    SELECT add_numbers(5, 10);

存储过程和自定义函数区别 

特性存储过程自定义函数
定义是一组预编译的 SQL 语句和流程控制语句的集合，可以包含多个 SQL 语句和复杂逻辑。是一个用户定义的函数，用于封装特定的功能逻辑，返回一个值。
返回值可以没有返回值，也可以通过 OUT 参数返回多个值。必须返回一个值，返回值类型在创建函数时指定。
参数类型支持 IN、OUT 和 INOUT 类型的参数。只支持 IN 类型的参数。
调用方式使用 CALL 语句调用，不能在 SELECT 中直接调用。可以在 SQL 语句中直接调用，如 SELECT、WHERE、ORDER BY 等。
事务处理可以包含事务操作，控制事务的提交和回滚。不支持事务操作。
代码重用适合封装复杂的业务逻辑，便于维护和代码重用。适合封装简单的逻辑，如计算、转换或条件判断，便于在多个地方重用。
性能存储过程在服务器端执行，与应用程序交互次数少，性能较高。自定义函数在查询中调用时，可能会导致性能问题，需要谨慎使用。
适用场景适用于执行复杂操作的场景，如批量更新数据、执行多个 SQL 语句或返回结果集。适用于需要返回单个值的场景，如计算、转换或简单的逻辑判断。

总结

  • 存储过程：适合执行复杂的操作，可以返回多个值或结果集，适用于批量更新数据、执行多个 SQL 语句或返回结果集等场景。

  • 自定义函数：适合返回单个值的简单逻辑，可以直接在 SQL 语句中调用，适用于计算、转换或条件判断等场景。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/15164.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

ZU47DR 100G光纤 高性能板卡

ZU47DR 100G光纤 高性能板卡

简介 2347DR是一款最大可提供8路ADC接收和8路DAC发射通道的高性能板卡。板卡选用高性价比的Xilinx的Zynq UltraScale RFSoC系列中XCZU47DR-FFVE1156作为处理芯片&#xff08;管脚可以兼容XCZU48DR-FFVE1156&#xff0c;主要差别在有无FEC&#xff08;信道纠错编解码&#xff0…
阅读更多...
详解SQLAlchemy的函数relationship

详解SQLAlchemy的函数relationship

在 SQLAlchemy 中&#xff0c;relationship 是一个非常重要的函数&#xff0c;用于定义模型之间的关系。它用于在 ORM 层面上表示数据库表之间的关联关系&#xff08;如 1 对 1、1 对多和多对多&#xff09;。relationship 的主要作用是提供一个高级接口&#xff0c;用于在模型…
阅读更多...
【Matlab优化算法-第14期】基于智能优化算法的VMD信号去噪项目实践

【Matlab优化算法-第14期】基于智能优化算法的VMD信号去噪项目实践

基于智能优化算法的VMD信号去噪项目实践 一、前言 在信号处理领域&#xff0c;噪声去除是一个关键问题&#xff0c;尤其是在处理含有高斯白噪声的复杂信号时。变分模态分解&#xff08;VMD&#xff09;作为一种新兴的信号分解方法&#xff0c;因其能够自适应地分解信号而受到…
阅读更多...
C++ 继承（1）

C++ 继承（1）

1.继承概念 我们平时有时候在写多个有内容重复的类的时候会很麻烦 比如我要写Student Teacher Staff 这三个类 里面都要包含 sex name age成员变量 唯一不同的可能有一个成员变量 但是这三个成员变量我要写三遍 太麻烦了 有没有好的方式呢&#xff1f; 有的 就是继承…
阅读更多...
生成式聊天机器人 -- 基于Pytorch + Global Attention + 双向 GRU 实现的SeqToSeq模型 -- 下

生成式聊天机器人 -- 基于Pytorch + Global Attention + 双向 GRU 实现的SeqToSeq模型 -- 下

生成式聊天机器人 -- 基于Pytorch Global Attention 双向 GRU 实现的SeqToSeq模型 -- 下 训练Masked 损失单次训练过程迭代训练过程 测试贪心解码(Greedy decoding)算法实现对话函数 训练和测试模型完整代码 生成式聊天机器人 – 基于Pytorch Global Attention 双向 GRU 实…
阅读更多...
《ARM64体系结构编程与实践》学习笔记（四）

《ARM64体系结构编程与实践》学习笔记（四）

MMU内存管理 1.MMU内存管理&#xff08;armv8.6手册的D5章节&#xff09;&#xff0c;MMU包含快表TLB&#xff0c;TLB是对页表的部分缓存&#xff0c;页表是存放在内存里面的。 AArch64仅仅支持Long Descriptor的页表格式&#xff0c;AArch32支持两种页表格式Armv7-A Short De…
阅读更多...
如何在Vscode中接入Deepseek

如何在Vscode中接入Deepseek

一、获取Deepseek APIKEY 首先&#xff0c;登录Deepseek官网的开放平台&#xff1a;DeepSeek 选择API开放平台&#xff0c;然后登录Deepseek后台。 点击左侧菜单栏“API keys”&#xff0c;并创建API key。 需要注意的是&#xff0c;生成API key复制保存到本地&#xff0c;丢失…
阅读更多...
Docker 部署 MinIO | 国内阿里镜像

Docker 部署 MinIO | 国内阿里镜像

一、导读 Minio 是个基于 Golang 编写的开源对象存储套件&#xff0c;基于Apache License v2.0开源协议&#xff0c;虽然轻量&#xff0c;却拥有着不错的性能。它兼容亚马逊S3云存储服务接口。可以很简单的和其他应用结合使用&#xff0c;例如 NodeJS、Redis、MySQL等。 二、…
阅读更多...
DeepSeek-R1 32B Windows+docker本地部署

DeepSeek-R1 32B Windows+docker本地部署

最近国产大模型DeepSeek兴起&#xff0c;本地部署了一套deepseek同时集成Open WebUI界面,给大家出一期教程。 软件&#xff1a;Ollama、docker、Open WebUI 一、用Ollama下载模型 首先我们需要安装Ollama&#xff0c;它可以在本地运行和管理大模型。 到Ollama官网 https://ol…
阅读更多...
TCP服务器与客户端搭建

TCP服务器与客户端搭建

一、思维导图 二、给代码添加链表 【server.c】 #include <stdio.h> #include <sys/socket.h> #include <sys/types.h> #include <fcntl.h> #include <arpa/inet.h> #include <unistd.h> #include <stdlib.h> #include <string.…
阅读更多...
python爬虫--简单登录

python爬虫--简单登录

1&#xff0c;使用flask框架搭建一个简易网站 后端代码app.py from flask import Flask, render_template, request, redirect, url_for, sessionapp Flask(__name__) app.secret_key 123456789 # 用于加密会话数据# 模拟用户数据库 users {user1: {password: password1}…
阅读更多...
ESXi Host Client创建ubuntu虚拟机教程及NVIDIA显卡驱动安装

ESXi Host Client创建ubuntu虚拟机教程及NVIDIA显卡驱动安装

参考文章 VMware虚拟机显卡直通记录 AIGC 实战&#xff08;环境篇&#xff09; - EXSI 8.0 Debian安装RTX3060显卡驱动 重点介绍 client版本是7.0.3 注意&#xff1a;下图中不要选择BIOS 按照两个链接中的方法进行操作&#xff0c;以及本章节的上面几个图片的配置之后&a…
阅读更多...
Maven入门核心知识点总结

Maven入门核心知识点总结

Maven 1. POM&#xff08;Project Object Model&#xff09;2. 坐标&#xff08;Coordinates&#xff09;3. 依赖管理&#xff08;Dependency Management&#xff09;4. 常用五个生命周期&#xff08;Life Circle&#xff09;5. Maven 仓库&#xff08;Maven Repository&#x…
阅读更多...
测试中的第一性原理：回归本质的质量思维革命

测试中的第一性原理：回归本质的质量思维革命

在软件工程领域&#xff0c;测试活动常被惯性思维和经验主义所主导——测试用例库无限膨胀、自动化脚本维护成本居高不下、测试策略与业务目标渐行渐远。要突破这种困境&#xff0c;第一性原理&#xff08;First Principles Thinking&#xff09;提供了独特的解题视角&#xff…
阅读更多...
Rust语言进阶之标准输入: stdin用法实例(一百零五)

Rust语言进阶之标准输入: stdin用法实例(一百零五)

简介&#xff1a; CSDN博客专家、《Android系统多媒体进阶实战》一书作者 新书发布&#xff1a;《Android系统多媒体进阶实战》&#x1f680; 优质专栏&#xff1a; Audio工程师进阶系列【原创干货持续更新中……】&#x1f680; 优质专栏&#xff1a; 多媒体系统工程师系列【…
阅读更多...
SpringBoot速成（七）注册实战P2-P4

SpringBoot速成（七）注册实战P2-P4

1.创建 数据库创建 依赖引入 <!-- mybatis起步依赖--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>3.0.3</version></dependency> <…
阅读更多...
Spring Boot接入Deep Seek的API

Spring Boot接入Deep Seek的API

1&#xff0c;首先进入deepseek的官网&#xff1a;DeepSeek | 深度求索&#xff0c;单击右上角的API开放平台。 2&#xff0c;单击API keys&#xff0c;创建一个API&#xff0c;创建完成务必复制&#xff01;&#xff01;不然关掉之后会看不看api key&#xff01;&#xff01;&…
阅读更多...
【C++学习篇】C++11第二期学习

【C++学习篇】C++11第二期学习

目录 1. 可变参数模板 1.1 基本语法及原理 1.2 包扩展 1.3empalce系列接⼝ 2. lamba 2.1 lambda的语法表达式 2.2 捕捉列表 2.3 lamba的原理 1. 可变参数模板 1.1 基本语法及原理 1. C11⽀持可变参数模板&#xff0c;也就是说⽀持可变数量参数的函数模板和类模板&…
阅读更多...
开放式TCP/IP通信

开放式TCP/IP通信

一、1200和1200之间的开放式TCP/IP通讯 第一步&#xff1a;组态1214CPU&#xff0c;勾选时钟存储器 第二步&#xff1a;防护与安全里面连接机制勾选允许PUT/GET访问 第三步&#xff1a;添加PLC 第四步&#xff1a;点击网络试图&#xff0c;选中网口&#xff0c;把两个PLC连接起…
阅读更多...
迁移学习 Transfer Learning

迁移学习 Transfer Learning

迁移学习&#xff08;Transfer Learning&#xff09;是什么&#xff1f; 迁移学习是一种机器学习方法&#xff0c;它的核心思想是利用已有模型的知识来帮助新的任务或数据集进行学习&#xff0c;从而减少训练数据的需求、加快训练速度&#xff0c;并提升模型性能。 &#x1f…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023