《SQL Server 2008从入门到精通》--20180716

• • 1.锁
  • 2.游标
  • 3.存储过程
    • 3.1.用户定义的存储过程
    • 3.2.扩展存储过程
    • 3.3.系统存储过程
    • 3.4.使用存储过程
      • 3.4.1.创建存储过程
      • 3.4.2.查看存储过程
      • 3.4.3.修改存储过程
      • 3.4.4.删除存储过程

1.锁

当多个用户同时对同一个数据进行修改时会产生并发问题，使用事务就可以解决这个问题。但是为了防止其他用户修改另一个还没完成的事务中的数据，就需要在事务中用到锁。
SQL Server 2008提供了多种锁模式：排他锁，共享锁，更新锁，意向锁，键范围锁，架构锁和大容量更新锁。
查询sys.dm_tran_locks视图可以快速了解SQL Server 2008内的加锁情况。

SELECT * FROM sys.dm_tran_locks;

注：关于锁的知识书中没细讲，将在以后的博客中补充。

2.游标

游标是类似于C语言指针一样的结构，是一种数据访问机制，允许用户访问单独的数据行。游标主要由游标结果集和游标位置组成。游标结果集是定义游标的SELECT语句返回行的集合，游标位置是指向这个结果集中某一行的指针。
示例1：用游标检索出student表中每行记录
Student表记录如图所示

执行下列语句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--声明student表的游标stu
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor--移动该记录指针
WHILE @@FETCH_STATUS=[e-mail]@FETCH_STATUS用于保存FETCH操作的结束信息，=0表示有记录检索成功
BEGIN
FETCH NEXT FROM stu_cursor--游标指针移动到下一条记录
END
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源

结果如图所示

2.1.游标定义的参数LOCAL和GLOBAL

游标定义参数LOCAL表示该游标只能作用于本次批处理或函数或存储过程。游标定义参数GLOBAL表示该游标可以作用于全局。
执行下列语句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

执行结果如下

语句中，声明了一个student表的游标stu_cursor，在打开游标时提示游标不存在。因为该游标参数是LOCAL，只能作用于当前批处理语句中，而打开游标语句和声明语句不在一个批处理中。如果去掉第一个GO，使两个语句在同一个批处理中，就能顺利执行不会报错。
执行下列语句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

执行结果：命令已成功完成
和LOCAL参数对比，GOLBAL参数设置游标作用于全局，因此OPEN和DECLARE语句不在同一个批处理中依然可以成功执行。

2.2.游标分为游标变量和游标类型

如下列语句

--语句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值
GO

--语句2
DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursor
SET @stu_cursor=CURSOR FOR--给该变量赋值
SELECT * FROM student

在语句1中直接声明了一个游标并赋值，而语句2中声明了游标类型的变量@stu_cursor，然后给该变量赋值。这两者是不同的。

2.3.游标参数FORWARD_ONLY和SCROLL

FORWARD_ONLY参数设置游标只能从结果集的开始向结束方向读取，使用FETCH语句时只能用NEXT，而SCROLL参数设置游标可以从结果集的任意方向，任意位置移动。如下列语句

--语句1，默认FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--语句2，FORWARD_ONLY参数，FETCH时只能从开始往结束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--语句3，SCROLL参数，FETCH时可以从任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO

2.4.游标的简单应用

示例2：将student表中stu_enter_score大于600分的学生都减去100分
Student表中的数据如图所示

执行下列语句

--游标的简单应用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET [e-mail]100 WHERE [e-mail]_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示

3.存储过程

存储过程是一组用于完成特定功能的语句集，经过编译后存储在数据库中。在SQL Server 2008中，既可以用T-SQL编写存储过程，也可以用CLR编写存储过程。

3.1.用户定义的存储过程

该种存储过程是指封装了可重用代码的模块或者历程，有2种类型：T-SQL存储过程和CLR存储过程。
T-SQL存储过程是指保存的T-SQL语句集合
CLR存储过程是指对Microsoft .NET Framework公共语言运行时（CLR）方法的引用

3.2.扩展存储过程

