第7章 SQL Server 2000高级应用.ppt

1、第7章 SQL Server 2000高级应用,2,7.1 Transact-SQL程序设计 7.2 存储过程 7.3 触发器 7.4 备份和还原,3,7.1 Transact-SQL程序设计,Transact-SQL语言就是在标准SQL的基础上进行扩充而推出的SQL Server专用的结构化SQL。 Transact-SQL语言最主要的用途是设计服务器端的能够在后台执行的程序块。 7.1.1 Transact-SQL程序的结构与批处理 Transact-SQL程序的结构 以下程序是打开教学管理数据库TEACH，并从数据表SC中读取学号为“S1”同学所学课程的平均分，如果该平均分大于或等于60

2、分，则程序输出“课程平均成绩超过60”，否则输出“课程平均成绩不超过60”。,存储过程、触发器等,4,/*Transact-SQL程序的实例*/ USE Teach /*将教学管理数据库Teach置为当前数据库*/ GO DECLARE AvgScore DECIMAL /*定义变量AvgScore */ SET AvgScore=60 /*给变量AvgScore赋值初值*/ IF(SELECT AVG(Score) FROM SC WHERE SNo=S1)=AvgScore -输出结果 PRINT 课程平均成绩超过+CONVERT(VARCHAR(10),AvgScore) ELSE -输

3、出结果 PRINT 课程平均成绩不超过+CONVERT(VARCHAR(10),AvgScore) -执行批处理 GO,5,Transact-SQL程序的批处理 在Transact-SQL程序内两个“GO”标记符之间的代码称为一个“批”。 SQL Server对Transact-SQL程序的编译和执行是按照“批”为单位来进行的，称为批处理。 一个Transact-SQL程序内可以包含多个“批” 。,通过该程序可以看出，一个Transact-SQL程序与一般的高级语言 的语法要素是基本一致的，主要含有注释、变量与常量、各种运算符、 函数与表达式、流程控制语句、批处理等 。,6,7.1.2 变量,

4、局部变量 DECLARE 变量名 变量类型 ，变量名 变量类型 SELECT 局部变量=变量值 或 SET 局部变量=变量值 例7-1 声明一个长度为8个字符的变量id，并赋值。 DECLARE id char(8) SELECT id=10010001,7,全局变量 全局变量由系统定义和维护的，只能使用预先说明及定义的全局变量。 全局变量对用户而言是只读的，用户无法对它们进行修改或管理 。 注释符 在Transact-SQL中可以使用两类注释符： （1）ANSI标准的注释符“- -”用于单行注释； （2）与C语言相同的程序注释符，即“/*/”，“/*”用于注释文字的开头，“*/”用于注释文字

5、的结尾，可在程序中标识多行文字为注释。,8,7.1.3 流程控制命令,BEGIN END,IF ELSE ,IF NOT EXISTS(SELECT 子查询) ELSE ,CASE WHEN THEN WHEN THEN ELSE END,CASE,9,WHILE BEGIN BREAK CONTINUE 命令行或程序块 END,WAITFOR DELAY | TIME | ERROREXIT | PROCESSEXIT | MIRROREXIT,GOTO 标识符,RETURN（整数值）,10,7.1.4 常用命令,BACKUP 用于将数据库内容或其事务处理日志备份到存储介质上（软盘、硬盘、磁

6、带等）。 CHECKPOINT 用于将当前工作的数据库中被更改过的数据页或日志页从数据缓冲器中强制写入硬盘。 DBCC 用于验证数据库完整性、查找错误、分析系统使用情况等。 DBCC CHECKALLOC,11,DECLARE DECLARE的语法格式如下： DECLARE local_variable data_type | cursor_variable_name CURSOR | table_type_definition , .n DECLARE命令用于声明一个或多个局部变量、游标变量或表变量。 例： DECLARE x CHAR, y CHAR(10) SELECT x =123,

7、y =data_type PRINT x PRINT y,12,EXECUTE 或 EXEC EXECUTE命令用来执行存储过程 。 KILL KILL命令用于终止某一过程的执行。 PRINT PRINT的语法格式如下： PRINT any ASCII text | local_variable | FUNCTION | string_expression PRINT 命令向客户端返回一个用户自定义的信息，即显示一个字符串、局部变量或全局变量。 如果变量值不是字符串的话，必须先用数据类型转换函数CONVERT()将其转换为字符串。,13,RAISERROR 用于在SQL Server 系统返回

