SQL Prompt是一款实用的SQL语法提示工具。SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索,为用户提供合适的代码选择。自动脚本设置使代码简单易读——当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用,能大幅提高编码效率。
Louis Davidson揭示了一些有用的提示功能,可在开发过程中重构单个代码块或模块,这将提高代码质量,减少繁琐的工作,使测试更简单,有时甚至是全部三种功能。
SQL Prompt是我一直使用的工具,用于代码完成和代码格式化任务,以至于在没有它的情况下编写查询感觉非常错误。用JOIN编写SQL语句,它会自动建议ON条件。在ORDER BY或者GROUP BY子句中,它有助于选择分组列。审查一些丑陋的代码(也许是自己写的)用您喜欢的样式很好地格式化它。
但是,SQL Prompt具有许多其他功能,这些功能我很少用,但在需要的时候非常有用。本教程演示了SQL Prompt如何帮助“小规模” SQL重构任务,该任务的范围通常仅限于您在本地工作的单批代码或代码对象。这些任务直接在SSMS查询窗口中执行,并且可以帮助您以各种方式重构代码,这些方式在测试存储过程和对存储过程进行故障排除时非常有用。这篇文章主要是教程的上半部分内容~
本文中的所有示例均从WideWorldImporters示例数据库的新恢复的副本开始。
重命名别名/变量
提示:将光标置于别名或变量上,从上下文菜单中选择“重命名别名/变量”,或按F2键,然后输入新名称。当前批次或对象中的所有匹配项均已更改。
有时,程序员为变量或别名选择了较差的名称。我已经看到@I,如果仔细检查,可以更有效地将其称为@EnableSystemFunctionsSetting。我们一次又一次地感到内疚,当您需要快速进行编码时,标准往往会失效,而我们常常忘记暂停,并仔细考虑可能使用的最佳名称。
下列代码显示了一个应用程序。Cities$List存储过程后面是第二个批处理(只是为了演示这种重命名的工作方式)。假设您想将@CityName重命名为@CityNameLike,使用户清楚的知道他们可以传递LIKE表达式。
CREATE OR ALTER PROCEDURE Application.Cities$List @CityName nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS DECLARE @CityNameValue nvarchar(50); SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityName; GO DECLARE @CityName int; SELECT @CityName GO
清单1
当然,一种久经考验并几乎是正确的方法是查找并替换(Ctrl + H)。但是,如果您不注意,可能会导致一些参数意外地重命名为@CityNameLikeUpperCaseFlag,并且在当前对象的预期范围之外重命名参数和变量。
图1
使用SQL Prompt的重命名别名/变量重构,您可以更精确地执行此任务。突出显示@CityName的出现项,或将光标放在其上,它仅突出显示当前范围内@CityName的另一确切出现项。与查找和替换不同,它不会突出显示任何不完全匹配的内容,并且不会触及第二批。
图2
现在,右键单击选择重命名别名/变量,或按F2,然后在矩形框内看到变量时,键入替换名称CityNameLike,然后按Enter以“提交”更改名称。如果输入有误,则可以使用单个“撤消”(Ctrl-Z)修复损坏。
接下来,您可能需要将Cities表的别名更改为较短的名称,例如c。如果单击的任何实例Cities,则SQL Prompt将突出显示其他实例,但由于Cities不是别名,因此SQL Prompt的Rename Alias / Variable方法将不起作用。不过,这很容易解决。只需建立Cities一个别名,然后突出显示它并点击F2即可。
图3
替换Cities为c,单击Enter,新代码将如下所示。
CREATE OR ALTER PROCEDURE Application.Cities$List @CityNameLike nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS DECLARE @CityNameValue nvarchar(50); SELECT c.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(c.CityName) ELSE c.CityName END, c.Location FROM Application.Cities AS c WHERE c.CityName LIKE @CityNameLike; GO
查找未使用的变量和参数
提示:在查询窗口中,可以选择突出显示代码块,从SQL Prompt菜单中选择“查找未使用的变量和参数”,或按(Ctrl B,Ctrl F),然后Prompt将在所有未使用的变量或参数下划线。
在图3中,您可以在下面看到一条绿色的波浪线@CityNameValue(和过程名称,稍后再介绍)。这是SQL Prompt代码分析功能,可提醒我们注意已声明但从未使用过的变量。
图4
即使您关闭了此功能(在使用非常大的脚本时,有时也会这样做),您仍然可以使用“查找未使用的变量和参数”(Ctrl B + Ctrl F),并且在@CityNameValue变量下方会看到相同的绿色波浪线,和相同的消息。让我们简单地删除该变量声明,然后继续进行此代码的更多改进。
使用代码片段进行重构
SQL Prompt的许多内置代码片段都包含$ SELECTEDTEXT $占位符。如果您在查询窗口中突出显示任何代码并调用这些代码段之一,它将把选定的文本插入到代码段内的占位符中。例如,我们可以将任何选定的代码插入:
-
BEGIN…END块(通过调用be片段)
-
IF块(ifs)
-
TRY…CATCH块(tc)
-
CTE(cte)
-
创建视图(cv)
-
创建内联表值函数(citf)
我将演示几个示例,并说明如何制作自己的代码片段,就像这些一样,您可以通过几次击键进行调用。
添加一个BEGIN…END块
前面清单中第一行代码下的另一条绿色花样是另一个SQL Prompt“警告”,该代码提示其内置代码分析规则之一。在这种情况下,这是一种样式规则(ST003),建议将过程主体包含在中BEGIN…END中。
图5
这不是存储过程的要求,但是正如规则(ST003)中的说明告诉我们的那样,多行用户定义函数需要它,因此保持一致是一个很好的做法。无论如何,我认为一个BEGIN…END块有助于弄清楚什么是对象中的代码,哪些不是对象的代码。
要解决此问题,只需在过程中突出显示查询,然后从SQL Prompt的“操作”菜单中调用be片段即可。或者,对于BEGIN…END、 WHILE 和IF,您只需右键单击代码,然后从上下文菜单中选择“环绕声”(Ctrl-K,Ctrl-S)。
图6
现在,BAM,过程主体位于一个BEGIN...END块中。在进行此操作时,我还使用了“插入分号” 操作向该END语句添加了分号。
CREATE OR ALTER PROCEDURE Application.Cities$List @CityNameLike nvarchar(50) = '%', @CityNameUpperCaseFlag bit = 0 AS BEGIN SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityNameLike; END;
清单3
将查询重构为代码模块
SQL Prompt还可以轻松地以视图、内联表值函数或公用表表达式的形式将查询重构为可重用的逻辑。例如,突出显示清单4中的查询…
SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers;
清单4
并调用cv(创建视图)代码段,您的查询现在包含在视图定义的开始部分。
CREATE VIEW [schema].[view_name] --WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA AS SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers; -- WITH CHECK OPTION GO
清单5
这很好地提醒了视图定义的重要部分,并且在给它命名后就可以在这里正常工作,但是在大多数情况下,这只是一个基本的起点。另外,没有语法验证,因此您可以很高兴地调用cv片段将清单6转换为视图,即使它不会编译,因为没有变量声明,即使有,也没有变量在视图中允许。
SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers WHERE @CustomerId = 1;
清单6
相反,您可能需要使用“创建内联表函数”(citf)代码段。
CREATE FUNCTION [schema].[function_name] (@parameter_name AS INT) RETURNS TABLE --WITH ENCRYPTION|SCHEMABINDING, ... AS RETURN ( SELECT CustomerID, CustomerName, BillToCustomerID, CustomerCategoryID FROM Sales.Customers WHERE @CustomerId = 1; ) GO
清单7
同样,这是创建函数的一个有用的开始,特别是因为我经常忘记创建任何类型函数的语法,但是当然,如果需要,您仍然需要处理@CustomerId变量并将其作为参数。
使用自定义片段进行重构
您可以轻松构建自己的代码片段来重构一段代码。例如,我经常需要做的一件事就是使用多行注释符号注释掉大块代码。
这是一个非常简单的自定义代码片段。要创建它,只需浏览SQL Prompt | Snippet Manager…| New…,为代码段添加一个初始名称和说明,然后输入代码段代码。创建此代码后,我只需要突出显示要注释掉的块,从“提示操作”列表中选择“co”,文本将被包含在注释中,我还将看到谁注释掉了文本,并且什么时候。
图7
对于这样的代码段,我还可以看到很多其他用途,例如变量声明(我创建了多少次@msg保存一条THROW语句的消息?)
内联执行:重构以进行测试
提示:在查询窗口中,突出显示存储过程的EXEC命令、标量函数或sp_executesql函数,然后从右键单击内容菜单中选择Inline Exec。SQL Prompt用等效的内联代码替换它。
假设我们要测试应用程序Cities$List存储过程,因此我们对该过程的调用进行了编码,如下所示:
EXECUTE Application.Cities$List @CityNameLike = 'Nash%'
清单8
这会返回大量数据,并且您想更深入地看一下代码,因为感觉有些不对劲。突出显示Cities$List,右键单击并选择Inline Exec。该过程的参数将被转换为变量,并将使用您传入的变量值(或者默认值,如果您已经省略了一个参数,就像我一样),如清单9所示。
DECLARE @CityNameLike1 nvarchar(50), @CityNameUpperCaseFlag1 bit; SET @CityNameLike1 = 'Nash%'; SET @CityNameUpperCaseFlag1 = NULL; BEGIN SELECT Cities.CityID, CASE WHEN @CityNameUpperCaseFlag1 = 1 THEN UPPER(Cities.CityName) ELSE Cities.CityName END, Cities.Location FROM Application.Cities WHERE Cities.CityName LIKE @CityNameLike1; END;
清单9
这可能不是要做的最复杂的重构,但这是一项常见而繁琐的任务,尤其是当您有很多参数并且一个或多个具有默认值时。
摘要
SQL Prompt包含许多不错的工具,可以帮助您重构代码,这些工具都很小,很容易每天使用。作为程序员,有时很难不仅仅依赖于查找和替换,但是在SQL Prompt中,我发现更多类似这些工具的工具时,在做事不那么简单的情况下,我发现它们越有用。