Mysql - 存储过程、函数、触发器

yueerba126

已于 2023-09-30 11:40:04 修改

阅读量252

点赞数 1

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

于 2023-09-27 10:09:08 首次发布

本文链接：https://blog.csdn.net/yueerba126/article/details/133318533

版权

Mysql 专栏收录该内容

20 篇文章 1 订阅

订阅专栏

👀数据库编程

📜 编写SQL脚本后，数据库会将其编译成二进制代码，以实现更快的执行速度。

数据库编程流程

💡1. 存储过程：

📜 存储过程是一组SQL语句的集合，可以被命名并存储在数据库中，以便在需要时进行调用。
🔄 存储过程可以包含业务逻辑、条件分支、循环等控制结构，并且可以接受参数和返回结果。
🔐 存储过程通常用于封装复杂的业务逻辑，提高数据库操作的可维护性和安全性。
📞 存储过程可以通过SQL语句进行调用，例如：EXECUTE PROCEDURE my_procedure(param1, param2);

💡2. 函数：

📜 函数是一种特殊的存储过程，主要用于执行查询并返回单一值或表达式的结果。
🔍 函数通常不会修改数据库的状态，而是用于计算和返回值。
🔄 常见的数据库函数包括SUM、COUNT、MAX、MIN，以及用于字符串操作和日期处理的函数。
📞 函数可以在SQL查询中直接使用，例如：SELECT my_function(param1, param2);

💡3. 触发器：

📜 触发器是一种特殊的数据库对象（存储过程），用于在数据库表上监视特定事件（例如插入、更新、删除）并自动触发相关操作。
🔐 触发器通常用于实施数据完整性约束、审计、日志记录等。
📞 触发器不需要手动调用，它们会在符合触发条件时自动执行。

💡4. 特点/组件对比：

特点/组件	函数	存储过程	触发器
定义	📜 一个特殊的存储过程	📜 一组预定义的 SQL 语句集	📜 特殊的存储过程，响应数据库事件自动执行
返回值	🔄 必须有一个	🚫 没有，但可以有输出参数	🚫 无
调用方式	📞 不能单独调用，通常在 SQL 语句中	📞 可以单独调用，使用 `CALL` 或特定语法	📞 不需要手动调用，根据数据库事件自动触发
主要功能	🔍 计算并返回值	🔄 执行一系列 SQL 操作	🔄 在特定数据库操作（如 INSERT、UPDATE、DELETE）前后自动执行
能否更改数据	🚫 通常不可以	✅ 可以	✅ 可以
输出	🔄 一个值或表	🔄 可以有多个输出参数	🚫 无
副作用	🚫 应该没有	🚫 可能有	🚫 可能有
使用场景	🛠 数据计算，转换，查询等	🛠 复杂的数据库操作，批处理	🛠 数据完整性检查，自动数据更新，日志记录等

💡5. 在 Java 中与数据库交互：

使用 JDBC（Java Database Connectivity）在 Java（或其他编程语言）中与数据库交互时，可以捕获并处理从数据库返回的输出。

从函数获得返回值：

CallableStatement stmt = conn.prepareCall("{? = CALL function_name(?)}");
stmt.registerOutParameter(1, Types.INTEGER); // 假设函数返回一个整数
stmt.setInt(2, inputParameter);
stmt.execute();
int result = stmt.getInt(1);

从存储过程获得输出参数：

CallableStatement stmt = conn.prepareCall("{CALL procedure_name(?, ?)}");
stmt.setInt(1, inputParameter);
stmt.registerOutParameter(2, Types.INTEGER); // 假设第二个参数是输出参数，类型为整数
stmt.execute();
int outputResult = stmt.getInt(2);

处理结果集：

ResultSet rs = stmt.executeQuery();
while (rs.next()) {
    // 处理结果集
}

👀数据库编程使用场景

以下是存储过程、函数和触发器在知名项目中的典型使用场景：

💡1. 存储过程：

存储过程主要用于封装复杂的业务逻辑，提高数据库操作的可维护性和安全性。

🎯使用场景：

