数据库设计开发案例教程(七)

1、CONFIDENTIAL数据库设计开发数据库设计开发技术案例教程（七）技术案例教程（七）存储过程和函数创建存储过程和函数创建2 主要内容1，存储过程的创建和执行2，函数的创建及其调用3， T-SQL中的简单操作 3 7.1 SQL Server的的T-SQL编程语言编程语言7.1.1 T-SQL介绍介绍T-SQL (Transaction-SQL)语言是SQL Server的专用语言。它包含两部分：一是SQL语句的标准语言部分，利用这些标准的SQL语言编写的应用程序和脚本，可以自如的移到其他的关系型数据管理系统中执行；二是在标准SQL语句上进行的扩充。因为标准的SQL语句形式简单，不能满足应用

2、程序的编程需要，因此各厂商都针对其各自的数据库软件版本做了某些程度的扩充和修改。微软公司在标准SQL语句上增加许多新功能（如语句的注释、变量、运算符、函数和流程控制语句等），而且还增强了可编程性和灵活性。4 7.1.2 T-SQL标识符规则标识符规则T-SQL的标识符规则是：第一个字符必须是下列字符之一：字母az和AZ，来自其他语言的字母字符，下划线_、或者数字符号#。在SQL Server中，某些处于标识符开始位置的字符具有特殊意义：以符号开始的标识符表示局部变量或参数；以#符号开始的标识符表示临时表或过程；以双数字符号（#）开始的标识符表示全局临时对象。注意：标识符不能是T-SQL的保留字

3、。不允许嵌入空格或其他特殊字符。当标识符用于T-SQL语句时，必须用单引号（）或括号（）分割不符合规则的标识符。5 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数SQL Server系统提供了多种函数，用户可以利用这些函数完成特定的运算和操作。常用的函数包括：系统函数、字符串函数、日期和时间函数、数学函数、转换函数。除此之外，用户还可以根据自己的需要利用CREATE FUNCTION命令创建函数。函数用函数名来标识，在函数名后边有一对括号“（）”，它通常是用来存放参数的，但有些函数是不需要参数的，如GETDATE（）函数。在T-SQL程序中可以利用函数名来调用函数，若该函数需要参

4、数，则调用时必须传递参数。下面根据函数的类型对常用函数进行详细的介绍。6 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数1.变量 利用变量可以保存批处理和脚本中的特定类型的数据值，还可以在语句间进行数据传递。T-SQL中的变量有两种类型，即全局变量和局部变量。其中，全局变量时系统预定义好的，SQL用户可以直接从系统中进行调用；而局部变量是用户根据自己的需要定义的，对于局部变量，需要注意的是必须先创建后使用。在本节中，将详细介绍这两种变量的创建和使用方法。1）全局变量全局变量是SQL系统本身创建和维护的，用来记录系统的各种活动状态，可以帮助用户测试系统的设定值或者T-SQL命令执行

5、后的状态值。当用户使用全局变量时，应该注意：全局变量是由系统在服务器级定义的；用户只能使用SQL系统预先定义好的全局变量，不能自己创建全局变量；在程序中调用全局变量时，全局变量的名称必须以标识符“”开头；全局变量对用户来说是只读的，用户无法对他们进行修改。7 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数a.SQL Server常用的全局变量 CONNECTIONS:返回自上次启动SQL Server以来连接或试图连接的次数 CURSOR_ROWS:返回在本连接中，最后打开的游标取出数据行的数目。 ERROR:返回最后执行T-SQ语句的错误代码 FETCH_STATUS:返回被F

6、ETCH语句执行的最后油表的状态，而不是任何当前被连接打开的游标的状态。 IDETITY：返回最后插入的标识列的列值。 LANGID：返回当前所使用语言的本地语言标识符（ID） LANGUAGE：返回当前使用的语言名 LOCK_TIMEOUT：返回当前会话的当前所超时设置，单位为ms MAX_CONNECTIONS:返回SQL Server允许的同时连接的最大数。返回的数不必为当前配置的数值。 OPTIONS：返回当前SET选项的信息 PROCID：返回当前过程的存储过程标识符（ID） ROWCOUNT：返回受上一语句影响的行数 SERVERNAME:返回运行SQL Server的本地服务器的

7、名称 SPID：返回当前用户进程的服务器进程标识符（ID） TOTAL_ERRORS：返回SQL Server自上次启动后所遇到的磁盘读/写错误数 VERSION:返回SQL Server当前安装的日期、版本和处理器类型8 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数以下示例：利用全局变量 CONNECTIONS显示到系统的当前日期和时间为止，在用户登录SQL Server的次数。select getdate() as todays date and timeselect connections as login attempts显示结果如图7-1所示。图7-19 7.1.3

