MySQL中存储过程和存储函数实例分析

MySQL PROCEDURE

存储过程（PROCEDURE）是一个指定的存储在服务器中的一组SQL语句。客户端不需要不断地重新发出单个语句，而是可以引用存储过程。存储过程类型分为存储过程（PROCEDURE）和存储函数（FUNCTION）。

Ø 存储函数：在语句中调用函数。这些函数返回标量

l 使用存储过程的好处：

1) Client func

通过存储过程，您可以在数据库中集中创建一条语句或一系列语句，供用不同编程语言编写或运行在不同平台上的多个客户端应用程序使用。

2) 安全性

存储过程为需要最高级别安全性的应用程序提供了解决方案。例如，银行使用存储过程和函数来执行所有常见操作。这提供了一个一致、安全的环境。可以对存储过程进行编码，以确保正确记录每个操作。在这种设置中，应用程序和用户无法直接访问数据库表，只能执行特定的存储过程。

3) 性能

由于服务器和客户端之间需要发送的信息较少，存储过程可以提高表现。客户端通过名称调用存储过程而不是传递ll 存储过程中包含的语句。

4) 函数库

函数库可以通过存储过程在数据库服务器中使用。这些库充当数据库的 API。

l 使用存储过程的问题：

1) 服务器负载增加

在数据库中在其内部执行存储过程会增加服务器负载并降低应用程序性能。您可以运行测试并使用常识来确保将逻辑包含在数据库本身中的便利性超过了它可能导致的性能问题。

2) 开发工具有限

支持 MySQL 中存储过程的开发工具并不像更通用的那样可用编程语言如此成熟和清晰。这种限制会使存储过程的编写和调试过程变得更加困难，需要在存储过程中予以考虑。做决定的过程。

3）语言能力和速度有限

虽然在很多情况下将逻辑包含在数据库本身具有很大的优势，但是与其他编程语言相比，其所能实现的目标仍然存在局限性。存储过程在数据库上下文中执行，在处理大量数据时比客户端应用程序中的存储过程表现更好，但客户端应用程序语言可能具有更强大和通用的处理、集成或其他库功能。您必须考虑所需的功能范围，以确保为每个存储过程使用最佳的解决方案。

4) 有限的调试和分析功能

1.1。 执行存储过程

用于调用存储过程的命令与MySQL中的其他命令非常相似。使用CALL语句来调用存储过程（过程）。存储过程使用输出变量或结果集来传递返回值。与任何其他函数一样，FUNCTION 的执行是从语句内部调用的（即通过调用相应函数的名称），并且该函数返回一个标量值。

每个存储过程都与特定的数据库关联。这有多重含义：

Ø USE ：当调用存储过程时，MySQL会在存储过程运行时执行隐式的USE 。不能在存储过程中发出 USE 语句。

Ø 限定名称：可以使用存储过程的数据库名称来限定存储过程名称。执行此操作以引用当前数据库外部的存储过程。例如，要调用与测试数据库关联的存储过程 p 或函数 f，请使用 CALL test.p() 或 test.f()。

Ø 数据库删除：当数据库被删除时，与其关联的所有存储过程也将被删除。

MySQL 允许在存储过程中使用常规 SELECT 语句。此类查询的结果集直接发送到客户端。

1) 存储过程示例

mysql> DELIMITER //

mysql> CREATE PROCEDURE record_count ()

-> BEGIN

->BEGIN

mysql> p>

-> SELECT '国家数', COUNT(*) FROM Country;

-> SELECT '城市数', COUNT(*) FROM City;

- > SELECT 'CountryLanguage count', COUNT(*) FROM CountryLanguage;

-> END//

mysql> DELIMITER;

Ø 复合语句

可以通过在存储过程中使用 BEGIN…END 语法并使用触发器来创建复合语句。 BEGIN…END 块可以包含零个或多个语句。空的复合语句是合法的，并且复合语句中的语句数量没有上限。

Ø 分隔符

在 BEGIN…END 语法中，必须使用分号（;）来终止每条语句。由于mysql客户端使用分号作为默认终止符ing 字符用于 SQL 语句，并且在交互式使用 mysql 命令行客户端或进行批处理时，必须使用 DELIMITER 语句来更改此设置。

在示例中，第一个 DELIMITER 语句用于将 SQL 语句终止符更改为两个正斜杠 (//)。此更改可确保客户端不会将复合语句中的分号解释为语句分隔符，并且客户端不会过早地将 CREATE PROCEDURE 语句发送到服务器。当创建存储过程的语句以 // 终止时，客户端会先将该语句发送到服务器，然后再发出第二个 DELIMITER 语句以将语句分隔符重置为分号。

2) 存储函数：示例

mysql> DELIMITER //

mysql> CREATE FUNCTION pay_check (gross_pay FLOAT(9,2),tax_rate FLOAT (3 ,2))

-> 返回 FLOAT(9,2)

-> 无 SQL

-> 开始

->声明 net_pay FLOAT(9,2)

-> 默认 0;

-> SET net_pay=gross_pay -gross_pay*tax_rate;

-> RETURN net_pay;

->END//

mysql> DELIMITER;

Ø RETURNS子句

RETURNS子句用于判断是否执行此函数should 返回值的类型。

Ø 特征