8、错误信息时，同时返回用户指定的信息。 READTEXT READTEXT命令的语法格式如下： READTEXT table.column text_pointer offset size HOLDLOCK READTEXT 命令用于从数据类型为TEXT、NTEXT 或IMAGE 的列中读取数据。 RESTORE RESTORE 命令用来将数据库或其事务处理日志备份文件由存储介质回存到SQL Server系统中。,14,SELECT SELECT 命令可用于给变量赋值，其语法格式如下： SELECT local_variable = expression ，.n SELECT 命令可以一次给多个

9、变量赋值。 SET 命令有两种用法。 （1）用于给局部变量赋值。 SET 命令一次只能给一个变量赋值。 （2）用于用户执行SQL 命令时，SQL Server 处理选项的设定 。 SET：选项ON； SET：选项OFF； SET：选项值。,15,SHUTDOWN SHUTDOWN WITH NOWAIT SHUTDOWN 命令用于停止SQL Server 的执行。 WRITETEXT WRITETEXT table.column text_pointer WITH LOG data WRITETEXT 命令用于向数据类型为TEXT、NTEXT 或IMAGE 的列中写入数据。 USE USE d

10、atabase USE命令用于改变当前使用的数据库为指定的数据库。,16,7.1.5 常用函数,统计函数 STDEV函数 STDEV函数返回表达式中所有数据的标准差。 STDEVP函数 STDEVP 函数返回表达式中所有数据的总体标准差。 VAR函数 VAR函数返回表达式中所有数据的统计变异数。 VARP函数 VARP函数返回表达式中所有数据的总体变异数。,17,算术函数,18,19,字符串函数 字符转换函数 ASCII（character_expression） 返回字符表达式最左端字符的ASCII 码值 CHAR （integer_expression） CHAR函数用于将ASCII 码转

11、换为字符 LOWER （character _expression） LOWER函数用于把字符串全部转换为小写 UPPER （character _expression） UPPER函数用于把字符串全部转换为大写 STR（float _expression ，length，） STR函数用于把数值型数据转换为字符型数据,20,去空格函数 LTRIM （character _expression） LTRIM函数用于把字符串头部的空格去掉。 RTRIM （character _expression） RTRIM函数用于把字符串尾部的空格去掉 。 取子串函数 LEFT（character_expr

12、ession, integer_expression） LEFT函数返回的子串是从字符串最左边起到第integer_expression 个字符的部分。 RIGHT（character_expression, integer_expression） RIGHT函数返回的子串是从字符串右边第integer_expression 个字符起到最后一个字符的部分。,21,取子串函数 SUBSTRING (expression, starting_ position, length) SUBSTRING函数返回的子串是从字符串左边第starting_position个字符起length个字符的部分。 字

13、符串比较函数 CHARINDEX （substring_expression，expression） CHARINDEX函数返回字符串中某个指定的子串出现的开始位置 。 PATINDEX（%substring_expression%，expression） 与CHARINDEX函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、VARCHAR和TEXT 数据类型。,22,字符串比较函数 SOUNDEX（character _expression） SOUNDEX函数返回一个四位字符码 。 DIFFERENCE （character_expression1，char

14、acter_expression2） DIFFERENCE函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异 。 值的差异是用0、1、2、3、4 来表示的，含义如下： 0 两个SOUNDEX函数返回值的第一个字符不同； 1 两个SOUNDEX函数返回值的第一个字符相同； 2 两个SOUNDEX函数返回值的第一、二个字符相同； 3 两个SOUNDEX函数返回值的第一、二、三个字符相同； 4 两个SOUNDEX函数返回值完全相同。,23,字符串操作函数 QUOTENAME（character_expression ，quote_ character） QUOTENAME函数返回被特定字符

15、括起来的字符串。 REPLICATE（character_expression，integer_expression） REPLICATE函数返回一个重复指定次数的字符串。 REVERSE（character_expression） REVERSE函数将指定的字符串的字符排列顺序颠倒。,24,REPLACE（string_expression1，string_expression2，string_expression3） REPLACE函数返回被替换了指定子串的字符串。 SPACE（integer_expression） SPACE函数返回一个有指定长度的空白字符串。 STUFF（charac