8、T-SQL 中的数据类型中的数据类型和函数和函数2）局部变量 局部变量是用户根据程序的需要在程序内部创建的，而且它的作用范围限制在程序内部。用户根据需要为该局部变量命名和设定数据类型，局部变量的名称不能与全局变量的名称相同。局部变量可以作为计数器用在循环语句中来计算循环执行的次数，或者控制循环执行的次数。而且，局部变量还可以作为临时的存储器来保存程序运行的结果或者传递函数的参数值。a.创建局部变量利用DECLARE语句创建局部变量，其语法格式如下：DECLARE local_variable data_typen其中：local_variable:用于指定新创建的局部变量的名称，局部变量名称前

9、的第一个字符必须为“”，而且该名称必须符合SQL对象的命名规则。Data_type:表示新创建的局部变量的数据类型及其长度。该类型可以是任何由系统内部提供的或用户自定义的数据类型，但局部变量的数据类型不能是text、ntext或image数据类型，用户在创建局部变量时应该注意这一点。n：表示在一个DECLARE语句中可以同时定义n个局部变量。10 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数局部变量赋值当用户在程序中创建局部变量后，局部变量的初始值为NULL，如果想要为局部变量重新赋值，可以利用T-SQL语句中的SELECT命令或者SET命令。其语法格式如下：SET local

10、_variable=expression或SELECT local_variable=expression,.n其中：local_variable:表示进行赋值的局部变量的名称Expression:为该变量所赋得值，可以是任何有效地SQL Server表达式。n：表示在SELECT赋值语句中，可以同时为n个局部变量赋值。以下示例：创建一个局部变量，并赋一个任意字符串作为局部变量的值。declare nvarchar_n nvarchar(10)set nvarchar_n=hello,everyoneselect nvarchar_n as nvarchar_n变量值为执行结果如图7-2所示。

11、图7-211 7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数3）.运算符SQL Server常用的运算符有：算术运算符、赋值运算符、逻辑运算符、比较运算符、字符串串联运算符。A算术运算符加(+)、减(-)、乘(*)、除(/)、取模(%)。其中，加、减、乘、除这4种运算符的表达式可以使数字类型分类的任何数据类型；而对于取模运算符，其计算表达式的数据类型只能是int、smallint和tinyint三种。加和减运算符也用于对datetime及smalldatetime值执行算术运算。B赋值运算符赋值运算符（=）C字符串串联运算符字符串串联运算符为加号（+），允许通过加号在多个字符串键

12、进行字符串串联操作。D比较运算符比较运算符测试两个表达式是否相同。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。比较运算符的结果为布尔数据类型，它有三种值：TRUE、FALSE或UNKNOWN。比较运算符及其含义如表7-1所示。运算符含义运算符含义=等于不等于大于!=不等于小于!=大于等于!不大于=小于等于表7-112 当比较运算符两边的表达式均为空值时，其运算符结果不定。当SET ANSI_NULLS(指定在对空值使用等于 (=) 和不等于 () 比较运算符时，这些运算符的 SQL-92 遵从行为。)为ON时，带有一个或两个NULL表达式的运算符返回

13、UNKNOWN。当SET ANSI_NULLS为OFF时，上述规则同样适用，只不过如果两个表达式都为NULL，那么=运算符返回TRUE。例如，如果SET ANSI_NULLS是OFF，那么NULL=NULL就返回TRUE。比较运算符可以用在WHERE子句中作为查询条件使用，也可以用在流控制语句中作为条件分支。例如if和where中使用这种表达式。E逻辑运算符逻辑运算符用来把多个逻辑表达式连接起来进行测试，以获得其真实情况。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。逻辑运算符及其含义如表7-2所示。7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数表7-

14、2运算符含义ALL如果一系列的比较都为TRUE，那么就为TRUEAND如果两个布尔表达式都为TRUE，那么就为TRUEANY如果一系列的比较中任何一个都为TRUE，那么就为TRUEBETWEEN如果操作数在某个范围之内，那么就为TRUEEXISTS如果子查询包含一些行，那么就为TRUEIN如果操作数等于表达式列表中的一个，那么就为TRUELIKE如果操作数与一种模式相匹配，那么就为TRUENOT对任何其他布尔运算符的取值反OR如果两个布尔表达式中的一个为TRUE，那么就为TRUESOME如果在一系列比较中，有些为TRUE，那么就为TRUE13 F运算符的优先级在一个复杂的表达式中往往会包含多个

