2021最新MySQL存储过程基础知识

最新MySQL存储过程基础知识，学习 MySQL：MySQL 存储过程的基础知识。在本文中，我们将学习 MySQL 中的存储过程。在本文中，我将介绍存储过程的基础知识，其中包括以下内容

1. MySQL存储过程总结
2. 使用 Query 和 MySQL 工作台创建存储过程
3. 创建参数化存储过程
4. 使用查询和 MySQL 工作台删除存储过程

MySQL存储过程基础知识

存储过程是包含在CREATE PROCEDURE语句中的SQL语句。存储过程可能包含条件语句，如 IF 或 CASE 或循环。存储过程还可以执行另一个存储过程或模块化代码的函数。

以下是存储过程的好处：

1. 减少网络流量：多个 SQL 语句被封装在一个存储过程中。当您执行它时，我们只发送存储过程的名称和参数，而不是发送多个查询
2. 易于维护：存储过程可重用。我们可以在一个 SP 内部实现业务逻辑，它可以被应用程序多次使用，或者一个应用程序的不同模块可以使用相同的过程。这样，存储过程使数据库更加一致。如果需要更改，则只需在存储过程中进行更改
3. 安全：存储过程比 AdHoc 查询更安全。可以授予用户执行存储过程的权限，而无需授予存储过程中使用的表的权限。存储过程有助于防止数据库被SQL注入

创建 MySQL 存储过程的语法如下：

Create Procedure [存储过程名称] ([Parameter 1], [Parameter 2], [Parameter 3] )
Begin
    select * from jhrs.com
    ...其它语句
End

在语法中：

1. 必须在Create Procedure关键字 之后指定过程的名称，例如 https://jhrs.com
2. 在过程名称之后，必须在括号中指定参数列表。参数列表必须以逗号分隔
3. SQL 查询和代码必须写在BEGIN和END关键字 之间

要执行存储过程，您可以使用 CALL 关键字。下面是语法：

CALL [Procedure Name] ([Parameters]..)

在语法中：

1. 过程名称必须在 CALL 关键字之后指定
2. 如果过程有参数，则参数值必须在括号中指定

让我们创建一个基本的存储过程。为了演示，我使用了jhrs.com数据库。

创建一个简单的存储过程

假设您要填充电影列表。输出应包含film_id、标题(title)、描述(description)、发行年份(release_year)和评级列(rating)。程序代码如下：

DELIMITER //
CREATE PROCEDURE sp_GetMovies()
BEGIN
    select title,description,release_year,rating from film;
END //
    
DELIMITER ;

要创建 MySQL 存储过程，请打开MySQL workbench连接到MySQL 数据库，将代码复制粘贴到查询编辑器窗口中，单击Execute。

您可以在存储过程下查看过程。请参阅下面的屏幕截图。

要执行该过程，请运行以下命令。

CALL sp_GetMovies

以下是输出的部分屏幕截图：

使用 MySQL workbench向导创建存储过程

我们可以使用 MySQL workbench向导来创建存储过程。假设您想从 jhrs.com 数据库中获取客户列表。为此，展开jhrs.com 模式 右键单击Stored Procedures选择Create a Stored procedure。

在新建过程窗口中，已创建创建模板。在模板中，将过程名称替换为sp_getCustomers。在代码块中，输入以下查询

select store_id, first_name, last_name, email, create_date, last_update from customer

单击应用。将打开一个对话框，将脚本应用到数据库。在 Review the script 屏幕上，您可以查看存储过程的代码。单击应用。

脚本应用成功，并成功创建了存储过程sp_GetCustomer。

在 MySQL Workbench 中，可以查看sakila schema的Stored Procedures文件夹下的存储过程。

创建参数化存储过程

MySQL 存储过程参数具有三种模式：IN、OUT 和 INOUT。当我们声明一个 IN 类型参数时，应用程序必须向存储过程传递一个参数。这是默认模式。OUT 类型参数，存储过程返回由 SQL 语句生成的最终输出。当我们声明 INOUT 类型参数时，应用程序必须传递一个参数，并基于输入参数；该过程将输出返回给应用程序。

当我们创建存储过程时，必须在括号内指定参数。语法如下：

(IN | OUT | INOUT) (Parameter Name [datatype(length)])