电商网站（如 Amazon）订单处理：
- 当用户下订单时，需要执行一系列的数据库操作，如检查库存、更新订单状态、记录交易历史等。这些操作可以通过一个存储过程来完成，确保所有步骤按照预期的顺序执行，且在一个事务中完成。
银行系统（如花旗银行）的资金转账：
- 转账需要从一个账户扣款，然后再给另一个账户加款。这两个操作需要在一个事务中执行，以确保数据的完整性。存储过程可以确保这两个操作要么都成功，要么都失败。

💡2. 函数：

函数主要用于执行查询并返回单一值或表达式的结果。

🎯使用场景：

社交媒体平台（如 Facebook）的好友推荐：
- 函数可以根据用户的兴趣和活动计算并返回与用户最相关的好友推荐列表。
视频流平台（如 Netflix）的推荐算法：
- 函数可以基于用户的观看历史和偏好，计算并返回一系列推荐的电影或电视节目。

💡3. 触发器：

触发器主要用于监视数据库表上的特定事件，并自动触发相关操作。

🎯使用场景：

电商网站（如 eBay）的库存监控：
- 当某个商品的库存低于预定值时，触发器可以自动发送通知给供应商或管理员，提示他们补货。
社交网络平台（如 Twitter）的用户行为跟踪：
- 当用户发表新的推文或关注新的用户时，触发器可以自动更新用户的活动日志，并可能触发其他相关的操作，如发送通知给被关注的用户。

👀数据库编程存在的问题

💡集群

多个数据库节点协同工作

集群问题/工具	存储过程	存储函数	触发器	解决方案
🔄 复制延迟	✅ 数据更改在主节点后可能不会立即反映到从节点上	❌ 不存在此问题	❌ 不存在此问题	优化网络连接，使用更快的复制策略
🌐 跨节点操作	✅ 查询和操作可能需要涉及多个数据库节点，导致性能下降	✅ 函数调用可能需要跨多个节点，增加延迟	❌ 不存在此问题	限制跨节点操作，优化查询
📊 数据不一致	❌ 不存在此问题	✅ 由于复制延迟，不同节点上的数据可能不一致	✅ 触发器在一个节点上的更改可能不同步到其他节点	使用强一致性策略，确保数据同步
⏱ 性能问题	❌ 不存在此问题	✅ 跨节点的函数调用可能导致额外的延迟	❌ 不存在此问题	优化查询，减少不必要的数据读取
⚡ 触发不一致	❌ 不存在此问题	❌ 不存在此问题	✅ 触发器在不同节点可能存在不一致性	确保所有节点的触发器都是最新的

💡分库

数据分布在多个数据库实例中

分库问题/工具	存储过程	存储函数	触发器	解决方案
🌐 跨数据库操作	✅ 存储过程可能需要跨多个数据库执行，导致性能降低	❌ 不存在此问题	❌ 不存在此问题	尽量避免跨数据库操作，或使用中间件管理
🔄 代码同步	✅ 在多个数据库实例中同步和维护存储过程变得困难	❌ 不存在此问题	❌ 不存在此问题	使用版本控制，定期检查和同步代码
📊 数据不一致	❌ 不存在此问题	✅ 不同的数据库实例可能返回不一致的结果	✅ 触发器可能导致不同数据库实例数据不同步	使用数据校验工具，定期进行数据校验
⏱ 性能问题	❌ 不存在此问题	✅ 跨数据库的函数调用可能增加延迟	❌ 不存在此问题	优化查询，使用缓存
⚡ 触发不一致	❌ 不存在此问题	❌ 不存在此问题	✅ 在不同的数据库实例中，触发器的行为可能不同	确保所有数据库实例的触发器都是最新的

💡分表

单个数据库内的数据分布在多个表中

分表问题/工具	存储过程	存储函数	触发器	解决方案
🌐 复杂查询	✅ 数据被分散在多个表中，查询变得复杂	❌ 不存在此问题	❌ 不存在此问题	使用中间件或优化查询来管理分表
🧩 数据整合	✅ 重新组织或合并分散的表可能是一个耗时的过程	❌ 不存在此问题	❌ 不存在此问题	定期进行数据归档和清理
📊 数据来源	❌ 不存在此问题	✅ 函数可能需要从多个表中获取数据	❌ 不存在此问题	确保数据来源清晰并且文档齐全
⏱ 性能问题	❌ 不存在此问题	✅ 跨多个表的函数调用可能增加延迟	❌ 不存在此问题	优化查询，减少不必要的数据读取
⚡ 触发管理	❌ 不存在此问题	❌ 不存在此问题	✅ 管理在多个表上的触发器可能变得复杂	确保触发器逻辑简单且文档齐全