15、运算符，该表达式的运算结果与运算符的优先执行次序有很大的关系。在SQL Server2005中队运算符的优先执行顺序进行了规定，下面按优先级从高到低依次列出：（） （括号）+（正）、-（负）、（按位NOT）*（乘）、/（除）、%（取模）+（加）、+（串联）、-(减)=、=、=、!=、!、!（比较运算符）NotAndAll 、any、between、in、like、or、some=（赋值） 在表达式中，各类运算符的优先级运算遵循的原则可总结如下： 1）.在较低等级的运算符之前先对较高等级的运算符进行求值。 2）.当一个表达式中的两个运算符有相同的优先级时，基于他们在表达式中的位置来对其从左到右进

16、行求值。7.1.3 T-SQL 中的数据类型中的数据类型和函数和函数14 批处理是包含个或多个Transact-SQL语句的组，从应用程序一次性地发送到SQL Server执行。流程控制语用来控制SQL语句、语句块或者存储过程的执行流程。1. 批处理 当用户利用Transact-SQL语言编写程序时，可以利用批处理语句来提高程序的执行效率。批处理是使用GO语句将多条SQL语句进行分隔，其中每两个GO之间的SQL语句就是一个批处理单元。一个批处理中可以只包含一条语句，也可以包含多条语句。在SQL Server执行批处理之前首先将批处理语句进行编译，使之成为一个可执行单元，然后再对编译成功的批处理

17、单元进行处理。如果批处理中某条语句编译出现错误（如语法错误），则使整个执行计划无法编译成功，从而导致批处理中的任何语句均无法执行。如果批处理语句在运行时出错（如算术溢出或违反约束），则会产生以下影两项：一、大多数运行时错误将停止执行批处理中当前语句和它之后的语句。二、少数运行时错误（如违反约束）仅停止执行当前语句，而继续执行批处理中其他所有语句。7.1.4 T-SQL 中的程序控制语句中的程序控制语句15 当利用批处理时，应注意以下规则： CREATE DEFAULT. CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER和 CREATE VIEW语句不能在批

18、处理中与其他语句组合使用。批处理必须以CREATE语句开始，所有跟在该批处理后的其他语句将被解释为第一个CREATE语句定义的一部分。不能在同一个批处理中要欢襄、然后引用新列。如果EXECUTE语句是批处理中的第一条语句，则不需要EXECUTE关键字。如果EXECUTE语句不是批处理中的第一条语句，则需要EXECUTE关键字。在书写批处理语句时，需要使用GO语句作为批处理命令的结束标志。 7.1.4 T-SQL 中的程序控制语句中的程序控制语句16 下面举例说明批处理的使用。以下示例：使用批处理创建一个Student的视图，然后在查看视图。因为CREATE VIEW必须是批处理中的唯一语句，所

19、以需要GO命令将CREATE VIEW语句与其周围的USE和SELECT话句隔离。USE MyschoolGOCREATE VIEW v_studentASSELECT StudentId, ClassId, StudentName,Sex,AddressFROM StudentGOSELECT*FROM v_studentGO显示结果，如图7-3所示。7.1.4 T-SQL 中的程序控制语句中的程序控制语句图7-317 以下示例：利用批处理语句查询Class表中的信息。use MySchoolgoselect * from Classgoselect StudentId,StudentNam

20、e,Sex,Addressfrom StudentGo结果如图7-4所示。7.1.4 T-SQL 中的程序控制语句中的程序控制语句图7-418 2. 流程控制语句 流程控制语句是用来控制程序执行和流程分支的语句。在SQL Server系统中，可以使用的流程控制语句有BEGINEND、IFELSE、CASE、WHILECONTINUEBREAK、GOTO、WAITFOR、RETURN等。 1）BEGIN- - -END语句 该语句是由一系列的Transact-SQL语句组成的一个语句块，使得SQL Server可以成组地执行T-SQL语句。在条件语句和循环语句等流程控制语句中，当符合特定条件需要

21、执行两个或多个语句时，就应该使用BEGIN - - - END语句将这些语句组合在一起。其中，BEGIN和END是流程控制语句的关键字。其语法格式如下： BEGIN ( sql_stat_ernent|statement_block) END其中， sql_ statement | statement_ block是任何有效的T-SQL语句或以语句块定义的语句分组。7.1.4 T-SQL 中的程序控制语句中的程序控制语句19 以下示例：在IF语句中应用BEGIN- - -END语句查询错误行数。DECLARE ErrorSaveVariable INTIF(ERROR0)BEGIN SET E

