第7章 SQL Server 2008高级应用

1、北京林业大学北京林业大学 软件教研室软件教研室1 1第第7章章 SQL Server 2008高级应用高级应用 北京林业大学北京林业大学 软件教研室软件教研室2 27.1 Transact-SQL程序设计程序设计7.2 存储过程存储过程7.3 触发器触发器7.4 备份和还原备份和还原 北京林业大学北京林业大学 软件教研室软件教研室3 37.1 Transact-SQL程序设计程序设计 Transact-SQL语言就是在标准语言就是在标准SQL的基础上进行扩的基础上进行扩充而推出的充而推出的SQL Server专用的结构化专用的结构化SQL。 Transact-SQL语言最主要的用途是设计服务器

2、端的语言最主要的用途是设计服务器端的能够在后台执行的程序块。能够在后台执行的程序块。7.1.1 变量变量 全局变量全局变量 全局变量由系统定义和维护的，只能使用预先说明及全局变量由系统定义和维护的，只能使用预先说明及定义的全局变量。定义的全局变量。全局变量对用户而言是只读的，用户无法对它们进行全局变量对用户而言是只读的，用户无法对它们进行修改或管理修改或管理 。 存储过程、存储过程、触发器等触发器等 北京林业大学北京林业大学 软件教研室软件教研室4 4 局部变量局部变量 DECLARE 变量名变量名 变量类型变量类型 ，变量名变量名 变量类型变量类型， SELECT 局部变量局部变量=变量值变

3、量值或或SET 局部变量局部变量=变量值变量值 例例7-1 声明一个长度为声明一个长度为8个字符的变量个字符的变量id，并赋值。，并赋值。DECLARE id char(8)SELECT id=10010001 北京林业大学北京林业大学 软件教研室软件教研室5 5【例【例7-2】 查询学号为查询学号为“S7”的学生的学号与姓名，的学生的学号与姓名，并存储到变量并存储到变量sno和和sn中。中。DECLA RE sno varchar(10)DECLARE sn varchar(10) SELECT sno = SNo,sn = SN FROM S WHERE SNo = S7注释符注释符 在在

4、T-SQL中可以使用两类注释符：中可以使用两类注释符：（1）ANSI标准的注释符标准的注释符“- -”用于单行注释；用于单行注释；（2）与）与C语言相同的程序注释符，即语言相同的程序注释符，即“/*/”，“/*”用于用于注释文字的开头，注释文字的开头，“*/”用于注释文字的结尾，可在程序中标用于注释文字的结尾，可在程序中标识多行文字为注释。识多行文字为注释。 北京林业大学北京林业大学 软件教研室软件教研室6 67.1.2 运算符运算符 运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。运算符是一种符号，用来指定要在一个或多个表达式中执行的操作。算术运算符算术运算符 算术运算符对两个表

5、达式执行数学运算，参与运算的算术运算符对两个表达式执行数学运算，参与运算的表达式必须是数值数据类型或能够进行算术运算的其表达式必须是数值数据类型或能够进行算术运算的其他数据类型。他数据类型。 北京林业大学北京林业大学 软件教研室软件教研室7 7赋值运算符赋值运算符等号等号 (=) 是唯一的是唯一的 Transact-SQL 赋值运算符。在以赋值运算符。在以下示例中，将创建一个下示例中，将创建一个 MyCounter 变量，然后赋变量，然后赋值运算符将值运算符将 MyCounter 的值赋为的值赋为1。DECLARE MyCounter INT;SET MyCounter = 1; 字符串串联运

6、算符字符串串联运算符加号加号 (+) 是字符串串联运算符，可以用它将字符串串是字符串串联运算符，可以用它将字符串串联起来。其他所有字符串操作都使用字符串函数进行联起来。其他所有字符串操作都使用字符串函数进行处理。处理。例如例如good + + morning的结果是的结果是good morning。北京林业大学北京林业大学 软件教研室软件教研室8 8比较运算符比较运算符比较运算符用来比较两个表达式值之间的大小关系，可以用于比较运算符用来比较两个表达式值之间的大小关系，可以用于除了除了text、ntext和和image 数据类型之外的所有数据类型。运算数据类型之外的所有数据类型。运算的结果为的结

7、果为True或或False，通常用来构造条件表达式。，通常用来构造条件表达式。北京林业大学北京林业大学 软件教研室软件教研室9 9逻辑运算符逻辑运算符逻辑运算符用来对多个条件进行运算，运算的结果为逻辑运算符用来对多个条件进行运算，运算的结果为True或或False，通常用来表示复杂的条件表达式。，通常用来表示复杂的条件表达式。北京林业大学北京林业大学 软件教研室软件教研室1010按位运算符按位运算符按位运算符对两个二进制数据或整数数据进行位操作，但是两按位运算符对两个二进制数据或整数数据进行位操作，但是两个操作数不能同时为二进制数据，必须有一个为整数数据。个操作数不能同时为二进制数据，必须有一