👀Oracle的优势

功能/特点	🌟 Oracle优势	📝 说明及补充
存储过程
🔥 1.1 复杂性和灵活性	Oracle的PL/SQL语言更为强大和复杂	1️⃣ Oracle的PL/SQL支持包、异常处理、记录类型、表类型等高级功能。 2️⃣ 而MySQL的SQL/PSM提供基本的存储过程支持，但不支持上述高级特性。
⚠️ 1.2 错误处理和调试	Oracle提供了更强大的错误处理和调试工具	1️⃣ Oracle的PL/SQL提供了强大的错误处理和异常处理功能。 2️⃣ MySQL也支持基本的错误处理，但不提供存储过程的调试功能。
🚀 1.3 性能优化	Oracle提供了更多的工具和技术来优化存储过程的性能	1️⃣ Oracle支持执行计划稳定性、PL/SQL编译器优化和本地编译。 2️⃣ MySQL主要依赖于其查询优化器进行性能优化，没有Oracle那样的高级编译优化特性。
存储函数
🛍️ 2.1 返回值的灵活性	Oracle的存储函数可以返回复杂的数据类型，如表或记录	1️⃣ Oracle的存储函数可以返回表或记录等复杂数据类型。 2️⃣ MySQL的存储函数主要返回基本数据类型，不支持返回表或记录等复杂数据结构。
⚠️ 2.2 错误处理和调试	同上	1️⃣ Oracle的PL/SQL为存储函数提供了强大的错误处理和调试能力。 2️⃣ 而MySQL也提供基本的错误处理，但缺乏调试功能。
触发器
⚡ 3.1 更强大的触发器	Oracle支持更多种类的触发器	1️⃣ Oracle支持AFTER LOGON、BEFORE LOGOFF、AFTER STARTUP等事件的触发器。 2️⃣ MySQL主要支持基于行和语句的触发器，没有Oracle这样的系统级事件触发器。
🔥 3.2 触发器的复杂性	Oracle的触发器可以包含更复杂的逻辑	1️⃣ 使用Oracle的PL/SQL，开发者可以在触发器中实现复杂的业务逻辑。 2️⃣ MySQL的触发器也支持业务逻辑，但由于SQL/PSM的限制，可能不如Oracle那样灵活和强大。

👀存储过程优缺点

✅ 优点：

预编译的 SQL 脚本：
- 🚫 不用去校验 SQL 语法
- 🚫 不用再次编译
实现了 SQL 编程，降低了锁表的时间及范围：
- 📦 可以定义变量，保存查询结果，进而利用它进行计算
- 🔄 减少子查询导致的锁表操作
对外封装了表结构：
- 🔒 提高了数据库的安全性
减少网络流量：
- 📡 只需要传输存储过程的名称和参数，而不是完整的 SQL 语句

❌ 缺点：

维护难度：
- 💼 可能需要专门的数据库开发人员
移植性问题：
- 🌍 不同的数据库系统可能有不同的存储过程语法

👀函数优缺点

✅ 优点：

代码复用：
- 🔄 可以在多个地方重复使用相同的函数
简化复杂操作：
- 📦 把复杂的操作封装在函数中，简化 SQL 查询
提高性能：
- 🚀 函数通常预编译，可以快速执行

❌ 缺点：

限制性：
- ⛔ 函数只能返回单个值
可能存在性能问题：
- 🐌 如果不当地使用，可能导致性能问题，如递归函数

👀触发器优缺点

✅ 优点：

数据完整性：
- 🔒 可以确保数据的完整性和一致性
自动执行：
- 🔄 当满足某些条件时，触发器会自动执行
日志记录：
- 📜 可以用于记录数据更改的历史

❌ 缺点：

性能问题：
- 🐌 过多的触发器可能导致性能下降
调试困难：
- 🔍 由于触发器在后台自动执行，可能导致难以调试的问题
可能导致复杂性增加：
- 🌍 过多的触发器可能增加数据库的复杂性

👀存储过程 (Stored Procedures)

