1、第8章 存储过程和触发器 第8章 存储过程和触发器 8.1 T-SQL语言 8.2 事务 8.3 存储过程 8.4 触发器【小结】【习题】第8章 存储过程和触发器【课程要求课程要求】1熟悉存储过程和触发器的概念。2能使用函数、变量。3会简单使用T-SQL语句建立和执行存储过程。4会简单使用T-SQL语句建立触发器。第8章 存储过程和触发器【本章涉及的需求分析内容本章涉及的需求分析内容】1使用T-SQL中变量、常量、函数、分支语句、循环语句。2使用存储过程：利用带参数、带输入参数、带输入/输出参数的存储过程，完成对数据的增、删、查、改。这样可以使应用程序代码简单，并隐蔽数据访问的代码。3触发器：
2、利用触发器实现关联表，修改一个主键表内容，触发外键表的同时修改。第8章 存储过程和触发器【课程内容课程内容】存储过程是将一些固定的操作集中起来，由数据库服务器来完成特定任务的一种方法。存储过程可以加快系统操作、封装复杂操作、实现代码重用，安全性高，并能减少网络流量。数据库管理员在进行数据管理或程序员进行数据库应用程序开发时，都希望在一个表中的数据插入或删除后，与之关联的另一个表也能根据业务规则自动完成插入或删除操作。这种情况使用触发器来完成。触发器是保证数据完整性和实施业务规则的一种有效的方法。第8章 存储过程和触发器 存储过程是通过存储过程名来调用的，而触发器是通过事件进行触发而被执行的。本
3、章主要介绍变量、运算符、流程控制语句、函数，事务，存储过程的创建和执行，触发器的创建和删除。第8章 存储过程和触发器 8.1 T-SQL语言语言Transact-SQL语言是SQL Server 2005对标准SQL语言的扩充，如引入了程序设计的思想，增强了程序的流程控制语句等。因此，在Transact-SQL语言中，标准的SQL语句畅通无阻。Transact-SQL语言最主要的用途是设计服务器端能够在后台执行的程序块，如存储过程、触发器等。第8章 存储过程和触发器 8.1.1 变量变量Transact-SQL中可以使用两种变量：局部变量和全局变量。1.局部变量局部变量局部变量是用户可自定义的
4、变量，它的作用范围仅在程序内部。局部变量在程序中通常用来储存从表中查询到的当选数据，或当作程序执行过程式中的暂存变量。局部变量必须以开头，而且必须先用DECLARE命令说明后才可使用。其说明形式如下：DECLARE 变量名 变量类型,其中，变量类型可以是SQL Server 2005支持的所有数据类型。第8章 存储过程和触发器 在Transact-SQL中不像一般的程序语言中一样使用“变量=变量值”来给变量赋值，而使用SELECT或SET命令来设定变量的值。其语法如下：SELECT 局部变量=变量值SET 局部变量=变量值例例8-1 声明一个长度为8个字符的变量id，并赋值。DECLARE i
5、d char(8)SELECT id=10010001第8章 存储过程和触发器 2.全局变量全局变量全局变量是SQL Server 2005系统内部使用的变量，其使用范围并不局限于某一程序，可被任何程序随时调用。全局变量通常存储一些SQL Server 2005的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。全局变量不是由用户的程序定义的，而是由系统定义和维护的，只能使用预先说明及定义的全局变量。引用全局变量时必须以“”开头。局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出错。第8章 存储过程和触发器 常用的全局变
6、量有以下几个。DATEFIRST：返回每周第一天的数字。IDENTITY：返回最后插入的标识值。LANGUAGE：返回当前使用的语言名。ROWCOUNT：返回受上一语句影响的行数。SERVERNAME：返回运行的本地服务器名称。TRANCOUNT：返回当前连接的活动事务数。第8章 存储过程和触发器 3.注释符注释符在Transact-SQL中可以使用两种注释符：(1)ANSI标准的注释符“-”，用于单行注释。(2)与C语言相同的注释符号，即“/*/”，可在程序中注释多行文字。“/*”用于注释文字的开头，“*/”用于注释文字的结尾。第8章 存储过程和触发器 8.1.2 运算符运算符运算符是一种符
7、号，用来指定要在一个或多个表达式中指定的操作。SQL Server 2005中使用如下几种运算符：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符、字符串运算符和一元运算符。第8章 存储过程和触发器 1.算术运算符算术运算符算术运算符用来在两个表达式上执行数学运算，这两个表达式可以是任意两个数值数据类型的表达式。算术运算符包括+(加)、(减)、*(乘)、/(除)、%(模)五个。在Transact-SQL中，“+”包含了三个方面的意义：(1)表示正号，即在数值前添加“+”号表示该数值是一个正数。(2)表示算术运算的加号，能将数值类型的两个数据相加。(3)连接两个字符型或binary型的数
8、据，这时的“+”号叫做字符串串联运算符。第8章 存储过程和触发器 2.赋值运算符赋值运算符Transact-SQL有一个赋值运算符，即等号(=)。例例8-2 下面的代码创建了SNo变量。然后用赋值运算符将SNo设置成一个由表达式返回的值。DECLARE SNo char(8)SET SNo=07303103第8章 存储过程和触发器 3.位运算符位运算符位运算符在两个表达式之间执行位操作，这两个表达式可以是任意两个整型数据类型的表达式。位运算符的符号及其含义如表8-1所示。第8章 存储过程和触发器 位运算符的操作数可以是整型或二进制字符串数据类型中的任何数据类型(但image 数据类型除外)，此
9、外，两个操作数不能同时为二进制字符串数据类型中的某种数据类型。第8章 存储过程和触发器 4.比较运算符比较运算符比较运算符用来测试两个表达式是否相同。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。比较运算符的符号及其含义如表8-2所示。第8章 存储过程和触发器 第8章 存储过程和触发器 比较运算的结果是布尔数据类型，它有三种值：TRUE、FALSE和NULL。返回布尔数据类型的表达式被称为布尔表达式。和其他SQL Server 2005数据类型不同，不能将布尔数据类型指定为表列或变量的数据类型，也不能在结果集中返回布尔数据类型。在WHERE子句中使用带
10、有布尔数据类型的表达式可以筛选出符合搜索条件的行，也可以在流程控制语言语句(例如IF和WHILE)中使用布尔表达式。第8章 存储过程和触发器 5.逻辑运算符逻辑运算符逻辑运算符用来对某个条件进行测试，以获得其真实情况。逻辑运算和比较运算一样，返回带有TRUE或FALSE值的布尔数据类型。逻辑运算符的符号及其含义如表8-3所示。第8章 存储过程和触发器 第8章 存储过程和触发器 6.一元运算符一元运算符一元运算符只对一个表达式执行操作，这个表达式可以是数字数据类型中的任何一种数据类型，如表8-4所示。第8章 存储过程和触发器 8.1.3 流程控制语句流程控制语句SQL语言使用的流程控制命令与常见
11、的程序设计语言类似，主要有以下几种控制命令。1.BEGINENDBEGINEND的语法格式如下：BEGINENDBEGINEND用来设定一个程序块，将BEGINEND内的所有程序视为一个单元执行。BEGINEND经常在条件语句(IFELSE)中使用。在BEGINEND中可嵌套另外的BEGINEND来定义另一个程序块。第8章 存储过程和触发器 2.IFELSEIFELSE的语法格式如下：IF ELSE条件表达式其中，可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IFELSE用来判断当某一条件成立时执行某段程序，条件不成
12、立时执行另一段程序。如果不使用程序块，那么IF或ELSE只能执行一条命令。IFESLE可以进行嵌套，在Transact-SQL中最多可嵌套32级。第8章 存储过程和触发器 例例8-3 从StudentScore数据中求出学号为07303103的同学的平均成绩，如果此平均成绩大于或等于60分，则输出“pass”信息。IF(SELECT AVG(SSScore)FROM StudentScore WHERE SNo=07303103 GROUP BY SNo)=60BEGINPRINT passEND第8章 存储过程和触发器 3.CASECASE 命令有两种语句格式。1)格式1CASE WHEN
13、THEN WHEN THEN ELSE END第8章 存储过程和触发器 该语句的执行过程是：将CASE后面表达式的值与各WHEN子句中表达式的值进行比较，如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句返回NULL。第8章 存储过程和触发器 例例8-4 从学生表StudentInformation中，选取SNo，SSex，如果SSex为“男”则输出“M”；如果SSex为女则输出“F”。USE StudentScoreManager SELECT SN
14、o,SSex=CASE SSex WHEN 男THEN M WHEN 女 THEN F ENDFROM StudentInformation执行结果如图8-1所示。第8章 存储过程和触发器 图8-1 例8-4执行结果 第8章 存储过程和触发器 2)格式2CASE WHEN THEN WHEN THEN ELSE END该语句的执行过程是：首先测试WHEN后的表达式的值，如果其值为真，则返回THEN后面的表达式的值；否则测试WHEN子句中表达式的值。如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值。如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。第8章
15、存储过程和触发器 注意：CASE命令可以嵌套在SQL命令中。例例8-5 从StudentScore表中查询所有同学选课成绩情况，凡成绩为空者均输出“未考”，小于60分的输出“不及格”，6070分的输出“及格”，7090分的输出“良好”，大于或等于90分的输出“优秀”。USE StudentScoreManagerSELECT SSno,SSCID,SSScore=第8章 存储过程和触发器 CASEWHEN SSScore IS NULL THEN 未考 WHEN SSScore=60 AND SSScore=70 AND SSScore=90 THEN优秀ENDFROM StudentScor
16、e执行结果如图8-2所示。第8章 存储过程和触发器 图8-2 例8-5执行结果 第8章 存储过程和触发器 4.WHLIECONTINUEBREAKWHILECONTINUEBREAK的语法格式如下：WHILEBEGINBREAKCONTINUE命令行或程序块ENDWHILE命令在设定的条件成立时会重复执行命令行或程序块；CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行，继续进行下一次循环；BREAK命令则让程序完全跳出循环，结束WHILE命令的执行。WHILE语句也可以嵌套。第8章 存储过程和触发器 例例8-6 用WHILE语句使得StudentSc
17、ore表中的平均成绩在60分以上。USE StudentScoreManagerWHILE(SELECT AVG(SSScore)FROM StudentScore)60BEGINUPDATE StudentScore SET SSScore=SSScore+1 WHERE SSSCORE IS NOT NULLEND第8章 存储过程和触发器 5.WAITFORWAITFOR的语法格式如下：WAITFORDELAY|TIMEWAITFOR命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到，再继续往下执行。其中“时间”必须为datetime类型的数据，但不能包括日期。参数说明：
18、DELAY：用来设定等待的时间，最多可达24小时。TIME：用来设定等待结束的时间点。例8-7 等待1小时2分3秒后执行SELECT语句。WAITFOR DELAY 01:02:03SELECT*FROM StudentScore第8章 存储过程和触发器 6.RETURNRETURN的语法格式如下：RETURN(整数值)RETURN命令用于结束当前程序的执行，返回到上一个调用它的程序或其他程序。在括号内可指定一个返回值；如果没有指定返回值，则SQL Server系统会根据程序执行的结果返回一个内定值，如表8-5所示。第8章 存储过程和触发器 第8章 存储过程和触发器 如果运行过程中产生了多个错
19、误，则SQL Server 2005系统将返回绝对值最大的内定值；但如果此时定义了返回值，则返回用户定义的值。RETURN语句不能返回NULL值。第8章 存储过程和触发器 8.1.4 常用函数常用函数1算数函数算数函数算数函数可对数据类型为整型、浮点型、实型、货币型和smallmoney型的类进行操作，它的返回值是6位小数。如果使用出错，则返回NULL值，并显示警告信息。可以在SELECT语句的SELECT和WHERE子句及表达式中使用算数函数。Transact-SQL中的算数函数如表8-6所示。第8章 存储过程和触发器 第8章 存储过程和触发器 第8章 存储过程和触发器 例例8-8 求指定角
20、度的正弦值。DECLARE angle floatSET angle=45.175643SELECT The SIN of the angle is:+CONVERT(varchar,SIN(angle)执行结果为：The SIN of the angle is:0.929607 第8章 存储过程和触发器 2字符串函数字符串函数字符串函数对二进制数据、字符串和表达式执行不同的运算。字符串函数作用于char、varchar、binary和varbinary数据类型，以及可以隐式转换为char或varchar的数据类型。可以在SELECT语句的SELECT和WHERE子句及表达式中使用字符串函数。
21、常见的字符串函数有以下几种：第8章 存储过程和触发器 1)字符转换函数(1)ASCII函数：ASCII函数返回字符表达式最左端字符的ASCII码值，其语法如下：ASCII()在ASCII函数中，纯数字的字符串可不用“”括起来，但含其他字符的字符串必须用“”括起来，否则会出错。第8章 存储过程和触发器 例例8-9 求字符A的ASCII值。SELECT ASCII(A)执行结果为：65(2)CHAR函数：CHAR函数用于将ASCII码转换为字符，其语法格式如下：CHAR()如果没有输入0255之间的ASCII码值，则CHAR函数会返回一个NULL值。第8章 存储过程和触发器 例例8-10 求66对
22、应的ASCII字符。SELECT CHAR(66)执行结果为：B(3)LOWER函数：LOWER函数把字符串全部转换为小写，其语法格式如下：LOWER()第8章 存储过程和触发器 例例8-11 将字符串STUDENT转换为小写。SELECT LOWER(STUDENT)执行结果为：student(4)UPPER函数：UPPER函数把字符串全部转换为大写，其语法格式如下：UPPER()第8章 存储过程和触发器 例例8-12 将字符串student转换为大写。SELECT UPPER(student)执行结果为：STUDENT(5)STR函数：STR函数把数值型数据转换为字符型数据，其语法格式如下
23、：STR(,length，)第8章 存储过程和触发器 自变量length和decimal必须是非负值，length指定返回的字符串长度；decimal指定返回的小数位数。如果没有指定长度，则length的缺省值为10，decimal缺省值为0。小数位数大于decimal值时，STR函数将其下一位四舍五入。指定长度应大于或等于数字的符号位数、小数点前的位数、小数点位数和小数点后的位数之和。如果小数点前的位数超过了指定的长度，则返回指定长度的整数。第8章 存储过程和触发器 例例8-13 将3124转换为字符型数据。SELECT STR(3124)执行结果为：3124第8章 存储过程和触发器 2)去
24、空格函数(1)LTRIM函数：LTRIM函数把字符串头部的空格去掉，其语法格式如下：LTRIM()例例8-14 使用LTRIM删除字符变量中的前导空格。DECLARE string_to_trim varchar(60)SET string_to_trim=Five spaces are at the beginning of this string.SELECT Here is the string without the leading spaces:+LTRIM(string_to_trim)第8章 存储过程和触发器 执行结果为：Here is the string without th
25、e leading spaces:Five spaces are at the beginning of this string.(2)RTRIM函数：RTRIM函数把字符串尾部的空格去掉，其语法格式如下：RTRIM()例例8-15 使用RTRIM删除字符变量中的尾随空格。DECLARE string_to_trim varchar(60)SET string_to_trim=Four spaces are after the period in this sentence.SELECT Here is the string without the leading spaces:+CHAR(1
26、3)+RTRIM(string_to_trim)第8章 存储过程和触发器 执行结果为：Here is the string without the leading spaces:Four spaces are after the period in this sentence.在许多情况下，往往需要得到头部和尾部都没有空格字符的字符串，这时可将上两个函数嵌套使用。第8章 存储过程和触发器 3)取字符串函数(1)LEFT函数：LEFT函数返回部分字符串，其语法格式如下：LEFT(，)LEFT函数返回的子串是从字符串最左边起到第integer_expression个字符的部分。若integer_e
27、xpression为负值，则返回NULL值。例例8-16 取出STUDENT前三个字符。SELECT LEFT(STUDENT,3)执行结果为：STU第8章 存储过程和触发器(2)RIGHT函数：RIGHT函数返回部分字符串，其语法格式如下：RIGHT(，)RIGHT函数返回的子串是从字符串右边第integer_expression个字符起到最后一个字符的部分。若integer_expression为负值，则返回NULL值。例例8-17 取出STUDENT后三个字符。SELECT RIGHT(STUDENT,3)执行结果为：ENT(3)SUBSTRING函数：SUBSTRING函数返回部分字符
28、串，其语法格式如下：SUBSTRING(，length)第8章 存储过程和触发器 SUBSTRING函数返回的子串是从字符串左边第starting_position个字符起length个字符的部分。其中表达式可以是字符串、二进制串或含字段名的表达式。SUBSTRING函数不能用于text和image数据类型。例例8-18 求出字符串I am a student左起第8个字符起7个字符的部分。SELECT SUBSTRING(I am a student,8,7)执行结果为：student第8章 存储过程和触发器 4)字符串比较函数(1)CHARINDEX函数：CHARINDEX函数返回字符串中
29、某个指定的子串出现的开始位置，其语法格式如下：CHARINDEX(，)其中，substring_expression是所要查找的字符表达式，expression可为字符串也可以为列名表达式。如果没有发现子串，则返回0值。此函数不能用于text和image数据类型。例例8-19 找出student中字符d出现的位置。SELECT CHARINDEX(d,student)第8章 存储过程和触发器 执行结果为：4(2)PATINDEX函数：PATINDEX函数返回字符串中某个指定的子串出现的开始位置，其语法格式如下：PATINDEX(，)其中，子串表达式前后必须有百分号“%”，否则返回值为0。与CH
30、ARINDEX函数不同的是，PATINDEX函数的子串可以使用通配符，且此函数可用于char、varchar和text数据类型。第8章 存储过程和触发器 3数据类型转换函数数据类型转换函数在一般情况下，SQL Server 2005会自动完成数据类型的转换，例如，可以直接将字符数据类型或表达式与datetime数据类型或表达式比较；当表达式中用了integer、smallint或tinyint时，SQL Server 2005也可将integer数据类型或表达式转换为smallint数据类型或表达式，这称为隐式转换。如果不能确定SQL Server 2005是否能完成隐式转换或者使用了不能隐式
31、转换的其他数据类型，那么就需要使用数据类型转换函数做显式转换了。此类函数有两个：CAST和CONVERT。第8章 存储过程和触发器 1)CAST函数CAST函数的语法格式如下：CAST(ASlength)2)CONVERT函数CONVERT函数的语法格式如下:CONVERT(，length，style)参数说明：(1)data_type为SQL Server系统定义的类型。用户自定义的数据类型不能在此使用。第8章 存储过程和触发器(2)length用于指定数据的长度，缺省值为30。把char或varchar类型转换为诸如int或smallint这样的integer类型，结果必须是带正号(+)或
32、负号()数值。text类型到char或varchar类型的转换最多为8000个字符，即char或varchar数据类型的最大长度。image类型存储的数据转换到binary或varbinary类型，最多为8000个字符。把整数值转换为money或smallint类型时，按定义的国家的货币单位来处理，如人民币、美元、英镑等。bit类型的转换把非零值转换为1，并仍以bit类型存储。试图转换到不同长度的数据类型，会截断转换值并在转换后显示“+”，以标识发生了这种截断。第8章 存储过程和触发器(3)用CONVERT函数的style选项能以不同的格式显示日期和时间。style是将datetime和sma
33、lldatetime数据转换为字符串时所选用的由SQL Server系统提供的转换样式编号，不同的样式编号有不同的输出格式，如表8-7所示。第8章 存储过程和触发器 第8章 存储过程和触发器 例例8-20 将10.6486转换为整型。SELECT CAST(10.6496 AS int)执行结果为：10例例8-21 将02-27-2009转换为datetime类型。SELECT CONVERT(datetime,02-27-2009)GO执行结果为：2009-02-27 00:00:00.000第8章 存储过程和触发器 4.日期函数日期函数日期函数用来操作datetime和smalldatet
34、ime类型的数据执行算术运算。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期函数。其使用方法如下：日期函数(参数)其中，参数的个数随函数的不同而不同。1)DAY函数DAY函数的语法格式如下：DAY()DAY函数返回date_expression中的日期值。第8章 存储过程和触发器 例例8-22 找出02-27-2009的日期值。SELECT DAY(02-27-2009)执行结果为：272)MONTH函数MONTH函数的语法格式如下：MONTH()MONTH函数返回date_expression中的月份值。与DAY函数不同的是，MONTH函数的参数为整
35、数时，一律返回整数值1，即SQL Server认为它是1900年1月。第8章 存储过程和触发器 例例8-23 找出02-27-2009的月份值。SELECT MONTH(02-27-2009)执行结果为：23)YEAR函数YEAR函数返回date_expression中的年份值。在使用日期函数时，其年份值应在17531999年之间，这是SQL Server系统所能识别的年份范围，否则会出现错误。第8章 存储过程和触发器 例例8-24 找出02-27-2009的年份值。SELECT YEAR(02-27-2009)执行结果为：20094)DATEADD函数DATEADD函数的语法格式如下：DAT
36、EADD()DATEADD函数返回指定日期date加上指定的额外日期间隔number产生的新日期。参数“datepart”在日期函数中经常被使用，它用来指定构成日期类型数据的各组件，如年、季、月、日、星期等，其取值如表8-8所示。第8章 存储过程和触发器 第8章 存储过程和触发器 例例8-25 求日期02-27-2009两天后的日期。SELECT DATEADD(dayofyear,2,02-27-2009)执行结果为：2009-03-01 00:00:00.000第8章 存储过程和触发器 5)DATEDIFF函数DATEDIFF函数语法如下：DATEDIFF(，)DATEDIFF函数返回两个
37、指定日期在datepart方面的不足之处，即date2超过date1的差距值，其结果是一个带有正负号的整数值。对不同的datepart，DATEDIFF函数所允许的最大差距值不一样，如datepart为millisecond时，DATEDIFF函数所允许的最大差距值为24天20小时30分23秒647毫秒。第8章 存储过程和触发器 例例8-26 求日期02-27-2009和03-01-2009之间相差几天。SELECT DATEDIFF(dayofyear,02-27-2009,03-01-2009)执行结果为：26)DATENAME函数DATENAME函数的语法格式如下：DATENAME(，)
38、DATENAME函数必须以字符串的形式返回日期的指定部分，此部分由datepart指定。第8章 存储过程和触发器 例例8-27 求02-27-2009是该年的第几天。SELECT DATENAME(dayofyear,02-27-2009)执行结果为：58第8章 存储过程和触发器 7)DATEPART函数DATEPART函数的语法格式如下：DATEPART(，)DATEPART函数以整数值的形式返回日期的指定部分，此部分由datepart指定。DATEPART(dd,date)等同于DAY(date)。DATEPART(mm,date)等同于MONTH(date)。DATEPART(yy,da
39、te)等同于YEAR(date)。第8章 存储过程和触发器 例例8-28 分别求出02-27-2009的日期、月份和年份。SELECT DATEPART(dd,02-27-2009)执行结果为：28SELECT DATEPART(mm,02-27-2009)执行结果为：2SELECT DATEPART(yy,02-27-2009)执行结果为：2009第8章 存储过程和触发器 8)GETDATE()GETDATE函数的语法格式如下：GETDATE()GETDATE函数以datetime的缺省格式返回系统当前的日期和时间，它常作为其他函数或命令的参数使用。例例8-29 求系统当前时间。SELECT
40、 GETDATE()执行结果为：2009-02-27 10:57:35.577第8章 存储过程和触发器 5.text函数和函数和image函数函数1)TEXTPTR函数TEXTPTR函数的语法格式如下：TEXTPTR()(1)TEXTPTR()函数返回一个指向存储文本的第一个数据库页的指针。(2)其返回值是一个varbinary(16)类型的二进制字符串。(3)如果数据类型为text、ntext或image的列没有赋予初值，则TEXTPTR()函数返回一个NULL指针。第8章 存储过程和触发器 2)TEXTVALID函数TEXTVALID函数的语法格式如下：TEXTVALID(，)TEXTVA
41、LID函数用于检查指定的文本指针是否有效。如果有效，则返回1；无效则返回0。如果列未被赋值，则返回NULL值。第8章 存储过程和触发器 6.用户自定义函数用户自定义函数从SQL Server 2000开始，用户可以自定义函数。在SQL Server 2000中用户自定义函数作为一个数据库对象来管理。可以使用SSMS或Transact-SQL命令来创建、修改和删除自定义函数。第8章 存储过程和触发器 8.2 事事 务务事务是一个逻辑工作单元。SQL Server 2005提供了几种自动的可以通过编程来完成的机制，包括事务日志、SQL事务控制语句，以及事务处理运行过程中通过锁定保证数据完整性的机制
42、。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定。事务和锁是两个紧密联系的概念。通过事务、批和锁的使用，还可以监测系统以及优化物理数据库。作业是一种多步执行的事务。第8章 存储过程和触发器 8.2.1 事务概述事务概述关系型数据库有4个显著的特征：安全性、完整性、检测性和并发性。数据库的安全性就是要保证数据库中数据的安全，防止未授权用户随意修改数据库中的数据。完整性是数据库的一个重要特征，也是保证数据库中的数据切实有效、防止错误、实现商业规则的一种重要机制。在数据库中，区别所保存的数据是无用的垃圾还是有价值的信息，主要是依据数据库的完整性是否健全，即实体完整性、域完整
43、性和参考完整性。发现任何影响系统性能的因素和瓶颈，采取切合实际的策略，解决问题，提高系统的性能。并发性是用来解决多个用户对同一数据进行操作时的问题。特别是对于网络数据库来说，这个特点更加突出。提高数据库的处理速度，单单依靠提高计算机的物理速度是不够的，还必须充分考虑数据库的并发性问题，提高数据库并发性的效率。第8章 存储过程和触发器 那么如何保证并发性呢？在SQL Server 2005中，通过使用事务和锁机制，可以解决数据库的并发性问题。在SQL Server 2005中，事务要求处理时必须满足ACID原则，即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。原子性：原子性也称为自动性
44、，是指事务必须执行一个完整的工作，要么执行全部数据的操作，要么全部不执行。一致性：一致性是指当事务完成时，所有的数据必须具有一致的状态。第8章 存储过程和触发器 隔离性：也称为独立性，是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务处理的数据，要么是其他事务执行之前的状态，要么是其他事务执行之后的状态，但不能处理其他正在处理的数据。持久性：是指当一个事务完成之后，将影响永久性地存于系统中，即事务的操作将写入数据库中。事务的这种机制保证了一个事务或者提交后成功执行，或者提交后失败回滚，二者必居其一，因此，事务对数据的修改具有可恢复性，即当事务失败时，它对数据的修改都会恢复到该事务执行
45、前的状态。而使用一般的批处理，则有可能出现有的语句被执行，而另一些语句没有被执行的情况，从而有可能造成数据不一致。第8章 存储过程和触发器 事务开始之后，事务所有的操作都陆续写到事务日志中。这些任务操作在事务日志中记录一个标志，用于表示执行了这种操作。当取消这种事务时，系统自动执行这种操作的反操作，保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期地发生。检查点的周期是系统根据用户定义的时间间隔和系统活动的频度由系统自动计算出来的时间间隔。检查点周期地检查事物日志。如果在事务日志中，事务全部完成，那么检查点将事务提交到数据库中，并且在事务日志中做一个检查点提交标记；如果在事务日志中，
46、事务没有完成，那么检查点不将事务日志中的事务提交到数据库中，并且在事务日志中做一个检查点未提交标记。第8章 存储过程和触发器 8.2.2 事务的类型事务的类型根据事务的设置和用途的不同，SQL Server 2005将事务分为多种类型。1.根据系统的设置分类根据系统的设置分类根据系统的设置，SQL Server 2005将事务分为两种类型：系统提供的事务和用户定义的事务，分别简称为系统事务和用户定义事务。第8章 存储过程和触发器 1)系统事务系统提供的事务是指在执行某些语句时，一条语句就是一个事务。但是要明确，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。
47、因此，只有一条语句构成的事务也可能包含了多行数据的处理。系统提供的事务语句如下：ALTER TABLE、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATE TABLE，这些语句本身就构成了一个事务。第8章 存储过程和触发器 例例8-30 使用CREATE TABLE创建一个表。CREATE TABLE student(ID char(10),Name char(6),Sex char(2)说明：这条语句本身就构成了一个事务。这条语句由于没有使用条件限制，那么这条语句就是创建包含3个列的表。要么创建全部
48、成功，要么全部失败。第8章 存储过程和触发器 2)用户定义事务在实际应用中，大多数的事务都是用户定义事务。在开发应用程序时，可以使用BEGIN TRANSACTION语句来定义明确的用户定义事务。在使用用户定义的事务时，一定要注意事务必须由明确的结束语句来结束。如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。事务的明确结束可以使用以下两个语句中的一个：COMMIT语句和ROLLBACK语句。COMMIT语句是提交语句，将全部完成的语句明确地提交到数据库中；ROLLBACK语句是取消语句，该语句将事务的操作全部取消，即表示事务操作失败。第
49、8章 存储过程和触发器 还有一种特殊的用户定义事务，这就是分布式事务。如上例的事务是在一个服务器上的操作，其保证的数据完整性和一致性是指一个服务器上的完整性和一致性。但是，如果是一个比较复杂的环境，可能有多台服务器，那么要保证在多台服务器环境中事务的完整性和一致性，就必须定义一个分布式事务。在这个分布式事务中，所有的操作都可以涉及对多个服务器的操作，当这些操作都成功时，所有这些操作都提交到相应服务器的数据库中；如果这些操作中有一个操作失败，那么这个分布式事务中的全部操作都将被取消。第8章 存储过程和触发器 2.根据运行模式分类根据运行模式分类根据运行模式，SQL Server 2005将事务分
50、为4种类型：自动提交事务、显式事务、隐式事务和批处理级事务。1)自动提交事务自动提交事务是指每条单独的语句都是一个事务。2)显式事务显式事务指每个事务均以BEGIN TRANSACTION语句显式开始，以COMMITTRANSACTION或ROLLBACKTRANSACTION语句显式结束。第8章 存储过程和触发器 例例8-31 向StudentInformation表中添加两条记录，如果成功则将结果显示出来，如果失败则显示错误信息。USE StudentScoreManagerBEGIN TRANSACTION DECLARE errorCount int SET errorCount=0