8、个为整数数据。一元运算符一元运算符一元运算符只对一个表达式进行运算一元运算符只对一个表达式进行运算 北京林业大学北京林业大学 软件教研室软件教研室1111运算符优先级和结合性运算符优先级和结合性SQL Server 2008中运算符的优先级顺序如下中运算符的优先级顺序如下 （按位取反）（按位取反） *（乘）、（乘）、/（除）、（除）、%（取余）（取余） +（正）、（正）、-（负）、（负）、+（加）、（加）、+（字符串串联）、（字符串串联）、-（减）、（减）、&（按位与）、（按位与）、（按位异或）、（按位异或）、|（按位或）（按位或） =、= 、= 、 、!=、!、!（比较运算符）（比较

9、运算符） Not And All、Any、Between、In、Like、Or、Some =（赋值）（赋值） 北京林业大学北京林业大学 软件教研室软件教研室12127.1.3 批处理批处理 批处理是包含一个或多个批处理是包含一个或多个T-SQL语句的组，批处理的语句的组，批处理的所有语句被整合成一个执行计划。所有语句被整合成一个执行计划。批处理是使用批处理是使用GO语句将多条语句将多条SQL语句进行分隔，其语句进行分隔，其中每两个中每两个GO之间的之间的SQL语句就是一个批处理单元。语句就是一个批处理单元。如果在编译过程中出现语法错误，那么批处理中所有如果在编译过程中出现语法错误，那么批处理中

10、所有的语句均无法正常执行。的语句均无法正常执行。【例【例7-3】 执行批处理程序，依次查询学生选课表执行批处理程序，依次查询学生选课表SC、学生总数。、学生总数。 GOSELECT * FROM SCSELECT COUNT(*) FROM SGO 北京林业大学北京林业大学 软件教研室软件教研室13137.1.4 流程控制命令流程控制命令 BEGINENDIF ELSE IF NOT EXISTS(SELECT 子查询子查询)ELSE CASE WHEN THEN WHEN THEN ELSE END CASE 北京林业大学北京林业大学 软件教研室软件教研室1414【例【例7-4】 从从SC数

11、据表中求出学号为数据表中求出学号为S7同学的平均同学的平均成绩，如果此平均成绩大于或等于成绩，如果此平均成绩大于或等于60分，则输出分，则输出“Pass!”信息。信息。 IF (SELECT AVG(Score) FROM SC WHERE SNo=S7)=60 BEGIN PRINT Pass! END 北京林业大学北京林业大学 软件教研室软件教研室1515【例【例7-5】 从从S数据表中读取学号为数据表中读取学号为S1同学的数据记同学的数据记录，如果存在，则输出录，如果存在，则输出“存在学号为存在学号为S1的学生的学生”，否则输出否则输出“不存在学号为不存在学号为S1的学生的学生”。USE

12、 Teach /*将教学管理数据库将教学管理数据库Teach置为当前数置为当前数据库据库*/GODECLARE message VARCHAR(255) /*定义变量定义变量message */IF EXISTS (SELECT * FROM S WHERE SNo=S1) SET message=存在学号为存在学号为S1的学生的学生ELSE SET message=不存在学号为不存在学号为S1的学生的学生PRINT messageGO 北京林业大学北京林业大学 软件教研室软件教研室1616【例【例7-6】 从学生表从学生表S中，选取中，选取Sno和和Sex，如果，如果Sex为为“男男”则输出

13、则输出“M”，如果为，如果为“女女”则输出则输出“F”。 SELECT SNo, Sex= CASE Sex WHEN 男男 THEN M WHEN 女女 THEN F ENDFROM S格式格式2：CASE WHEN THEN WHEN THEN ELSE END CASE命令可以嵌套到命令可以嵌套到SQL命令中。命令中。 北京林业大学北京林业大学 软件教研室软件教研室1717【例【例7-7】 从从SC表中查询所有同学选课的成绩情况，凡成绩为表中查询所有同学选课的成绩情况，凡成绩为空的输出空的输出“未考未考”、小于、小于60分的输出分的输出“不及格不及格”、60分至分至70分分的输出的输出“

14、及格及格”、70分至分至90分的输出分的输出“良好良好”、大于或等于、大于或等于90分的输出分的输出“优秀优秀”。 SELECT SNo,CNo, Score= CASE WHEN Score IS NULL THEN 未考未考 WHEN Score=60 AND Score=70 AND Score=90 THEN 优秀优秀 ENDFROM SC 北京林业大学北京林业大学 软件教研室软件教研室1818WHILE BEGIN BREAK CONTINUE 命令行或程序块命令行或程序块END WAITFOR DELAY | TIME | ERROREXIT | PROCESSEXIT | MIR