存储过程是预编译的 SQL 代码，可以在数据库中存储并由应用程序调用。这可以提高性能并确保代码的重用。

💡1.1 基本语法

存储过程的基本语法如下：

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL code
END;

🚀 优点：可以重复使用、减少网络流量、提高性能。

💡1.2 变量

✍1、系统变量

系统变量是数据库管理系统预定义的变量，它们通常用于获取或设置数据库的配置信息。

🎯🎯特性:

预定义: 由数据库管理系统提供。
全局范围: 对整个数据库系统有效。
前缀: 通常使用@@前缀。

🎯🎯示例:

获取当前数据库版本:

SELECT @@VERSION;

获取最后插入的记录的ID:

SELECT @@IDENTITY;

✍2、用户定义变量

用户定义变量是由用户创建的，用于存储数据，这些数据可以在整个会话期间访问。

🎯🎯特性:

会话范围: 这些变量在用户会话中是持续的。
前缀: 通常使用@前缀。

🎯🎯示例:

声明并设置一个变量:

DECLARE @UserName VARCHAR(50);
SET @UserName = 'John Doe';

使用用户定义变量在查询中:

SELECT * FROM Users WHERE Name = @UserName;

✍3、局部变量

局部变量是在存储过程、函数或块内声明的变量。它们只在声明它们的特定块或存储过程中可用。

🎯🎯特性:

有限的作用域: 仅在声明它们的块或存储过程中有效。
前缀: 通常使用@前缀。

🎯🎯示例:

声明并使用局部变量:

BEGIN
    DECLARE @TotalAmount INT;
    SET @TotalAmount = (SELECT SUM(Amount) FROM Orders);
    PRINT @TotalAmount;
END;

💡1.3 IF

🎯🎯特性:

条件判断: 用于根据给定条件来决定哪些语句应该执行。
支持嵌套: 可以在一个 IF 语句中嵌套另一个 IF 语句。
与其他语句组合: 可以与 BEGIN...END 语句组合来在一个块中执行多条语句。

🎯🎯基本语法:

IF condition
BEGIN
    -- Code to execute if condition is TRUE
END
ELSE
BEGIN
    -- Code to execute if condition is FALSE
END;

🎯🎯示例:

基本的 IF…ELSE 语句:

DECLARE @Age INT = 18;

IF @Age >= 18
BEGIN
    PRINT 'You are eligible to vote.';
END
ELSE
BEGIN
    PRINT 'You are not eligible to vote.';
END;

嵌套的 IF 语句:

DECLARE @Score INT = 85;

IF @Score >= 90
BEGIN
    PRINT 'Grade A';
END
ELSE 
BEGIN
    IF @Score >= 80
    BEGIN
        PRINT 'Grade B';
    END
    ELSE
    BEGIN
        PRINT 'Grade C';
    END;
END;

没有 ELSE 的 IF 语句:

DECLARE @Number INT = 5;

IF @Number % 2 = 0
BEGIN
    PRINT 'Even Number';
END;

💡1.4 参数

参数在存储过程中非常有用，因为它们允许我们传递值到存储过程，从而使其更加灵活和可重用。这些参数可以是输入参数（向存储过程传递值）或输出参数（从存储过程返回值）。
🎯🎯特性:

灵活性: 通过参数，存储过程可以用不同的值执行，从而使其更加灵活。
多参数支持: 一个存储过程可以接受多个参数。
类型: 参数可以是任何支持的数据类型，如整数、字符串、日期等。

🎯🎯基本语法:

CREATE PROCEDURE ProcedureName 
    (@ParamName1 DataType1, @ParamName2 DataType2, ...)
AS
BEGIN
    -- SQL statements using the parameters
END;

🎯🎯示例:

基本的参数使用:

CREATE PROCEDURE InsertUser 
    (@UserName VARCHAR(50), @UserAge INT)
AS
BEGIN
    INSERT INTO Users (Name, Age) VALUES (@UserName, @UserAge);
END;

调用存储过程:

EXEC InsertUser 'John Doe', 25;

使用输出参数:

CREATE PROCEDURE GetTotalUsers 
    @TotalCount INT OUTPUT
AS
BEGIN
    SELECT @TotalCount = COUNT(*) FROM Users;
END;

调用存储过程并获取输出值:

