【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解

本系列博文还在更新中，收录在专栏：#MS-SQL Server 专栏中。

本系列文章列表如下：

【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作
【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤
【SQL Server】数据库开发指南（二）MSSQL数据库开发对于库、表、数据类型、约束等相关操作
【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践
[ 云原生 | Docker ] 构建高可用性的 SQL Server：Docker 容器下的主从同步实现指南
【SQL Server】数据库开发指南（五）T-SQL 高级查询综合应用与实战
【SQL Server】数据库开发指南（六）索引和视图的使用技巧、方法与综合应用
【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解
【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究
【SQL Server】数据库开发指南（九）详细讲解 MS-SQL 触发器的基础概念与应用场景

前言

Transact-SQL（T-SQL）是一种用于 Microsoft SQL Server 和 Azure SQL Database 等关系数据库管理系统的扩展 SQL 语言。

在数据库开发中，存储过程是一个非常重要的概念，它是一个可重复使用的程序，可用于执行特定的任务和数据处理。它具有很多的优点，例如减少网络流量，维护方便，提高性能等等。在本文章中，我们将深入探讨存储过程的定义，种类以及使用方法。

一、什么是存储过程

存储过程（Procedure）是一组为了完成特定功能的 SQL 语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的 SQL 语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

1.1 存储过程的优点

存储过程提供了性能优化、代码复用、安全性、事务控制、网络传输减少等多个方面的优点，使得数据库应用程序更加高效、安全、可靠和易于维护。

总结起来存储结构的几个特点有：

特点	说明
提高性能	存储过程的语句在首次执行时会被编译和优化，并生成执行计划，下次执行时可以直接使用缓存中的执行计划，减少了编译和优化的开销，提高了执行效率。
代码复用	存储过程可以在多个地方被调用，实现了代码的复用，避免了重复编写相同的SQL语句，提高了开发效率。
安全性和权限控制	存储过程可以定义特定的权限，只有具有足够权限的用户才能执行存储过程，有效地保护数据库的安全性。
事务控制	存储过程可以将一系列数据库操作封装在一个事务中，保证这些操作要么全部执行成功，要么全部回滚，确保数据的一致性和完整性。
网络传输减少	存储过程在数据库服务器上执行，可以减少客户端与服务器之间的网络传输，降低了网络开销和延迟。
简化复杂操作	存储过程可以封装复杂的业务逻辑和数据处理过程，简化了客户端应用程序的开发和维护。
提高数据的一致性	通过存储过程执行数据操作，可以确保对数据的访问方式是一致的，避免了数据不一致性的问题。

二、存储过程的种类

2.1 SQL Server 系统存储过程

系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。

系统存储过程目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以sp下划线开头的存储过程。尽管这些系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。

常用系统存储过程有：

-- sp_help：显示数据库对象的详细信息，如表、视图、存储过程等。
EXEC sp_help 'TableName';
-- sp_helptext：显示存储过程、触发器、函数等对象的定义文本。
EXEC sp_helptext 'StoredProcedureName';
-- sp_who：显示当前活动的数据库会话和进程信息。
EXEC sp_who;
-- sp_renamedb：重命名数据库。
EXEC sp_renamedb 'OldDatabaseName', 'NewDatabaseName';
-- sp_adduser：在数据库中创建新用户。
EXEC sp_adduser 'UserName', 'Password', 'DatabaseName';
-- sp_addlogin：在SQL Server实例中创建新的登录账号。
EXEC sp_addlogin 'LoginName', 'Password', 'DefaultDatabase';
-- sp_change_users_login：修复数据库中的用户与登录账号之间的不一致性。
EXEC sp_change_users_login 'Auto_Fix', 'UserName', 'LoginName';
-- sp_spaceused：显示数据库或表的空间使用情况。
EXEC sp_spaceused 'TableName';
-- sp_configure：配置和修改SQL Server实例的全局配置选项。
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- sp_executesql：执行动态生成的SQL语句。
EXEC sp_executesql N'SELECT * FROM TableName WHERE ColumnName = @Param', N'@Param int', @Param = 123;
-- sp_rename：重命名数据库对象，如表、列等。
EXEC sp_rename 'TableName', 'NewTableName';
-- sp_depends：查找指定对象的依赖关系，包括引用该对象的其他对象和被该对象所引用的对象。
EXEC sp_depends 'ObjectName';
-- sp_helpindex：显示表的索引信息。
EXEC sp_helpindex 'TableName';
-- sp_helpconstraint：显示表的约束信息。
EXEC sp_helpconstraint 'TableName';
-- sp_lock：显示当前数据库中的锁信息。
EXEC sp_lock;
-- sp_monitor：显示SQL Server实例的系统统计信息。
EXEC sp_monitor;
-- sp_resetstatus：重置数据库的状态为正常。
EXEC sp_resetstatus 'DatabaseName';
-- sp_refreshview：刷新视图的元数据信息。
EXEC sp_refreshview 'ViewName';
-- sp_change_users_default_db：修改用户的默认数据库。
EXEC sp_change_users_default_db 'UserName', 'NewDefaultDatabase';
-- sp_estimate_data_compression_savings：估计表的数据压缩节省空间的情况。
EXEC sp_estimate_data_compression_savings 'TableName';