22、rrorSaveVariable = ERROR PRINT Error pncountered, + CAST (ErrorSaveVariable AS VARCHAR (10)END 显示结果如图7-5所示。7.1.4 T-SQL 中的程序控制语句中的程序控制语句图7-520 2）IF- - - EISE语句 IF- - -ELSE语句是条件判断语句。如果后面给出的条件满足（布尔表达式返回TRUE则执行IF关键字之后的T-SQL语句，若不满足，则执行ELSE后面的语句，但ELSE关键字是可选的。其语法形式如下：IF Boolean_expression SQL_statement | s

23、tatement_block ELSE SQL_statement I statement_block 其中：Boolean_expression:返回TRUE成FALSE的布尔表达式。如果布尔表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来。7.1.4 T-SQL 中的程序控制语句中的程序控制语句21 3）以下示例：利用IF- - -ELSE语句查看Product表中产品类别名称为“西药”的所有产品的平均价格，并且判断平均价格偏高还是偏低。 IF (SELECT AVG(price2) FROM Product WHERE Category =西药)11 END显示结果，如

24、图7-6所示。7.1.4 T-SQL 中的程序控制语句中的程序控制语句图7-6IF- - -ELSE结构可以用在批处理、存储过程（经常使用这种结构测试是否存在着某个参数）以及特殊查询中。可以在其化IF之后或ELSE下面嵌套另一个IF测试，对于嵌套层数没有限制。22 4）WHILE-CONTINUF-BREAK语句 WHILE-CONTINUE-BREAK语句是SQL语言中的循环语句，用来重复执行SQL语句或语句块。如果WHILF,后面的逻辑表达式为真，则重复执行循环内部的语句。其中，CONTINUE语句可以使程序跳过CONTINUE后面的语句，重新回到WHILE循环的第一行命令；BREAK语句

25、可以使程序完全跳出WHILE循环，而去执行WHILE循环后面的语句行。 其语法形式如下： WHILE Boolean_expression SQL_ Statement| statement_block BREAK SQL_ Statement | statement_block CONTINUE 7.1.4 T-SQL 中的程序控制语句中的程序控制语句图7-6IF- - -ELSE结构可以用在批处理、存储过程（经常使用这种结构测试是否存在着某个参数）以及特殊查询中。可以在其化IF之后或ELSE下面嵌套另一个IF测试，对于嵌套层数没有限制。23 4）WHILE-CONTINUF-BREAK语句

26、 WHILE-CONTINUE-BREAK语句是SQL语言中的循环语句，用来重复执行SQL语句或语句块。如果WHILF,后面的逻辑表达式为真，则重复执行循环内部的语句。其中，CONTINUE语句可以使程序跳过CONTINUE后面的语句，重新回到WHILE循环的第一行命令；BREAK语句可以使程序完全跳出WHILE循环，而去执行WHILE循环后面的语句行。 其语法形式如下： WHILE Boolean_expression SQL_ Statement| statement_block BREAK SQL_ Statement | statement_block CONTINUE 7.1.4 T

27、-SQL 中的程序控制语句中的程序控制语句24 7.1.4 T-SQL 中的程序控制语句中的程序控制语句以下示例：用WHILE 循环语句计算100以内的所有整数的和。 DECLARE sum_num int,i int SELECT sum_num=0,i=1 WHILE i100 BEGIN SET sum_num=sum_num+i SET i=i+1 END PRINT 100似内的整数求和为：PRINT sum_num 显示结果，如图7-7所示。图7-7注意 如果嵌套了两个或多个WHILE循环，内层的BREAK将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外

28、层循环重新开始执行。25 7.1.4 T-SQL 中的程序控制语句中的程序控制语句 5）GOTO语句 GOTO语句是SQL程序中的无条件跳转语句，可以使程序直接跳到指定的标识符位置处继续执行。而茌GOTO语句和所指定的标签之间的语句块将不再被执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中。标识符可以是数字或字符的组合，但必须以“：”结尾但在用GOTO语句调用标识符时，只写标识符名称而不必加“：”。其语法形式如下： 定义标签： Label： 改变执行： GOTO Label 以下示例： 在下列程序中利用GOTO语句和IF语句实现循环，从而计算100以内的所有整之和。 DECLARE

29、 sum_num int, i int SELECT sum_num=0, i=1 Labell: SET sum_num=sum_num +i SET i =i+1 IF i 100 GOTO Labell ELSE PRINT 100以内的整数求和为：PRINT sum_num显示结果，如图7-8所示。图7-826 7.1.4 T-SQL 中的程序控制语句中的程序控制语句6）WAITFOR语句 WAITFOR语句在SQL中起暂停正在执行的语句、语句块或者存储过程的作用，直到某时间、时间间隔到达后才继续执行。 其语法形式如下： WAITFOR DELAY time| TIME time DE

30、LAY关键字后为amountof- timetopass.是在完成WAITFOR语句之前等待的时间间隔。完成WAITFOR语句之前等待的时间最多为24小时。 TIME关键字后为time_ to_ execute用于指定某一时刻，其数据类型是有效的datetime，格式为hh：mm：ss，不允许有日期部分。以下示例：在WAITFOR语句中使用DELAY参数设置查询语句执行前需要等待的时间间隔。USE MySchoolWAITFOR DELAY 00 :00 :02 SELECT* FROM Class .注意 执行WAITFOR语句后，在到达指定的时间之前或指定的事件出现之前，将无法使用与SQL

31、 Server的连接。若要查看活动的进程和正在等待的进程，请用sp_help(报告有关数据库对象（sysobjects 表中列出的任何对象）、用户定义数据类型或 Microsoft SQL Server 所提供的数据类型的信息。)。27 7.1.4 T-SQL 中的程序控制语句中的程序控制语句7）RETURE语句RETURN integer_expression 其中，参数integer_ expression为返回的整数值。28 7.1.5 T-SQL 中的异常处理中的异常处理在执行编译成功的SQL命令或程序块时，可能会由于不合适的数据引起执行期间的错误，这样的错误称为异常。例如，新插入的数

32、据或更新后的数据违反了约束条件，或给出的数据不符合运算符的要求，在这些情况下都会产生异常。一旦异常发生，就应该合理地处理异常。本节介绍异常捕获和异常处理的两种方式：1，使用TRY-CATCH结构捕获异常和处理异常。2，使用RAISEERROR语句返回错误信息。29 7.1.5 T-SQL 中的异常处理中的异常处理1TRY-CATCH结构 TRY-CATCH结构的语句形式如下：BEGIN TRY SQL_statement | statement_blockEND TRYBEGIN CATCH SQL_statement | statement_block END CATCH其中，SQL_sta

33、tement表示任意一条T - SQL语句，statement_ block表示被包含在一个批处理或程序块中的Transact - SQL语句组。 BEGIN TRY- - - END TRY之间包含着需要执行的并且有可能产生错误的语句或语句组，BEGIN CATCH-END CATCH之间包含着处理上述错误所需要的语句或语句组。30 7.1.5 T-SQL 中的异常处理中的异常处理其中，SQL_statement表示任意一条T - SQL语句，statement_ block表示被包含在一个批处理或程序块中的Transact - SQL语句组。 BEGIN TRY- - - END TRY之

34、间包含着需要执行的并且有可能产生错误的语句或语句组，BEGIN CATCH-END CATCH之间包含着处理上述错误所需要的语句或语句组。 在使用该结构时，需要注意以下几点：个TRY-CATCH结构将捕获所有严重级别高于10的执行错误，不关闭数据库连接.一个TRY块的后面必须紧跟着CATCH块，也就是说在END TRY相BEGIN CATCH之间包含任何语句时都会产生语法错误。一个TRY-CATCH结构不能跨越多个批处理，即该结构中的所有命令都属于一个批处理。如果在TRY块中不包含错误，则程序将直接执行END CATCH之后的命令。TRY-CATCH结构可以进行嵌套。在CATCH块部分还可以使

35、用下面的系统函数来确定错误的详细信息，这些常用的函数如下：ERROR_ NUMBER()：返回错误编号。ERROR_ SEVERITY()：返回错误的严重级别。ERROR_ STATE()：返回错误的状态。ERROR_PROCEDURE()：返回产生错误的存储过程或触发器的名称。ERROR_LINE()：返回错误发生的行号。ERROR_ MESSAGE()：返回错误发生的提示信息。31 7.1.5 T-SQL 中的异常处理中的异常处理2 .RAISERROR语句用户可以使用RAISERROR语句生成错误提示信息并且启动错误处理的过程。这些错误信息可以是存储在sys. Messages(对于系统

36、定义和用户定义的消息，系统中的错误消息的每个 message_id 或 language_id 在表中都对应一行。)中的用户已定义信息，也可以是一个临时的动态信息。RAISERROR语句的语法形式如下：RAISERROR(msg_id|msg_str|local_variable ,severity,state)其中：msg_id:表示存储在sys.messages中的用户已定义消息的消息编号。利用sp_ addmessage(将新的用户定义错误消息存储在 SQL Server 数据库引擎实例中。)可以增加新的用户自定义消息，消息编号必须是大于50 000的。msg_str:用户临时使用的动态

37、消息，该字符串中最多包括2047个字符。local_ variable:可用的字符型变量，包括一个字符串。该变量必须是char型或varchar型，或者是可以隐式地转换成这两种类型的变量。severity:用户定义的与该消息关联的严重级别。任何用户都可以指定018之间的严重级别。只有sysadmin(系统管理员)固定服务器角色成员或具有ALTER TRACE权限的用户才能指定1925之间的严重级别。state:介于1127之间的任意整数。state昀默认值为l，值为0或大于127会生成错误。下面将使用如下的代码插入一条新的客户信息，如果要插入的ClassId已经存在，则使用RAISERROR语

38、句返回一条错误信息，并且被CATCH语句捕获进行相应的错误处理。注意，本例中设置错误级别为l6，可以被CATCH捕获并做相应的处理，如果错误级别小于11，如设置为10，则输出RAISFRROR定义的错误信息，不会转到CATCH块中处理。所以说，在TRY块中只有11 19的RAISERROR的严重性错误才被转移到相应的CATCH块中处理。32 7.1.5 T-SQL 中的异常处理中的异常处理先去掉Class表中的主键BEGIN TRYDECLARE id intSELECT id=3IF EXISTS(SELECT *FROM Class WHERE ClassId=id)RAISERROR(错

39、误发生了,16,1)ELSEINSERT INTO Class(ClassName, GradeId) values(Y2340,2)END TRYBEGIN CATCHPRINT将要添加的客户已经存在，换一个客户编号重试！PRINT ERROR_MESSAGE()END CATCHGO显示结果，如图7-9所示。图7-933 7.1.6 T-SQL 中的游标操作中的游标操作先去掉Class表中的主键BEGIN TRYDECLARE id intSELECT id=3IF EXISTS(SELECT *FROM Class WHERE ClassId=id)RAISERROR(错误发生了,16,

40、1)ELSEINSERT INTO Class(ClassName, GradeId) values(Y2340,2)END TRYBEGIN CATCHPRINT将要添加的客户已经存在，换一个客户编号重试！PRINT ERROR_MESSAGE()END CATCHGO显示结果，如图7-9所示。图7-934 7.1.6 T-SQL 中的游标操作中的游标操作游标是一种数据结构。通过这种结构，程序可以将查询结果保存在其中，并可对结果集的某（或某些行）数据进行操作。游标中的数据保存在内存中，从其中提取数据的速度要比从数据表中直接提取数据的速度快得多。事务是指一个单元的工作，这些工作要么全部正确执行

41、，要么全部不执行，通过事务来保证数据完整性。在多用户环境中，有可能同时多个事务访问同一资源。本节学习要点：a，游标的定义和使用方法,b，事务的定义、操作以及具体应用1.游标概述能够部分读取返回的结果集合中的数据行，并允许应用程序通过游标来定位修改表中的数据。35 7.1.6 T-SQL 中的游标操作中的游标操作2.游标的用法在SQL Server 2005中，使用游标的一般步骤如下：声明游标(DECLARE CURSOR)。打开游标( OPEN CURSOR)。从打开的游标中提取数据( FETCH CURSOR)。根据需要，对游标中当前位置的行执行修改操作（更新或删除）。关闭游标( CLOSE

42、 CURSOR)。释放游标( DEALLOCATE CURSOR)。1)声明游标( DECLARE CURSOR)可以使用DECLARE语句声明或创建一个游标。语法格式如下：DECLARE cursor _name CURSOR LOCAL | GLOBAL FORWARD_ONLY| SCROLL STATIC | KEYSET I DYNAIVIIC I FAST_FORWARD READ_ONLY | SCROLL-LOCKS| OPTIMISTIC FOR selectstatement FOR UPDATEOF column_name， ;36 7.1.6 T-SQL 中的游标操作中