DECLARE @UserCount INT;
EXEC GetTotalUsers @TotalCount = @UserCount OUTPUT;
PRINT @UserCount;

💡1.5 CASE

CASE 是 SQL 中的一个条件表达式，它允许在查询中进行条件判断，从而返回特定的值。它类似于编程语言中的 switch 或 if-else 语句，但是专为 SQL 设计。
🎯🎯特性:

条件判断: 允许在查询中进行条件判断。
灵活性: 可以有多个 WHEN 子句，以检查多个条件。
默认结果: 使用 ELSE 子句为不满足任何条件的情况提供默认值。

🎯🎯基本语法:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

🎯🎯示例:

基于列值的简单 CASE 表达式:

SELECT 
    Name,
    CASE 
        WHEN Grade = 'A' THEN 'Excellent'
        WHEN Grade = 'B' THEN 'Good'
        WHEN Grade = 'C' THEN 'Average'
        ELSE 'Needs Improvement'
    END AS Performance
FROM Students;

基于多列或表达式的复杂 CASE 表达式:

SELECT 
    Name, Salary, Bonus,
    CASE 
        WHEN Salary > 50000 AND Bonus > 10 THEN 'High Earner'
        WHEN Salary > 30000 AND Bonus <= 10 THEN 'Medium Earner'
        ELSE 'Others'
    END AS IncomeCategory
FROM Employees;

CASE 在聚合函数中的应用:

SELECT 
    Department,
    SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) AS MaleCount,
    SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS FemaleCount
FROM Employees
GROUP BY Department;

💡1.6 WHILE

WHILE 循环是 SQL 中的一个流程控制语句，它允许你重复执行一块代码，直到给定的条件为 FALSE。这与大多数编程语言中的 while 循环非常相似。

🎯🎯特性:

条件检查: 在每次迭代开始时检查条件。
前置检查: 先检查条件，然后执行循环体。
灵活性: 可以与其他控制流语句（如 IF, BREAK 和 CONTINUE）一起使用。

🎯🎯基本语法:

WHILE condition
BEGIN
    -- SQL statements to execute while the condition is TRUE
END;

🎯🎯示例:

基本的 WHILE 循环:

如你已提供的示例，这个循环会执行10次，并将 @Counter 的值从0增加到9。

DECLARE @Counter INT = 0;
WHILE @Counter < 10
BEGIN
    -- Code to execute
    PRINT @Counter; -- This will print numbers from 0 to 9.
    SET @Counter = @Counter + 1;
END;

使用 WHILE 循环进行数据库操作:

假设我们要复制某个表中前10行的数据到另一个表：

DECLARE @ID INT;
SET @ID = (SELECT MIN(ID) FROM SourceTable);

WHILE @ID IS NOT NULL
BEGIN
    INSERT INTO TargetTable
    SELECT * FROM SourceTable WHERE ID = @ID;

    SET @ID = (SELECT MIN(ID) FROM SourceTable WHERE ID > @ID);
END;

与其他控制语句结合使用:

使用 BREAK 提前退出循环:

DECLARE @Counter INT = 0;
WHILE @Counter < 10
BEGIN
    IF @Counter = 5
    BEGIN
        BREAK; -- Exit the loop when @Counter is 5
    END;

    PRINT @Counter;
    SET @Counter = @Counter + 1;
END;

WHILE 循环在 SQL 中非常有用，尤其是当需要重复执行某些任务或操作一系列数据时。但是，要注意避免无限循环，并确保循环在合理的时间内完成其任务。

💡1.7 REPEAT

REPEAT...UNTIL 结构确实为 MySQL 提供了一个重要的循环控制机制。与其他大多数编程语言中的 do...while 循环类似，REPEAT...UNTIL 允许你至少执行一次循环体，然后再检查条件。

🎯🎯特性:

后置检查: 在每次迭代结束时检查条件。
至少执行一次: 与 WHILE 循环不同，REPEAT...UNTIL 至少执行一次循环体，即使条件从一开始就为 TRUE。
条件为 TRUE 时停止: 循环会继续执行，直到条件成为 TRUE。

🎯🎯基本语法:

REPEAT
    -- SQL statements to execute
UNTIL condition
END REPEAT;

🎯🎯示例:

基本的 REPEAT 循环:

如您所提供的示例，这个循环会执行5次，并将 v_counter 的值从0递增到5。

