您所在位置：主题：[转帖]SQL Server 7.0 入门（六）
qingdaofeng
[专家分：3160] 发布于 2005-01-15 11:47:00
建立存储过程体
存储过程逻辑驻留在存储过程体中。一个存储过程体中可以包含任意条Transact SQL语句。下面的Transact SQL语句不能在任何存储过程体中出现：
· CREATE DEFAULT
· CREATE TRIGGER
· CREATE PROCEDURE
· CREATE VIEW
· CREATE RULE
1、 局部变量
局部变量保持存储过程中间值。当一个值在存储过程中需要多次，或者某个查询的结果需要在随后的查询中使用时，需要使用局部变量。在这些情形下，值被存储在局部变量中，并可用于将来的使用。本地变量的名称以“@”符号开头。变量的名称可以包含字符和数值。局部变量在使用前需要进行类型声明。对局部变量进行赋值需要使用SELECT语句。SELECT可以从一个表中检索出值并将其赋给某个变量，也可以给变量赋一个常量值。一个简单的SELECT语句可以给多个局部变量赋值。
例如：
DECLARE @var1 integer, @var2 varchar(20)
SELECT @var1 = 32,
@var2 = ‘MyAge’
如果从SELECT查询中没有返回任何数据，而SELECT又要将数据的值赋予局部变量，则该局部变量的值将不会发生改变。
2、 条件词句
存储过程中提供的条件语句包括：
· IF……ELSE语句。
· WHILE语句。
1） IF……ELSE语句。在该语句中包含三个部分：布尔运算表达式，IF语句块和ELSE语句块。语法如下：
IF (boolen_expr)
{statements}
ELSE
{statements}
在IF或ELSE语句块中可以有多条语句，这种情形下，需要语句BEGIN和END来标志语句块。
2） WHILE语句。WHILE语句用于处理直到某个条件为TRUE前重复执行的语句。语法如下：
WHILE (boolen_expr)
BEGIN
statement(s)
BREAK
Statement(s)
CONTINUE
END
BEGIN和END语句标志循环体。BREAK语句结束循环的执行（即走到END语句之后）。CONTINUE语句将控制处理过程回到循环的开始处（即BEGIN语句的右边）。
注意：如果有两个或多个WHILE循环被嵌套，则内部的BREAK退出的是次外层的循环。内部循环结束之后的所有语句在内部循环执行之后才能继续执行。
3、 GOTO语句
在存储过程的执行中，语句是顺序执行的。GOTO语句则是用来打破这种语句执行的顺序，它立即跳到某条语句上执行，而这条语句往往不紧跟在前一语句之后。GOTO语句与一个标志（Label）一起使用，该标志用来标识一条语句。
例如：
USE pubs
GO
DECLARE @num int
SELECT
IF @num = 0
GOTO Err
ELSE
BEGIN
PRINT ‘authors found’
SELECT * FROM authors
GOTO Last
END
Err: PRINT ‘no authors found’
Last: PRINT ‘Finish execution’
GO
4、 RETURN语句
RETURN语句用于无条件的退出存储过程。RETURN之后的任何语句都不再执行。RETURN语句可以给调用语句返回一个值，但不能返回NULL值。SQL Server经常为存储过程返回一个状态值。如果成功地执行，则返回一个0，如果出现了错误，则返回一个为负数的错误码。
存储过程返回的错误码
值
说 明
值
说 明
0
过程执行成功
-8
发生了非致命的内部问题
-1
漏掉了对象
-9
达到了系统极限
-2
发生了数据类型错误
-10
发生了致命的内部不一致错误
-3
该处理被选择成了死锁的牺牲者
-11
发生了致命的内部不一致错误
-4
发生了权限错误
-12
表或索引被破坏
-5
发生了语法错误
-13
数据库被破坏
-6
发生了混杂的用户错误
-14
发生了硬件错误
-7
资源错误，如空间不够等
5、 使用游标（CURSOR）在需要一行一行处理时，游标十分有用。游标可以打开一个结果集合（按照指定的标准选择的行），并提供在结果集中一行一行处理的功能。基于游标的类型，可以对其进行回滚或者前进。在使用游标时需要5个步骤：
i. 声明游标
在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。
形式1 （ANSI 92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
形式2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
ii. 打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
iii. 从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来构造游标处理的循环。
例如：
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
iv. 关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
v. 释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name
下面给出游标的一个完整的例子：
USE master
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
DECLARE table_cur CURSOR FOR
SELECT name FROM sysobjects WHERE type=’u’
OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
SELECT @msg = “Building indexes for table”+@TableName+”…”
PRINT @msg
SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
EXEC (@cmd)
PRINT “ “
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO
下面的脚本将为PUBS数据库执行sp_BuildIndexes
USE pubs
GO
EXEC ap_BuildIndexes
注意：上面也是创建用户定义的系统存储过程的示例。
使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。
存储过程逻辑驻留在存储过程体中。一个存储过程体中可以包含任意条Transact SQL语句。下面的Transact SQL语句不能在任何存储过程体中出现：
· CREATE DEFAULT
· CREATE TRIGGER
· CREATE PROCEDURE
· CREATE VIEW
· CREATE RULE
1、 局部变量
局部变量保持存储过程中间值。当一个值在存储过程中需要多次，或者某个查询的结果需要在随后的查询中使用时，需要使用局部变量。在这些情形下，值被存储在局部变量中，并可用于将来的使用。本地变量的名称以“@”符号开头。变量的名称可以包含字符和数值。局部变量在使用前需要进行类型声明。对局部变量进行赋值需要使用SELECT语句。SELECT可以从一个表中检索出值并将其赋给某个变量，也可以给变量赋一个常量值。一个简单的SELECT语句可以给多个局部变量赋值。
例如：
DECLARE @var1 integer, @var2 varchar(20)
SELECT @var1 = 32,
@var2 = ‘MyAge’
如果从SELECT查询中没有返回任何数据，而SELECT又要将数据的值赋予局部变量，则该局部变量的值将不会发生改变。
2、 条件词句
存储过程中提供的条件语句包括：
· IF……ELSE语句。
· WHILE语句。
1） IF……ELSE语句。在该语句中包含三个部分：布尔运算表达式，IF语句块和ELSE语句块。语法如下：
IF (boolen_expr)
{statements}
ELSE
{statements}
在IF或ELSE语句块中可以有多条语句，这种情形下，需要语句BEGIN和END来标志语句块。
2） WHILE语句。WHILE语句用于处理直到某个条件为TRUE前重复执行的语句。语法如下：
WHILE (boolen_expr)
BEGIN
statement(s)
BREAK
Statement(s)
CONTINUE
END
BEGIN和END语句标志循环体。BREAK语句结束循环的执行（即走到END语句之后）。CONTINUE语句将控制处理过程回到循环的开始处（即BEGIN语句的右边）。
注意：如果有两个或多个WHILE循环被嵌套，则内部的BREAK退出的是次外层的循环。内部循环结束之后的所有语句在内部循环执行之后才能继续执行。
3、 GOTO语句
在存储过程的执行中，语句是顺序执行的。GOTO语句则是用来打破这种语句执行的顺序，它立即跳到某条语句上执行，而这条语句往往不紧跟在前一语句之后。GOTO语句与一个标志（Label）一起使用，该标志用来标识一条语句。
例如：
USE pubs
GO
DECLARE @num int
SELECT
IF @num = 0
GOTO Err
ELSE
BEGIN
PRINT ‘authors found’
SELECT * FROM authors
GOTO Last
END
Err: PRINT ‘no authors found’
Last: PRINT ‘Finish execution’
GO
4、 RETURN语句
RETURN语句用于无条件的退出存储过程。RETURN之后的任何语句都不再执行。RETURN语句可以给调用语句返回一个值，但不能返回NULL值。SQL Server经常为存储过程返回一个状态值。如果成功地执行，则返回一个0，如果出现了错误，则返回一个为负数的错误码。
存储过程返回的错误码
值
说 明
值
说 明
0
过程执行成功
-8
发生了非致命的内部问题
-1
漏掉了对象
-9
达到了系统极限
-2
发生了数据类型错误
-10
发生了致命的内部不一致错误
-3
该处理被选择成了死锁的牺牲者
-11
发生了致命的内部不一致错误
-4
发生了权限错误
-12
表或索引被破坏
-5
发生了语法错误
-13
数据库被破坏
-6
发生了混杂的用户错误
-14
发生了硬件错误
-7
资源错误，如空间不够等
5、 使用游标（CURSOR）在需要一行一行处理时，游标十分有用。游标可以打开一个结果集合（按照指定的标准选择的行），并提供在结果集中一行一行处理的功能。基于游标的类型，可以对其进行回滚或者前进。在使用游标时需要5个步骤：
i. 声明游标
在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。
形式1 （ANSI 92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
形式2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR {READ ONLY | UPDATE ][OF column_list]}]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
ii. 打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN { { [GLOBAL] cursor_name } | cursor_variable_name}
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
iii. 从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}]
FROM [GLOBAL] cursor_name} | cursor_variable_name}
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来构造游标处理的循环。
例如：
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
iv. 关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
v. 释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name
下面给出游标的一个完整的例子：
USE master
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
DECLARE table_cur CURSOR FOR
SELECT name FROM sysobjects WHERE type=’u’
OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
SELECT @msg = “Building indexes for table”+@TableName+”…”
PRINT @msg
SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”')”
EXEC (@cmd)
PRINT “ “
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO
下面的脚本将为PUBS数据库执行sp_BuildIndexes
USE pubs
GO
EXEC ap_BuildIndexes
注意：上面也是创建用户定义的系统存储过程的示例。
使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。