43、的游标操作2）打开游标( OPEN CURSOR)可以使用OPEN语句打开声明过的游标。语法格式如下：OPEN cursor_name其中，cursor_ name是已声明过并且没有打开的游标名称。3）从打开的游标中提取数据( FETCH CURSOR)可以使用FETCH语句来提取数据。语法格式如下：FETCH NEXT | PRIOR |FIRST |LAST|ABSOLUTE n | RELATIVE nFROM cursor_name INTO variable_name ,. . n 4）关闭游标(CLOSE CURSOR)当不再使用游标时，应及时调用CLOSE语句关闭游标，以便释放游

44、标所占用的系统资源。在关闭游标时，SQL Server删除游标中的所有数据，并释放游标对数据库的所有锁定。所以，在游标关闭后，禁止提取游标数据，或通过游标定位修改或删除操作。但是，关闭游标并不改变游标的定义，应用程序可以再次执行OPEN语句打开游标。可以使用CLOSE关闭游标。语法格式如下：CLOSE GLOBAL cursor_name | cursor_variable_name 其中，cursor_name是要被关闭的游标名。37 7.1.6 T-SQL 中的游标操作中的游标操作释放（删除）游标(DEALLOCATE CURSOR)由于关闭游标时并没有删除游标，因此，游标仍然占用着一定的