15、ROREXIT GOTO 标识符标识符 RETURN（整数值整数值） 北京林业大学北京林业大学 软件教研室软件教研室1919【例【例7-8】 以下程序计算以下程序计算1100之间所有能被之间所有能被3整除的数的个数及总整除的数的个数及总和。和。DECLARE s SMALLINT,i SMALLINT,nums SMALLINT SET s=0SET i=1SET nums=0WHILE (i=100) BEGIN IF (i%3=0) BEGIN SET s=s+i SET nums=nums+1 END SET i=i+1 ENDPRINT sPRINT nums 北京林业大学北京林业大学

16、 软件教研室软件教研室2020【例【例7-9】 等待等待1小时小时2分零分零3秒后才执行秒后才执行SELECT语句。语句。WAITFOR DELAY 01:02:03SELECT * FROM S【例【例7-10】 指定在指定在11:24:00时间点时开始执行时间点时开始执行SELECT语句。语句。WAITFOR TIME 11:24:00SELECT * FROM S 北京林业大学北京林业大学 软件教研室软件教研室2121【例【例7-11】 求求1+2+3+10的总和。的总和。DECLARE s SMALLINT,i SMALLINTSET i=1SET s=0BEG:IF (i=10) B

17、EGIN SET s=s+i SET i=i+1 GOTO BEG ENDPRINT s 北京林业大学北京林业大学 软件教研室软件教研室22227.1.5 常用命令常用命令 BACKUP 用于将数据库内容或其事务处理日志备份到存储介质用于将数据库内容或其事务处理日志备份到存储介质上（软盘、硬盘、磁带等）。上（软盘、硬盘、磁带等）。CHECKPOINT 用于将当前工作的数据库中被更改过的数据页或日志用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。页从数据缓冲器中强制写入硬盘。DBCC 用于验证数据库完整性、查找错误、分析系统使用情用于验证数据库完整性、查找错误、分析系

18、统使用情况等。况等。 北京林业大学北京林业大学 软件教研室软件教研室2323DECLAREDECLARE的语法格式如下：的语法格式如下：DECLARE local_variable data_type | cursor_variable_name CURSOR | table_type_definition , .n DECLARE命令用于声明一个或多个局部变量、游标命令用于声明一个或多个局部变量、游标变量或表变量。变量或表变量。 例：例：DECLARE x CHAR, y CHAR(10)SELECT x =123, y =data_typePRINT xPRINT y 北京林业大学北京林业

19、大学 软件教研室软件教研室2424EXECUTEEXECUTE命令用来执行存储过程命令用来执行存储过程 。KILLKILL命令用于终止某一过程的执行。命令用于终止某一过程的执行。PRINT PRINT的语法格式如下：的语法格式如下：PRINT any ASCII text | local_variable | FUNCTION | string_expression PRINT 命令向客户端返回一个用户自定义的信息，命令向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。即显示一个字符串、局部变量或全局变量。 北京林业大学北京林业大学 软件教研室软件教研室2525RAISE

20、RROR用于在用于在SQL Server 系统返回错误信息时，同时返回系统返回错误信息时，同时返回用户指定的信息。用户指定的信息。 READTEXTREADTEXT命令的语法格式如下：命令的语法格式如下：READTEXT table.column text_pointer offset size HOLDLOCKREADTEXT 命令用于从数据类型为命令用于从数据类型为TEXT、NTEXT 或或IMAGE 的列中读取数据。的列中读取数据。RESTORERESTORE 命令用来将数据库或其事务处理日志备命令用来将数据库或其事务处理日志备份文件由存储介质回存到份文件由存储介质回存到SQL Serv

21、er系统中。系统中。 北京林业大学北京林业大学 软件教研室软件教研室2626SELECTSELECT 命令可用于给变量赋值，其语法格式如下：命令可用于给变量赋值，其语法格式如下：SELECT local_variable = expression ，.nSELECT 命令可以一次给多个变量赋值。命令可以一次给多个变量赋值。 SET 命令有两种用法。命令有两种用法。（1）用于给局部变量赋值。）用于给局部变量赋值。（2）用于用户执行）用于用户执行SQL 命令时，命令时，SQL Server 处理选处理选项的设定项的设定 。SET：选项：选项ON；SET：选项：选项OFF；SET：选项值。：选项值。

22、 北京林业大学北京林业大学 软件教研室软件教研室2727SHUTDOWNSHUTDOWN WITH NOWAITSHUTDOWN 命令用于停止命令用于停止SQL Server 的执行。的执行。WRITETEXTWRITETEXT table.column text_pointer WITH LOG dataWRITETEXT 命令用于向数据类型为命令用于向数据类型为TEXT、NTEXT 或或IMAGE 的列中读取数据。的列中读取数据。USEUSE databaseUSE命令用于改变当前使用的数据库为指定的数据库。命令用于改变当前使用的数据库为指定的数据库。 北京林业大学北京林业大学 软件教研室

