[MySQL]：存储过程和select语句

I'm looking into stored procedures at the moment.

我正在研究存储过程。

According to this article (page 8) in the dev section of the mysql website...

根据mysql网站开发部分的这篇文章（第8页）...

Ordinarily, it's not normal to put SELECT statements in stored procedures, this is for illustration. I decided that some procedure should simply select from our table, so that when you call the procedure it will be obvious that it's working.

通常，将SELECT语句放在存储过程中是不正常的，这只是为了说明。我决定一些程序应该只从我们的表中选择，这样当你调用程序时，很明显它正在工作。

Why is that?

这是为什么？

Is using Stored-procedures to simplify complicated select statements not 'best-practices'?

使用存储过程来简化复杂的选择语句而不是“最佳实践”吗？

What are some specific situations where it is beneficial to use a stored procedure? Example?

在哪些特定情况下使用存储过程是有益的？例？

2 个解决方案

#1

Generally stored procedures are intended for complex processing in the database. There are debates raging about their benefits. I never saw that SELECTs in a stored procedure was a bad thing but I wouldn't expect that every single SQL statement that has to be written goes into a stored procedure either. It should be reserved for those processing that involve multiple statements and will have to be performed repeatedly.

通常，存储过程用于数据库中的复杂处理。关于他们的好处的争论很激烈。我从来没有看到存储过程中的SELECT是一件坏事，但我不希望每个必须写入的SQL语句都进入存储过程。它应该保留给涉及多个语句的处理，并且必须重复执行。

Jeff has a rant about them here.

杰夫在这里对他们大吼大叫。

To answer your direct question for specific examples, I have found that I avoid them because of the portability issue. I attempt to do all my processing application side. At the same time I do not have to worry about network bandwidth in my application so each situation is different.

为了回答您直接问题的具体示例，我发现由于可移植性问题我避免使用它们。我尝试做所有处理应用程序。同时我不必担心应用程序中的网络带宽，因此每种情况都不同。

#2

A specific situation where it is beneficial to use Stored Procedure/Routines is that it can provide error checking on parameters similar to functions in OO paradigm. It gives added 'Encapsulation'

使用存储过程/例程有利的特定情况是它可以提供与OO范例中的函数类似的参数的错误检查。它增加了'封装'

A simple example:

一个简单的例子：

CREATE PROCEDURE select_table(IN @id INT)
BEGIN
  IF @id < O THEN
    -- ERROR!  do something here
  ELSEIF
    SELECT * from TABLE WHERE id = @id;
  END IF
END

#1