45、系统资源。如果一个游标确定不再使用，将其关闭后，还需要使用DEALLOCATE语句来删除。语法格式如下：其中：DEALLOCATE cursor_namecursor_ name是已声明的游标名称。下面通过一个综合性的例题，说明游标的使用过程。-创建游标DECLARE myCursor CURSOR FORSELECT TOP 5 StudentName,Sex,Major FROM Student-打开游标OPEN myCursor-提取第一行的数据FETCH NEXT FROM myCursor-提取剩余行的数据WHILE FETCH_STATUS=0BEGINFETCH NEXT FRO

46、M myCursorEND-释放游标CLOSE myCursorDEALLOCATE myCursor显示结果，如图7-10所示。图7-1038 7.1.6 T-SQL 中的游标操作中的游标操作383.使用游标修改数据在SQL Server中，UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。这样，就可以通过游标更新和删除数据表中的数据。用于游标操作时，UPDATE语句的语法格式如下： UPDATE table_name SET column _name= expression WHERE CURRENT OF cursor_ name其中，C

47、URRENT OF cursor_ name表示当前游标的当前数据行。CURRENT OF子句只能用在UPDATE和DELETE操作的语句中。以下示例：将游标myCursor当前行中的Sex列的值修改为“女”。 UPDATE Student SET Sex=女WHERE CURRENT OF myCursor用于游标操作,DELETE语句的语法格式如下： DELETE FROM table_name WHERE CURRENT OF cursor_name 以下示例：将游标myCursor的当前行删除。 DELETE FROM Student WHERE CURRENT OF myCursor