23、软件教研室28287.1.6 常用函数常用函数 统计函数统计函数 STDEV函数函数 STDEV函数返回表达式中所有数据的标准差。函数返回表达式中所有数据的标准差。 STDEVP函数函数 STDEVP 函数返回表达式中所有数据的总体标准差。函数返回表达式中所有数据的总体标准差。VAR函数函数VAR函数返回表达式中所有数据的统计变异数。函数返回表达式中所有数据的统计变异数。VARP函数函数VARP函数返回表达式中所有数据的总体变异数。函数返回表达式中所有数据的总体变异数。北京林业大学北京林业大学 软件教研室软件教研室2929算术函数算术函数 北京林业大学北京林业大学 软件教研室软件教研室3030

24、北京林业大学北京林业大学 软件教研室软件教研室3131字符串函数字符串函数 字符转换函数字符转换函数 ASCII（character_expression） 返回字符表达式最左端字符的返回字符表达式最左端字符的ASCII 码值码值 CHAR （integer_expression） CHAR函数用于将函数用于将ASCII 码转换为字符码转换为字符 LOWER （character _expression）LOWER函数用于把字符串全部转换为小写函数用于把字符串全部转换为小写 UPPER （character _expression） UPPER函数用于把字符串全部转换为大写函数用于把字符串全部

25、转换为大写 STR（float _expression ，length，）STR函数用于把数值型数据转换为字符型数据函数用于把数值型数据转换为字符型数据 北京林业大学北京林业大学 软件教研室软件教研室3232去空格函数去空格函数LTRIM （character _expression） LTRIM函数用于把字符串头部的空格去掉。函数用于把字符串头部的空格去掉。 RTRIM （character _expression）RTRIM函数用于把字符串尾部的空格去掉函数用于把字符串尾部的空格去掉 。取子串函数取子串函数 LEFT（character_expression, integer_expres

26、sion）LEFT函数返回的子串是从字符串最左边起到第函数返回的子串是从字符串最左边起到第integer_expression 个字符的部分。个字符的部分。 RIGHT（character_expression, integer_expression） RIGHT函数返回的子串是从字符串右边第函数返回的子串是从字符串右边第integer_expression 个字符起到最后一个字符的部分。个字符起到最后一个字符的部分。 北京林业大学北京林业大学 软件教研室软件教研室3333取子串函数取子串函数 SUBSTRING (expression, starting_ position, length)

27、SUBSTRING函数返回的子串是从字符串左边第函数返回的子串是从字符串左边第starting_position个字符起个字符起length个字符的部分。个字符的部分。 字符串比较函数字符串比较函数 CHARINDEX （substring_expression，expression）CHARINDEX函数返回字符串中某个指定的子串出现的开始函数返回字符串中某个指定的子串出现的开始位置位置 。 PATINDEX（ %substring_expression%，expression）与与CHARINDEX函数不同的是，函数不同的是，PATINDEX函数的子串中可函数的子串中可以使用通配符，且此函

28、数可用于以使用通配符，且此函数可用于CHAR、VARCHAR和和TEXT 数据类型。数据类型。 北京林业大学北京林业大学 软件教研室软件教研室3434字符串比较函数字符串比较函数 SOUNDEX（character _expression） SOUNDEX函数返回一个四位字符码函数返回一个四位字符码 。 DIFFERENCE （character_expression1，character_expression2） DIFFERENCE函数返回由函数返回由SOUNDEX 函数返回的两个字符函数返回的两个字符表达式的值的差异表达式的值的差异 。 值的差异是用值的差异是用0、1、2、3、4 来表示

29、的，含义如下：来表示的，含义如下：0 两个两个SOUNDEX函数返回值的第一个字符不同；函数返回值的第一个字符不同；1 两个两个SOUNDEX函数返回值的第一个字符相同；函数返回值的第一个字符相同；2 两个两个SOUNDEX函数返回值的第一、二个字符相函数返回值的第一、二个字符相同；同；3 两个两个SOUNDEX函数返回值的第一、二、三个字函数返回值的第一、二、三个字符相同；符相同；4 两个两个SOUNDEX函数返回值完全相同。函数返回值完全相同。北京林业大学北京林业大学 软件教研室软件教研室3535字符串操作函数字符串操作函数 QUOTENAME（character_expression ，