扩展存储过程是指可以动态加载和运行的DLL，允许使用编程语言（如C语言）创建自己的外部例程。扩展存储过程直接在SQL Server 2008的实例的地址空间中运行，可以使用SQL Server扩展存储过程API完成编程。

3.3.系统存储过程

系统存储过程是指存储在源数据库中，以sp开头的存储过程，出现在每个系统定义数据库和用户定义数据库的sys架构中。

3.3.1.创建存储过程规则

在设计和创建存储过程时，应该满足一定的约束和规则。

• CREATE PROCEDURE定义自身可以包括任意数量和类型的SQL语句，但下表中的语句除外。不能在存储过程的任何位置使用这些语句。
• 可以引用在统一存储过程中创建的对象，只要引用时已创建了该对象
• 可以在存储过程内引用临时表
• 如果在存储过程中创建了本地临时表，该临时表仅为该存储过程而存在，退出该存储过程后，该临时表会消失
• 如果执行的存储过程调用了另一个存储过程，被调用的存储过程可以访问第一个存储过程的所有对象，包括临时表
• 如果执行对远程SQL Server 2008实例进行更改的远程存储过程，这些更改将不能被回滚。远程存储过程不参与事务处理
• 存储过程中的参数的最大数量为2100
• 存储过程中的局部变量的最大数量仅受可用内存的限制
• 根据可用内存的不同，存储过程最大可达128MB

语句	语句	语句
CREATE AGGREGATE	CREATE RULE	CREATE DEFAULT
CREATE SCHEMA	CREATE(ALTER) FUNCTION	CREATE(ALTER) TRIGGER
CREATE(ALTER) PROCEDURE	CREATE(ALTER) VIEW	SET PARSEONLY
SET SHOWPLAN_ALL	SET SHOWPLAN_TEXT	SET SHOWPLAN_XML
USE database_name

3.3.2.限定存储过程内的名称

在存储过程内，如果用于语句的对象没有限定架构，则架构将默认为该存储过程的架构。如果创建该存储过程的用户没有限定INSERT,SELECT,UPDATE或DELETE语句中引用的表名或试图名，则默认情况下通过该存储过程进行的访问将受到该过程创建者权限的限制。如果有其他用户要使用存储过程，则所有用于数据定义语言（DDL）的语句（如CREATE,ALTER,EXECUTE,DROP,DBCC或动态SQL语句）的对象名应该用该对象架构的名称来限定。

3.3.3.加密存储过程的定义

如果要创建存储过程并确保其他用户无法查看该存储过程的定义，则可以使用WITH ENCRYPTION，这样，过程定义将以不可读的形式存储。

3.3.4.SET语句选项

当创建或者更改T-SQL存储过程后，数据库引擎将保存SET QUOTED_IDENTIFIER和SET ANSI_NULLS的设置，执行存储过程时将使用这些原始设置而忽略任何客户端会话的ET QUOTED_IDENTIFIER和SET ANSI_NULLS设置。其他SET选项在创建或更改存储过程后不保存。

3.4.使用存储过程

3.4.1.创建存储过程

示例3：将示例2用存储过程实现
Student表的数据如图所示

执行下列语句

CREATE PROCEDURE alter_data
@a int--参数
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>[e-mail]
BEGIN
UPDATE student
SET [e-mail]100 WHERE [e-mail]_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

结果如图所示

3.4.2.查看存储过程

可以通过使用系统存储过程或者目录视图查看存储过程的定义

3.4.2.1.图形化界面

如下图

3.4.2.2.系统存储过程sp_helptext查看存储过程定义

执行下列语句

EXEC sp_helptext 'alter_data'

结果如图所示

3.4.2.3.系统存储过程sp_depends查看存储过程相关信息

执行下列语句

EXEC sp_depends 'alter_data'

结果如图所示

3.4.2.4.目录视图查看存储过程

执行下列语句

SELECT * FROM sys.procedures

结果如图所示

3.4.3.修改存储过程

用ALTER PROCEDURE语句修改存储过程，只需将上面示例中的CREATE修改成ALTER运行就行了。

3.4.4.删除存储过程

执行下列语句删除存储过程

DROP PROCEDURE alter_data