DECLARE v_counter INT DEFAULT 0;

REPEAT
    SET v_counter = v_counter + 1;
    -- Your repeated code here
UNTIL v_counter >= 5 
END REPEAT;

使用 REPEAT 循环处理数据:

假设我们想从一个表中读取数据，并且基于某个条件处理数据。

DECLARE v_id INT;
DECLARE v_name VARCHAR(255);

-- Initialize v_id with the minimum ID from the table
SELECT MIN(id) INTO v_id FROM data_table;

REPEAT
    -- Fetch the name based on the current ID
    SELECT name INTO v_name FROM data_table WHERE id = v_id;

    -- Process the data (e.g., print or insert into another table)
    -- ...

    -- Get the next ID
    SELECT MIN(id) INTO v_id FROM data_table WHERE id > v_id;
UNTIL v_id IS NULL 
END REPEAT;

使用 REPEAT...UNTIL 循环时，重要的是确保循环最终会满足退出条件，以避免无限循环。此外，由于循环至少执行一次，所以确保在进入循环之前，循环体中的代码是安全的并且不会导致任何意外的副作用。

💡1.8 LOOP

确实，LOOP 提供了一个简单的方式来重复执行代码块，但关键是确保在某个时刻退出循环，以避免无限循环。

🎯🎯特性:

基本循环: LOOP 提供了一个简单的方法来重复执行代码。
需要退出条件: 由于 LOOP 会无限次地执行，所以必须提供一个退出条件。
与控制语句配合: 使用 LEAVE 或其他控制语句来退出循环。

🎯🎯基本语法:

label_name: LOOP
    -- SQL statements to execute within the loop
    IF condition THEN
        LEAVE label_name;
    END IF;
END LOOP label_name;

🎯🎯示例:

基本的 LOOP 使用:

如您所提供的示例，这个循环会执行5次，并将 v_counter 的值从0递增到5。

DECLARE v_counter INT DEFAULT 0;

my_loop: LOOP
    IF v_counter >= 5 THEN
        LEAVE my_loop;
    END IF;
    
    SET v_counter = v_counter + 1;
    -- Your repeated code here
END LOOP my_loop;

使用 LOOP 进行数据库操作:

假设我们想从一个表中读取数据，并且基于某个条件处理数据。

DECLARE v_id INT;
DECLARE v_name VARCHAR(255);

-- Initialize v_id with the minimum ID from the table
SELECT MIN(id) INTO v_id FROM data_table;

data_loop: LOOP
    -- Fetch the name based on the current ID
    SELECT name INTO v_name FROM data_table WHERE id = v_id;

    -- Process the data (e.g., print or insert into another table)
    -- ...

    -- Get the next ID
    SELECT MIN(id) INTO v_id FROM data_table WHERE id > v_id;

    IF v_id IS NULL THEN
        LEAVE data_loop;
    END IF;
END LOOP data_loop;

使用 LOOP 时，特别要注意退出条件。确保你的退出条件最终会被满足，以避免代码陷入无限循环。

💡1.9 游标 (Cursors)

游标在 SQL 中是一个非常有用的工具，尤其是当我们需要逐行处理查询结果时。游标的工作原理是将查询结果集存储在一个控制结构中，然后允许你从中逐行检索数据。

🎯🎯特性:

逐行处理: 游标允许你逐行处理查询结果。
灵活的数据处理: 游标非常适合需要对每一行数据进行复杂处理的情况。
资源消耗: 游标通常比批量查询更耗资源，因此应谨慎使用。

🎯🎯基本语法:

声明游标:

DECLARE cursor_name CURSOR FOR SELECT_statement;

打开游标:

OPEN cursor_name;

从游标中获取数据:

FETCH NEXT FROM cursor_name INTO @variable_name1, @variable_name2, ...;

关闭游标:

CLOSE cursor_name;

删除游标:

DEALLOCATE cursor_name;

🎯🎯示例:

假设我们有一个名为 Employees 的表，并且我们想逐行打印出每个员工的名字。

DECLARE @EmployeeName NVARCHAR(100);

DECLARE EmployeeCursor CURSOR FOR 
SELECT Name FROM Employees;

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @EmployeeName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @EmployeeName;
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeName;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

