我们能在SQL中向视图传递参数吗？

Question

我们能否在Microsoft SQL Server中向视图传递参数？

我尝试以以下方式创建视图，但它不起作用：

create or replace view v_emp(eno number) as select * from emp where emp_id=&eno;

- arunachalam

---

一个 view 是一个存储了 select 查询的 SQL 文本。参数不在讨论范围内。当你的存储查询返回你想要过滤的列时，你可以在调用查询中进行操作。例如："SELECT * FROM v_emp WHERE emp_id = ?" - Epicurist

@ Epicurist“参数不在讨论范围内”这是一个过于大胆的陈述。反例 - Lukasz Szozda

使用存储过程怎么样？ - farid wahyu

Answer 1

正如之前所述，你无法这样做。

一个可能的解决方案是实现一个存储函数，例如：

CREATE FUNCTION v_emp (@pintEno INT)
RETURNS TABLE
AS
RETURN
   SELECT * FROM emp WHERE emp_id=@pintEno;

这使得你可以将其作为普通视图来使用，包括：

SELECT * FROM v_emp(10)

- Alex Bagnolini

---

这与视图有哪些实际差异？您可以分配用户权限仅访问此功能吗？ - MikeMurko

在MySQL中，您编写存储过程并将存储过程的最后一个语句作为要返回的结果集。 - bobobobo

@MikeMurko 一个重要的区别是，如果是视图，则可以查询有关视图列的模式/元数据。如果是存储过程或函数，则数据库可能无法提供该信息。 - nagu

如果您有一组用户可以访问您的数据库，而且您不希望他们运行“select * from [view]”并影响性能，那么您可以授予对某些函数的访问权限，这将强制他们提供过滤参数，例如利用某个索引集。 - Jmoney38

很抱歉打断讨论。对我来说，这是一个有趣的解决方案，但我想知道性能方面哪个更好，使用函数表还是存储过程？ - farid wahyu

Answer 2

有两种方法可以实现你想要的。不幸的是，这两种方法都不能使用视图来完成。

你可以创建一个表值用户定义函数，该函数接受你想要的参数并返回查询结果

或者你可以做几乎相同的事情，但创建一个存储过程而不是一个用户定义函数。

例如：

存储过程可能如下所示：

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
SELECT
    * 
FROM
    emp 
WHERE 
    emp_id=@enoNumber

或者用户定义的函数将看起来像这样

CREATE FUNCTION u_emp
(   
    @enoNumber INT
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT    
        * 
    FROM    
        emp 
    WHERE     
        emp_id=@enoNumber
)

- Gavin

---

请记住，在SELECT中很难轻松地使用SP选项：阅读更多。 - saastn

Answer 3

通常视图是不带参数的。但您可以注入一些参数。例如，使用会话上下文：

CREATE VIEW my_view
AS
SELECT *
FROM tab
WHERE num = SESSION_CONTEXT(N'my_num');

调用：

EXEC sp_set_session_context 'my_num', 1; 
SELECT * FROM my_view;

另一个例子：

EXEC sp_set_session_context 'my_num', 2; 
SELECT * FROM my_view;

DBFiddle演示

对于Oracle也适用（当然上下文函数的语法是不同的）。

- Lukasz Szozda

---

我认为这非常方便。就像在Java中可以将参数传递给Web应用程序一样。 - 8forty

易于使用且功能强大！换句话说...完美！谢谢！ - Riccardo Bassilichi

我累了。在视图中添加 WHERE COUL = SESSION_CONTEXT(N'Ket'); 会导致错误“SESSION_CONTEXT”不是已识别的内置函数名称。 - user123456

@user123456 你必须使用SQL Server 2016及以上版本或Azure SQL数据库。 - Lukasz Szozda

Answer 4

根据Mladen Prajdic所说，不能这样做。将视图视为表格或多个表格的“静态过滤器”。例如：视图可以将Order和Customer表格结合起来，以便您获得新的行“表格”来自Order加上包含客户名称和客户号码的新列（表格的组合）。或者你可以创建一个视图，仅从Order表中选择未处理的订单（静态过滤器）。

然后，您就可以像从任何其他“常规”表格中选择一样从视图中进行选择-所有“非静态”过滤必须在视图外部完成（例如，“获取所有名为Miller的客户的订单”或“获取12月24日到达的未处理订单”）。

