mysql 存储过程中游标_MySQL存储过程中的游标

最新推荐文章于 2024-07-28 21:37:11 发布

culi3118

最新推荐文章于 2024-07-28 21:37:11 发布

阅读量1.1k

点赞数

文章标签：数据库 java python mysql php

原文链接：https://www.sitepoint.com/cursors-mysql-stored-procedures/

版权

mysql 存储过程中游标

After my previous article on Stored Procedures was published on SitePoint, I received quite a number of comments. One of them suggested further elaboration on CURSOR, an important feature in Stored Procedures.

在上一篇有关存储过程的文章在SitePoint 上发布之后，我收到了很多评论。其中一位建议进一步详细说明CURSOR，这是存储过程的重要功能。

As cursors are a part of a Stored Procedure, we will elaborate a bit more on SP in this article as well. In particular, we will see how to return a dataset from an SP.

由于游标是存储过程的一部分，因此我们还将在本文中详细介绍SP。特别是，我们将看到如何从SP返回数据集。

什么是游标？ (What is a CURSOR?)

A cursor can’t be used by itself in MySQL. It is an essential component in stored procedures. I would be inclined to treat a cursor as a “pointer” in C/C++, or an iterator in PHP’s foreach statement.

游标本身不能在MySQL中使用。它是存储过程中的重要组成部分。我倾向于将游标视为C / C ++中的“指针”，或者视为PHP的foreach语句中的迭代器。

With cursors, we can traverse a dataset and manipulate each record to accomplish certain tasks. When such an operation on a record can also be done in the PHP layer, it saves data transfer amounts as we can just return the processed aggregation/statistical result back to the PHP layer (thus eliminating the select – foreach – manipulation process at the client side).

使用游标，我们可以遍历数据集并处理每条记录以完成某些任务。当这样的记录操作也可以在PHP层中完成时，它可以节省数据传输量，因为我们只需将处理后的聚合/统计结果返回给PHP层即可(从而省去了客户端的select - foreach操作过程)侧)。

Since a cursor is implemented in a stored procedure, it has all the benefits (and limitations) of an SP (access control, pre-compiled, hard to debug, etc).

由于游标是在存储过程中实现的，因此游标具有SP的所有优点(和局限性)(访问控制，预编译，难以调试等)。

The official documentation on cursors is located here. It contains only four commands that are related to cursor declaration, opening, closing, and fetching. As mentioned above, we will also touch on some other stored procedure statements. Let’s get started.

游标的官方文档位于此处。它仅包含四个与游标声明，打开，关闭和获取有关的命令。如上所述，我们还将介绍其他一些存储过程语句。让我们开始吧。

一个现实世界的问题 (A real world question)

My personal website has a page showing the scores of my favorite NBA team: LA Lakers. The table structure behind it is straightforward:

我的个人网站上有一个页面，显示我最喜欢的NBA球队：洛杉矶湖人队的得分。它后面的表结构很简单：

Fig 1. The Lakers matches status table structure

图1.湖人队比赛状态表结构

I have been updating this table since 2008. Some of the latest records showing Lakers’ 2013-14 season are shown below:

自2008年以来，我一直在更新此表。一些显示湖人队2013-14赛季的最新记录如下：

Fig 2. The Lakers matches status table data (partial) for 2013-2014 season

图2.湖人队2013-2014赛季比赛状态表数据(部分)

(I am using MySQL Workbench as the GUI tool to manage my MySQL databases. You can use your favorite tool.)

(我使用MySQL Workbench作为管理我MySQL数据库的GUI工具。您可以使用自己喜欢的工具。)

Well, I have to admit Lakers are not playing very well these days. 6 consecutive losses up to Jan 15th. I get this “6 consecutive losses” by manually counting from the last played match all the way up (towards earlier games) and see how long an “L” (meaning a loss) in winlose column can appear. This is certainly doable but if the requirement becomes more complicated in a larger table, it takes more time and is more error prone.

好吧，我不得不承认湖人最近的表现并不好。截至1月15日，连续6次亏损。通过手动从上次比赛开始一直计数(直到较早的比赛)，我得到了“连续6次亏损”，并看到在winlose列中出现“ L”(意味着亏损)多winlose 。这当然是可行的，但是如果需求在更大的表中变得更加复杂，则将花费更多的时间并且更容易出错。

