为什么在关系数据库中不支持从存储过程中进行选择?

It is well known that you cannot perform a SELECT from a stored procedure in either Oracle or SQL Server (and presumably most other mainstream RDBMS products).

众所周知，您不能在Oracle或SQL Server(以及大多数其他主流RDBMS产品)中执行存储过程中的选择。

Generally speaking, there are several obvious "issues" with selecting from a stored procedure, just two that come to mind:

一般来说，从存储过程中进行选择有几个明显的“问题”，只有两个问题会出现在脑海中:

a) The columns resulting from a stored procedure are indeterminate (not known until runtime)

a)存储过程生成的列是不确定的(直到运行时才知道)

b) Because of the indeterminate nature of stored procedures, there would be issues with building database statistics and formulating efficient query plans

b)由于存储过程的不确定性，构建数据库统计数据和制定高效的查询计划都存在问题

As this functionality is frequently desired by users, a number of workaround hacks have been developed over time:

由于用户经常需要这个功能，因此随着时间的推移，已经开发了一些变通的技巧:

http://www.club-oracle.com/threads/select-from-stored-procedure-results.3147/

http://www.club-oracle.com/threads/select-from-stored-procedure-results.3147/

http://www.sommarskog.se/share_data.html

http://www.sommarskog.se/share_data.html

SQL Server in particular has the function OPENROWSET that allows you to join to or select from almost anything: https://msdn.microsoft.com/en-us/library/ms190312.aspx

SQL Server尤其具有OPENROWSET函数，允许您加入或选择几乎任何东西:https://msdn.microsoft.com/en-us/library/ms190312.aspx

....however, DBA's tend to be very reluctant to enable this for security reasons.

....然而，由于安全原因，DBA往往不太愿意启用它。

So to my question: while there are some obvious issues or performance considerations involved in allowing joins to or selects from stored procedures, is there some fundamental underlying technical reason why this capability is not supported in RDBMS platforms?

因此，我的问题是:尽管在允许连接到存储过程或从存储过程中选择过程中存在一些明显的问题或性能考虑因素，但是在RDBMS平台中不支持这种功能的根本技术原因是什么呢?

