存储过程和视图有什么区别? [英] What is the difference between a stored procedure and a view?

问题描述

我对以下几点感到困惑:

I am confused about a few points:

1. 存储过程和视图有什么区别?

1. What is the difference between a stored procedure and a view?

在 SQL Server 中什么时候应该使用存储过程，什么时候应该使用视图?

When should I use stored procedures, and when should I use views, in SQL Server?

视图是否允许创建我们可以传递参数的动态查询?

Do views allow the creation of dynamic queries where we can pass parameters?

哪个最快，哪个比另一个快?

Which one is the fastest, and on what basis is one faster than the other?

视图或存储过程会永久分配内存吗?

Do views or stored procedures allocate memory permanently?

如果有人说视图创建虚拟表，而过程创建材料表，这是什么意思?

What does it mean if someone says that views create a virtual table, while procedures create a materials table?

如果有的话，请告诉我更多的点.

Please let me know about more points, if there are any.

推荐答案

一个视图代表一个虚拟表.您可以在一个视图中连接多个表，并使用该视图来呈现数据，就像数据来自单个表一样.

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

存储过程使用参数来执行函数...无论是更新和插入数据，还是返回单个值或数据集.

A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Creating Views and Stored Procedures - 有一些来自 Microsoft 的信息，如何时以及为何使用每个.

Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each.

假设我有两张桌子:

• tbl_user，列:user_id、user_name、user_pw
• tbl_profile，列:profile_id、user_id、profile_description

• tbl_user, with columns: user_id, user_name, user_pw
• tbl_profile, with columns: profile_id, user_id, profile_description

所以，如果我发现自己从这些表中查询了很多......而不是在每条 SQL 中都进行连接，我会定义一个像这样的视图:

So, if I find myself querying from those tables A LOT... instead of doing the join in EVERY piece of SQL, I would define a view like:

CREATE VIEW vw_user_profile
AS
  SELECT A.user_id, B.profile_description
  FROM tbl_user A LEFT JOIN tbl_profile B ON A.user_id = b.user_id
GO

因此，如果我以后想通过user_id查询profile_description，我所要做的就是:

Thus, if I want to query profile_description by user_id in the future, all I have to do is:

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

该代码可用于如下存储过程:

That code could be used in a stored procedure like:

CREATE PROCEDURE dbo.getDesc
    @ID int
AS
BEGIN
    SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
END
GO

所以，稍后，我可以调用:

So, later on, I can call:

dbo.getDesc 25

然后我会得到 user_id 25 的描述，其中 25 是你的参数.

and I will get the description for user_id 25, where the 25 is your parameter.

显然还有很多细节，这只是基本思路.

There is obviously a lot more detail, this is just the basic idea.

这篇关于存储过程和视图有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！