SQL 存储过程和视图有什么区别？

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

提问by NoviceToDotNet

I am confused about a few points:

我对以下几点感到困惑：

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

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

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

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

5. Do views or stored procedures allocate memory permanently?

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

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

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

3. 视图是否允许创建动态查询，我们可以在其中传递参数？

4. 哪一个最快，一个比另一个快的依据是什么？

5. 视图或存储过程是否永久分配内存？

6. 如果有人说视图创建一个虚拟表，而过程创建一个材料表，这意味着什么？

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

请让我知道更多的点，如果有的话。

回答by Patrick

A view represents a virtualtable. 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- has some information from Microsoft as to when and why to use each.

创建视图和存储过程- 有一些来自微软的关于何时以及为什么使用它们的信息。

Say I have two tables:

假设我有两个表：

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

• tbl_user, 列：user_id, user_name,user_pw
• tbl_profile, 列：profile_id, user_id,profile_description

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:

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

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

Thus, if I want to query profile_descriptionby user_idin the future, all I have to do is:

因此，如果我想查询profile_description由user_id在将来，所有我需要做的就是：

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

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

我将获得user_id25的描述，其中25是您的参数。

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

显然还有很多细节，这只是基本的想法。

回答by cusimar9

Plenty of info available here

这里有很多可用的信息

Here is a good summary:

这是一个很好的总结：

A Stored Procedure:

存储过程：

• Accepts parameters
• Can NOTbe used as building block in a larger query
• Can contain several statements, loops, IF ELSE, etc.
• Can perform modifications to one or several tables
• Can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

• 接受参数
• 能不被用作一个大的查询积木
• 可以包含多个语句、循环、IF ELSE 等。
• 可以对一张或几张表进行修改
• 不能用作 INSERT、UPDATE 或 DELETE 语句的目标。

A View:

一个看法：

• Does NOTaccept parameters
• Can be used as building block in a larger query
• Can contain only one single SELECT query
• Can NOTperform modifications to any table
• But can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

• 难道不接受参数
• 可以在更大的查询中用作构建块
• 只能包含一个 SELECT 查询
• 可以不进行修改的任何表
• 但可以（有时）用作 INSERT、UPDATE 或 DELETE 语句的目标。

回答by Mahesh

First you need to understand, that both are different things. Stored Proceduresare best used for INSERT-UPDATE-DELETEstatements. Whereas Viewsare used for SELECTstatements. You should use both of them.

首先你要明白，两者是不同的东西。Stored Procedures最适合用于INSERT-UPDATE-DELETE语句。而Views用于SELECT语句。你应该同时使用它们。

In views you cannot alter the data.Some databases have updatable Views where you can use INSERT-UPDATE-DELETEon Views.

在视图中，您不能更改数据。某些数据库具有可更新的视图，您可以INSERT-UPDATE-DELETE在其中使用Views.

回答by reggie

A SQL View is a virtual table, which is based on SQL SELECT query. A view references one or more existing database tables or other views. It is the snap shot of the database whereas a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

SQL View 是一个虚拟表，它基于 SQL SELECT 查询。一个视图引用一个或多个现有的数据库表或其他视图。它是数据库的快照，而存储过程是一组编译成单个执行计划的 Transact-SQL 语句。

View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed.

视图是简单地展示存储在数据库表中的数据，而存储过程是一组可以执行的语句。

A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements.

视图更快，因为它显示引用的表中的数据，而存储过程执行 sql 语句。

Check this article : View vs Stored Procedures. Exactly what you are looking for

查看这篇文章：查看与存储过程。正是您要找的

回答by Aaron Digulla

A view is a simple way to save a complex SELECTin the database.

视图是一种SELECT在数据库中保存复杂数据的简单方法。

A store procedure is used when simple SQL just isn't enough. Store procedures contain variables, loops and calls to other stored procedures. It's a programming language, not a query language.

当简单的 SQL 还不够时使用存储过程。存储过程包含变量、循环和对其他存储过程的调用。它是一种编程语言，而不是查询语言。

1. Views are static. Think of them as new tables with a certain layout and the data in them is created on the fly using the query you created it with. As with any SQL table, you can sort and filter it with WHERE, GROUP BYand ORDER BY.

2. The depends on what you do.

