SQL Server存储过程

SQL Server存储过程

SQL Server 中视图通过简单的 SELECT 查询来解决复杂的查询，但是视图不能提供业务逻辑功能，而存储过程可以办到这点。

什么是存储过程？

存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的 SQL 语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

存储过程的优点

下面是一些在使用存储过程的主要优点：

好处说明
模块化编程可以写一个存储过程一次，然后一次又一次地调用它，从应用程序的不同部分（甚至多个应用程序）。
性能
存储过程提供更快的代码执行和减少网络流量。
  • 更快的执行：存储过程进行解析，并为他们创造尽快优化和存储过程是存储在内存中。这意味着它会执行比从应用程序到SQL Server发送SQL多行代码快了很多。这样做需要SQL Server来编译和每次运行时优化改进你的SQL代码。
  • 减少网络流量：如果通过网络发送的SQL多行代码到你的SQL Server中，这会影响网络性能。如果你有数以百计的SQL代码行和/或你有很多活动在应用程序，这是尤其如此。运行SQL Server上的代码（作为存储过程中）消除了需要发送此代码在网络上。唯一的网络通信将提供的参数和任何查询的结果。
安全用户无需执行任何直接的语句可以执行存储过程。因此，存储过程可以谁也不能正常访问这些任务的用户提供先进的数据库功能，但这种功能是在严格控制的方式提供。

SQL Server 创建一个存储过程

我们需要使用 CREATE PROCEDURE 语句创建一个存储过程，接着要补充存储过程的代码，如果存储过程将要接受参数，它们需要被包括在名称后，如下：

CREATE PROCEDURE myStoredProcedure AS
...

OR

CREATE PROCEDURE myStoredProcedure @{Parameter Name} {data type} AS
...

详细示例

下述代码创建了一个被称为 “LatestTasks” 的存储过程。
它接受一个参数名为 @Count. 当调用这个存储过程，通过 @count 参数，它决定你想要多少行返回。
代码如下：

CREATE PROCEDURE LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks, DateCreated
FROM Tasks
ORDER BY DateCreated DESC

在SQL Server管理套件运行这段代码，会看到它被在存储过程节点创建为 “LatestTasks”。

在SQL Server 2014，可以在存储过程节点/文件夹中创建通过右键单击一个存储过程，选择存储过程....这将打开一个模板，这是随时可以填入自己的具体程序。

SQL Server 执行存储过程

创建了存储过程后，要在任何时候执行它，可以执行或者调用 EXEC。如果存储过程的参数要求提供这些程序名在后面，像这样：

EXECUTE LatestTasks
EXEC LatestTasks

EXEC LatestTasks @Count = 5

详细示例

在下面的例子中，我们在同一时间执行两次存储过程。
我们第一次调用它的时候 @Count 传递值为 3，第二次传递的值为 5。
该截图显示，通过传递参数（和值），存储过程返回结果的基础上提供值。顶端结果集返回3行，因为我们通过值3。第二个结果集返回5行，因为我们提供一个值5：

SQL Server 使用GUI

还可以使用图形用户界面来执行存储过程。

具体方法如下：

  1. 使用对象资源管理器，浏览到存储过程
  2. 右键单击该存储过程并选择 Execute Stored Procedure...:

  1. 会出现一个对话框。输入您所选择的参数值：

  1. 点击 OK
  2. SQL Server 现在会生成 SQL 代码并执行存储过程。

SQL Server 修改存储过程

如果需要修改现有的存储过程，只需更换掉 CREATE ，使用 ALTER。
我们在 “Latest” 和 “Tasks”间添加一个空格（即“Latest Tasks”），并添加描述字段，如下：

ALTER PROCEDURE LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS "Latest Tasks", Description, DateCreated
FROM Tasks
ORDER BY DateCreated DESC

SQL Server 系统存储过程

SQL Server 包含了大量的系统存储过程，以帮助数据库管理任务。
通过 GUI 执行的任务可以通过系统存储过程来完成。
例如，有些东西可以用系统存储过程的包括：

  1. 配置安全帐户
  2. 建立链接服务器
  3. 创建一个数据库维护计划
  4. 创建全文检索目录
  5. 添加远程登录
  6. 配置复制
  7. 设置调度作业
  8. 以及更多...

SQL Server 命名约定

一起来看看扩展系统存储过程节点，我们发现，他们的名字都以 sp 开始，这样的命名表明它是一个存储过程。
该系统存储过程显然遵循的命名约定，在存储过程制定一个一致的命名约定是好的，但是每个人的命名习惯都有不同。
有些人前缀的存储过程 usp，另外其他人使用 SQL 关键字，如 SELECT，INSERT，UPDATE，DELETE；也有人使用的缩写是一些下划线（例如，latest_tasks）。
因此，我们的存储过程可以被命名为以下任意一种，这取决于命名约定的使用。

  • LatestTasks
  • latest_tasks
  • uspLatestTasks
  • usp_latest_tasks
  • selectLatestTasks
  • select_LatestTasks
  • select_latest_tasks
  • getLatestTasks
  • get_latest_tasks

不管选择哪一种，都要保持一致性，这样才会在需要使用存储过程时显得更加容易使用。
所以这是存储过程覆盖。在下一节中，我们将会了解用户登录。