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

这篇文章主要为大家展示了“MySQL中存储过程和存储函数的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中存储过程和存储函数的示例分析”这篇文章吧。

MySQL PROCEDURE

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

Ø  存储过程：通过CALL 语句调用过程。这些过程可以使用输出变量或结果集传回值。

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

l  使用存储过程的好处：

1)         客户机功能

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

2)         安全性

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

3)         性能

因为服务器和客户机之间需要发送的信息变少了，所以存储过程可提升性能。客户机按名称调用存储过程，而不是传递该存储过程所包含的所有语句。

4)         函数库

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

l  使用存储过程的问题：

1)         增加了服务器负载

在数据库自身中执行存储过程可增加服务器负载并降低应用程序的性能。可以运行测试并运用常识来确保在数据库本身中包含逻辑所带来的方便比可能引发的性能问题更为显著。

2)         开发工具有限

MySQL 中支持存储过程的开发工具不像在更通用的编程语言中那样成熟和明确。此局限性会使存储过程的编写和调试过程更加困难，在决策过程中需要加以考虑。

3)         语言功能和速度有限

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

4)         调试和概要分析功能有限

1.1.        执行存储过程

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

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

Ø  USE <database>：调用存储过程时，MySQL 会在该存储过程运行期间执行隐式USE <database>。不能在存储过程内发出USE 语句。

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

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

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

1)         存储过程示例

mysql> DELIMITER //

mysql> CREATE PROCEDURE record_count ()

-> BEGIN

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

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

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

-> END//

mysql> DELIMITER ;

Ø  复合语句

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

Ø  分隔符

在BEGIN…END 语法中，必须使用分号(;) 终止每个语句。由于mysql 客户机使用分号作为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))

-> RETURNS FLOAT(9,2)

-> NO SQL

-> BEGIN

-> DECLARE net_pay FLOAT(9,2)

-> DEFAULT 0;

-> SET net_pay=gross_pay - gross_pay * tax_rate;

-> RETURN net_pay;

-> END//

mysql> DELIMITER ;

Ø  RETURNS 子句

RETURNS 子句用于确定此函数要返回的值的类型。

Ø  特征

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

l  CONTAINS SQL 表示存储函数包含用于读取或写入数据的语句。如果未显式提供以上任何特征，则此为默认值。

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

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

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

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

Ø  DECLARE 语句

在存储过程中使用DECLARE 语句来声明本地变量并初始化用户变量。可将DEFAULT 子句添加到DECLARE 语句的结尾，以便为用户变量指定初始值。如果省去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 模式显示特定存储过程。如果未指定任何模式，则会根据所使用的语句，列出所有存储过程或所有存储函数的信息。例如，以下语句显示名称以“film”开头的过程的相关信息：

SHOW PROCEDURE STATUS LIKE 'film%'\G

Ø  INFORMATION_SCHEMA.ROUTINES

INFORMATION_SCHEMA.ROUTINES 表包含存储过程（过程和函数）的相关信息，并返回可同时在SHOW CREATE … 和SHOW … STATUS 语句中找到的大部分详细信息，以包含用于创建存储过程的实际语法。在这三个选项中，此表可完整地呈现数据库中的可用存储过程。

示例：

mysql> SELECT routine_name, routine_schema, routine_type, definer

> FROM INFORMATION_SCHEMA.ROUTINES

> WHERE routine_name LIKE 'film%';

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

| routine_name | routine_schema | routine_type | definer |

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

| film_in_stock | sakila | PROCEDURE | root@localhost |

| film_not_in_stock | sakila | PROCEDURE | root@localhost |

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

2 rows in set (0.00 sec)

Ø  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 ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost' WITH GRANT OPTION;

mysql> GRANT ALL ON world_innodb.* TO 'magellan'@'localhost';

mysql> GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE world_innodb.record_count TO 'magellan'@'localhost';

权限对应允许的操作

CREATE ROUTINE：创建存储过程。

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

EXECUTE：执行存储过程。

GRANT OPTION：将权限授予其他帐户。

以上是“MySQL中存储过程和存储函数的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！