16、ter_expression1，start_ position，length，character_expression2） STUFF函数用另一子串替换字符串中指定位置长度的子串。 数据类型转换函数 CAST（ AS length ） CONVERT（，length ， ，style）,25,日期函数 DAY（） DAY函数返回date_expression 中的日期值。 MONTH（） MONTH函数返回date_expression中的月份值。 YEAR（） YEAR函数返回date_expression中的年份值。 DATEADD（ ） DATEADD函数返回指定日期date加上指定的额

17、外日期间隔number产生的新日期。,26,DATEDIFF（，） DATEDIFF函数返回两个指定日期在datepart方面的不同之处，即date2超过date1的差距值，其结果值是一个带有正负号的整数值。 DATENAME（，） DATENAME函数以字符串的形式返回日期的指定部分，此部分由datepart 来指定。 DATEPART（，） DATEPART函数以整数值的形式返回日期的指定部分，此部分由datepart 来指定。 GETDATE（） GETDATE函数以DATETIME 的缺省格式返回系统当前的日期和时间，它常作为其他函数或命令的参数使用。,27,TEXT函数和IMAGE函

18、数 TEXTPTR（） TEXTPTR函数返回一个指向存储文本的第一个数据库页的指针。 TEXTVALID（，） TEXTVALID函数用于检查指定的文本指针是否有效。 用户自定义函数 从SQL Server 2000 开始，用户可以自定义函数，在SQL Server 2000 中用户自定义函数作为一个数据库对象来管理，可以使用企业管理器或Transact-SQL 命令来进行用户自定义函数的创建、修改和删除。,28,7.2 存储过程,系统存储过程,扩展存储过程,用户自定义存储过程,7.2.1 存储过程的概念、优点及分类 存储过程是一组为了完成特定功能的SQL语句集 。 存储过程的优点 ： 存储

19、过程的分类： 模块化的程序设计 高效率的执行 减少网络流量 可以作为安全机制使用,29,7.2.2 创建存储过程,当创建存储过程时，需要确定存储过程的三个组成部分： （1）所有的输入参数以及传给调用者的输出参数。 （2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。 （3）返回给调用者的状态值以指明调用是成功还是失败。 用CREATE PROCEDURE命令创建存储过程,30,CREATE PROCEDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE

20、 | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 例7-11 在Teach数据库中，创建一个名称为MyProc的不带参数的存储过程，该存储过程的功能是从数据表S中查询所有男同学的信息。 CREATE PROCEDURE MyProc AS SELECT * FROM S WHERE sex=男,31,例7-12 定义具有参数的存储过程。在Teach数据库中，创建一个名称为InsertRecord的存储过程，该存储过程的功能是向S数据表中插入一条记录，新记录的值由参数提供。 CREATE PROC

21、EDURE InsertRecord (sno char(6), sn char(20), age numeric(5), sex char(2), dept char(10) ) AS INSERT INTO S VALUES(sno,sn,age,sex,dept),32,例7-13 定义具有参数默认值的存储过程。在Teach数据库中，创建一个名称为Insert RecordDefa的存储过程，该存储过程的功能是向S数据表中插入一条记录，新记录的值由参数提供，如果未提供系别Dept的值时，由参数的默认值代替。 CREATE PROCEDURE InsertRecordDefa (sno c

22、har(6), sn char(20), age numeric(5), sex char(2), dept char(10)= 无 ) AS INSERT INTO S VALUES(sno,sn,age,sex,dept),33,例7-14 定义能够返回值的存储过程。在Teach数据库中，创建一个名称为QueryTeach的存储过程。该存储过程的功能是从数据表S中根据学号查询某一同学的姓名和系别，查询的结果由参数sn和dept返回。 CREATE PROCEDURE QueryTeach (sno char(6), sn char(20) OUTPUT, dept char(10) OUT

23、PUT ) AS SELECT sn=SN,dept=Dept FROM S WHERE SNo=sno,34,查看存储过程,重新命名存储过程,删除存储过程,执行存储过程,修改存储过程,EXEC sp_helptext 存储过程名称,sp_rename 原存储过程名, 新存储过程名,DROP PROCEDURE procedure ,n,ALTER PROCEDURE procedure_name,EXEC MyProc,35,7.3 触发器 SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。,7.3.1 DML触发器的概念、分类与作用 触发器是一种特