- Thorsten Dittmar

Answer 5

为什么在视图中需要参数？您可以使用WHERE子句。

create view v_emp as select * from emp ;

并且您的查询应该能够完成工作：

select * from v_emp where emp_id=&eno;

- Mahesh

---

在某些情况下，如果是针对表的WHERE而不是视图的WHERE，性能会有很大提升。 - Doug_Ivison

虽然Doug所说的有一定道理，但现代数据库可以聪明地“扩展”视图，并最终以与手动执行完整查询相同的结果。因此不要假设它会低效，因为数据库可能会让你惊喜-查看生成的查询计划。一个值得注意的例外是，如果视图具有影响输出的GROUP BY子句，则无法从“外部”执行WHERE。 - Simon_Weaver

Answer 6

一种不需要存储过程或函数的方法是在您的数据库中创建一个设置表，包含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

- Bozonik

---

使用它来请求查看会很糟糕。但是作为配置/阶段/环境，使用这样的隐藏参数确实非常可用。对我来说是一个加分项。 - TPAKTOPA

Answer 7

不要这样做。如果必须这样做，那么请使用用户定义的函数，并将参数传递给该函数。

- Mladen Prajdic

Answer 8

我们可以编写一个带有输入参数的存储过程，然后使用该存储过程从视图中获取结果集。 请参见下面的示例。

存储过程如下：

CREATE PROCEDURE [dbo].[sp_Report_LoginSuccess] -- [sp_Report_LoginSuccess] '01/01/2010','01/30/2010'
@fromDate datetime,
@toDate datetime,
@RoleName varchar(50),
@Success int
as
If @RoleName != 'All'
Begin
   If @Success!=2
   Begin
   --fetch based on true or false
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName)) and Success=@Success
   End
   Else
   Begin
    -- fetch all
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  And RTrim(Upper(RoleName)) = RTrim(Upper(@RoleName))
   End

End
Else
Begin
   If @Success!=2
   Begin
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
  and Success=@Success
 End
 Else
 Begin
  Select * from vw_Report_LoginSuccess
  where logindatetime between  dbo.DateFloor(@fromDate) and dbo.DateSieling(@toDate)
 End

End

我们可以获取结果集的视图是：

CREATE VIEW [dbo].[vw_Report_LoginSuccess]
AS
SELECT     '3' AS UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101) AS LoginDateTime,
                      CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
                      dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM         dbo.tblLoginStatusDetail INNER JOIN
                      dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
                      dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                      dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE     (dbo.tblLoginStatusDetail.Success = 0)
UNION all
SELECT     dbo.tblLoginStatusDetail.UserDetailID, dbo.tblLoginStatusDetail.Success, CONVERT(varchar, dbo.tblLoginStatusDetail.LoginDateTime, 101)
                      AS LoginDateTime, CONVERT(varchar, dbo.tblLoginStatusDetail.LogoutDateTime, 101) AS LogoutDateTime, dbo.tblLoginStatusDetail.TokenID,
                      dbo.tblUserDetail.SubscriberID, dbo.aspnet_Roles.RoleId, dbo.aspnet_Roles.RoleName
FROM         dbo.tblLoginStatusDetail INNER JOIN
                      dbo.tblUserDetail ON dbo.tblLoginStatusDetail.UserDetailID = dbo.tblUserDetail.UserDetailID INNER JOIN
                      dbo.aspnet_UsersInRoles ON dbo.tblUserDetail.UserID = dbo.aspnet_UsersInRoles.UserId INNER JOIN
                      dbo.aspnet_Roles ON dbo.aspnet_UsersInRoles.RoleId = dbo.aspnet_Roles.RoleId
WHERE     (dbo.tblLoginStatusDetail.Success = 1) AND (dbo.tblUserDetail.SubscriberID LIKE N'P%')  

- sunil

Answer 9

据我所知，视图可以像select命令一样使用。您也可以向此选择添加参数，例如在where语句中，如下所示:

 WHERE  (exam_id = @var)

- Reza Ameri

Answer 10

不，查询视图与从表中SELECT没有任何区别。

如果要实现你想要的功能，请使用具有一个或多个参数的表值用户定义函数。

- MartW