48、 当游标基于多个数据表时，UPDATE语句和DELETE语句一次只能修改或删除一个基表的数据，而其他基表中的数据不受影响。39 7.1.6 T-SQL 中的游标操作中的游标操作3.使用游标修改数据在SQL Server中，UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。这样，就可以通过游标更新和删除数据表中的数据。用于游标操作时，UPDATE语句的语法格式如下： UPDATE table_name SET column _name= expression WHERE CURRENT OF cursor_ name其中，CURRENT OF c

49、ursor_ name表示当前游标的当前数据行。CURRENT OF子句只能用在UPDATE和DELETE操作的语句中。以下示例：将游标myCursor当前行中的Sex列的值修改为“女”。 UPDATE Student SET Sex=女WHERE CURRENT OF myCursor用于游标操作,DELETE语句的语法格式如下： DELETE FROM table_name WHERE CURRENT OF cursor_name 以下示例：将游标myCursor的当前行删除。 DELETE FROM Student WHERE CURRENT OF myCursor 当游标基于多个数据表

50、时，UPDATE语句和DELETE语句一次只能修改或删除一个基表的数据，而其他基表中的数据不受影响。40 7.2存储过程存储过程存储过程( Stored Procedure)是SQL Server服务器上一组预先定义并编译好的T-sql语句。使用存储过程可以提高Transact-SQL语句的运行性能和执行效率。存储过程还可以用作一种安全机制，使用户通过它访问未授权的表或视图。本章学习要点：A,存储过程的基本概念B,创建存储过程C,管理存储过程1 存储过程概述 1）存储过程的基本概念 SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程的过程中，可以将某些

51、多次调用以实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名调用它们，这些过程就叫做存储过程。 存储过程可以实现以下功能：接收输入参数并以输出参数的格式向调用过程或批处理返回多个值。包括用于在数据库中执行操作（包括调用其他过程）的编程语句。向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。41 7.2存储过程存储过程2）存储过程的分类 在SQL Server 2005中，存储过程主要分为：系统存储过程和用户定义的存储过程。(1)系统存储过程系统存储过程由SQL Server 2005提供，用户可以直接使用。在SQL Server