Can we do this with a single SQL statement? I am not an SQL expert and I haven’t been able to figure out how to achieve the desired result (“6 consecutive losses”) from one SQL statement. The input of gurus will be highly appreciated – leave it in the comments below.

我们可以用一个SQL语句来做到这一点吗？我不是SQL专家，我无法从一个SQL语句中弄清楚如何实现期望的结果(“连续6次丢失”)。大师的意见将受到高度赞赏–留在下面的评论中。

Can we do this in PHP? Yes, of course. We can retrieve the game data (particularly, the winlose column) for current season and do a traverse on the records to calculate the current longest win/lose streak. But to do that, we will have to grab all data for that year and most of the data will be wasted (as it is not likely for a team to have a win/lose streak for more than 20+ games in a 82-game regular season). However, we don’t know how many records should be retrieved into PHP to determine the streak, so this waste is a must. And finally, if the current win/lose streak is the only thing we want to know from that table, why pull all the raw data?

我们可以用PHP做到吗？当然是。我们可以检索当前赛季的比赛数据(特别是winlose列)，并在记录上进行遍历以计算当前最长的获胜/失利连胜纪录。但是要做到这一点，我们将必须获取当年的所有数据，并且大部分数据都将被浪费(因为一支球队在82场比赛中赢得20场以上比赛的胜利/失败连胜的可能性不大常规赛)。但是，我们不知道应该从PHP中检索多少记录来确定条纹，因此这种浪费是必须的。最后，如果当前唯一要从该表中获胜的消息是，为什么要提取所有原始数据？

Can we do this via other means? Yes, it is possible. For example, we can create a redundant table specifically designed to store the current win/lose streak. Every insertion of the record will update that table too. But this is way too cumbersome and too error prone.

我们可以通过其他方式做到吗？是的，有可能。例如，我们可以创建一个冗余表，专门用于存储当前的赢/输。每次插入记录也会更新该表。但这太麻烦了并且容易出错。

So, what is a better way to achieve this result?

那么，有什么更好的方法来达到这个结果呢？

在存储过程中使用游标 (Using Cursor in a Stored Procedure)

As the name of this article suggests, we will see a better alternative (in my view) to solve this problem: using cursor in a Stored Procedure.

就像本文的名称所暗示的那样，(在我看来)我们将看到一个更好的解决方案：在存储过程中使用游标。

Let’s create the first SP in MySQL Workbench as follows:

让我们在MySQL Workbench中创建第一个SP，如下所示：

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
    declare current_win char(1);
    declare current_streak int;
    declare current_status char (1);

    declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

    set current_streak=0;

    open cur;

    fetch cur into current_win;
    set current_streak = current_streak +1;

    start_loop: loop
        fetch cur into current_status;
            if current_status <> current_win then 
                leave start_loop;
            else
                set current_streak=current_streak+1;
            end if;

    end loop;

    close cur;

    select current_streak into longeststreak;
    select current_win into `status`;
END

In this SP, we have one input parameter and two output parameters. This defines the signature of the SP.

在此SP中，我们有一个输入参数和两个输出参数。这定义了SP的签名。

In the SP body, we also declared a few local variables to hold the streak status (win or lose, current_win), current streak and current win/lose status for a particular match.

在SP主体中，我们还声明了一些局部变量来保存特定比赛的连胜状态(赢或输， current_win )，当前连胜和当前赢/输状态。

declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

The above line is the cursor declaration. We declared a cursor named cur and the dataset bind to that cursor is the win/lose status for those matches played (thus its winlose column is either “W” or “L” instead of nothing) in a particular year ordered by id (the latest played games will have the highest ID) descending.

上一行是游标声明。我们声明了一个名为cur的游标，并且绑定到该游标的数据集是按id排序的特定年份中所进行的那些比赛的获胜/失败状态(因此其winlose “ W”或“ L”而不是什么)最新玩过的游戏的ID最高)降序。

Though not displayed explicitly, we can imagine that this dataset will contain a series of “L”s and “W”s. Based on the data shown in Figure 2 above, it should be: “LLLLLLWLL…” (6 Ls, 1 Ws, etc).