尽管游标在某些情况下非常有用，但它们通常比批量查询更加耗资源。因此，除非有特殊的需要，否则应尽量避免使用游标。如果可能的话，尝试使用集合操作来完成任务，这样通常会更加高效。

💡1.10 条件处理程序 (Condition Handlers)

条件处理程序是 SQL 存储过程和函数中的一个重要组件，它允许你处理可能在执行 SQL 语句时发生的错误和异常。这是一种错误处理机制，可以确保当遇到问题时，存储过程能够优雅地处理错误，而不是直接失败。

🎯🎯特性:

错误捕获: 可以捕获并处理各种 SQL 错误和异常。
灵活的响应: 允许你定义特定的响应，例如设置变量、写入日志或终止过程。
两种类型的处理程序: CONTINUE 和 EXIT。CONTINUE 允许代码继续执行，而 EXIT 会终止当前的存储过程或函数。

🎯🎯基本语法:

声明处理程序:

DECLARE [CONTINUE | EXIT] HANDLER FOR condition_value [,...] statement;

其中，condition_value 可以是一个预定义的条件（如 NOT FOUND、SQLWARNING 或 SQLEXCEPTION）或一个特定的错误代码。

🎯🎯示例:

处理 NOT FOUND 异常:

当在游标或某些查询操作中没有找到数据时，通常会发生 NOT FOUND 异常。你可以捕获此异常并采取相应的措施：

DECLARE v_name VARCHAR(50);
DECLARE no_data INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = 1;

-- Assume some cursor operations here

FETCH my_cursor INTO v_name;

IF no_data = 1 THEN
    -- Handle the situation when no data is found.
END IF;

处理特定的 SQL 错误:

你可以为特定的错误代码定义处理程序：

DECLARE EXIT HANDLER FOR 1062 -- Error code for duplicate entry
BEGIN
    -- Handle the duplicate entry error.
END;

处理任何 SQL 异常:

捕获并处理任何 SQL 异常：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    -- Handle any SQL exception.
END;

错误处理是任何编程任务的关键部分，尤其是数据库编程。使用条件处理程序可以确保存储过程和函数能够优雅地处理错误和异常，提供更稳定和可靠的性能。

👀存储函数 (Stored Functions)

存储函数是一组预编译的 SQL 语句，它返回一个值。与存储过程不同，存储函数总是返回一个值。

💡1.1 创建函数

CREATE FUNCTION function_name ([param datatype, ...]) 
RETURNS return_datatype
[LANGUAGE SQL]
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- SQL statements
    RETURN value;
END;

💡1.2 调用函数

在 SQL 语句中像常规函数一样调用它。

SELECT function_name(arguments);

💡1.3 删除函数

DROP FUNCTION function_name;

💡1.4 示例

考虑一个简单的函数，它接受两个整数并返回它们的和：

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT
BEGIN
    RETURN a + b;
END;

👀触发器 (Triggers)

触发器是一种特殊类型的存储过程，它会自动在特定的数据库事件（如 INSERT、UPDATE 或 DELETE）上被触发。

💡2.1 创建触发器

CREATE TRIGGER trigger_name 
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] 
ON table_name FOR EACH ROW 
BEGIN
    -- SQL statements
END;

💡2.2 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

💡2.3 触发器的类型

BEFORE INSERT: 在插入操作之前触发。
AFTER INSERT: 在插入操作之后触发。
BEFORE UPDATE: 在更新操作之前触发。
AFTER UPDATE: 在更新操作之后触发。
BEFORE DELETE: 在删除操作之前触发。
AFTER DELETE: 在删除操作之后触发。

💡2.4 示例

考虑一个触发器，它在向表中插入数据后自动更新另一个表：

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON table1
FOR EACH ROW 
BEGIN
    INSERT INTO table2 (column1, column2) VALUES (NEW.column1, NEW.column2);
END;

yueerba126

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
Mysql - 存储过程、函数、触发器

CALL当您在 Java 中调用数据库函数时，您可以像处理任何其他 SQL 查询的结果那样处理返回值。如果存储过程有输出参数，您可以使用和方法来捕获输出。除了返回值和输出参数外，存储过程和函数还可能返回一个或多个结果集。这些可以像处理任何其他 SQL 查询的结果集那样处理。
复制链接

扫一扫