52、 2005中，许多管理活动都是通过一些系统存储过程完成的。例如，sp_ help（帮助）、sp_ who（谁在使用系统）sp_adduser（添加用户）等都是系统存储过程、系统在储过程存放在master数据库中，它们主要用于系统管理、用户登录管理、权限设置、数据库对象管理、数据复制等操作。系统存储过程带有sp_前缀，为了避免混淆，用户定义的存储过程一般不要使用sp_作为前缀。(2)用户定义的存储过程用户定义的存储过程用于实现用户自己所需要实现的操作。在SQL Server 2005中，用户定义的存储过程可以调用数据定义语言( DDL)和数据操作语言(DML)语句，并且返回值或者对NET Fra

53、mework公共语言运行时(CLR)方法的引用。因此，用户定义的存储过程可分为T-SQL存储过程成CLR存储过程。当通过Transact-SQL语句能完成所要求的功能时，使用T-SQL语句编写存储过程。但Transact-SQL不支持对象、数组、集合、类的使用。为了实现数据库中复杂的编程逻辑或访问外部资源，可以以任何NET支持的语言（如VB. NET或C#等语言）来创建CLR存储过程。例如，从数据库表中检索数据并以XML的格式存储到一个文件中，这时T-SQL不能实现此功能，可以使用NET语言创建CLR存储过程来完成此任务。42 7.2存储过程存储过程2存储过程的优点一个设计良好的数据库应用程序

54、通常都会用到存储过程。使用存储过程具有以下优点：实现模块化程序设计。存储过程内可引用其他存储过程，可以简化一系列复杂语句。可以减少网络通信流量。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。可以作为一种安全机制。43 7.2存储过程存储过程7.2.1 创建存储过程创建存储过程1.使用SQL Server Management Studio创建存储过程具体操作步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”中依次展开“数据库”节点、AsscentWebDb节点、“可编程性”节点。（2）右键单击“存储过程”节点，从弹出的快捷

55、菜单中选择“新建存储过程”选项，如图7-11所示。图7-1144 7.2存储过程存储过程3)在右边查询窗口中，显示出存储过程的模板，用户可以根据模板输入存储过程所包含的文本，单击“执行”按钮，当语句成功执行后，存储过程就创建成功了。2.使用Transact-SQL语句创建存储过程 使用CREATE PROCEDURE命令创建存储过程的语法格式如下：CREATE PROC EDURE Procedure_name ,parameter data_type = default OUTPUT ,. . n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRY

56、PTION ASSQL_statement45 7.2存储过程存储过程7.2.2 执行存储过程执行存储过程存储过程可以通过EXECUTE语句来执行，其语法格式如下： EXEC UTE return_status = procedure_ name | procedure_name_var parameter = value |OUTPUT | DEFAULT , . . n WITH RECOMPILE 46 7.2存储过程存储过程7.2.3 存储过程中的输入、输出参数存储过程中的输入、输出参数1不带参数的存储过程调用以下示例：调用存储过程的形式。存储过程usp_SelectClassesAl

57、l：CREATE PROCEDURE dbo.usp_SelectClassesAllASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSELECTClassID,ClassName,GradeIDFROMdbo.Class调用：USE MyschoolEXECUTE usp_SelectClassesAll显示结果，如图7-12所示。图7-1247 7.2存储过程存储过程2带输入参数的存储过程的调用以下示例：调用带参数存储过程的形式。存储过程usp_InsertClass：CREATE PROCEDURE dbo.us

58、p_InsertClassClassName varchar(50),GradeID intAS SET NOCOUNT ONINSERT INTO dbo.Class (ClassName,GradeID) VALUES (ClassName,GradeID)select SCOPE_IDENTITY()调用：USE MySchoolEXECUTE usp_InsertClass S2110,2-OrEXECUTE usp_InsertClass S2111,2-OrEXECUTE usp_InsertClass S2112,2显示结果，如图7-13所示。图7-1348 7.2存储过程存储过

59、程3带输出参数的存储过程的调用以下示例：调用带有输出参数的存储过程的形式。存储过程selectstudent：create PROCEDURE dbo.selectstudentStudentId int,StudentName nvarchar(255) output,Sex nvarchar(255) outputasselect StudentName=StudentName,Sex=Sex from Student where StudentId=StudentId调用：USE MyschoolGODECLARE StudentName nvarchar (255) , Sex nva

60、rchar (255)EXEC selectstudent 144 , StudentName OUTPUT, Sex OUTPUT显示输出值：SELECT StudentName , Sex显示结果，如图7-14所示。图7-1449 7.2存储过程存储过程4带有返回值的存储过程的调用以下示例：调用带有返回值存储过程的形式。存储过程Insertclass：create PROCEDURE InsertclassClassName varchar(50),GradeId intasinsert Class(ClassName,GradeId) values (ClassName,GradeId)