系统存储过程创建并存放在与系统数据库 master 中，一些系统存储过程只能由系统管理员使用，而有些系统存储过程通过授权可以被其它用户所使用。

2.2 SQL Server 扩展存储过程

扩展存储过程（Extended Stored Procedure）是一种特殊的存储过程，它是使用 C 或 C++ 编程语言编写的外部可执行程序，可以在 SQL Server 中被调用。扩展存储过程允许开发人员在 SQL Server 中执行特定的操作，例如访问操作系统资源，实现自定义的数据访问方法，或与第三方库进行交互等。相比于传统的 T-SQL 存储过程，扩展存储过程更加灵活和强大，但也更加复杂和难以维护。需要注意的是，扩展存储过程的使用需要小心谨慎，特别是在安全方面，以防止恶意代码的执行和数据泄露。

扩展存储过程以前缀xp_来标识，对于用户来说，扩展存储过程和普通话存储过程一样，可以用相同的方法来执行。

2.2.1 常见的扩展存储过程与用法举例

xp_cmdshell：该扩展存储过程允许在SQL Server中执行操作系统命令，例如创建、删除文件目录等等。例如，在执行以下语句之后，将在SQL Server主机上创建c:test目录：

EXEC xp_cmdshell 'mkdir c:test'

xp_readerrorlog：该扩展存储过程可用于从SQL Server错误日志中读取信息。例如，以下语句将返回最近50个错误日志记录：

EXEC xp_readerrorlog 0, 1, N'', NULL, NULL, NULL, N'asc'

xp_fixeddrives：该扩展存储过程可用于返回计算机上所有可用的逻辑驱动器的名称和类型。例如，以下语句将返回有关计算机上所有逻辑驱动器的信息：

EXEC xp_fixeddrives

xp_sprintf：这个扩展存储过程看起来就有点像入门写C语言的printf 函数了。燃鹅即时这样，我认为其实用到它的机会并不多。大部分使用的场景如下：

DECLARE @i VARCHAR(20) = 1,
@j varchar(50) = 'ajofen',
@s VARCHAR(80)

EXEC master..xp_sprintf @s OUTPUT,'there are two varaint one is %s and another is %s ',@i,@j
PRINT @s

xp_sendmail：该扩展存储过程可用于通过SMTP服务器发送电子邮件。例如，以下语句将向收件人发送一封测试邮件：

EXEC master.dbo.xp_sendmail 
@recipients = 'receiver@example.com', 
@subject = 'Test email', 
@message = 'This is a test email', 
@anonymous = 'true', 
@body_format = 'HTML'

xp_regread 和 xp_regwrite：这两个扩展存储过程用于读取和写入Windows注册表中的值。例如，以下语句将从HKEY_LOCAL_MACHINESoftwareMicrosoftWindowsCurrentVersion键中读取注册表项的值：

EXEC master.dbo.xp_regread 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = 'SoftwareMicrosoftWindowsCurrentVersion', 
@value_name = 'ProductName', 
@value = @product_name OUTPUT

xp_create_subdir：该扩展存储过程用于创建新目录。例如，以下语句将在c:temp目录下创建名为test的新目录：

EXEC xp_create_subdir 'c:temptest'

xp_sscanf：这个扩展存储过程是对插入的字符串变量进行格式化取值。

DECLARE @i VARHAR(50) = 1,
@j varchar(50) ,
@s VARCHAR(80)