尽管没有明确显示，但我们可以想象该数据集将包含一系列“ L”和“ W”。根据上面图2中显示的数据，它应该是：“ LLLLLLWLL…”(6 L，1 W等)。

To calculate the win/lose streak, we begin with the latest (and the first in the dataset) match data. When a cursor is opened, it always starts at the first record in the associated dataset.

为了计算赢/输连胜，我们从最新的(也是数据集中的第一个)比赛数据开始。打开游标时，它总是从关联数据集中的第一条记录开始。

After the first data is grabbed, the cursor will move to the next record. In this way, a cursor behaves very much like a queue, traversing the dataset in a FIFO (First In First Out) manner. This is exactly what we wanted.

抓取第一个数据后，光标将移至下一个记录。这样，游标的行为非常类似于队列，以FIFO(先进先出)的方式遍历数据集。这正是我们想要的。

After getting the current win/lose status and set the streak number, we continue to loop through (traverse) the remainder of the dataset. With each loop iteration, the cursor will “point” to the next record until we break the loop or all the records are consumed.

在获得当前的输赢状态并设置连胜号码后，我们继续遍历(遍历)数据集的其余部分。每次循环迭代时，游标将“指向”下一条记录，直到我们中断循环或消耗完所有记录为止。

If the next win/lose status is the same as the current win/lose status, it means the streak goes on and we increase the streak number by 1 and continue the traversing; otherwise, it means the streak discontinues and we can leave the loop earlier.

如果下一个获胜/失败状态与当前获胜/失败状态相同，则表示连胜继续，我们将连胜数增加1并继续遍历；否则，这意味着条纹将中断，我们可以更早地退出循环。

Finally, we close the cursor and release the resources. Then we return the desired output.

最后，我们关闭游标并释放资源。然后，我们返回所需的输出。

Next, we can enhance the access control of the SP as described in my previous article.

接下来，我们可以按照我之前的文章中所述，增强对SP的访问控制。

To test the output of this SP, we will write a short PHP script:

为了测试此SP的输出，我们将编写一个简短PHP脚本：

<?php
$dbms = 'mysql';

$host = 'localhost';
$db = 'sitepoint';
$user = 'root';
$pass = 'your_pass_here';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$cn->exec('call streak(2013, @longeststreak, @status)');
$res=$cn->query('select @longeststreak, @status')->fetchAll();

var_dump($res); //Dump the output here to get a raw view of the output

$win=$res[0]['@status']='L'?'Loss':'Win';
$streak=$res[0]['@longeststreak'];

echo "Lakers is now $streak consecutive $win.\n";

This will output something like the following figure:

这将输出如下图所示的内容：

(This output is based on Lakers’ match up to Jan 15th, 2014.)

(此输出基于截至2014年1月15日的湖人比赛。)

从存储过程返回数据集 (Return a dataset from a Stored Procedure)

A few discussions went along on how to return a dataset from an SP, which constructs the dataset out of the results from a few repeated calls to another SP.

进行了一些讨论，讨论如何从SP返回数据集，这从重复调用另一个SP的结果中构造数据集。

A user may want to know more from our previously created SP that only returns a win/lose streak for one year; thus we can get a table showing the win/lose streaks for all the years in a form like:

用户可能想从我们之前创建的SP中了解更多信息，该SP仅返回一年的双赢/连败；因此，我们可以得到一张表格，以表格形式显示多年来所有年份的赢/输条纹：

YEAR	Win/Lose	Streak
2013	L	6
2012	L	4
2011	L	2

年	输赢	条纹
2013年	大号	6
2012年	大号	4
2011年	大号	2

(Well, a more useful result can be to return the longest win streak and loss streak in a particular season. This requirement can be easily expanded from the previous SP so I will leave it to interested parties to implement. For the purpose of this article, we will stick to the current win/loss streak.)

(嗯，更有用的结果可能是返回特定季节中最长的连胜和连败。此要求可以从以前的SP轻松扩展，因此我将其留给有兴趣的各方来实施。出于本文的目的，我们将坚持目前的赢/赔率。)

MySQL SP can only return scalar results (an integer, a string, etc), unless the result is returned by a select ... from ... statement (and it becomes a dataset). The issue here is that the table-form data we want to see does not exist in our current db structure and is constructed from another SP.

