{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2025-03-02 11:46:07","updateBy":"admin","updateTime":"2025-03-02 11:46:07","remark":null,"id":102,"articleTitle":"MySQL（十一）存储过程PROCEDURE","articleUrl":"mysql_procedure","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"0","articleSummary":"MySQL 存储过程是一种存储在数据库服务器中的预编译 SQL 语句集合。你可以将它看作数据库中的一种可编程对象，类似于其他编程语言中的函数或子程序。它封装了一系列复杂的数据库操作，可以被应用程序或其他 SQL 语句调用。","articleContent":"MySQL 存储过程是一种**存储在数据库服务器中的预编译 SQL 语句集合**。你可以将它看作数据库中的一种可编程对象，类似于其他编程语言中的函数或子程序。它封装了一系列复杂的数据库操作，可以被应用程序或其他 SQL 语句调用。\n\n## 核心概念与特点\n\n1.  **预编译：**\n    *   存储过程在创建时被解析和编译一次，然后以编译后的形式存储在数据库中。\n    *   **优点：** 后续调用时无需再次解析和编译，执行效率通常比直接执行等效的 SQL 语句串更高，尤其对于复杂操作。\n\n2.  **封装性：**\n    *   将复杂的业务逻辑（涉及多个 SQL 语句、控制流、变量操作等）封装在一个命名单元中。\n    *   **优点：**\n        *   **代码重用：** 应用程序只需调用存储过程名并传递参数，无需重复编写复杂的 SQL。提高了代码的可维护性。\n        *   **业务逻辑集中化：** 业务规则在数据库层实现，确保所有应用都使用同一套逻辑，增强数据一致性和安全性。\n        *   **简化应用代码：** 应用层代码更简洁，专注于业务展示和交互。\n\n3.  **减少网络流量：**\n    *   应用程序只需发送一个调用存储过程的命令（`CALL procedure_name(...)`）和必要的参数，而不是发送一大串 SQL 语句。\n    *   **优点：** 对于需要多次与数据库交互的操作，能显著减少客户端与服务器之间的网络通信量，提高性能，特别是在网络延迟较高或操作非常复杂时。\n\n4.  **增强安全性：**\n    *   可以授予用户执行某个存储过程的权限，而**不直接**授予用户访问底层基表的权限（如 `SELECT`, `UPDATE`, `DELETE`, `INSERT`）。\n    *   **优点：** 用户只能通过预定义的、受控的接口（存储过程）来操作数据，提高了安全性，实现了最小权限原则。可以严格控制对数据的访问和修改方式。\n\n5.  **支持变量与控制流：**\n    *   存储过程内部可以声明和使用局部变量。\n    *   支持丰富的控制流语句：\n        *   `IF ... THEN ... ELSEIF ... ELSE ... END IF`\n        *   `CASE ... WHEN ... THEN ... ELSE ... END CASE`\n        *   `LOOP ... END LOOP`\n        *   `WHILE ... DO ... END WHILE`\n        *   `REPEAT ... UNTIL ... END REPEAT`\n        *   `ITERATE` (类似 `continue`)\n        *   `LEAVE` (类似 `break`)\n    *   **优点：** 可以实现非常复杂的业务逻辑判断和循环处理。\n\n6.  **支持参数：**\n    *   可以定义输入参数(`IN`)、输出参数(`OUT`)和输入输出参数(`INOUT`)，使得存储过程更加灵活，可以接收外部输入并返回结果。\n    *   **优点：** 增强了与调用者的交互能力。\n\n7.  **事务处理：**\n    *   存储过程内部可以使用 `START TRANSACTION`, `COMMIT`, `ROLLBACK` 等语句显式控制事务。一个存储过程内的所有操作可以作为一个原子单元执行。\n    *   **优点：** 确保复杂操作的数据一致性。\n\n## 创建存储过程的基本语法\n\n```sql\nDELIMITER // -- 改变分隔符，避免过程体内的分号冲突\n\nCREATE PROCEDURE procedure_name (\n    [IN | OUT | INOUT] parameter_name parameter_data_type [(length)],\n    ...\n)\n[characteristic ...] -- 可选特性，如 SQL SECURITY DEFINER/INVOKER, COMMENT 等\nBEGIN\n    -- 存储过程体：包含变量声明、SQL语句、控制流语句等\n    DECLARE variable_name data_type [DEFAULT value]; -- 声明局部变量\n\n    SQL statements;\n    Control flow statements (IF, CASE, LOOP, etc.);\n    ...\nEND //\n\nDELIMITER ; -- 恢复默认分隔符\n```\n\n*   `DELIMITER`: 临时改变语句分隔符（通常为 `;`），因为过程体内包含大量分号结尾的 SQL 语句。使用 `//`（或其他符号）作为新的分隔符来结束 `CREATE PROCEDURE` 语句。最后恢复默认分隔符。\n*   `CREATE PROCEDURE procedure_name`: 定义存储过程的名称。\n*   `( [IN|OUT|INOUT] param_name param_type, ... )`: 定义参数列表。\n    *   `IN` (默认)：输入参数，调用者传入值给过程。过程内部可以读取但不能修改传入的值（对调用者而言）。\n    *   `OUT`：输出参数，过程内部可以修改其值，修改后的值在调用结束后返回给调用者。调用时通常传入变量接收结果。初始值为 `NULL`。\n    *   `INOUT`：既是输入参数也是输出参数。调用者传入值，过程可以读取并修改它，修改后的值返回给调用者。\n*   `BEGIN ... END`: 包裹存储过程的主体代码块。\n*   `DECLARE`: 在 `BEGIN` 块内声明**局部变量**，仅在过程内部可见。\n*   `characteristic`: 可选特性，例如：\n    *   `LANGUAGE SQL`: 默认，表示使用 SQL。\n    *   `[NOT] DETERMINISTIC`: 指明过程是否总是对相同输入产生相同输出（如 `NOW()` 或 `RAND()` 使其非确定性）。影响优化器和缓存。\n    *   `SQL SECURITY { DEFINER | INVOKER }`:\n        *   `DEFINER` (默认)：以存储过程**创建者/定义者**的权限执行。调用者只需有 `EXECUTE` 权限。\n        *   `INVOKER`：以存储过程**当前调用者**的权限执行。调用者需有过程涉及对象的相应权限。\n    *   `COMMENT 'string'`: 添加注释。\n\n## 调用存储过程\n\n使用 `CALL` 语句：\n\n```sql\nCALL procedure_name([argument_list]);\n```\n\n*   `argument_list` 是传递给 `IN` 和 `INOUT` 参数的实参列表。\n*   对于 `OUT` 和 `INOUT` 参数，需要传递**变量**来接收返回值。调用后，通过 `SELECT @out_param;` 查看输出变量的值（假设 `@out_param` 是用户会话变量）。\n\n**示例调用：**\n\n```sql\n-- 假设有过程：CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)\nCALL GetCustomerOrders(123);\n\n-- 假设有过程：CREATE PROCEDURE CalculateTotal(IN price DEC(10,2), IN quantity INT, OUT total DEC(10,2))\nSET @price = 19.99;\nSET @qty = 5;\nCALL CalculateTotal(@price, @qty, @total);\nSELECT @total; -- 输出计算后的总价\n```\n\n## 修改和删除存储过程\n\n* **修改：** MySQL 不支持直接修改存储过程体。通常的做法是先删除 (`DROP PROCEDURE`) 再重新创建 (`CREATE PROCEDURE`)。\n\n* **删除：**\n\n  ```sql\n  DROP PROCEDURE [IF EXISTS] procedure_name;\n  ```\n\n## 查看存储过程信息\n\n* **查看定义：**\n\n  ```sql\n  SHOW CREATE PROCEDURE procedure_name;\n  ```\n\n* **查看数据库中的存储过程列表：**\n\n  ```sql\n  SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];\n  ```\n\n* **从 `information_schema.ROUTINES` 表查询：**\n\n  ```sql\n  SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'your_database_name';\n  ```\n\n## 简单示例\n\n以下是一个带日志记录与错误处理的存储过程\n\n```sql\nDELIMITER \nCREATE DEFINER=`root`@`%` PROCEDURE `GenerateDailyStatistics`()\nBEGIN\n    -- ==== 声明区域 ====\n    -- 日志变量\n    DECLARE v_log_id BIGINT UNSIGNED;\n    DECLARE v_start_time DATETIME(6);\n    DECLARE v_end_time DATETIME(6);\n    DECLARE v_error_code VARCHAR(20) DEFAULT NULL;\n    DECLARE v_error_message TEXT DEFAULT NULL;\n    DECLARE v_affected_rows INT DEFAULT 0;\n    DECLARE v_in_transaction BOOLEAN DEFAULT FALSE;\n    \n    -- 业务变量\n    DECLARE v_stat_date DATE;\n    DECLARE v_web_views INT DEFAULT 0;\n    DECLARE v_article_count INT DEFAULT 0;\n    DECLARE v_comment_count INT DEFAULT 0;\n    DECLARE v_website_count INT DEFAULT 0;\n    \n    -- 错误处理\n    DECLARE EXIT HANDLER FOR SQLEXCEPTION\n    BEGIN\n        -- 第一步：立即获取错误信息（在任何其他语句之前）\n        GET DIAGNOSTICS CONDITION 1\n            v_error_code = MYSQL_ERRNO,\n            v_error_message = MESSAGE_TEXT;\n        \n        -- 第二步：回滚事务（如果已开始）\n        IF v_in_transaction THEN\n            ROLLBACK;\n            SET v_in_transaction = FALSE;\n        END IF;\n        \n        -- 第三步：设置结束时间\n        SET v_end_time = NOW(6);\n        \n        -- 第四步：更新错误日志\n        BEGIN\n            -- 嵌套错误处理：防止日志更新失败导致循环\n            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;\n            \n            UPDATE mysql_task_log \n            SET \n                affected_rows = 0,\n\t\t\t\t\t\t\t\tend_time = v_end_time,\n                duration_ms = TIMESTAMPDIFF(MICROSECOND, v_start_time, v_end_time)/1000,\n                status = 'FAILED',\n                error_code = v_error_code,\n                error_message = LEFT(v_error_message, 65535)  -- 确保不超过TEXT限制\n            WHERE id = v_log_id;\n        END;\n    END;\n    \n    -- ===================== 业务逻辑 =====================\n    -- 记录开始时间\n    SET v_start_time = NOW(6);\n    \n    -- 插入开始日志\n    INSERT INTO mysql_task_log (\n        task_name,\n        task_type,\n        start_time,\n        status,\n        execution_context,\n        create_time\n    ) VALUES (\n        'GenerateDailyStatistics',\n        'PROCEDURE',\n        v_start_time,\n        'RUNNING',\n        '执行每日统计任务',\n        v_start_time\n    );\n    \n    -- 获取日志ID\n    SET v_log_id = LAST_INSERT_ID();\n    \n    -- 开始事务\n    START TRANSACTION;\n    SET v_in_transaction = TRUE;\n    \n    -- 业务逻辑\n    SET v_stat_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);\n    \n    SELECT COUNT(*) INTO v_web_views \n    FROM web.sys_oper_log \n    WHERE DATE(oper_time) = v_stat_date AND title = '查询文章';\n    \n    SELECT COUNT(*) INTO v_article_count \n    FROM web.app_article;\n    \n    SELECT COUNT(*) INTO v_comment_count \n    FROM web.app_comment;\n    \n    SELECT COUNT(*) INTO v_website_count \n    FROM web.app_websites;\n    \n    INSERT INTO web.app_daily_statistics \n        (date, web_views, article_count, comment_count, website_count)\n    VALUES \n        (v_stat_date, v_web_views, v_article_count, v_comment_count, v_website_count)\n    ON DUPLICATE KEY UPDATE \n        web_views = VALUES(web_views),\n        article_count = VALUES(article_count),\n        comment_count = VALUES(comment_count),\n        website_count = VALUES(website_count);\n    \n    SET v_affected_rows = ROW_COUNT();\n    \n    -- 提交事务\n    COMMIT;\n    SET v_in_transaction = FALSE;\n    \n    -- ===================== 更新日志 =====================\n    SET v_end_time = NOW(6);\n    \n    UPDATE mysql_task_log \n    SET \n        end_time = v_end_time,\n        duration_ms = TIMESTAMPDIFF(MICROSECOND, v_start_time, v_end_time)/1000,\n        status = 'SUCCESS',\n        affected_rows = v_affected_rows\n    WHERE id = v_log_id;\nEND\nDELIMITER ;\n```\n\n","categoryId":4,"viewCount":68,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}