当前位置： 文江博客话题详情

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

发布于 2024-10-20 20:14:06 字数 304 浏览 3 评论 0原文

我对以下几点感到困惑：

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

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

  3. 视图是否允许创建可以传递参数的动态查询？

  4. 哪一个是最快的，在什么基础上一个比另一个更快？

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

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

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

原文

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?

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

谁与争疯 2024-10-27 20:14:07

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

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

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

  2. 这取决于你做什么。

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

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

  4. 我听不懂你在说什么。

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

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.

  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 BY and 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.

回复 原文
小矜持 2024-10-27 20:14:07

当 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

我可以更新数据...作为一个例子，我可以执行其中任何一个...

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

或者

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

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

在现有表上创建了一个视图

Create View Junk as SELECT * from [TableName]

我使用... THEN

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

，并且

DELETE from Junk Where ID>4

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

显然，您无法更新任何聚合或计算的字段，但任何字段只是一个直接视图的视图应该是可更新的。

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

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

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 ...

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

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.

回复 原文
冰葑 2024-10-27 20:14:07
  1. 视图是一种动态查询，您可以在其中使用“WHERE”子句。
  2. 存储过程是固定数据选择，它返回预定义的结果。
  3. 视图或存储过程都不会分配内存。只有物化视图
  4. 一张表只是一个实体，一个视图可以从不同的实体或表收集数据
  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
回复 原文
明天过后 2024-10-27 20:14:07

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

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..

回复 原文
沉睡月亮 2024-10-27 20:14:07

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

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

存储过程类似于函数，但直接通过其名称进行调用。而不是查询本身内部实际使用的函数。

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

@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.

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.

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.

回复 原文
明月夜 2024-10-27 20:14:06

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

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

创建视图和存储过程 - 来自 Microsoft 的一些信息，说明何时以及为什么要使用每个。

假设我有两个表：

  • tbl_user，其中包含列：user_id、user_name、user_pw
  • tbl_profile，包含列：profile_id、user_id、profile_description

因此，如果我发现自己从这些表中查询很多......而不是这样做在每条 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

因此，如果我将来想通过 user_id 查询 profile_description，我所要做的就是：

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

该代码可以在存储过程中使用，例如：

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

因此，稍后，我可以调用：

dbo.getDesc 25

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

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

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 - 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

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

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

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.

回复 原文
柠檬色的秋千 2024-10-27 20:14:06

此处提供了大量信息

这里有一个很好的总结：

存储过程：

  • 接受参数
  • 不可以用作较大查询中的构建块
  • 可以包含多个语句、循环、IF ELSE 等。
  • 可以对一个或多个表执行修改
  • 不能用作 INSERT、UPDATE 或 DELETE 的目标
    陈述。

视图：

  • 是否不接受参数
  • 可用作较大查询中的构建块
  • 只能包含一个 SELECT 查询
  • 可以不执行修改任何表
  • ，但（有时）可以用作 INSERT、UPDATE 或
    删除语句。

Plenty of info available here

Here is a good summary:

A Stored Procedure:

  • Accepts parameters
  • Can NOT be 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.

A View:

  • Does NOT accept parameters
  • Can be used as building block in a larger query
  • Can contain only one single SELECT query
  • Can NOT perform modifications to any table
  • But can (sometimes) be used as the target of an INSERT, UPDATE or
    DELETE statement.
回复 原文
德意的啸 2024-10-27 20:14:06

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

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

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

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

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.

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.

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

回复 原文
吲‖鸣 2024-10-27 20:14:06

首先你需要明白，两者是不同的东西。 存储过程最适合用于INSERT-UPDATE-DELETE语句。而视图用于SELECT语句。你应该同时使用它们。

在视图中您无法更改数据。 某些数据库具有可更新的视图，您可以在视图上使用INSERT-UPDATE-DELETE。

First you need to understand, that both are different things. Stored Procedures are best used for INSERT-UPDATE-DELETE statements. Whereas Views are used for SELECT statements. You should use both of them.

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

回复 原文
清风无影 2024-10-27 20:14:06

除了上面的评论之外，我还想补充几点关于Views的观点。

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

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 Employee which 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, Age etc, without exposing sensitive columns (like Salary etc. we mentioned before). Now we can remove permission to directly query the table Employee and just keep the read permission on the View. In this way, we can implement security using Views.
回复 原文
~没有更多了~

关于作者

沙沙粒小

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

linfzu01

文章 0 评论 0

§对你不离不弃

文章 0 评论 0

可遇━不可求

文章 0 评论 0

枕梦

文章 0 评论 0

qq_3LFa8Q

文章 0 评论 0

JP

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文