MySQL SP只能返回标量结果(整数，字符串等)，除非结果是由select ... from ...语句返回的(并且成为数据集)。这里的问题是，我们要查看的表形式数据在当前的db结构中不存在，而是由另一个SP构造的。

To tackle this, we need the help of a temporary table, or if situation allows and requires, a redundant table. Let’s see how we can achieve our target via a temporary table.

为了解决这个问题，我们需要一个临时表，或者在情况允许的情况下需要一个冗余表。让我们看看如何通过临时表实现目标。

First, we will create a second SP as shown below:

首先，我们将创建第二个SP，如下所示：

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
    declare cur_year, max_year, min_year int;

    select max(year), min(year) from lakers into max_year, min_year;

    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));

    set cur_year=max_year;

    year_loop: loop
        if cur_year<min_year then
            leave year_loop;
        end if;

        call streak(cur_year, @l, @s);
        insert into yearly_streak values (cur_year, @l, @s);

        set cur_year=cur_year-1;
    end loop;

    select * from yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;

END

A few key things to notice here:

这里需要注意的一些关键事项：

We determine the max year and min year by selecting from the table lakers;
我们通过从表中的lakers选择来确定最大年份和最小年份；
We created a temp table to hold the output, with the structure requested by the output (season, streak, win);
我们创建了一个临时表来保存输出，并具有输出所要求的结构( season ， streak ， win )；
In the loop, we first execute our previously created SP, with necessary parameters (call streak(cur_year, @l, @s);), then grab the data returned and insert into the temp table (insert into yearly_streak values (cur_year, @l, @s);).
在循环中，我们首先使用必要的参数( call streak(cur_year, @l, @s); )执行先前创建的SP，然后获取返回的数据并插入到临时表中( insert into yearly_streak values (cur_year, @l, @s); )。
Finally, we select from the temp table and return the dataset, then do some cleaning (DROP TEMPORARY TABLE IF EXISTS yearly_streak;).
最后，我们从临时表中选择并返回数据集，然后进行一些清理( DROP TEMPORARY TABLE IF EXISTS yearly_streak; ，则DROP TEMPORARY TABLE IF EXISTS yearly_streak; )。

To get the results, we create another short PHP script as shown below:

为了获得结果，我们创建了另一个简短PHP脚本，如下所示：

<?php
... // Here goes the db connection parameters

$cn=new PDO($dsn, $user, $pass);

$res=$cn->query('call yearly_streak')->fetchAll();

foreach ($res as $r)
{
    echo sprintf("In year %d, the longest W/L streaks is %d %s\n", $r['season'], $r['streak'], $r['win']);
}

And the display will be like:

显示屏将类似于：

Please note that the above is a bit different from calling our first SP.

请注意，以上内容与调用我们的第一个SP有所不同。

The first SP does not return a dataset but only two parameters. In that case, we use PDO exec then query to fetch the output; while in the second SP, we returned a dataset from the SP, so we use PDO query directly to invoke the call to the SP.

第一个SP不返回数据集，而仅返回两个参数。在这种情况下，我们使用PDO exec然后query以获取输出；而在第二个SP中，我们从SP返回了数据集，因此我们直接使用PDO query来调用对SP的调用。

Voila! We did it!

瞧！我们做到了！

结论 (Conclusion)

In this article, we dug further into MySQL stored procedures and took a look at the cursor functionality. We have demonstrated how to fetch scalar data by output parameters (defined as out var_name vartype in the SP declaration) and also fetch a calculated dataset via a temp table. During this process, a few statements otherwise used in stored procedures also surfaced.

在本文中，我们进一步研究了MySQL存储过程，并研究了游标功能。我们已经演示了如何通过输出参数(在SP声明中定义为out var_name vartype )获取标量数据，以及如何通过临时表获取计算出的数据集。在此过程中，还出现了一些其他用于存储过程的语句。

The official documentation on the syntax of stored procedure and various statements can be found on the MySQL website. To create a stored procedure, please refer to these documents and to understand the statements, please see here.

有关存储过程语法和各种语句的官方文档可以在MySQL网站上找到。要创建存储过程，请参考这些文档并了解声明，请参见此处。

Feel free to comment and let us know your thoughts!

随时发表评论，让我们知道您的想法！