30、quote_ character） QUOTENAME函数返回被特定字符括起来的字符串。函数返回被特定字符括起来的字符串。 REPLICATE（character_expression，integer_expression） REPLICATE函数返回一个重复指定次数的字符串。函数返回一个重复指定次数的字符串。 REVERSE（character_expression） REVERSE函数将指定的字符串的字符排列顺序颠倒。函数将指定的字符串的字符排列顺序颠倒。 北京林业大学北京林业大学 软件教研室软件教研室3636REPLACE（string_expression1，string_expres

31、sion2，string_expression3）REPLACE函数返回被替换了指定子串的字符串。函数返回被替换了指定子串的字符串。 SPACE（integer_expression） SPACE函数返回一个有指定长度的空白字符串。函数返回一个有指定长度的空白字符串。 STUFF（character_expression1，start_ position，length，character_expression2） STUFF函数用另一子串替换字符串中指定位置长度的子串。函数用另一子串替换字符串中指定位置长度的子串。 数据类型转换函数数据类型转换函数 CAST（ AS length ） CONV

32、ERT（，length ， ，style） 北京林业大学北京林业大学 软件教研室软件教研室3737日期函数日期函数 DAY（）DAY函数返回函数返回date_expression 中的日期值。中的日期值。 MONTH（）MONTH函数返回函数返回date_expression中的月份值。中的月份值。 YEAR（）YEAR函数返回函数返回date_expression中的年份值。中的年份值。 DATEADD（ ）DATEADD函数返回指定日期函数返回指定日期date加上指定的额外日期间加上指定的额外日期间隔隔number产生的新日期。产生的新日期。 北京林业大学北京林业大学 软件教研室软件教研室

33、3838DATEDIFF（，）DATEDIFF函数返回两个指定日期在函数返回两个指定日期在datepart方面的不同方面的不同之处，即之处，即date2超过超过date1的差距值，其结果值是一个带有的差距值，其结果值是一个带有正负号的整数值。正负号的整数值。 DATENAME（，）DATENAME函数以字符串的形式返回日期的指定部分，此函数以字符串的形式返回日期的指定部分，此部分由部分由datepart 来指定。来指定。 DATEPART（，）DATEPART函数以整数值的形式返回日期的指定部分，此函数以整数值的形式返回日期的指定部分，此部分由部分由datepart 来指定。来指定。 GETD

34、ATE（）（）GETDATE函数以函数以DATETIME 的缺省格式返回系统当前的的缺省格式返回系统当前的日期和时间，它常作为其他函数或命令的参数使用。日期和时间，它常作为其他函数或命令的参数使用。北京林业大学北京林业大学 软件教研室软件教研室3939TEXT函数和函数和IMAGE函数函数 TEXTPTR（）TEXTPTR函数返回一个指向存储文本的第一个数据库页的函数返回一个指向存储文本的第一个数据库页的指针。指针。TEXTVALID（，）TEXTVALID函数用于检查指定的文本指针是否有效。函数用于检查指定的文本指针是否有效。 北京林业大学北京林业大学 软件教研室软件教研室4040用户自定义

35、函数用户自定义函数 从从SQL Server 2000开始，用户可以自定义函数，在开始，用户可以自定义函数，在SQL Server 2008中用户自定义函数作为一个数据库对象来管理，中用户自定义函数作为一个数据库对象来管理，可以利用可以利用T-SQL命令来创建（命令来创建（CREATE FUNCTION）、修）、修改（改（ALTER FUNCTION）和删除（）和删除（DROP FUNCTION）。）。（1）创建标量值函数）创建标量值函数标量值函数的函数体由一条或多条标量值函数的函数体由一条或多条T-SQL语句组成，这些语句以语句组成，这些语句以Begin开始，以开始，以End结束。创建标量值

36、函数的语法为：结束。创建标量值函数的语法为：CREATE FUNCTION function_name ( parameter_name As parameter_data_type = default READONLY ,.n )RETURNS return_data_type WITH ENCRYPTION AS BEGIN function_body Return scalar_expressionEND北京林业大学北京林业大学 软件教研室软件教研室4141【例【例7-12】 标量值函数示例。标量值函数示例。CREATE FUNCTION dbo.Fun1()RETURNS intAS

37、BEGIN declare n int select n=3 return nEND（2）创建内联表值函数）创建内联表值函数创建内联表值函数的语法如下：创建内联表值函数的语法如下：Create Function function_name ( parameter_name As parameter_data_type = default Readonly ,.n )Returns Table With Encryption As Return （select_statement）北京林业大学北京林业大学 软件教研室软件教研室4242【例【例7-13】 内嵌表值函数示例。内嵌表值函数示例。CRE

38、ATE FUNCTION dbo.Fun2()RETURNS TABLEAS return select Sno,Sname from S（3）多语句表值函数）多语句表值函数与内联表值函数不同的是，多语句表值函数在返回语句之前还有其与内联表值函数不同的是，多语句表值函数在返回语句之前还有其他的他的Transact-SQL语句，具体的语法如下：语句，具体的语法如下：Create Function function_name ( parameter_name As parameter_data_type = default Readonly ,.n )Returns return_variable