EDIT:
A bit more clarification from the initial feedback....yes, you can return a resultset from a stored procedure, and yes, you can use a (table valued) function rather than a stored procedure if you want to join to (or select from) the resultset - however, this is not the same thing as JoiningTo / SelectingFrom a stored procedure. If you are working in a database that you have complete control over, then you have the option of using a TVF. However, it is extremely common that you find yourself working in a 3rd party database and you are forced to call pre-existing stored procedures; or, often times you would like to join to system stored procedures such as: sp_execute_external_script (https://msdn.microsoft.com/en-us/library/mt604368.aspx).

编辑:更多的澄清从最初的反馈....是的，您可以从存储过程返回一个resultset，是的，如果您想要连接(或选择)resultset，您可以使用(表值)函数而不是存储过程——但是，这与从存储过程中加入/选择并不相同。如果您正在一个完全控制的数据库中工作，那么您可以选择使用TVF。然而，您发现自己在第三方数据库中工作是非常常见的，您不得不调用已存在的存储过程;或者，您经常希望加入系统存储过程，比如:sp_execute_external_script (https://msdn.microsoft.com/en-us/library/mt604368.aspx)。

EDIT 2:
On the question of whether PostgreSQL can do this, the answer is also no: Can PostgreSQL perform a join between two SQL Server stored procedures?

编辑2:关于PostgreSQL是否可以这样做的问题，答案也是否定的:PostgreSQL是否可以在两个SQL Server存储过程之间执行连接?

6 个解决方案

#1

TL;DR: you can select from (table-valued) functions, or from any sort of function in PostgreSQL. But not from stored procedures.

您可以从(表值)函数中选择，或者从PostgreSQL中的任何类型的函数中选择。但不是存储过程。

Here's an "intuitive", somewhat database-agnostic explanation, for I believe that SQL and its many dialects is too much of an organically grown language / concept for there to be a fundamental, "scientific" explanation for this.

这里有一个“直观的”，有点与数据库无关的解释，因为我相信SQL和它的许多方言都是一个有机的语言/概念，而这是一个根本的、“科学”的解释。

Procedures vs. Functions, historically

I don't really see the point of selecting from stored procedures, but I'm biased by years of experience and accepting the status quo, and I certainly see how the distinction between procedures and functions can be confusing and how one would wish them to be more versatile and powerful. Specifically in SQL Server, Sybase or MySQL, procedures can return an arbitrary number of result sets / update counts, although this is not the same as a function that returns a well-defined type.

我并没有真正理解从存储过程中进行选择的意义，但是我对多年的经验和对现状的接受有偏见，我当然也看到了过程和函数之间的区别是如何令人困惑的，以及人们希望它们更加通用和强大。特别是在SQL Server、Sybase或MySQL中，过程可以返回任意数量的结果集/更新计数，尽管这与返回定义良好的类型的函数不同。

Think of procedures as imperative routines (with side effects) and of functions as pure routines without side-effects. A SELECT statement itself is also "pure" without side-effects (apart from potential locking effects), so it makes sense to think of functions as the only types of routines that can be used in a SELECT statement.

把程序看作是命令式的例程(有副作用)，并将其作为无副作用的纯例程。SELECT语句本身也是“纯”的，没有副作用(除了可能的锁效应)，因此将函数视为SELECT语句中惟一可以使用的例程类型是有意义的。

In fact, think of functions as being routines with strong constraints on behaviour, whereas procedures are allowed to execute arbitrary programs.

实际上，可以将函数看作是对行为具有强烈约束的例程，而过程则允许执行任意的程序。

4GL vs. 3GL languages

Another way to look at this is from the perspective of SQL being a 4th generation programming language (4GL). A 4GL can only work reasonably if it is restricted heavily in what it can do. Common Table Expressions made SQL turing-complete, yes, but the declarative nature of SQL still prevents its being a general-purpose language from a practical, every day perspective.

另一种看待这个问题的方式是从SQL是第四代编程语言(4GL)的角度来看。4GL只有在其功能受到严格限制的情况下才能正常工作。常见的表表达式使SQL turing-complete，是的，但是SQL的声明性本质仍然阻止它作为一种通用语言，从实际的，每天的角度来看。

Stored procedures are a way to circumvent this limitation. Sometimes, you want to be turing complete and practical. So, stored procedures resort to being imperative, having side-effects, being transactional, etc.

存储过程是一种规避这种限制的方法。有时，你希望图灵是完整而实际的。因此，存储过程诉诸于必要的、具有副作用的、事务性的等等。

Stored functions are a clever way to introduce some 3GL / procedural language features into the purer 4GL world at the price of forbidding side-effects inside of them (unless you want to open pandora's box and have completely unpredictable SELECT statements).

存储函数是一种很聪明的方法，它可以将一些3GL /过程语言特性引入到更纯粹的4GL世界中，但代价是其内部会产生令人生畏的副作用(除非您想打开潘多拉的盒子并拥有完全不可预知的选择语句)。

The fact that some databases allow for their stored procedures to return arbitrary numbers of result sets / cursors is a trait of their allowing arbitrary behaviour, including side-effects. In principle, nothing I said would prevent this particular behaviour also in stored functions, but it would be very unpractical and hard to manage if they were allowed to do so within the context of SQL, the 4GL language.

一些数据库允许其存储过程返回任意数量的结果集/游标，这是它们允许任意行为(包括副作用)的一个特点。原则上，我所说的任何内容都不能阻止存储函数中的这种特殊行为，但是如果允许它们在SQL (4GL语言)上下文中这样做，那么将非常不实用，而且很难管理。

Thus:

因此:

• Procedures can call procedures, any function and SQL
• 过程可以调用过程、任何函数和SQL
• "Pure" functions can call "pure" functions and SQL
• “纯”函数可以调用“纯”函数和SQL。
• SQL can call "pure" functions and SQL
• SQL可以调用“纯”函数和SQL

But:

但是:

• "Pure" functions calling procedures become "impure" functions (like procedures)
• “纯”函数调用过程成为“不纯”函数(如过程)

And:

和:

• SQL cannot call procedures
• SQL不能调用过程
• SQL cannot call "impure" functions
• SQL不能调用“不纯”函数

Examples of "pure" table-valued functions:

Here are some examples of using table-valued, "pure" functions:

下面是使用表值“纯”函数的一些例子:

Oracle

CREATE TYPE numbers AS TABLE OF number(10);
/

CREATE OR REPLACE FUNCTION my_function (a number, b number)
RETURN numbers
IS
BEGIN
    return numbers(a, b);
END my_function;
/

And then:

然后:

SELECT * FROM TABLE (my_function(1, 2))

SQL Server

CREATE FUNCTION my_function(@v1 INTEGER, @v2 INTEGER)
RETURNS @out_table TABLE (
    column_value INTEGER
)
AS
BEGIN
    INSERT @out_table
    VALUES (@v1), (@v2)
    RETURN
END

And then

然后

SELECT * FROM my_function(1, 2)

PostgreSQL

Let me have a word on PostgreSQL.

让我来说说PostgreSQL。

PostgreSQL is awesome and thus an exception. It is also weird and probably 50% of its features shouldn't be used in production. It only supports "functions", not "procedures", but those functions can act as anything. Check out the following:

PostgreSQL非常棒，因此是一个例外。它也很奇怪，大概50%的特性不应该用于生产中。它只支持“函数”，不支持“过程”，但这些函数可以充当任何角色。看看下面的:

CREATE OR REPLACE FUNCTION wow ()
RETURNS SETOF INT
AS $$
BEGIN
    CREATE TABLE boom (i INT);

    RETURN QUERY
    INSERT INTO boom VALUES (1)
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

Side-effects:

副作用:

• A table is created
• 创建一个表
• A record is inserted
• 插入记录

Yet:

然而,:

SELECT * FROM wow();

Yields

收益率

wow
---
1

#2

Speaking only for Microsoft SQL Server: Stored Procedures and Functions (at least scalar UDFs and Multi-statement TVFs) are different constructs.

只针对Microsoft SQL Server:存储过程和函数(至少是标量udf和多语句TVFs)是不同的构造。

• Stored Procedures are pre-parsed query batches. You execute ad hoc queries batches or query batches saved in Stored Procedures. For example, from .NET there are two different Command Types: Text and StoredProcedure. You cannot just execute a Function.

  存储过程是预先解析的查询批次。执行存储过程中保存的临时查询批或查询批。例如，从。net中有两种不同的命令类型:文本和存储过程。你不能只执行一个函数。

• Functions, even TVFs (which, if they are Inline-TVFs, are essentially Views that can take parameters) are not independently runnable pieces of code. They are not queries in themselves and hence need to be called within a query.

  函数，甚至是TVFs(如果它们是内线TVFs，本质上就是可以接受参数的视图)都不是独立运行的代码片段。它们本身不是查询，因此需要在查询中调用。

  Also, unlike Stored Procedures, Functions can be optimized, meaning that they can be moved around the execution plan. The timing and frequency of their execution is not guaranteed to be how you specified in the query (such as, per row vs once and the result cached). In fact, this sometimes causes problems when non-deterministic results are desired but only a single value is returned for all rows. This is probably the main reason (of maybe a few) that Functions do not allow for changing state of the database and some other handy things: because you have no control over whether or not those things would actually happen or in what order, or how many times. Stored Procedures, on the other hand, are the execution plan.

  此外，与存储过程不同，函数可以优化，这意味着可以围绕执行计划移动它们。它们执行的时间和频率不能保证是您在查询中指定的方式(例如，每一行vs一次，结果缓存)。事实上，当需要不确定性结果时，这有时会导致问题，但对于所有行只返回一个值。这可能是函数不允许更改数据库状态和其他一些方便的事情的主要原因(可能是少数):因为您无法控制这些事情是否会实际发生，以什么顺序发生，或发生多少次。另一方面，存储过程是执行计划。

That being said, for what it's worth, it is possible to select from a Stored Procedure without using OPENQUERY / OPENROWSET, but it requires SQLCLR. In fact, most of the restrictions placed on T-SQL Functions can be overcome in SQLCLR code (such as "no Dynamic SQL"). However, this does not make SQLCLR functions immune from the Query Optimizer changing the timing and frequency of the execution from what you want / expect.

也就是说，不管怎样，从存储过程中选择不使用OPENQUERY / OPENROWSET是可能的，但是它需要SQLCLR。事实上，在SQLCLR代码(比如“无动态SQL”)中，可以克服对T-SQL函数的大多数限制。但是，这并不能使SQLCLR函数免受查询优化器的影响，因为查询优化器会根据您的期望更改执行的时间和频率。

#3

I don't think your question is really about stored procedures. I think it is about the limitations of table valued functions, presumably from a SQL Server perspective:

我不认为你的问题是关于存储过程的。我认为这是关于表值函数的局限性，大概是从SQL Server的角度来看:

• You cannot use dynamic SQL.
• 不能使用动态SQL。
• You cannot modify tables or the database.
• 不能修改表或数据库。
• You have to specify the output columns and types.
• 您必须指定输出列和类型。
• Gosh, you can't even use rand() and newid() (directly)
• 天哪，你甚至不能直接使用rand()和newid()

(Oracle's restrictions are slightly different.)

(Oracle的限制略有不同。)

The simplest answer is that databases are both a powerful querying language and an environment that supports ACID properties of transactional databases. The ACID properties require a consistent view, so if you could modify existing tables, what would happen when you do this:

最简单的答案是，数据库既是一种强大的查询语言，也是一种支持事务数据库的ACID属性的环境。ACID属性需要一个一致的视图，所以如果您可以修改现有的表，那么当您这样做时会发生什么:

select t.*, (select count(*) from functionThatModifiesT()) -- f() modifies "t"
from t;

Which t is used in the from? Actually, SQL Server sort of has answer to this question, but you get the same issue with multiple references in the same clause. In a sense, user defined functions are limited in the same way that this is not accepted:

哪个t在from中使用?实际上，SQL Server差不多已经回答了这个问题，但是在同一个子句中有多个引用也有同样的问题。从某种意义上说，用户定义的函数与不被接受的方式是有限的:

select a = 1, a + 1

Defining the semantics is very, very tricky and not worth the effort because there are other powerful features that are more important.

定义语义非常非常棘手，不值得花费精力，因为还有其他更重要的功能。

In my opinion, though, the final straw in SQL Server is the ability for stored procedures to "return" multiple result sets. That simply has no meaning in the world of tables.

但是，在我看来，SQL Server中的最后一根稻草是存储过程“返回”多个结果集的能力。这在表格的世界里毫无意义。

EDIT:

编辑:

Postgres's use of create function is very powerful. It does allow the function to modify the underlying database, which brings up interesting transactional issues. However, you still do have to define the columns and their types.

Postgres使用的create功能非常强大。它允许函数修改底层数据库，从而产生有趣的事务性问题。但是，仍然需要定义列及其类型。

#4

My experience is only with SQL Server, and pretty much only anecdotal based on my own usage...but what would you want to accomplish by SELECTing from a stored procedure in the first place? What is your use case?

我的经验只适用于SQL Server，而且几乎都是基于我自己的使用……但是，首先从存储过程中进行选择，您想要完成什么?你的用例是什么?

In my experience, stored procs are the results of what you select, not a source of selection in the first place. You create a stored procedure to return a result set and then in (usually in code) do something with that result set. Or call the stored procedure to do something other than selecting - INSERT or DELETE for example.

根据我的经验，存储procs是您选择的结果，而不是首选源。您创建一个存储过程来返回结果集，然后(通常是在代码中)对结果集做一些事情。

If you want to capture results of a query in TSQL and do further somethings with that you could put your SELECT statements in a CTE, or make a view to select from.

如果您想在TSQL中捕获查询的结果并进行进一步操作，您可以将SELECT语句放到CTE中，或者创建一个视图从中进行选择。

#5

A store procedure while it can and typically does return a result set should be thought of as a device to execute business logic. Views or Table functions should be used to provide the functionality you desire.

存储过程虽然可以而且通常会返回结果集，但应该将其看作是执行业务逻辑的设备。应该使用视图或表函数来提供所需的功能。

#6

In Oracle you can select from stored FUNCTIONS. They are strongly typed so you can treat them as regular subqueries. You typically have to use SELECT FROM TABLE (CAST (function_call(args) AS TABLE_COLL_TYPE))

在Oracle中，您可以从存储的函数中进行选择。它们是强类型的，因此您可以将它们视为常规子查询。通常必须使用SELECT FROM表(CAST (function_call(args)作为TABLE_COLL_TYPE))

Also, you can "join" with a stored function by using values from another table as arguments to the function, e.g.

此外，您还可以使用从另一个表中的值作为参数来“联接”一个存储函数，例如:

select t1.a, t1.b, func.c
from t1, table (function_call (a, b)) as func