通过多个特征，可以确定存储函数所使用的数据的性质。在MySQL中，这些特性仅供参考。服务器不使用这些特性来限制存储函数允许执行的语句类型。

l CONTAINS SQL表示存储函数包含读取或写入数据的语句。如果未明确提供上述任何特征，则这是默认值。

l NO SQL表示存储函数不包含任何SQL语句。

l READS SQL DATA表示存储函数包含读取数据的语句（例如SELECT），但不包含写入数据的语句。

l 修改IES SQL DATA 表示存储过程包含用于写入数据的语句（例如 INSERT 或 DELETE）。

注意：启用二进制日志记录后，如果您创建函数时未指定以下选项之一，MySQL 会生成错误：NO SQL、READS SQL DATA 或 DETERMINISTIC。

Ø DECLARE语句

在存储过程中使用DECLARE语句声明局部变量并初始化用户变量。可以在DECLARE语句末尾添加DEFAULT子句来指定用户变量的初始值。如果省略 DEFAULT 子句，则用户变量的初始值为 NULL。

Ø SET语句

使用SET语句，可以使用=或:=作为赋值运算符给定义的变量赋值。

Ø RETURN语句

RETURN语句用于终止存储函数的执行，并将值表达式返回给函数调用者。

1.2。 检查存储过程

Ø SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION

这些语句为 MySQL 扩展，类似于 SHOW CREATE TABLE。这些语句返回可用于重新创建指定存储过程的特定字符串。这些语句的主要限制之一是您必须知道过程或函数的名称，并且必须将其标识为过程或函数，然后才能尝试查看信息。

Ø SHOW PROCEDURE STATUS 和 SHOW FUNCTION STATUS

这些语句特定于 MySQL。它们返回存储过程的特征，例如数据库、名称、类型、创建者以及创建和修改日期。这些语句的优点是基于 LIKE 模式显示特定的存储过程。如果未指定架构，则会列出所有存储过程或所有存储函数的信息，具体取决于所使用的语句。例如，以下语句显示有关名称以 开头的过程的信息h "film":

SHOW PROCEDURE STATUS LIKE 'film%'\G

Ø INFORMATION_SCHEMA.ROUTINES

INFORMATION_SCHEMA.ROUTINES 表包含有关存储过程的信息（过程和函数），并返回可同时在 SHOW CREATE... 和 SHOW... 中使用的信息。 STATUS 语句中的大多数详细信息包括创建存储过程的实际语法。在这三个选项中，此表提供了数据库中可用存储过程的完整表示。

示例：

mysql> SELECTroutine_name、routine_schema、routine_type、definer

> FROM INFORMATION_SCHEMA.ROUTINES

> WHEREroutine_name LIKE '电影%';

+--------------------------------+----------------+- -------------+----------------+

|例程名称 |例程架构 |例程类型 |定义器 |

+--------------------------------+----------------+--- ----- ------+----------------+

|库存电影 |萨基拉 |程序 | root@localhost |

|电影_不库存 |萨基拉 |程序 | root@localhost |

+--------------------+------------ ----+- -------------+----------------+

2 行一组（0.00 秒）

Ø mysql系统数据库中与编程组件相关的表

mysql系统数据库包含提供与MySQL存储过程的功能相关的信息的表。这些表包括：

l mysql.event表，包含MySQL服务器中存储的事件信息；

l mysql.proc表包含MySQL服务器中存储过程和函数的信息；

l mysql.procs_priv表为引用存储过程的用户提供访问控制授权详细信息；

<强>1.3。 存储过程和执行安全性

存储过程和函数的使用涉及多个权限。

默认操作：当您创建存储过程时，MySQL 会自动授予您的帐户 EXECUTE 权限存储过程的 ALTER ROUTINE 权限。具有撤销权限和 GRANT OPTION 权限的用户可以稍后撤销或删除这些权限。创建存储过程后，您可以通过发出 SHOW GRANTS 语句来验证这些权限。

授予权限：当在全局级别或数据库级别授予所有权限时，GRANT ALL 语句包括除 GRANT OPTION 之外的所有存储过程权限。要授予 GRANT OPTION 权限，请在语句末尾包含WITH GRANT OPTION 子句。您可以在单个存储过程级别授予 EXECUTE、ALTER ROUTINE 和 GRANT OPTION 权限，但仅限于现有存储过程。要授予对单个存储过程的权限，请使用其数据库名称限定存储过程，并提供关键字 PROCEDURE 或 FUNCTION 来指示存储过程类型，如下例所示：

mysql> GRANT EXECUTE, ALTER程序例程 world_innodb.record_countTO 'magellan'@'localhost' with GRANT OPTION;

mysql> 授予 world_innodb.* 上的所有内容 TO 'magellan'@'localhost';

mysql> 授予执行，更改例程ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost';

权限对应于允许的操作

CREATE ROUTINE：创建存储过程。

ALTER ROUTINE：更改或删除存储过程。

EXECUTE：执行存储过程。

授予选项：向其他帐户授予权限。

以上就是《MySQL中存储过程和存储函数示例分析》一文的全部内容。感谢您的阅读！相信大家都有一定的了解，希望分享的内容对大家有所帮助。如果您想了解更多知识，请关注行业资讯频道！