3. The depends on the database. Simple views just run the query and filter the result. But databases like Oracle allow to create a "materialized" view which is basically a table which is updated automatically when the underlying data of the view changes.

   A materialized view allows you to create indexes on the columns of the view (especially on the computed columns which don't exist anywhere in the database).

4. I don't understand what you're talking about.

1. 视图是静态的。将它们视为具有特定布局的新表，其中的数据是使用您创建的查询即时创建的。与任何 SQL 表一样，您可以使用WHERE,GROUP BY和对其进行排序和过滤ORDER BY。

2. 这取决于你做什么。

3. 这取决于数据库。简单视图只运行查询并过滤结果。但是像 Oracle 这样的数据库允许创建一个“物化”视图，它基本上是一个表，当视图的基础数据发生变化时会自动更新。

   物化视图允许您在视图的列上创建索引（尤其是在数据库中不存在的计算列上）。

4. 我不明白你在说什么。

回答by rsc

Main difference is that when you are querying a view then it's definition is pasted into your query. Procedure could also give results of query, but it is compiled and for so faster. Another option are indexed views..

主要区别在于，当您查询视图时，它的定义会粘贴到您的查询中。过程也可以给出查询结果，但它是编译的，所以速度更快。另一种选择是索引视图。

回答by Glyn Roberts

Mahesh is not quite correct when he suggests that you can't alter the data in a view. So with patrick's view

Mahesh 建议您不能更改视图中的数据时，他的观点并不完全正确。所以在帕特里克看来

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

I CAN update the data ... as an example I can do either of these ...

我可以更新数据......作为一个例子，我可以做这些......

Update vw_user_profile Set profile_description='Manager' where user_id=4

or

或者

Update tbl_profile Set profile_description='Manager' where user_id=4

You can't INSERT to this view as not all of the fields in all of the table are present and I'm assuming that PROFILE_ID is the primary key and can't be NULL. However you can sometimes INSERT into a view ...

您不能插入此视图，因为并非所有表中的所有字段都存在，并且我假设 PROFILE_ID 是主键并且不能为 NULL。但是，您有时可以插入视图...

I created a view on an existing table using ...

我使用...在现有表上创建了一个视图

Create View Junk as SELECT * from [TableName]

THEN

然后

Insert into junk (Code,name) values 
('glyn','Glyn Roberts'),
('Mary','Maryann Roberts')

and

和

DELETE from Junk Where ID>4

Both the INSERT and the DELETE worked in this case

INSERT 和 DELETE 在这种情况下都有效

Obviously you can't update any fields which are aggregated or calculated but any view which is just a straight view should be updateable.

显然，您不能更新任何聚合或计算的字段，但任何只是直接视图的视图都应该是可更新的。

If the view contains more than one table then you can't insert or delete but if the view is a subset of one table only then you usually can.

如果视图包含多个表，则不能插入或删除，但如果视图只是一个表的子集，则通常可以。

回答by Ajendra Prasad

In addition to the above comments, I would like to add few points about Views.

除了上面的评论，我想补充几点关于视图。

1. Views can be used to hide complexity. Imagine a scenario where 5 people are working on a project but only one of them is too good with database stuff like complex joins. In such scenario, he can create Views which can be easily queried by other team members as they are querying any single table.
2. Security can be easily implemented by Views. Suppose we a Table Employeewhich contains sensitive columns like Salary, SSN number. These columns are not supposed to be visible to the users who are not authorized to view them. In such case, we can create a View selecting the columns in a table which doesn't require any authorization like Name, Ageetc, without exposing sensitive columns (like Salary etc. we mentioned before). Now we can remove permission to directly query the table Employeeand just keep the read permission on the View. In this way, we can implement security using Views.

1. 视图可用于隐藏复杂性。想象一个场景，其中 5 个人正在处理一个项目，但只有其中一个人对复杂联接等数据库内容太擅长了。在这种情况下，他可以创建视图，其他团队成员可以在查询任何单个表时轻松查询这些视图。
2. 视图可以轻松实现安全性。假设我们有一个表Employee，其中包含诸如Salary、SSN number 之类的敏感列。这些列不应该对无权查看它们的用户可见。在这种情况下，我们可以创建一个视图，选择不需要任何授权的表中的列，如Name，Age等，而不会暴露敏感列（如我们之前提到的 Salary 等）。现在我们可以去掉直接查询Employee表的权限，只保留View的读权限。这样，我们就可以使用 Views 来实现安全性。

回答by swissben

1. A VIEW is a dynamic query where you can use a "WHERE"-Clause
2. A stored procedure is a fixed data selection, which returns a predefined result
3. Nor a view, nor a stored procedure allocate memory. Only a materialized view
4. A TABLE is just one ENTITY, a view can collect data from different ENTITIES or TABLES

1. VIEW 是一个动态查询，您可以在其中使用“WHERE”-Clause
2. 存储过程是一个固定的数据选择，它返回一个预定义的结果
3. 既不是视图，也不是存储过程分配内存。只有物化视图
4. 一个表只是一个实体，一个视图可以从不同的实体或表中收集数据

回答by Robbie Tapping

@Patrick is correct with what he said, but to answer your other questions a View will create itself in Memory, and depending on the type of Joins, Data and if there is any aggregation done, it could be a quite memory hungry View.

@Patrick 的说法是正确的，但要回答您的其他问题，一个视图将在内存中创建自己，并且取决于连接、数据的类型以及是否进行了任何聚合，它可能是一个非常需要内存的视图。

Stored procedures do all their processing either using Temp Hash Table e.g #tmpTable1 or in memory using @tmpTable1. Depending on what you want to tell it to do.

存储过程使用临时哈希表（例如#tmpTable1）或在内存中使用@tmpTable1 进行所有处理。取决于你想告诉它做什么。

A Stored Procedure is like a Function, but is called Directly by its name. instead of Functions which are actually used inside a query itself.

存储过程就像一个函数，但直接通过它的名字来调用。而不是在查询本身内部实际使用的函数。

Obviously most of the time Memory tables are faster, if you are not retrieveing alot of data.

显然，如果您不检索大量数据，大多数时候 Memory 表会更快。