存储过程结构化的最佳实践

Question

存储过程结构化的最佳实践

23

作为一名主要编写C＃代码的开发人员，我在编写C＃代码时采用了一些良好的实践。但是，当我有时编写存储过程时，我很难将这些实践应用于存储过程代码。

在多个场合中，我继承了可怕的存储过程代码，前三或四层存储过程设置了一些临时表，并且大部分只是互相调用。没有真正的工作，只有几行代码。然后最后调用“最终”存储过程，一个3000-5000行SQL代码的巨兽。该代码通常存在许多代码异味，例如代码重复、错综复杂的控制流（又称意大利面条式代码）以及一个方法依次堆叠做了太多事情，没有清晰的分离一块工作从哪里开始和结束（甚至没有注释作为除数）。

我还注意到使用了被注释掉的select语句，从中间临时表中进行选择。这些选择可以在调试目的下重新打开，但需要在期望特定顺序返回结果集的任何调用代码之前删除。

显然，我的团队成员也共享着我缺乏良好SQL编写实践的问题。

那么...（真正的问题来了）...编写模块化可维护存储过程的良好实践是什么？

自制的实践方法和参考书籍/博客都可以。这些方法以及帮助执行某些任务的工具都可以。

让我们总结一些我没有找到良好实践的领域

模块化和封装（存储过程之间的通信是否真的要通过临时表进行？）
- 在C＃中，我使用程序集、类和带有访问修饰符的方法来实现这一点。
调试/测试（比修改调试目标更好吗？）
- 调试工具？
- 调试跟踪？
- 测试夹具？
强调代码/逻辑/数据/控制流使用代码的结构
- 在C＃中，我重构并拆分较小的方法，每个方法只执行一个逻辑任务。
代码复制

我大多数接触到的是SQL Server作为数据库管理系统，但欢迎提供DBMS通用答案或指出其他DBMS特性对上述情况有帮助的答案。

为了提供一些背景：我遇到的大多数存储过程都是在报告场景中，在这种场景下，基础是从大表中创建一些汇总值。但是在此过程中，您需要排除某些异常表中出现的某些值，添加尚未完成的某些内容表中的一些值，与去年进行比较（您能想象处理产品在年度之间更改部门的丑陋代码吗？）等等。

- Albin Sunnanbo

3

出于这些原因，以及无法在 SCM 中使用存储过程，我已经避免除触发器等最必要和明显的用途之外的所有情况。我们将业务逻辑保留在业务层（.NET）中，并仅将数据库用作数据存储库。 - gahooa

理想情况下，存储过程不应该调用其他存储过程。这就是业务逻辑层的作用。不幸的是，如果您已经有一个充满嵌套SP调用的数据库，这种想法并不能真正帮助到您。您是在寻求如何使当前模型更易于维护的建议，还是如何重构系统以更好地遵循最佳实践的建议？ - James Johnson

@gahooa 我尽可能地在.NET中完成了大部分工作（部分原因是这是我的主要领域），但在某些情况下，比如继承一个庞大的代码库或者当你有大量数据时，你几乎只能使用存储过程。 - Albin Sunnanbo

6

我非常强烈地认为，你应该把你的数据库视为.NET中的类一样对待成一个服务，并设置边界。也就是说，它有一个明确定义的接口，并保证所提供的内容的完整性。将存储过程纳入版本控制，并且限制或不允许访问基表是其中非常重要的一部分。尽可能多的约束条件应用于数据库层面，以确保数据库能够满足其保证。如果你把数据库当作一个简单的表集合来使用，那么这就是你能获得的全部内容。 - Cade Roux

3

如果可以的话，我会给@cade Roux的评论点赞一百万次。将数据库用作简单的存储设备是业余数据库设计师的标志。 - HLGEM

4个回答

7

使用临时表进行通信有时会导致代码出现问题。这些存储过程通常不能由用户单独运行，因为它们会相互干扰（如果您为不同存储过程的输入和输出重用临时表名称并且它们未被重新创建，或者如果您在两个不同的表模式中使用相同的名称）。它们很难进行故障排除 - 像任何功能一样，只有在必要时使用它们，并且不存在更好的替代方案。在 SQL Server 中，暂时使用真实的表也可能存在问题。

在 SQL Server 中，存储过程之间传递数据（而不仅仅是参数）可能会存在问题。现在有表值参数以及许多以前可能使用存储过程完成的事情现在可以使用内联表值函数或多语句表值函数来完成（通常优于存储过程）。

在 SQL Server 中，避免在大型行集上频繁使用标量函数和多语句表值函数-它们的性能不太好，因此在 C＃中可能很明显的模块化技术在这里并不适用。

我建议您查看Ken Henderson's Guru's Guide to SQL Server Stored Procedures - 该书于2002年出版，仍然具有有关数据库应用程序设计的大量有用信息。

- Cade Roux

也许我误解了，但是临时表可以在多个用户之间运行而不会出现问题。但也许你说的是一个特殊情况？ - Mark SQLDev

@Mark SQLDev 你是正确的 - 本地临时表是连接本地的 - 我发现临时表（和表变量）很难调试。当然，你需要决定什么时候材料化以及何时材料化，而不是留给优化器自己决定。你还需要跟踪命名，以免出现冲突等问题。 - Cade Roux

2

这是个好问题。作为一个C#开发人员，不得不涉足SQL，它似乎天生就与我所熟悉的C#最佳实践背道而驰。

常见表达式很适合在存储过程中隔离查询，但只能使用一次！这会导致你定义视图，但这样你就失去了封装性。