39、 Table With Encryption As Begin function_body ReturnEnd北京林业大学北京林业大学 软件教研室软件教研室4343【例【例7-14】 创建返回创建返回table的函数，通过学号作为实参调用该函数，的函数，通过学号作为实参调用该函数，显示该学生不及格的课程名及成绩。显示该学生不及格的课程名及成绩。Create function score_table(student_id char(6)returns T_score table(Cname varchar(20),Grade int)ASBEGIN INSERT INTO T_score SEL

40、ECT Cname,Score from SC,C WHERE SC.Cno=C.Cno and SC.Sno=student_id and Score60 RETURNEND/*多语句表值函数的调用多语句表值函数的调用*/select * from score_table(S3)北京林业大学北京林业大学 软件教研室软件教研室44447.2 存储过程存储过程 系统存储过程系统存储过程 扩展存储过程扩展存储过程 用户自定义存储过程用户自定义存储过程 7.2.1 存储过程的概念、优点及分类存储过程的概念、优点及分类 存储过程是一组为了完成特定功能的存储过程是一组为了完成特定功能的SQL语句集语句集

41、 。存储过程的优点存储过程的优点 ： 存储过程的分类：存储过程的分类： 模块化的程序设计模块化的程序设计 高效率的执行高效率的执行 减少网络流量减少网络流量 可以作为安全机制使用可以作为安全机制使用 北京林业大学北京林业大学 软件教研室软件教研室45457.2.2 创建存储过程创建存储过程 当创建存储过程时，需要确定存储过程的三个当创建存储过程时，需要确定存储过程的三个组成部分：组成部分：（1）所有的输入参数以及传给调用者的输出参数。）所有的输入参数以及传给调用者的输出参数。（2）被执行的针对数据库的操作语句，包括调用其他）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。存储过程的语

42、句。（3）返回给调用者的状态值以指明调用是成功还是失）返回给调用者的状态值以指明调用是成功还是失败。败。用用CREATE PROCEDURE命令创建存储过程命令创建存储过程 北京林业大学北京林业大学 软件教研室软件教研室4646CREATE PROCEDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 例例7-15

43、在在Teach数据库中，创建一个名称为数据库中，创建一个名称为MyProc的不带参数的存储过程，该存储过程的功能是从数据的不带参数的存储过程，该存储过程的功能是从数据表表S中查询所有男同学的信息。中查询所有男同学的信息。CREATE PROCEDURE MyProc ASSELECT * FROM S WHERE sex=男男 北京林业大学北京林业大学 软件教研室软件教研室4747例例7-16 定义具有参数的存储过程。在定义具有参数的存储过程。在Teach数据库数据库中，创建一个名称为中，创建一个名称为InsertRecord的存储过程，该的存储过程，该存储过程的功能是向存储过程的功能是向S数

44、据表中插入一条记录，新记数据表中插入一条记录，新记录的值由参数提供。录的值由参数提供。CREATE PROCEDURE InsertRecord ( sno char(6),sn char(20),age numeric(5),sex char(2),dept char(10)ASINSERT INTO S VALUES(sno,sn,age,sex,dept) 北京林业大学北京林业大学 软件教研室软件教研室4848例例7-17 定义具有参数默认值的存储过程。在定义具有参数默认值的存储过程。在Teach数数据库中，创建一个名称为据库中，创建一个名称为Insert RecordDefa的存储过的

45、存储过程，该存储过程的功能是向程，该存储过程的功能是向S数据表中插入一条记录，数据表中插入一条记录，新记录的值由参数提供，如果未提供系别新记录的值由参数提供，如果未提供系别Dept的值时，的值时，由参数的默认值代替。由参数的默认值代替。CREATE PROCEDURE InsertRecordDefa ( sno char(6),sn char(20),age numeric(5),sex char(2),dept char(10)= 无无)ASINSERT INTO S VALUES(sno,sn,age,sex,dept) 北京林业大学北京林业大学 软件教研室软件教研室4949例例7-18

46、 定义能够返回值的存储过程。在定义能够返回值的存储过程。在Teach数据库数据库中，创建一个名称为中，创建一个名称为QueryTeach的存储过程。该存储的存储过程。该存储过程的功能是从数据表过程的功能是从数据表S中根据学号查询某一同学的姓中根据学号查询某一同学的姓名和系别，查询的结果由参数名和系别，查询的结果由参数sn和和dept返回。返回。CREATE PROCEDURE QueryTeach ( sno char(6),sn char(20) OUTPUT,dept char(10) OUTPUT)ASSELECT sn=SN,dept=DeptFROM SWHERE SNo=sno北京

47、林业大学北京林业大学 软件教研室软件教研室5050利用对象资源管理器创建存储过程利用对象资源管理器创建存储过程具体操作步骤为：具体操作步骤为：（1）在选定的数据库下打开）在选定的数据库下打开“可编程性可编程性”节点。节点。（2）找到）找到“存储过程存储过程”节点，单击鼠标右键，节点，单击鼠标右键，在弹出的快捷菜单中选择在弹出的快捷菜单中选择“新建存储过程新建存储过程”。（3）在新建的查询窗口中可以看到关于创建存）在新建的查询窗口中可以看到关于创建存储过程的语句模板，在其中添上相应的内容，单击储过程的语句模板，在其中添上相应的内容，单击工具栏上的工具栏上的“执行执行”按钮即可。按钮即可。北京林业

48、大学北京林业大学 软件教研室软件教研室5151查看存储过程查看存储过程 重新命名存储过程重新命名存储过程 删除存储过程删除存储过程 执行存储过程执行存储过程 EXEC sp_helptext 存储过程名称存储过程名称 通过对象资源管理器很容易实现重命名存储过程通过对象资源管理器很容易实现重命名存储过程DROP PROCEDURE procedure ,n EXEC MyProc 修改存储过程修改存储过程 ALTER PROCEDURE procedure_name北京林业大学北京林业大学 软件教研室软件教研室5252【例【例7-21】 执行数据库执行数据库Teach中已定义的不带参数的存中已定

49、义的不带参数的存储过程储过程MyProc。EXEC MyProc【例【例7-22】 执行数据库执行数据库Teach中的带参数的存储过程中的带参数的存储过程InsertRecord，调用时向存储过程中传递，调用时向存储过程中传递5个参数值，个参数值，存储过程在执行过程中利用这存储过程在执行过程中利用这5个参数的值组成一条新个参数的值组成一条新记录，并插入到学生表记录，并插入到学生表S中。中。EXEC InsertRecord sno = S1, sn = 王大利王大利, age = 18, sex = 男男,dept= 计算机系计算机系北京林业大学北京林业大学 软件教研室软件教研室5353【例【

50、例7-23】 执行数据库执行数据库Teach中的带默认参数值的存储中的带默认参数值的存储过程过程InsertRecordDefa，调用时向存储过程中传递，调用时向存储过程中传递4个个参数值，而未给第参数值，而未给第5个参数个参数dept传递值，这样存储过传递值，这样存储过程程InsertRecordDefa在执行过程中，将利用参数在执行过程中，将利用参数dept的默认值的默认值“无无”进行运算。进行运算。 EXEC InsertRecordDefa sno = S10, sn = 高平高平, age = 18, sex = 女女【例【例7-24】 执行数据库执行数据库Teach中的带输出参数的

51、存储过中的带输出参数的存储过程程QueryTeach，存储过程，存储过程QueryTeach执行完毕后，所执行完毕后，所需结果保存到输出参数需结果保存到输出参数sn和和dept中。中。DECLARE sn char(20)DECLARE dept char(10)EXEC QueryTeach S10,sn OUTPUT,dept OUTPUTSELECT 姓名姓名 =sn, 系别系别=dept北京林业大学北京林业大学 软件教研室软件教研室54547.3 触发器触发器 7.3.1 触发器的概触发器的概述述 触发器是一种特殊类型的存储过程。触发器是一种特殊类型的存储过程。 使用触发器主要有以下优

52、点。使用触发器主要有以下优点。（1）触发器是自动执行的，在数据库中定义了某个对象之后，或）触发器是自动执行的，在数据库中定义了某个对象之后，或对表中的数据做了某种修改之后立即被激活。对表中的数据做了某种修改之后立即被激活。（2）触发器可以实现比约束更为复杂的完整性要求。）触发器可以实现比约束更为复杂的完整性要求。（3）触发器可以根据表数据修改前后的状态，根据其差异采取相）触发器可以根据表数据修改前后的状态，根据其差异采取相应的措施。应的措施。（4）触发器可以防止恶意的或错误的）触发器可以防止恶意的或错误的Insert、Update和和Delete操作。操作。北京林业大学北京林业大学 软件教研室

53、软件教研室5555使用触发器应遵守的规则使用触发器应遵守的规则在创建和使用触发器时，需要遵循下列规则。在创建和使用触发器时，需要遵循下列规则。（1）CREATE TRIGGER语句必须是批处理中的第一个语句，且该批处语句必须是批处理中的第一个语句，且该批处理中随后出现的其他所有语句都将被解释为理中随后出现的其他所有语句都将被解释为CREATE TRIGGER语句定义语句定义的一部分。的一部分。（2）每一个触发器都是一个数据对象，因此其名称必须遵循标识符的命）每一个触发器都是一个数据对象，因此其名称必须遵循标识符的命名规则。名规则。（3）在默认情况下，创建触发器的权限将分配给数据表的所有者，且不

54、）在默认情况下，创建触发器的权限将分配给数据表的所有者，且不可以将该权限转给其他用户。可以将该权限转给其他用户。（4）虽然触发器可引用当前数据库以外的对象，但只能在当前数据库中）虽然触发器可引用当前数据库以外的对象，但只能在当前数据库中创建触发器。创建触发器。（5）虽然不能在临时数据表上创建触发器，但是触发器可以引用临时数）虽然不能在临时数据表上创建触发器，但是触发器可以引用临时数据表。据表。（6）既不能在系统数据表创建触发器，也不可以引用系统数据表。）既不能在系统数据表创建触发器，也不可以引用系统数据表。（7）在包含使用）在包含使用DELETE或或UPDATE操作所定义的外键的表中，不能定操

55、作所定义的外键的表中，不能定义义INSTEAD OF和和INSTEAD OF UPDATE 触发器。触发器。（8）下面的语句不可以用于创建触发器：）下面的语句不可以用于创建触发器：ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RESIZE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。北京林业大学北京林业大学 软件教研室软件教研室5656触发器的种类触发器的种类DML触发器触发器DML触发器在用户对表中的数据进行插入（触发器在用户对表中的数

56、据进行插入（Insert）、修）、修改（改（Update）和删除（）和删除（Delete）操作时自动运行。）操作时自动运行。 DDL触发器触发器DDL触发器不会被针对表或视图的触发器不会被针对表或视图的 Update、Insert 或或 Delete 语句触发语句触发 登录触发器登录触发器登录触发器将是由登录（登录触发器将是由登录（LOGON）事件而激活的触发器，）事件而激活的触发器，与与 SQL Server 实例建立用户会话时将引发此事件。实例建立用户会话时将引发此事件。北京林业大学北京林业大学 软件教研室软件教研室57577.3.2 触发器的工作原理触发器的工作原理 SQL Server

57、在工作时为每个触发器在服务器的内存在工作时为每个触发器在服务器的内存上建立两个特殊的表：插入表和删除表。上建立两个特殊的表：插入表和删除表。（1）插入表的功能）插入表的功能插入表用来存储向原表插入的内容插入表用来存储向原表插入的内容（2）删除表的功能）删除表的功能删除表用来存储所有的删除行删除表用来存储所有的删除行 北京林业大学北京林业大学 软件教研室软件教研室5858INSERT触发器的工作原理触发器的工作原理北京林业大学北京林业大学 软件教研室软件教研室5959DELETE触发器的工作原理触发器的工作原理北京林业大学北京林业大学 软件教研室软件教研室6060UPDATE触发器的工作原理触发

58、器的工作原理北京林业大学北京林业大学 软件教研室软件教研室61617.3.3 创建触发器创建触发器 创建创建DML触发器触发器使用使用Create Trigger创建创建DML触发器触发器使用使用Create Trigger创建创建DML触发器的语法格式为：触发器的语法格式为：Create Trigger trigger_name On table | view With EncryptionFor | After | Instead Of Insert , Update , Delete As sql_statement ;北京林业大学北京林业大学 软件教研室软件教研室6262【例【例7-2

59、5】 设计一个触发器，该触发器的作用为：设计一个触发器，该触发器的作用为：当在学生表当在学生表S中删除某一个学生时，在学生选课表中删除某一个学生时，在学生选课表SC中的成绩记录也全部被删除。中的成绩记录也全部被删除。提示：在此例中，由于涉及了学生表的删除操作，提示：在此例中，由于涉及了学生表的删除操作，因而需要设计一个因而需要设计一个DELETE类型的触发器。类型的触发器。在新建的查询窗口中输入如下语句：在新建的查询窗口中输入如下语句：GOCREATE TRIGGER del_S ON SAFTER DELETEAS DELETE FROM SC WHERE SC.Sno IN (SELECT

60、 Sno FROM DELETED)GO该触发器建立完毕后，当执行如下操作时，将会连带删除该触发器建立完毕后，当执行如下操作时，将会连带删除SC表中表中S1学生的选课全部记录。学生的选课全部记录。 Delete from S where Sno=S1北京林业大学北京林业大学 软件教研室软件教研室6363【例【例7-26】 设计一个触发器，该触发器能够保证在学生选课表设计一个触发器，该触发器能够保证在学生选课表SC表中添加新的记录时，新学生的学号必须已经存在于学生基本信息表表中添加新的记录时，新学生的学号必须已经存在于学生基本信息表S中。中。在新建的查询窗口中输入如下语句：在新建的查询窗口中输入如下语句