SQLServer程序设计.ppt

第11章 SQL Server程序设计,111程序注释语句 112批处理 113事务 114 变量 115 程序控制语句 11.6 游标 117 用户自定义函数 11.8本章总结,学习目标：通过本章学习，你能够学会：在进行SQL Server程序设计时涉及的批处理、事务、局部变量、全局变量的概念及使用方法在进行SQL Server程序设计时涉及到的程序控制语句的使用方法在进行SQL Server程序设计时还会用到游标，在本章中还可以学到游标的使用方法在进行SQL Server程序设计时为使编程简洁清晰还会用到用户自定义函数，在本章将会学到用户如何定义自己的函数,111程序注释语句,前面介绍的Transact-SQL语句以及一些编程的方法和技巧在处理一些简单的查询和实际应用时还可以完全胜任，但是，在有些时候，这些工具无法满足一些特殊要求，例如，要实现与用户的交互操作等。那么，了解和掌握如何使用Transact-SQL语句进行程序设计是非常必要的。,在程序设计的过程中，注释语句的主要作用是帮助他人了解程序的具体内容，同时也便于我们对程序结构有所了解和掌握。试想如果你编了一段100行左右的程序，如果一个月后在来浏览，在没有注释的情况下，即使记忆力再好的程序员，也得花一番功夫才能把这段程序侧底弄清楚。因此经常书写程序注释语句是一种非常好的编程习惯。,实例11.1下面是一个关于创建学生表的程序，请大家注意注释语句的书写操作步骤：通过开始菜单打开查询分析器。在查询分析器的编辑窗口输入程序设计语句，如下图所示,批处理是一个以GO为结束标志的SQL语句组。批中的所有SQL语句作为一个整体编译成一个执行单元后一次提交给SQL Server服务器执行。有多个批时,系统按批地顺序依次执行。,112批处理,实例11.2使用批处理选择Pubs数据库，然后创建一个视图，最后使用视图进行查询。操作步骤：通过开始菜单打开查询分析器。在查询分析器中输入如下语句执行结果如图11.2所示,图11.2批处理执行结果,由上面的例子可以看出来，所有的批处理命令都使用GO作为结束标志。当编译器读到GO时，它就会把GO前面所有的语句当作一个批处理，而打包成一个数据包发送到服务器。GO本身并不是Transact-SQL语句组部分，它只是一个用于表示批处理结束的前端指令。当有多个批时,系统按批地顺序依次执行,关于批中有如下几点需要注意：(1)一个批处理中若有多个存储过程,则从第二个存储过程开始,每个存储过程的前面都要加关键字EXECUTE 或EXEC.(2)一般地，一个CREATE语句必须独自占用一个批处理，且必须以CREATE语句开始。(3)若使用ALTERTABLE语句修改表的结构，则不能在同一个批中引用新定义或修改的列。,执行一个批时，若其中有一个语句出现编译错误，则SQL Server将取消该批内所有语句的执行。但若一个批通过了编译而在运行时出现错误，则批内发生错误之前的语句将被执行，发生错误的语句不被执行，发生错误之后的语句视具体情况可能被执行或不被执行。因此，对于仅执行了一部分的批，可能引起整个过程发生错误或产生数据垃圾。,为防止这类情况出现，可以在SQL Server编程中引入“事务”（将在下节讲到），将批处理放在一个事务中，并设置批处理发生错误时引发事务回滚，使所有尚未提交的操作都回滚到发生错误之前的状态。,事务是SQL Server的一个工作单元，事务通常以BEGIN TRANSACTION开始，以COMMIT TRANSACTION结束。如果在一个事务模块中有一个SQL语句执行失败或发生不该有的结果，可以通过设置回滚命令ROLLBACK TRANSACTION取消该语句导致的错误操作结果(例如取消已插入的数据或恢复已被修改的数据)，使数据库恢复到该错误语句执行前的状态。,113事务,实例11.3利用事务在第4章中的学生库的学生信息表中插入两行数据，在插入第2行数据后回滚到两次插入数据之间，然后查询。步骤：启动“查询分析器”，输入SQL语句：得到如下执行结果如图11.3所示,11.3利用事务执行语句,然后对“课程”表进行查询，执行结果如下，如图11.4所示。,图11.4 事务执行后的查询结果,这个例子一共执行了两次插入操作，第一次操作完成后，设置了一个保存点，第二次插入操作执行完后，程序执行了事务回滚，使第二次的插入操作被取消，但是第一次插入操作依然有效，这样查询后就只能得到一行插入数据。如果不设置保存点，则默认回滚整个事务，两次插入操作都将被取消。,从上面的例子也可以看出，在一个事务中可以包含多个批处理，当然一个批处理也可以包含多个事务。两者的区别是，批语句的组合发生在编译阶段，而事务语句的组合发生在执行阶段。一个批若在编译阶段发生语法错误，则在运行阶段整个批都不能执行。若编译通过而在运行时发生错误，如违反主键惟一性约束等，则有两种情况：若没有设置事物回滚，默认时系统仅回滚发生错误的语句；若设置了事务回滚，则系统回滚事务。,由上面的例子我们可以看出，建立一个事务包含四个语句，即开始事务、设置保存点、回滚事务、提交事务。(1)开始(或启动)一个事务单元。命令格式：BEGIN TRANSACTION 单元名称(2)在事务中设置一个保存点。保存点的设置可以使得回滚事务语句回滚到该保存点，以便取消该事务中的部分操作对数据库所引起的变动。命令格式：SAVE TRANSACTION 保存点名称,(3)回滚一个事务单元。该语句用于取消事务中的部分或全部操作，使得这些操作对数据库所作的改动恢复为改动前的状态。命令格式：ROLLBACK TRANSACTION 单元名称|保存点名称(4)提交(或完成)一个事务单元。提交一个事物后，在该事物中对数据库所做的变动被保存到数据库中。命令格式：COMMIT TRANSACTION 单元名称,注意，一旦执行了COMMIT TRANSACTION语句，则事务被提交，此时将不再能够回滚事务。在一个事务内允许有重复的保存点名称，但若在ROLLBACK TRANSACTION语句中使用重复的保存点名称，则事物只回滚到离它最近的使用该保存点名称的SAVE TRANSACTION语句处。,Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。在使用方法以及具体意义上，这两种变量都有着较大的区别，本节将分别介绍这两种变量的使用方法。,114 变量,局部变量是在批处理（也可以是存储过程或触发器）内由用户定义并使用的变量。用户在使用局部变量前必须事先声明，而它的使用范围也仅限于声明它的批处理（也可以是存储过程或触发器）以内。实例11.4 利用局部变量在数据库学生库的学生信息表中查询学生“和平”的年龄。步骤：启动查询分析器，输入如下SQL语句：按“F5”键或单击工具栏“执行查询”图标执行。执行结果如图11.5所示,局部变量,图11.5使用局部变量查询的结果,从上面的例子，我们可以看出，通过声明局部变量可以查询学生信息表中任意的学生信息，只要修改赋给局部变量的值就可以。在使用局部变量查询需要做到两点：一是声明局部变量，二是给局部变量赋值。,（1）声明局部变量 命令格式：DECLARE(局部变量名 数据类型，.n)命令说明：DECLARE：该关键字表示将要声明变量。数据类型可以是除text、ntext和image外的数据类型或用户定义的数据类型。，.n：表示一个DECLARE语句中可以声明多个局部变量，这些变量用逗号相隔。(2)局部变量的赋值 声明局部变量后，系统自动为变量赋初值NULL。若需要另外赋值，可以使用SET或SELECT语句。赋值语法如下：命令格式：SELECT 局部变量名=表达式 FROM 表名,.n WHERE clause+n)或 SET 局部变量名=表达式,命令说明：表达式可以是一个具体的数据如数字、字符串等，也可以是一个表达式或另一个局部变量或全局变量，还可以是从一个查询语句中查询出来的数据。FROM子句用于向变量所赋的值源于由一个表中查询所得数据的情形。一个SELECT语句可以为多个变量赋值，但一个SET语句仅能为一个变量赋值。若使用SELECT语句为变量赋值，则不能与其查询功能同时使用。若使用SELECT语句从表中取数为变量赋值，则其返回的数据必须惟一，否则仅将最后一个数据赋给变量。,下面的例子是使用SELECT语句进行赋值，从表格中查询出一条数据赋值给已经声明的变量：USE pubs GO DECLARE author varchar(20)SELECT author=au_id FROM authors WHERE state=MI GO,11.4.2 全局变量,在SQL Server中全局变量作为一种特殊函数由系统预先定义，因此有的教材把全局变量放到函数部分介绍，称为SQL Server的内置函数。常用的全局变量有三十多个，用于记录数据库服务器的工作信息及性能数据。全局变量的作用范围是整个系统，通常利用全局变量来检测系统的设置值或执行查询命令后的状态值。,实例11.5 使用全局变量来查询SQL Server实例当前使用的语言和当前所使用语言的标识符。,由上例可以看出全局变量以开头，无需定义直接使用即可。除了上面的全局变量外，还有如CONNECTIONS返回自上次启动SQL Server数据库服务器以来连接或试图连接的次数，ERROR返回最近一次执行T-SQL语句的错误代码，version表示SQL Server的版本号等等，其他全局变量可参见帮助。,SQL Server支持结构化的编程方法，可以进行顺序结构、选择结构、循环结构的编程。本节结合例题介绍SQLServer2000的程序控制语句。,115 程序控制语句,1151 BEGINEND语句,BEGIN END语句相当于其他计算机语言中的大括号或复合语句，它将多条T-SQL语句封装成为一个整体的语句块，T-SQL中允许使用嵌套的BEGIN END语句。通常把BEGIN END语句放在语句IF ELSE或者WHILE中，使其中的语句作为一个整体来执行。,实例11.6在学生成绩表期中成绩中检查全体学生某门课程(课程编号为1001)的期中成绩。如果某个学生的成绩低于60分，则显示文本：“1001科目的分数低于60分”，并显示所有未及格学生的学号、姓名与期中成绩分数。,命令格式：BEGIN T-SQL语句块 END 命令说明：BEGIN、END分别表示语句块的开始和结束。T-SQL语句块可以是一条或一条以上的T-SQL语句。,1152 IFELSE语句,若希望在满足某种条件的情况下执行一些语句，而在该条件不满足的情况下执行另外一些语句。在这种情况下，我们就需要使用条件语句来在执行T-SQL语句时强加条件，来控制语句执行的顺序。,实例11.7在学生信息表中统计学生的平均年龄，如果平均年龄小于20则输出年龄组合正常，否则输出大于平均年龄的学生的信息。,由上例我们可归纳出IF.ELSE语句的命令格式：IF 返回布尔表达式T-SQL语句|BEGINEND语句块 ELSE T-SQL语句|BEGINEND语句块命令说明：返回“真”(TRUE)或“伪”(FALSE)的布尔表达式。如果布尔表达式中含有SELECT语句，则必须用圆括号将SELECT语句括起来。T-SQL语句|BEGINEND语句块：只能是一条。,11.5.3 CASE语句,当有多个选择方案时，如果使用IF ELSE语句进行选择判断，则由于需要层层嵌套而非常不便，此时可以在SELECT语句中采用CASE结构。CASE语句有两种格式：简单CASE函数与CASE搜索函数。前者将某个表达式与一组简单表达式进行比较以确定结果；后者则需计算一组布尔表达式以确定结果。下面用实例加以说明。,实例11.8在pubs数据库中根据作者来自的州的名字，打印出作者的姓名和州的全名,命令格式：CASE 输入表达式 WHEN 比较的表达式 THEN 结果表达式 n ELSE 返回结果表达式 END 命令说明；CASE后面的输入表达式，用于进行比较。WHEN后面给出用于进行比较的表达式的数据类型必须相同，或者能够隐性转换。n)表示可以有多个WHENTHEN结构。,实例11.9 显示学生成绩中的期中成绩分数。,由上例可见，CASE搜索函数的WHEN后表达式是布尔表达式，这是与简单CASE函数不同之处。命令格式；CASE WHEN 逻辑表达式 THEN ELSE 结果表达式 END,1154 WHILE语句,WHILE语句是一种条件循环语句，在满足条件时执行循环体，不满足条件时跳出循环结构。WHILE语句可以嵌套执行，嵌套时首先运行内层循环，内层循环结束之后再运行外层循环。,实例11.10在pubs数据库中，将书价小于$30的书提高50%，如果提高后的书价超过$50，则输出书价太贵。,由此，我们可给出命令格式：WHILE 逻辑表达式BEGIN 表达式 BREAK CONTINUEEND 命令说明：当逻辑表达式为真时，服务器将重复执行SQL语句组。BREAK的作用是在某些情况发生时，立即无条件地跳出循环，并开始执行紧接在END后面的语句。CONTINUE的作用是在某些情况发生时，跳出本次循环，开始执行下一次循环。,1155 RETURN语句,RETURN语句用于从当前过程、批处理或语句块中无条件退出，并不再执行位于RETURN之后的语句。命令格式：RETURN 整型表达式 命令说明：RETURN一般用于从存储过程给调用过程或应用程序返回整型值。除非特别指明，所有系统存储过程返回0值表示成功，返回非0值则表示失败。,11.6 游标,数据库的游标是类似于C语言指针一样的语言结构。SELECT语句是针对数据库中表的所有数据进行处理的，如果需要对某一行数据进行处理，使用SELECT语句就显得力不从心。游标正好弥补了这一缺陷，它类似于一个指针，可以定位于SELECT语句结果集合中的某指定行，从而对该行的数据进行操作。游标的使用过程依次为：声明游标、打开游标、使用游标、关闭游标、释放游标。,声明游标,实例11.11 定义游标，打开学生信息表。,上面的例子首先通过DECLARE关键字来定义游标，然后选择一个表，打开游标，就可以查询到当前表中记录的个数。其中cursor_row表示当前游标记录数目是一个全局变量。由此，我们给出打开游标的命令格式：DECLARE 游标名称 INSENSITIVE SCROLL CURSOR FOR SELECT语句 FOR READ ONLY|UPDATE OF 列名1，列名2，列名3,命令说明：游标名：为声明游标所起的名字。INSENSITIVE：使用INSENSITIVE定义的游标，把提取出来的数据放入一个在tempdb数据库里创建的临时表里。任何通过这个游标进行的操作，都在这个临时表里进行。所以所有对基本表的改动都不会在用游标进行的操作中体现出来。SCROLL：使用SCROLL关键字定义的游标，具有包括如下所示的所有取数功能：FIRST：取第一行数据；LAST：取最后一行数据；PRIOR：取前一行数据；NEXT：取最后一行数据；RELATIVE：按相对位置取数据；ABSOLUTE：按绝对位置取数据。如果没有在声明时使用SCROLL关键字，那么所声明的游标只具有默认的NEXT功能。,SELECT语句：一些标准的SELECT语句，主要用来定义游标所要进行处理的结果集。在声明游标的SELECT语句中，不允许使用如COMPUTE，COMPUTE BY和INTO等关键字。FOR READ ONLY：声明只读光标。不允许通过只读光标进行数据的更新。UPDATE OF 列名1，列名2，列名3：定义在这个游标里可以更新的列。如果定义了OF 列名1，列名2，列名3，那么只有列在表中的列可以被更新；如果没有定义OF 列名1，列名2，列名3，那么游标里的所有列都可以被更新。,打开游标,在可以使用游标之前，必须首先打开游标。开游标的语法如下：OPEN cursor_name使用关键字OPEN来打开游标，cursor_name为打开游标的名字。具体例子可参考实例11.11,1163 关闭与释放游标,在打开游标后，SQL Server服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。关闭游标的语法：CLOSE cursor_name,关闭游标后可以再次打开游标，在一个批处理中也可以多次打开和关闭游标。而游标本身也会占用一定的计算机资源，所以在使用完游标后，为了回收被游标占用的资源，应该将游标释放。释放游标的语法是：DEALLOCATE cursor_name 当释放完游标后，如果要重新使用这个游标必须重新执行声明游标的语句。,1164 使用游标,在打开游标以后，就可以打开游标通过FETCH语句提取数据了。实例11.12根据例11.11使用绝对位置以及相对位置来提取信息。,FETCH语句的目的是从T-SQL服务器游标中检索特定的记录。使用游标提取数据的语法如下：FETCH NEXT PRIOR FIRST LAST ABSOLUTE n nvarI RELATIVEn nvarFROM 游标名称 INTO 变量,命令说明：NEXT：从当前记录立即返回结果集合以及对返回的记录增加当前记录。如果FETCI NEXT是通过游标第一次提取，返回结果集合的第一条记录。NEXT是提取游标选项的默认选项。在处理当前记录之前立即返回记录以及减少返回记录的当前记录数目。PRIOR：读取当前游标指针所指向行的上一行数据，并将游标指向该上一行。如果FETCH PRIOR是通过游标第一次提取，没有记录返回并且光标停留在第一行。FIRST：返回第一条记录并且作为当前记录。LAST：返回最后一条记录并作为当前记录。ABSOLUTE n nva:如果n或者nva正数，返回从游标头起的第n记录，并将其作为当前记录；如果为负数，那么返回游标尾前面的第n条记录；如果是0，则没有记录返回。RELATIVEn nvar：相对当前游标位置。,实例11.13 修改第5行的班级内容：,117 用户自定义函数,在SQL Server 2000中，为了使逻辑编程更为简洁清晰，设计了函数的功能。有两类函数，系统内置函数和用户自定义函数。系统内置函数包括数学函数(如max、min、count等)、字符串函数(如len、left等)、日期函数(如year、getdate等)、系统函数(如user)，这些系统内置函数。,除了可以使用系统提供的内置函数外，用户可以定义自己的函数，来补充和扩展系统支持的内置函数。用户自定义函数可以像系统函数一样在查询或存储过程中使用，也可以想存储过程一样通过EXECUTE命令来执行。,1171创建和调用用户自定义函数,实例11.14创建一个函数，根据是否为团员返回不同的数据，如果输入为1，即为团员则返回该团员的信息，否则返回非团员的信息。,语句如下：CREATE FUNCTION ee(yong BIT)RETURNS info TABLE/*定义返回的表结构*/(学号 char(7),姓名 char(8),期末成绩 int)ASBEGINIF yong=1 INSERT info/*返回数据*/SELECT 学生信息.学号,姓名,期末成绩 FROM 学生信息,学生成绩 WHERE 是否团员=1 and 学生信息.学号=学生成绩.学号ELSE INSERT info/*返回数据*/SELECT 学生信息.学号,姓名,期末成绩 FROM 学生信息,学生成绩 WHERE 是否团员=0 and 学生信息.学号=学生成绩.学号RETURNENDGOUSE 学生库SELECT*FROM dbo.ee(1)GO,我们可以给出创建函数的命令格式：CREATE FUNCTION 所有者 函数的名称(自定义函数的参数 AS 定义函数参数的数据类型)：RETURNS 函数返回值的数据类型 WITH，n AS BEGIN 函数体 RETURN 标量值表达式 END：=ENCRYPTION|SCHEMABINDING,1172修改和删除用户自定义函数,删除用户自定义的函数的语法是：DROP FUNCTION owner_name.function_name,.n命令说明：(1)DROP FUNCTION：删除函数命令的关键字。(2)owner_name：删除表的权力仅属于表的拥有者，且不能转移。(3)function_name：函数的名称。,实例11.15将对前面定义的ee函数进行修改，修改为如果为团员则输出期末成绩大于80的学生的信息。语句如下：ALTER FUNCTION ee(yong BIT)/*修改函数ee*/RETURNS info TABLE/*定义返回的表结构*/(学号 char(7),姓名 char(8),期末成绩 int)AS,BEGINIF yong=1 INSERT info/*返回数据*/SELECT 学生信息.学号,姓名,期末成绩 FROM 学生信息,学生成绩 WHERE 是否团员=1 and 学生信息.学号=学生成绩.学号 and 期末成绩80ELSE INSERT info/*返回数据*/SELECT 学生信息.学号,姓名,期末成绩 FROM 学生信息,学生成绩 WHERE 是否团员=0 and 学生信息.学号=学生成绩.学号RETURNENDGO,11.8本章总结,SQL Server支持结构化的编程方法，可以进行逻辑编程，处理各种结构的编程，从而完成较为复杂的作业。在本章中主要讲述了批处理、事务、变量等概念，目的是让大家在进行程序设计时使用这些SQL Server本身特有的方法以达到进行复杂程序设计的目的。另外还介绍了5种程序控制语句，游标的使用方法以及用户自定义函数。大家应该对SQL Server的编程逻辑有清楚的认识，能够胜任复杂的数据查询和修改工作。,11.9思考与实训练习,思考题1什么叫批处理，什么叫事务，它们之间的关系是什么？2执行一个批处理时，如果批处理内有一个语句错误，那么批内的其他语句是否还执行？3执行一个事务时，如果其内部发生错误，但又没与设置回滚，是否这些错误会被提交？4SQL Server 2000中有几种变量，如何去定义，区别是什么？5IFELSE中的SQL语句应该有几句？如果要在IF语句中执行多个SQL语句该如何处理？6什么情况下使用CASE语句？CASE语句有几种格式？他们之间的区别是什么？7游标的使用过程是什么样？8如何进行用户自定义函数的创建和使用？,实训,1实训目的（1）掌握SQL Server结构化的编程方法，使用这些SQL Server本身特有的方法以达到进行复杂程序设计的目的。（2）熟练掌握SQL Server中的5种控制语句编程的方法。2实训环境 SQL Server 2000的运行环境。SQL Server 2000的查询分析器。,3实训内容（1）利用批处理在学生库中建立一个表名为专业的表，在pubs数据库中建立一个视图。创建一个专业表，结构如表11.1所示 表11.1专业表结构列名称数据类型说 明专业号int用于存储专业号，固定4位字符专业名称varchar(20)用于存储专业名称，最长20位字符 在pubs数据库中创建一个视图，使除了来自CA州的作家外，所有的作家都可以被浏览。然后查询该视图。在查询分析器中通过批处理完成上述的操作。（2）编写一个事务控制程序，要求在事务中包含3个操作：第一个操作是在Northwind数据库的Employees表中插入一条数据，并检索插入是否成功，然后设置一个保存点，紧接着执行第二个操作，删除刚才插入的数据，并检索删除是否成功，然后回滚事务，最后执行检索操作，看插入的数据是否还存在。打开查询分析器。输入程序语句。,（3）利用局部变量在数据库学生库的学生信息表中查询学生“王义”的班级。并在表学生信息与学生成绩中将学生王义的学号修改为9922。（4）使用SQL Server提供的程序控制语句IFELSE，打印Northwind数据库中Employees的两类名单。要求打印年纪在20-40岁之间的青年工作人员名单。打印年纪在40岁以上的老年工作人员名单。（5）利用CASE语句判断一个变量x的取值为2或3时返回相应信息，否则返回，error。（6）利用循环语句打印8个数1，2，3，4.8。（7）使用游标读取学生信息表中的第一条记录和最后一条记录。（8）输入一个数量型用户自定义函数，要求输入长方形的长和宽，计算长方形的面积。4.实训要求（1）将实训3的内容在SQL Server 2000环境中调试成功，并将编写的程序调试、运行正确成功后，存入磁盘中。（2）每次实训后，总结一下实训的重点内容和应注意的问题，并写成实训报告的形式上交。,