EXEC master..xp_sscanf '3 + 31 = 34','%s + %s =34',@i OUTPUT,@s OUTPUT
SELECT @i,@j,@s

xp_enumgroups 和 xp_logininfo：这两个扩展存储过程用于获取有关Windows组和用户的信息。例如，以下语句将返回所有本地用户和组的信息：

EXEC xp_enumgroups '', 0, @groups OUTPUT
EXEC xp_logininfo '', @groups, @accounts OUTPUT

xp_delete_file 和 xp_deletemail：这两个扩展存储过程可用于删除文件和电子邮件收件箱中的邮件。例如，以下语句将删除c:temptest.txt文件：

EXEC xp_delete_file 'c:temptest.txt', NULL, 0

xp_terminate_process：该扩展存储过程用于杀死指定的进程。例如，以下语句将杀死进程ID为1234的进程：

EXEC xp_terminate_process 1234

2.3 用户自定义存储过程

2.3.1 创建存储过程的语法

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

2.3.2 创建不带参数存储过程

--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;

--调用、执行存储过程
exec proc_get_student;

2.3.3 修改存储过程

这里有两种方法进行修改：

1. 使用alter语句进行修改

   --修改存储过程
   alter proc proc_get_student
   as
   select * from student;
   

2. 使用sp_rename函数修改存储过程的名称

   sp_rename '存储过程名', '新存储过程名', 'OBJECT'
   

2.3.4 创建带参存储过程

--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

2.3.5 创建通配符参数的存储过程

在工作用，可以使用通配符参数存储过程来搜索满足特定条件的数据，从而避免查询的重复和繁琐，同时也可以对搜索结果进行排序或其他变换操作，进一步提高查询效率。

--带通配符参数存储过程
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

2.3.6 创建带输出参数的存储过程

带有输出参数的存储过程在 SQL Server 中的应用场景主要是用于向调用过程传递有关在存储过程执行过程中计算出的值，因此，可以在SQL服务器中使用输出参数来实现跨存储过程的参数传递，或者用来实现输出参数的值及其状态的检查。

输出参数的主要作用是让程序可以从存储过程中获取计算出的值，它允许在运行时在存储过程中作出更多的决策，因为用户可以使用输出参数来方便地获取数据，从而避免执行另一个查询来获取数据，并且可以将数据传递给另一个存储过程。

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

2.3.7 创建不缓存存储过程

不缓存存储过程的应用场景主要有两个：

1. 执行非常耗时的任务，比如复杂的查询和大数据的处理等；
2. 当数据库环境变化时，可以快速更新存储过程信息而不需要重启服务器。

不缓存存储过程的作用是提高执行效率和实现动态更新存储过程的信息。它可以有效地减少数据库服务器的开销，确保服务器的稳定可用性，并且能够有效地处理海量的数据。

--WITH RECOMPILE 不缓存
if (object_id('proc_temp', 'P') is not null)
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from student;
go

exec proc_temp;

2.3.8 创建加密的存储过程

SQL Server 中的加密存储过程的应用场景主要是为了提高数据库安全性，例如，可以避免对数据库对象的意外删除或修改，防止对数据的不恰当访问，以及为数据库安全性预防网络黑客攻击。加密存储过程也可以用来防止用户从数据库获取非授权信息。加密存储过程也可以用来存储和传输敏感数据，保护数据库的安全性和完整性。

这种创创建方式以及作用类似于我之前的视图讲解中的一样，有兴趣的同学可以参看之前的博文《【SQL Server】数据库开发指南（六）索引和视图的使用技巧、方法与综合应用》

--加密WITH ENCRYPTION 
if (object_id('proc_temp_encryption', 'P') is not null)
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from student;
go

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

2.3.9 创建带游标参数存储过程

if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

2.3.10 创建分页存储过程

分页存储过程可以在SQL Server数据库中实现结果集的分页显示、灵活的指定页码和页大小，实现不同结果集的分页显示：

---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from product
;    
    select * from (
        select row_number() over(order by pid) as rowId, * from product 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

文末总结

本文章详细介绍了存储过程的定义、类型以及创建、修改方法。通过学习本文章，我们可以了解到存储过程的优点，了解存储过程的种类以及创建方法，并深入了解如何创建带参数的存储过程、带输出参数的存储过程和分页存储过程等。相信这些知识将为数据库开发者提供宝贵的帮助和参考。