我们可以将参数传递给 SQL 中的视图吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1687279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can we pass parameters to a view in SQL?
提问by arunachalam
Can we pass a parameter to a view in Microsoft SQL Server?
我们可以将参数传递给 Microsoft SQL Server 中的视图吗？
I tried to create viewin the following way, but it doesn't work:
我尝试create view通过以下方式，但它不起作用：
create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;
回答by Alex Bagnolini
As already stated you can't.
如前所述，你不能。
A possible solution would be to implement a stored function, like:
一个可能的解决方案是实现一个存储函数，例如：
CREATE FUNCTION v_emp (@pintEno INT)
RETURNS TABLE
AS
RETURN
SELECT * FROM emp WHERE emp_id=@pintEno;
This allows you to use it as a normal view, with:
这允许您将其用作普通视图，具有：
SELECT * FROM v_emp(10)
回答by Gavin
There are 2 ways to acheive what you want unfortunatly neither can be done using a view.
不幸的是，有两种方法可以实现您想要的目标，但使用视图都无法做到。
You can either create a table valued user defined function that takes the parameter you want and returns a query result
您可以创建一个表值用户定义函数，该函数采用您想要的参数并返回查询结果
Or you can do pretty much the same thing but create a stored procedure instead of a user defined function.
或者您可以做几乎相同的事情，但创建一个存储过程而不是用户定义的函数。
For Example
例如
the stored procedure would look like
存储过程看起来像
CREATE PROCEDURE s_emp
(
@enoNumber INT
)
AS
SELECT
*
FROM
emp
WHERE
emp_id=@enoNumber
Or the user defined function would look like
或者用户定义的函数看起来像
CREATE FUNCTION u_emp
(
@enoNumber INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
*
FROM
emp
WHERE
emp_id=@enoNumber
)
回答by Thorsten Dittmar
No you can't, as Mladen Prajdic said. Think of a view as a "static filter" on a table or a combination of tables. For example: a view may combine tables Orderand Customerso you get a new "table" of rows from Orderalong with new columns containing the customer's name and the customer number (combination of tables). Or you might create a view that selects only unprocessed orders from the Ordertable (static filter).
不，你不能，正如 Mladen Prajdic 所说。将视图视为表或表组合上的“静态过滤器”。例如：一个视图可以组合表Order，Customer所以你会得到一个新的行“表”Order以及包含客户姓名和客户编号（表的组合）的新列。或者您可以创建一个仅从Order表中选择未处理订单的视图（静态过滤器）。
You'd then select from the view like you would select from any other "normal" table - all "non-static" filtering must be done outside the view (like "Get all the orders for customers called Miller" or "Get unprocessed orders that came in on Dec 24th").
然后，您可以像从任何其他“普通”表中选择一样从视图中进行选择 - 所有“非静态”过滤都必须在视图之外完成（例如“获取名为 Miller 的客户的所有订单”或“获取未处理的订单” 12 月 24 日进来”）。
回答by Lukasz Szozda
Normally views are not parameterized. But you could always inject some parameters. For example using session context:
通常视图没有参数化。但是你总是可以注入一些参数。例如使用会话上下文：
CREATE VIEW my_view
AS
SELECT *
FROM tab
WHERE num = SESSION_CONTEXT(N'my_num');
Invocation:
调用：
EXEC sp_set_session_context 'my_num', 1;
SELECT * FROM my_view;
And another:
还有一个：
EXEC sp_set_session_context 'my_num', 2;
SELECT * FROM my_view;
The same is applicable for Oracle (of course syntax for context function is different).
这同样适用于 Oracle（当然上下文函数的语法不同）。
回答by Mahesh
Why do you need a parameter in view? You might just use WHEREclause.
为什么在视图中需要参数？您可能只使用WHERE子句。
create view v_emp as select * from emp ;
and your query should do the job:
并且您的查询应该可以完成这项工作：
select * from v_emp where emp_id=&eno;
回答by Bozonik
A hacky way to do it without stored procedures or functions would be to create a settings table in your database, with columns Id, Param1, Param2, etc. Insert a row into that table containing the values Id=1,Param1=0,Param2=0, etc. Then you can add a join to that table in your view to create the desired effect, and update the settings table before running the view. If you have multiple users updating the settings table and running the view concurrently things could go wrong, but otherwise it should work OK. Something like:
在没有存储过程或函数的情况下执行此操作的一种hacky 方法是在您的数据库中创建一个设置表，其中包含 Id、Param1、Param2 等列。在该表中插入一行，其中包含值 Id=1,Param1=0,Param2 =0 等。然后您可以在视图中向该表添加连接以创建所需的效果，并在运行视图之前更新设置表。如果您有多个用户更新设置表并同时运行视图，事情可能会出错，但否则它应该可以正常工作。就像是：
CREATE VIEW v_emp
AS
SELECT *
FROM emp E
INNER JOIN settings S
ON S.Id = 1 AND E.emp_id = S.Param1
回答by Mladen Prajdic
no. if you must then use a user defined function to which you can pass parameters into.
不。如果您必须使用用户定义的函数，您可以将参数传递给该函数。
回答by MartW
No, a view is queried no differently to SELECTing from a table.
不，视图的查询与从表中选择没有什么不同。
To do what you want, use a table-valued user-defined functionwith one or more parameters
要执行您想要的操作，请使用带有一个或多个参数的表值用户定义函数
回答by Kris
A view is nothing more than a predifined 'SELECT' statement. So the only real answer would be: No, you cannot.
视图只不过是一个预定义的“SELECT”语句。所以唯一真正的答案是：不，你不能。
I think what you really want to do is create a stored procedure, where in principle you can use any valid SQL to do whatever you want, including accept parameters and select data.
我认为你真正想做的是创建一个存储过程，原则上你可以使用任何有效的 SQL 来做你想做的任何事情，包括接受参数和选择数据。
It seems likely that you really only need to add a where clause when you select from your view though, but you didn't really provide enough details to be sure.
当您从视图中进行选择时，您似乎真的只需要添加一个 where 子句，但您并没有真正提供足够的细节来确定。
回答by Reza Ameri
As I know view can be something just like select command. You also can add parameters to this select for example in where statements like this:
据我所知，视图可以像选择命令一样。您还可以向此选择添加参数，例如在如下语句中：
WHERE (exam_id = @var)