24、殊类型的存储过程。 触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用。 触发器有4个要素： 名称：触发器有一个符合标志符命名规则的名称。 定义的目标：触发器必须定义在表或者视图上。 触发条件：是UPDATE、INSERT还是DELETE语句。 触发逻辑：触发之后如何处理。,36,强化约束,跟踪变化,存储过程的调用,级联运行,触发器的种类 AFTER触发器 AFTER触发器是告诉SQL语句执行了INSERT、UPDATE或者DELETE操作后干什么。 1个操作可定义多个AFTER触发器，用系统过程sp_settriggerorder 设置触发顺序。 INSTEA

25、D OF触发器 告诉当要执行INSERT、UPDATE或DELETE操作时用什么别的操作来代替。 1个操作只能定义1个INSTEAD OF触发器。 触发器的作用,37,7.3.2 触发器的工作原理,SQL Server在工作时为每个触发器在服务器的内存上建立两个特殊的表：插入表(Inserted)和删除表(Deleted)。 （1）插入表的功能 一旦对该表执行了插入（INSERT）操作，那么对该表插入的所有行来说，都有一个相应的副本存放到Inserted表中，即Inserted表用来存储原表插入的内容。 （2）删除表的功能 一旦对该表执行了删除（DELETE）操作，则将所有的删除行存放至Del

26、eted表中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从Deleted表中得以还原。 （3）UPDATE 操作包括2部分，即先把老内容删除，再把新值插入 在Deleted表中存放旧值，然后在Inserted表中存放新值。,38,7.3.3 创建触发器,在创建触发器以前必须考虑到以下几个方面： CREATE TRIGGER语句必须是批处理的第一个语句。 表的所有者具有创建触发器的缺省权限，表的所有者不能把该权限传给其他用户。 触发器是数据库对象，所以其命名必须符合命名规则。 尽管在触发器的SQL语句中可以引用其他数据库中的对象，但是触发器只能创建在当前数据库中。

27、 虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。不应引用系统表，而应使用信息架构视图。 不能在视图上定义 AFTER 触发器。 INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。 一个触发器只能对应一个表，这是由触发器的机制决定的。 尽管TRUNCATE TABLE语句如同没有WHERE从句的DELETE语句，但是由于TRUNCATE TABLE语句没有被记入日志，所以该语句不能触发DELETE型触发器。 WRITETEXT语句不能触发INSERT或

28、UPDATE型的触发器。,39,TRUNCATE TABLE 若要删除表中的所有行，则 TRUNCATE TABLE 语句是一种快速、无日志记录的方法。该语句总是比不带条件的 DELETE 语句要快，因为 DELETE 语句要记录对每行的删除操作，而 TRUNCATE TABLE 语句只记录整个数据页的释放。TRUNCATE TABLE 语句立即释放由该表的数据和索引占用的所有空间。所有索引的分发页也将释放。 与 DELETE 语句相同，使用 TRUNCATE TABLE 清空的表的定义，同其索引和其它相关的对象一起仍保留在数据库中。必须使用 DROP TABLE 语句才能除去表的定义。 语法

29、 TRUNCATE TABLE name 下例删除 authors 表中的所有数据。 TRUNCATE TABLE authors,40,例7-22 创建一个触发器，当向S表中插入一条记录时，自动显示S表中的记录。 CREATE TRIGGER ChangeDisplay ON S AFTER INSERT AS SELECT * FROM S 使用系统存储过程查看触发器 EXEC sp_help 触发器名 了解触发器的一般信息，如触发器的名字、属性、类型、创建时间,41,EXEC sp_helptext 触发器名 查看触发器的正文信息 EXEC sp_depends 触发器名 EXEC sp

30、_depends 表名 查看指定触发器所引用的表或指定的表所涉及到的所有触发器 修改触发器 使用sp_rename修改触发器的名字 sp_rename oldname,newname,42,通过ALTER TRIGGER命令修改触发器正文 ALTER TRIGGER trigger_name ON（table | view） WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE NOT FOR REPLICATION AS 删除触发器 用系统命令DROP TRIGGER删除指定的触发器 DROP TRIGGER 触发器名 删除触发器所在的