SQLServer存储过程

存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令，通俗来说，存储过程就是能完成一定操作的一组SQL语句，可视为批处理文件。

存储过程优势

• 存储过程只在创造时进行编译，以后每次执行存储过程无需再次编译。
• SQL语句每执行一次就编译一次，使用存储过程可提高数据库执行效率。
• 当对数据库进行复杂操作时，可将复杂的操作用存储过程封装起来与事务结合一起使用。
• 存储过程可重复使用，可减少数据库开发人员的工作量。
• 存储过程安全性高，仅对特定用户开放权限。

存储过程缺点

• 移植不便，存储过程依赖于数据库管理系统。
• 不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装。
• 代码可读性差，不移维护。
• 不支持集群

SQL Server的存储过程是使用T_SQL编写的代码段，目的在于能够方便的完成数据库操作。T_SQL是SQL Server与应用程序之间的编程接口。

存储过程分类

1. 系统存储过程

系统存储过程是SQL Server自身提供的存储过程，可作为命令执行操作。主要用来从系统表中获取信息，以完成数据库服务器的管理工作。

系统存储过程位于数据库服务器中，以sp_开头。系统存储过程定位在系统定义master和用户定义的数据库中，调用时不必在存储过程前添加数据库限定名。

2. 用户存储过程/自定义存储过程

自定义存储过程即用户使用T_SQL语句编写的，为实现某特定业务需求。自定义存储过程可接收输入参数、向客户端返回结果、返回输出参数等。

创建自定义存储过程时

• 存储过程前添加##表示创建全局的临时存储过程

• 存储过程前添加#表示创建局部临时存储过程

局部临时存储过程只能在创建它的会话中使用，会话结束将被删除。两种存储过程都存放在tempdb数据库中。

用户定义的存储过程分为

• T_SQL

T_SQL存储过程是指保存的T_SQL语句集合，可接收和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。

• CLR
  CLR 存储过程是指引用Microsoft .NET Framework公共语言的方法存储过程，可接收和返回用户提供的参数。

增删改查

创建存储过程

-- 创建无参数的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE name="getUsers"))
  DROP PROC prop_get_users
GO
CREATE PROCEDURE getUsers
AS 
SELECT * FROM Uses;

-- 调用执行存储过程
EXEC getBooks;

-- 创建带参数的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE name="searchUsers"))
  DROP PROC proc_search_users
GO
CREATE PROC searchUsers(@UserID int)
AS 
SELECT * FROM Users WHERE UserID=@UserID;

-- 执行存储过程
EXEC searchUsers 100;

-- 创建带多个参数的存储过程
IF(EXISTS(SELECT * FROM sys.objects WHERE name="searchUsers"))
  DROP PROP proc_search_users
GO
CREATE PROC searchUsers(@UserID int, @UserName varchar(20))
AS
  SELECT * FROM Users WHERE UserID=@UserID AND UserName=@UserName;

-- 执行存储过程
EXEC searchUsers 100, "admin";

-- 创建带返回值的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE NAME = "getUserID") )
  DROP PROC proc_get_userid
GO 
CREATE PROC getUserID(@UserName varchar(20), @UserID int output)
AS
  SELECT @UserID = UserID FROM Users WHERE UserName = @UserName;

-- 执行存储过程
DECLARE @id int -- 声明变量用于接收存储过程的返回值
EXEC getUserID "admin",@id output
SELECT @id AS UserID; -- AS 为返回的列起别名

修改存储过程

ALTER PROCEDURE dbo.getUsers
AS
SELECT auth FROM Users;

删除存储过程

DROP PROCEDURE getUsers;

重命名存储过程

sp_rename getUsers,proc_get_users

基础语法

-- 定义变量并赋值
declare @var int
set @var=1
print @var

-- 定义变量并使用select语句赋值
declare @var nvarchar(50)
select @var="superman"
print @var

declare @var nvarchar(50)
select @var = UserName from Users where UserID=1
print @var


-- 定义变量并使用update语句赋值
declare @var nvarchar(50)
update Users set @var=UserName where UserID=1
print @var

-- 创建临时表
create table #Tmp
{
  [UserID] [int] NOT NULL,
  [UserName] [nvarchar](50) NOT NULL,
  [Password] [nvarchar](max) NULL,
}

实例：

编写存储过程，传入分号间隔的两个字符串，将对应的字符串提取后插入数据表对应的列中，若存在则更新。

keys = "01;02;03"
vals = "superman;antman,ironman"

实例：查询用户表中是否存在某账户的记录

CREATE PROC checkUser
@UserName varchar(50)
@Output varchar(8) output
AS 
BEGIN
  IF(SELECT COUNT(1) FROM Users AS u WHERE u.UserName=@UserName)>0
  SET
    @Output = "success"
  ELSE
  SET
    @Output = "failure"
END
GO

DECLARE @output varchar(8)
EXEC checkUser "ironman",@output output
PRINT @output