SQL 存储过程对比 观看次数

Stored Procedures Vs. Views

提问by Vishal

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ?

我两个都用过，但我不清楚什么时候我应该更喜欢一个。我的意思是我知道存储过程可以接受参数……但实际上我们仍然可以使用视图执行相同的操作，对吗？

So considering performance and other aspects when and why should I prefer one over the other ?

那么考虑到性能和其他方面，我何时以及为什么应该更喜欢一个？

采纳答案by gbn

Well, I'd use stored proc for encapsulation of code and control permissions better.

好吧，我会使用存储过程来更好地封装代码和控制权限。

A view is not really encapsulation: it's a macro that expands. If you start joining views pretty soon you'll have some horrendous queries. Yes they canbe JOINed but they shouldn't..

视图并不是真正的封装：它是一个可扩展的宏。如果你很快就开始加入视图，你会遇到一些可怕的问题。是的，他们可以加入，但他们不应该......

Saying that, views are a tool that have their place (indexed views for example) like stored procs.

话虽如此，视图是一种工具，它有自己的位置（例如索引视图），就像存储过程一样。

回答by Matti Virkkunen

The advantage of views is that they can be treated just like tables. You can use WHERE to get filtered data from them, JOIN into them, et cetera. You can even INSERT data into them if they're simple enough. Views also allow you to index their results, unlike stored procedures.

视图的优点是可以像对待表一样对待它们。您可以使用 WHERE 从它们中获取过滤的数据，加入它们，等等。如果它们足够简单，您甚至可以将数据插入其中。与存储过程不同，视图还允许您为其结果建立索引。

回答by TheCodeKing

A View is just like a single saved query statement, it cannot contain complex logic or multiple statements (beyond the use of union etc). For anything complex or customizable via parameters you would choose stored procedures which allow much greater flexibility.

一个视图就像一个单独保存的查询语句，它不能包含复杂的逻辑或多个语句（除了使用联合等）。对于任何复杂或可通过参数自定义的内容，您可以选择具有更大灵活性的存储过程。

It's common to use a combination of Views and Stored Procedures in a database architecture, and perhaps for very different reasons. Sometimes it's to achieve backward compatibility in sprocs when schema is re-engineered, sometimes to make the data more manipulatable compared with the way it's stored natively in tables (de-normalized views).

在数据库体系结构中使用视图和存储过程的组合是很常见的，并且可能出于非常不同的原因。有时是为了在重新设计模式时在 sproc 中实现向后兼容性，有时是为了使数据与在表中本地存储的方式（非规范化视图）相比更易于操作。

Heavy use of Views can degrade performance as it's more difficult for SQL Server to optimize these queries. However it is possible to use indexed-views which can actually enhance performance when working with joins in the same way as indexed-tables. There are much tighter restrictions on the allowed syntax when implementing indexed-views and a lot of subtleties in actually getting them working depending on the edition of SQL Server.

大量使用视图会降低性能，因为 SQL Server 更难优化这些查询。但是，可以使用索引视图，这在以与索引表相同的方式使用连接时实际上可以提高性能。在实现索引视图时对允许的语法有更严格的限制，并且根据 SQL Server 的版本，在实际让它们工作时有很多微妙之处。

Think of Views as being more like tables than stored procedures.

认为视图更像是表而不是存储过程。

回答by Nasir

The main advantage of stored procedures is that they allow you to incorporate logic (scripting). This logic may be as simple as an IF/ELSE or more complex such as DO WHILE loops, SWITCH/CASE.

存储过程的主要优点是它们允许您合并逻辑（脚本）。该逻辑可能像 IF/ELSE 一样简单，也可能更复杂，例如 DO WHILE 循环、SWITCH/CASE。

回答by SidC

I correlate the use of stored procedures to the need for sending/receiving transactions to and from the database. That is, whenever I need to send data to my database, I use a stored procedure. The same is true when I want to update data or query the database for information to be used in my application.

我将存储过程的使用与从数据库发送/接收事务的需要相关联。也就是说，每当我需要将数据发送到我的数据库时，我都会使用存储过程。当我想更新数据或查询数据库以获取要在我的应用程序中使用的信息时，情况也是如此。

Database views are great to use when you want to provide a subset of fields from a given table, allow your MS Access users to view the data without risk of modifying it and to ensure your reports are going to generate the anticpated results.

当您想要提供给定表中的字段子集时，数据库视图非常适合使用，允许您的 MS Access 用户查看数据而没有修改数据的风险，并确保您的报告将生成预期的结果。

回答by Joachim VR

Views are useful if there is a certain combination of tables, or a subset of data you consistently want to query, for example, an user joined with its permissions. Views should in fact be treated as tables.

如果有特定的表组合或您一直想要查询的数据子集，例如，某个用户以其权限加入，则视图很有用。视图实际上应该被视为表格。

Stored procedures are pieces of sql code that are 'compiled', as it where, to run more optimally than a random other query. The execution plan of sql code in a stored procedure is already built, so execution runs slightly smoother than that of an ordinary sql statement.

存储过程是“编译”的 sql 代码片段，因为它比随机其他查询运行得更好。存储过程中sql代码的执行计划已经构建好了，所以执行起来比普通的sql语句要稍微流畅一些。

回答by Hibou57

Two rationales.

两个道理。

Use stored procedure instead of view if you don't want insertion to be possible. Inserting in a view may not give what it seems to do. It will insert in a table, a row which may not match the query from the view, a row which will then not appear in the view; inserted somewhere, but not where the statement make it seems.

如果您不想插入，请使用存储过程而不是视图。在视图中插入可能不会给出它似乎做什么。它将在表中插入一个可能与视图中的查询不匹配的行，然后将不会出现在视图中的行；插入某处，但不是声明所显示的位置。

Use a view if you can't use the result of a stored procedure from another stored procedure (I was never able to make the latter works, at least with MySQL).

如果您不能使用来自另一个存储过程的存储过程的结果，请使用视图（我永远无法使后者工作，至少在 MySQL 中）。