一. 什么是存储过程
在SQL-Server中，存储过程是一组T-SQL命令集合，可视为存储在数据库中的一段子程序，用户通过指定存储过程的名字和参数来执行它。T-SQL是标准SQL的扩展，不仅结合了SQL的数据操作能力，而且具备了过程化语言的流程控制能力。
二. 存储过程运行流程
三. 创建存储过程语法
创建存储过程
CREATE PROCEDURE proc_name
[{@paramter_name}{type} [ = default_value ] [OUTPUT] ]
[{ WITH [ RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION ]}]
AS
BEGIN
statements
END
1. 命名存储过程
CREATE PROCEDURE proc_name
对于自己来讲，我比较倾向于使用小写字母配合下划线的方式命名存储过程，因为对于T-SQL关键字全部使用大写表示，这样就可以形成良好的区分度，使整个结构更加清晰，可读性更高。存储过程的命名应该能表达这个存储过程能做什么，针对那些表，对于团队来说，最好能制定出一套命名规范，并坚持使用下来，这样即使存储过程的数量级很大的时候，也可以有良好的组织性。应该避免的一种情况是使用sp_前缀命名存储过程，因为SQL-Server的系统存储过程就是以sp_开头的，这样你自己编写的存储过程可能和系统存储过程出现冲突，带来额外的麻烦。
2.参数的使用
{@parameter_name} {type} [=default_value] [OUTPUT]存储过程在执行的时候可以通过传入参数的方法提供信息，这有点类似于函数的参数功能，当然参数只是可选的，可以使用N个参数，或者一个参数也不使用。当使用多个参数的时候，中间应该用逗号分隔开来。
参数的命名必须使用@作为前缀，同时必须声明数据类型。参数的命名推荐使用小写字母配合下划线的方式。也可以给参数赋值一个默认值，不过这只是可选的，同样，参数也可以指定为输出参数，输出参数即可以用来传入值，也可以用来传出值，它使用OUTPUT关键字表示。
带参数的存储过程
CREATE PROCEDURE proc_book_add
(
--不带默认值的参数
@book_name NVARCHAR(20),
--带默认值的参数
@book_able BIT = 1,
--输出参数
@book_id INT OUTPUT
)
AS
3.存储过程附加指令
WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION }
RECOMPILE指示SQL-Server在每次运行存储过程的时候都重新编译整个过程。在之前的存储过程运行流程图中可以看到，存储过程会重用首次编译的计划，加上这个指令后，存储过程将不遵循这个原则。通常这个指令是与数据库执行优化有关，有兴趣的读者可以google一下。
ENCRYPTION指示对存储过程进行加密，保护源代码不被窥视和修改。
这是加密后的proc_book_add,用户现在便无法查看源代码和修改了。
四. 执行存储过程与删除存储过程
在未进入如何写存储过程的主体前，先介绍一下如何执行存储过程和删除存储过程，这两个操作应该是比较简单的。
1.执行存储过程
{ EXECUTE | EXEC} proc_name [@pram=value [OUTPUT]]在带参数的情况下，优先使用@parameter=value的形式。如果参数有默认值，并且传入的也是默认值，则可以省略参数；如果参数是一个输出参数，则需要携带OUTPUT关键字。执行带参数的存储过程
--声明一个变量
DECLARE @bookId INT;
--输入参数 与 输出参数
EXEC proc_book_add @book_name='SQL',@book_able=1,@book_id=@bookId OUTPUT
2.删除存储过程
DROP PROCEDURE proc_name
五.T-SQL程序设计
1.定义代码块
BEGIN statements END
组合BEGIN-END类似于C#中的标签{ }，用来把一连串代码定义为一块。
2.声明变量
DECLARE @variable type
变量的命名同样使用@开头，首字符小写命名。
声明变量
--声明一个变量
DECLARE @bookId INT;
--声明多个变量
DECLARE @bookName NVARCHAR(50),@bookAble BIT;
3.变量赋值
SET @variable = value
变量赋值
--SET语句只能用来给单个变量赋值
SET @bookId=1
SET @bookAble=1
SET @bookName='SQL'
4.分支语句
分支语句
--完整分支语句模板
IF bool_statement
statements
ELSE IF bool_statement
statements
ELSE
statements
与其他开发语言类似，IF后面可以跟ELSE也可以不跟，可以有ELSE IF也可以没有。同时需要注意一点的是，IF后只执行一块代码，如果要执行多条语句，必须用BEGIN-END包裹起来。
分支语句实例
--分支语句
--单条语句也可以使用BEGIN-END包裹
IF @bookAble=1
SET @bookName='SQL YES'
ELSE
BEGIN
SET @bookName='SQL NO'
END
5.循环语句
循环语句
--循环语句模板
WHILE bool_expression
{ sql_statement | statement_block }
[ BREAK | CONTINUE ]
--循环实例
DECLARE @count INT;
SET @count = 1;
WHILE @count<10
BEGIN
SET @count += 1
PRINT @count
END
6.使用RETURN
RETURN语句可以立刻停止存储过程的执行，任何位于RETURN语句之外的语句都不会被执行。在存储过程结束的地方，实际上也是在逻辑上执行了一个RETURN，其返回的值为0，表示成功执行。与输出参数的对比：输出参数用于将信息返回到调用存储过程的代码中，并且它可以是任何数据类型。而RETURN只能返回整数值，通常用于表示成功或失败。
六. 事务处理
1.什么是事务
事务是指做为单个逻辑工作单元的一系列操作，它保证成批的T-SQL操作要么全部执行，要么全部不执行。
2.事务的创建，提交，回滚，保留点
BEGIN TRAN[SACTION] [ tran_name ]
COMMIT [ TRAN[SACTION] tran_name ]
ROLLBACK [ TRAN[SACTION] tran_name ]
SAVE TRAN[SACTION] tran_name
七.存储过程实例
简单图书馆管理系统示例
--简单图书馆管理系统
--创建reader表
CREATE TABLE reader
(
reader_id INT IDENTITY(1,1) NOT NULL,
reader_email VARCHAR(120) NOT NULL,
reader_name NVARCHAR(16) NULL,
reader_password VARCHAR(16) NOT NULL,
reader_enable BIT DEFAULT(1) NOT NULL,
PRIMARY KEY(reader_id)
)
--创建book表
CREATE TABLE book
(
book_id INT IDENTITY(1,1) NOT NULL,
book_name NVARCHAR(50) NOT NULL,
book_stock INT NOT NULL, --书的库存
book_borrowed INT DEFAULT(0) NOT NULL, --书的已借出数量
book_enable BIT DEFAULT(1) NOT NULL,
CHECK (book_stock>=0),
CHECK (book_borrowed>=0),
CHECK (book_borrowed <= book_stock),
PRIMARY KEY(book_id)
)
--创建borrow表
CREATE TABLE borrow
(
borrow_id INT IDENTITY(1,1) NOT NULL,
book_id INT NOT NULL,
reader_id INT NOT NULL,
borrow_start_date SMALLDATETIME NOT NULL, --借阅开始日期
borrow_end_date SMALLDATETIME NOT NULL, --借阅归还日期
borrow_is_returned BIT DEFAULT(0) NOT NULL, --归还标记
CHECK(borrow_start_date <=borrow_end_date),
PRIMARY KEY(borrow_id),
FOREIGN KEY(book_id) REFERENCES book(book_id),
FOREIGN KEY(reader_id) REFERENCES reader(reader_id)
)
--增加书籍存储过程
CREATE PROC proc_book_add
(
@book_id INT OUTPUT,
@book_name NVARCHAR(50),
@book_stock INT
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO book (book_name,book_stock) VALUES (@book_name,@book_stock)
--返回生成的唯一ID号
SET @book_id=SCOPE_IDENTITY();
END
--检查某本书是否有库存
CREATE PROC proc_book_has_stock
(
@book_id INT,
@has_stock BIT OUTPUT
)
AS
BEGIN
DECLARE @stockNum INT;
DECLARE @borrowNum INT;
SET NOCOUNT ON;
SELECT @borrowNum=book_borrowed,@stockNum=book_stock FROM book WHERE book_id=@book_id;
--比较是否有库存
IF (@stockNum > @borrowNum)
SET @has_stock = 1
ELSE
SET @has_stock = 0
END
--借阅图书
CREATE PROC proc_borrow_add
(
@book_id INT,
@reader_id INT,
@borrow_start_date SMALLDATETIME,
@borrow_end_date SMALLDATETIME
)
AS
BEGIN
SET NOCOUNT ON;
--设置事务处理
BEGIN TRAN
--更新图书的借阅量+1
UPDATE book SET book_borrowed=book_borrowed+1
--插入一条图书借阅信息
INSERT INTO borrow (book_id,reader_id,borrow_start_date,borrow_end_date) VALUES (@book_id,@reader_id,@borrow_start_date,@borrow_end_date)
COMMIT
END
--归还图书
CREATE PROC proc_borrow_return
(
@borrow_id INT
)
AS
BEGIN
DECLARE @bookId INT;
SET NOCOUNT ON;
--设置事务处理
BEGIN TRAN
--获取book的唯一编号
SELECT @bookId=book_id FROM borrow WHERE borrow_id=@borrow_id
--更新borrow的归还标记为1
UPDATE borrow SET borrow_is_returned=1 WHERE borrow_id=@borrow_id
--更新书的已借阅量-1
UPDATE book SET book_borrowed=book_borrowed-1 WHERE book_id=@bookId
COMMIT
END
八.存储过程的优点（与静态嵌入SQL对比）与缺点
优点：1. 数据库提供了存储过程语法检查，使得开发调试更为简单;
2. 实现了数据访问层的相对独立，更新存储过程比更改，测试，重新部署程序集更为快捷;
3. 当WEB服务器与数据库不在同一服务器的时候，使用存储过程可以减少网络通信量;
缺点：1.存储过程对应于专门的数据库，可移植性差;
2.存储过程需要专门的DBA编写;
3.存储过程容易混合业务逻辑，导致整个存储过程变得复杂和难以维护;
