为什么select语句应该在存储过程中使用而不是从表或视图中进行选择

本文介绍了为什么select语句应该在存储过程中使用而不是从表或视图中进行选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我见过人们在存储过程中使用select语句而不是从views / tables中选择。 示例。假设有一个名为xview的视图。 而不是选择*来自xview， 他们这样做： 创建过程spSelect As 从xview中选择* 结束 然后他们执行spSelect to返回相同的结果。 问题 1.以这种方式使用任何一种的利弊是什么 2.应该从视图中选择哪些实例，反之亦然。 我尝试过： 我还没有尝试过，但我只是不明白为什么他们这样做

I've seen people using select statements in stored procedures instead of selecting from views/tables. Example. Suppose there's a view with name xview. Instead of witting "select * from xview", They do this: Create procedure spSelect As Select * from xview End Then they execute spSelect to return the same result. Question 1. What are the pros and cons of using either in such ways 2. What instances should selecting from views be used and vice versa. What I have tried: I haven't tried that yet but I just don't understand why they do that

推荐答案

在旧版本的SQL中存储过程中包含的'某些'选择语句有一个好处，因为它们被编译以提供更好的性能。 我从未见过如此简单，你。我怀疑即使在旧版本中这也会有所帮助。 在较新版本的SQL中，所有查询都经过编译以获得更好的性能，无论它们是否是在存储过程中与否。这种做法几乎完全没有意义。 我说'几乎'非常刻意。如果这不是出于纯习惯，则可能存在（或可能）某些内部常见实践，或者需要（或需要）存储过程的自定义查询执行器。这些可能性背后的原因可能很少或很多。 我能想到的唯一其他可能性是SQL存储过程中包含许多业务逻辑。如果是这种情况，那么非常值得。 例如： 大多数客户只想要一张桌子上的所有东西，但有些人必须拥有并排除'columnname = value'。根据选择的复杂程度，您可以在SP中包含该逻辑，而无需更改软件。当你销售这样的产品时非常有用 In older versions of SQL there was a benefit to 'some' select statements being included in stored procedures because these were 'compiled' to give better performance. I've never seen one quite so simple, tho. I doubt even in older versions that this would help. In newer versions of SQL, all queries are 'compiled' for better performance, regardless of whether or not they are in a stored procedure or not. This practice is almost entirely pointless. I say 'almost' quite deliberately. If this isn't done out of pure habit then there are (or were) probably some internal common practices, or a custom query executor that requires (or required) stored procedures. The reasons behind these possibilities could be few or many. The only other possibility I can think of is that much of the business logic is contained in the SQL Stored Procedures. If this is the case then it is very worth while. Example: Most clients just want everything from a table but some must have and 'columnname=value' excluded. Depending on how complex that select is then you can include that logic in the SP without having the change the software. VERY useful when you sell a product like that

除了Andy上面所说的，它还有助于安全和维护，通过单一访问点保持一切。最好只允许通过SP访问代码，而不是直接访问SP和视图，并且具有随之而来的复杂性。 In addition to what Andy says above, it helps with security and maintenance to keep everything through a single point of access. It's better to only ever allow code to be accessed via SPs rather than having both SPs and Views directly accessed and having the complications that come with that.

添加到以前的帖子。我也找不到很好的理由将程序用作设计原则。应该有真正的原因。 特别是处理SELECT语句的程序经常变得笨拙。考虑一下您希望根据某些条件接收一组行的情况。你被绑定到程序的参数，如果你想添加使用新标准的能力，你需要在程序中更改参数和查询。 数据库结构的整体更改会导致客户端发生更改。在许多情况下，程序添加了一个额外的层，需要更改，从而导致额外的工作。 什么是安全性，是程序可用于防止来电者看到基础表。然而，一个观点也是如此。这是视图设计的一件事。 如果我们考虑外部选择，那么程序可能会变得方便。例如： - 如果您需要在同一次调用期间更改多个表或行 - 如果您需要将业务逻辑包含在更改操作中，则过程是一个很好的候选者为此 - 如果你需要阻止客户端直接对表进行修改。如上所述，视图也可以隐藏表格，但更复杂的视图可能无法更新，因此在这种情况下，您需要访问表格本身或创建另一个视图以进行更改。 影响程序选择的一个重要因素是事务处理。如果事务在数据库服务器上启动和结束，则过程可能是强制性的。特别是如果包括多个操作。如果在客户端处理交易，那么您可以更自由地选择。 To add to previous posts. I also can't find very good reasons to use procedures as a design principle. There should be real reasons behind. Especially procedures handling SELECT statement often become clumsy. Think about a situation where you expect to receive a set of rows based on some criteria. You're tied to the parameters the procedure has and if you want to add ability to use a new criteria, you need to change the parameters along with the query inside the procedure. In overall changes in the database structure causes changes on the client. In many cases the procedures add an extra layer that needs to be changed thus causing extra work. What comes to security, yes procedures can be used to prevent the caller from seeing the underlying tables. However so can a view. This is one thing what the view is designed for. If we think outside selecting, then procedures may become handy. For example: - if you need to change several tables or rows during the same call - if you need to include business logic into change operations, procedure is a good candidate for that - if you need to prevent the client side for making modifications directly to tables. As said a view can also hide the table but more complex views may not be updatable so in such case you'd need t give access to the table itself or create another view for changes. One important thing that affects the choice for procedure is transaction handling. If the transactions are started and ended on the database server, then procedures may be mandatory. Especially if multiple operations are included. If the transaction is handled on the client side then you can choose more freely.