一个存储过程的结果集很难在另一个存储过程中使用，因此你可能会想编写表值函数。这增加了你的权限维护负担，并迫使你两次编写函数，一次作为函数，另一次作为调用该函数的存储过程。否则，你的数据访问层(DAL)将有不同的接口，具体取决于它是一个过程还是其他类型的内容。

所有这些原因，在长时间的开发中，我都坚持使用简单的CRUD存储过程(不相互调用)，以及当关系复杂时，只使用少量的孤立查询。其他所有内容都在业务逻辑层(BLL)中处理。

从物理上讲，SQL通过功能或其所涉及的表在单独的文件中进行隔离，并在源代码控制中进行管理。

避免使用SELECT *，更喜欢指定列。这可以避免在更改表时遇到运行时问题但未更新所有过程。是的，存储过程可以进行重新编译，但它肯定会忽略一些过程，特别是当与视图有关时。此外，SELECT *几乎总是返回比你实际需要的更多的列，这是带宽的浪费。

- n8wrl

1

SQL的最佳实践绝对不同于您在C#中使用的最佳实践。试图在关系型世界中使用C#的最佳实践是导致数据库性能不佳的原因之一。数据库的设计旨在最大化性能，而不是使可维护性更容易。 - HLGEM

当您需要在存储过程中多次使用临时表时，可以使用临时表代替CTE。 - HLGEM

1

上面的评论提供了关于SQL代码编写时应该做和不应该做的很好的建议。如果我理解你的问题正确，你是在问SQL开发人员是否正常在单个存储过程中写入数百甚至数千行代码。在C#中，这是绝对不允许的。你应该使用方法、程序集和类来封装逻辑成小块。SQL开发人员倾向于在一个存储过程中编写整个逻辑以完成相关任务；正如HLGEM上面提到的，“如果可能，不要嵌套存储过程”。不要嵌套视图。

例如：在C#中，简单的获取和插入设计如下：

- 调用GetData方法 - 调用GetData方法 - 调用Transform Data方法 - 调用CheckAlphaNumeric方法 - 调用数据增强方法 - 调用Load Transformed Data方法

SQL开发人员会这样设计：在单个存储过程中：

- 获取数据并使用临时表或表变量进行转换，然后将其加载到最终表中。

如果要更改SQL的编写方式以匹配C#开发人员的编写结构，则需要这样做：

执行主存储过程（调用下面的存储过程）：

执行 GetData 存储过程并加载到 Stage 表中
执行 Transform 存储过程，读取 Stage 表并转换数据
执行 Load Data 存储过程将 Staged 或已转换的数据加载到最终表中。

- Dax B

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HLGEM · Accepted Answer

我写了很多复杂的存储过程。以下是我认为的最佳实践：

除非你正在进行一个带有许多可能不需要的参数的搜索存储过程（那么它目前是最佳解决方案之一），否则不要在存储过程中使用动态SQL。如果必须在存储过程中使用动态SQL，请始终使用一个调试输入参数。如果设置了调试参数，则打印创建的SQL语句而不是执行它。这将节省数小时的调试时间！

如果在存储过程中执行了多个操作查询（插入/更新/删除），请使用Try Catch块和事务处理。向输入参数添加一个测试参数，并将其设置为1时，总是回滚整个事务。在测试模式下回滚之前，我通常会有一个部分来返回我正在影响的表中的值，以确保我对数据库所做的操作与我想象中的一致。或者，您可以像下面所示一样进行检查。只需在已注释掉的选择周围放入以下代码（并取消注释它们），一旦拥有@test参数即可。

If @test =1
Begin
Select * from table1 where field1 = @myfirstparameter
End

现在你不必每次测试都要去注释和取消注释了。

@test或@debug应该始终设置默认值为0，并放在列表的最后一个。这样添加它们就不会破坏现有的proc调用。

考虑为执行插入/更新/删除的procs设置日志记录和/或错误日志表。如果您在操作过程中将步骤和/或错误记录在表变量中，即使回滚后仍然可以将它们插入日志表中。知道复杂proc的哪个部分失败以及错误是什么可能在以后非常有价值。

在可能的情况下，请勿嵌套存储的procs。如果您需要在循环中运行多个记录，请使用具有表值参数的存储的proc替换其中一个，并设置proc以集合方式而不是单个记录方式运行。如果表值参数有一个记录或多个记录，则可以使用此方法。

如果有一个具有许多子查询或派生表的复杂select，请考虑改用公用表表达式（CTE）。将任何相关的子查询或游标重构为性能更好的基于集合的代码。始终从数据集合的角度思考，而不是一个记录。

在任何可想象的情况下，请不要嵌套视图。性能损失比节省的开发时间严重得多。而且相信我，当改变需要针对视图链中最远的视图时，嵌套视图不会节省维护时间。

所有存储的procs（和其他数据库代码）都应该处于源代码控制中。

对于较小的数据集来说，表变量效果很好，但是在大型数据集中，使用临时表（真正以#或##开始的表，而不是暂时存储表）可能更好。如果使用临时表，请在不需要它们时将其删除。尽量避免使用全局临时表。

学习编写高性能的SQL。一旦掌握了技巧，编写性能良好的SQL通常比编写性能差的SQL容易得多。如果您编写复杂的存储的procs，则没有任何借口不知道哪些技术比其他技术更有效。学习如何确保您的查询是可搜索的。避免游标、相关子查询、标量函数和其他按行运行的操作。