在语法中：

1. 指定参数的类型。它可以是 IN、OUT 或 INOUT
2. 指定参数的名称和数据类型

IN 参数示例

假设我们想根据评分获得电影列表。所述param_rating是一个输入参数，并且数据类型是VARCHAR。程序代码如下：

DELIMITER //
CREATE PROCEDURE sp_GetMoviesByRating(IN rating varchar(50))
BEGIN
    select title,description,release_year,rating from film where rating=rating;
END //
DELIMITER ;

为了用jhrs.com -2评级填充电影列表，我们将jhrs.com -2值传递给sp_getMoviesByRating()过程。

CALL sp_GetMoviesByRating('jhrs.com -2')

OUT 参数示例

假设我们想要获得评级为jhrs.com-13的电影的数量。所述Total_Movies是一个输出参数，并且数据类型是一个整数。使用 INTO 关键字将电影计数分配给OUT变量 ( Total_Movies )。程序代码如下：

DELIMITER //
CREATE PROCEDURE sp_CountMoviesByRating(OUT Total_Movies int)
BEGIN
    select count(title) INTO Total_Movies from film where rating='jhrs.com -13';
END //
DELIMITER ;

要存储过程返回的值，请传递名为@PGRatingMovies的会话变量。

CALL sp_CountMoviesByRating(@PGRatingMovies)
Select @PGRatingMovies as Movies

INOUT 参数的示例

假设我们想根据评分获得电影的总数。输入参数是param_rating在过程，并且数据类型是VARCHAR（10） 。输出参数为 Movies_count，数据类型为整数。

程序代码：

DELIMITER //
CREATE PROCEDURE sp_CountMoviesByRating_Inout(inout Movies_count int, In param_rating varchar(10))
BEGIN
    select count(title) INTO Movies_count from film where rating=param_rating ;
END //
DELIMITER ;

使用CALL关键字执行程序并将输出保存在名为@MoviesCount 的会话变量中

CALL sp_CountMoviesByRating_Inout(@T,'jhrs.com-13');
Select @T as Movies

使用查询查看数据库中的存储过程列表

要查看存储过程列表，可以查询information_schema.routines 表。它包含在数据库上创建的存储过程和存储函数的列表。要查看在 sakila 数据库中创建的存储过程的列表，请运行以下查询。此外，它还提供对存储过程的 所有者、创建日期、安全类型和SQL 数据访问。

select routine_name, routine_type,definer,created,security_type,SQL_Data_Access from information_schema.routines where routine_type='PROCEDURE' and routine_schema='jhrs.com';

删除存储过程

要删除存储过程，您可以使用 drop procedure 命令。语法如下

Drop procedure [IF EXISTS] <Procedure Name>

在语法中，存储过程的名称后面必须跟有Drop Procedure关键字。如果要从 sakila 数据库中删除sp_getCustomers过程，可以运行以下查询。

Drop procedure sp_getCustomers

当您尝试删除数据库中不存在的过程时，查询显示错误：

ERROR 1305 (42000): PROCEDURE sakila.getCustomer does not exist

为避免这种情况，您可以在 drop procedure 命令中包含 [IF EXISTS] 选项。当您包含 IF EXISTS 关键字时，查询将返回警告而不是错误：

Query OK, 0 rows affected, 1 warning (0.01 sec) 1305 PROCEDURE sakila.getCustomer does not exist

使用 MySQL workbench向导删除存储过程

您可以使用 MySQL 工作台向导删除该过程。要删除任何过程，请展开sakila模式 展开存储过程右键单击sp_GetMovies单击“删除存储过程”。

一个对话框打开。您可以选择在放弃之前查看该过程，或者您可以放弃而不查看它。在删除数据库对象之前检查它是一种很好的做法，因此选择 Review SQL。

在“查看要执行的 SQL 代码”对话框中，您可以查看 drop 语句和对象名称。

单击执行。该过程将被成功删除。

概括

在本文中，我们学习了 MySQL 存储过程的基础知识。我已经涵盖了以下主题：

1. 创建 MySQL 存储过程的语法以及如何创建它们我们创建过程语句和 MySQL 工作台向导
2. 如何创建参数化的 MySQL 存储过程