【重学 MySQL】七十七、掌握存储过程与存储函数的查看、修改与删除技巧

用户11332765

发布于 2024-10-28 10:33:31

20900

代码可运行

文章被收录于专栏：编程编程

运行总次数：0

代码可运行

【重学 MySQL】七十七、掌握存储过程与存储函数的查看、修改与删除技巧

在MySQL中，存储过程和存储函数是数据库编程的重要组成部分，它们允许用户将一系列SQL语句封装成一个独立的单元，以便重复使用和调用。

查看存储过程与存储函数

使用SHOW CREATE语句

查看存储过程的创建信息：SHOW CREATE PROCEDURE 存储过程名;
查看存储函数的创建信息：SHOW CREATE FUNCTION 存储函数名;

这两条语句会返回存储过程或函数的定义，包括参数、返回类型、函数体等详细信息。

使用SHOW STATUS语句

查看存储过程和函数的状态信息：SHOW PROCEDURE STATUS [LIKE 'pattern']; 或 SHOW FUNCTION STATUS [LIKE 'pattern'];

其中，pattern是一个可选的模式匹配字符串，用于筛选结果。这些语句会返回存储过程或函数的名称、数据库、类型、创建者、创建和修改日期等状态信息。

查询INFORMATION_SCHEMA.ROUTINES表

MySQL的INFORMATION_SCHEMA数据库包含了关于数据库元数据的信息。通过查询ROUTINES表，可以获取存储过程和函数的详细信息。

查询存储过程的信息：

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, CREATED, LAST_ALTERED, ROUTINE_COMMENT
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = '数据库名';

查询存储函数的信息：

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, CREATED, LAST_ALTERED, ROUTINE_COMMENT
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_SCHEMA = '数据库名';

修改存储过程与存储函数

MySQL不提供直接修改存储过程或函数代码的功能，但可以通过以下方式实现“修改”：

使用ALTER语句修改特性

虽然不能直接修改存储过程或函数的代码，但可以使用ALTER语句修改其特性，如安全性（SQL SECURITY）、数据访问权限（CONTAINS SQL、NO SQL、READS SQL DATA、MODIFIES SQL DATA）和注释（COMMENT）等。

修改存储过程的特性：

ALTER PROCEDURE 存储过程名 MODIFIES SQL DATA SQL SECURITY INVOKER COMMENT '新注释';

修改存储函数的特性：

ALTER FUNCTION 存储函数名 READS SQL DATA COMMENT '新注释';

删除并重新创建

如果需要修改存储过程或函数的代码，一种常见的方法是删除原有的存储过程或函数，并重新创建一个新的同名存储过程或函数。这可以通过以下步骤实现：

使用DROP语句删除原有的存储过程或函数：

DROP PROCEDURE IF EXISTS 存储过程名;
DROP FUNCTION IF EXISTS 存储函数名;

使用CREATE语句重新创建存储过程或函数，并包含新的代码或修改后的代码。

删除存储过程与存储函数

使用DROP语句

可以使用DROP语句删除存储过程或函数。如果存储过程或函数不存在，可以使用IF EXISTS子句来防止发生错误。

删除存储过程：

DROP PROCEDURE IF EXISTS 存储过程名;

删除存储函数：

DROP FUNCTION IF EXISTS 存储函数名;

使用MySQL Workbench等图形化工具

如果使用MySQL Workbench等图形化数据库管理工具，也可以通过图形界面删除存储过程或函数。通常，这些工具会在数据库对象的列表中显示存储过程和函数，右键单击对象并选择删除选项即可。

关于使用存储过程的争议

关于使用存储过程的争议，主要围绕其优缺点以及在现代开发环境中的适用性展开。

优点

性能提升：
- 存储过程在服务器端执行，减少了网络流量，因为只需要发送执行命令而不是大量的数据。
- 一旦编译，存储过程可以被多次快速执行，提高了执行效率。
安全性增强：
- 通过限制对底层表的直接访问，存储过程有助于保护数据。
- 可以封装复杂的操作，并授予用户仅执行存储过程的权限，而不是更广泛的数据库对象权限。
代码重用：
- 编写一次存储过程后，可以在应用程序中多次调用它，有助于减少重复代码，使维护更加容易。
事务管理：
- 存储过程通常支持事务处理，确保一系列操作要么全部完成，要么一个都不做，这对于需要一致性的操作至关重要。
错误处理：
- 存储过程中可以包含错误处理逻辑，使得在遇到问题时能够更好地控制和响应。

缺点与争议

紧耦合：
- 存储过程通常与数据库紧密绑定，可能导致应用程序与数据库之间的耦合度增加。
- 如果需要更换数据库系统或调整数据库结构，可能需要重新编写存储过程。
难以测试：
- 存储过程中的逻辑通常较难进行单元测试，尤其是当它们包含复杂的事务或数据库操作时。
- 这使得保证代码质量变得更加困难。
跨平台问题：
- 不同的数据库管理系统（DBMS）可能有不同的存储过程语言和语法，导致存储过程缺乏跨平台兼容性。
与现代开发方法的冲突：
- 敏捷开发方法强调快速迭代和灵活变更。使用存储过程可能会增加变更数据库的成本，因为它需要更多的协调和部署步骤。
- 现代架构（如微服务架构）倾向于将业务逻辑从数据库层移出到应用服务层，以便更好地实现服务之间的解耦和服务重用。
部署和版本控制复杂性：
- 存储过程作为数据库的一部分，其版本控制和部署可能比应用程序代码更复杂。
- 如果项目需要频繁发布新版本，管理数据库中的存储过程可能会成为一个额外的负担。
性能瓶颈：
- 在高并发环境下，存储过程可能会成为性能瓶颈。
- 存储过程在执行过程中可能会锁定数据库表或行，以确保数据的一致性和完整性，这可能导致资源争用和性能下降。
技术依赖：
- 存储过程非常考验创建人的SQL能力。如果SQL编写不当，可能会影响存储过程的性能和可维护性。