SQLServer的程序设计.ppt

第11章SQLServer的程序设计,教学提示：SQLServer程序设计是读者学习SQLServer数据库的一个重要环节，它对以后程序开发有着直接的决定因素。本章从最简单的Transcat-SQL的语法入手由浅入深地讲解函数、事务、锁和游标等，它们是灵活应用Transcat-SQL语句的关键，在程序设计和开发中起着重要的作用。教学目标：通过本章的学习，读者应该掌握编程的基础知识、基本语句；理解事务、锁和游标等基本原理；能对事务、锁和游标进行简单地运行。,第11章SQLServer的程序设计,Transact-SQL的语法规则Transact-SQL的语法规则见表11-1,编程基础知识,第11章SQLServer的程序设计,SQLServer的变量在Transact-SQL语句中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。1局部变量局部变量是一个能够有特定数据类型的对象，它的作用范围仅局限于程序内部，局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。局部变量被引用时，要在其名称前加上标识符，而且必须先用DECLARE命令定义后才可以使用，其说明形式如下：DECLAREvariable_namedatatype，variable_namedatatype其语法如下：SELECTvariable_name=valueSETvariable_name=value【例11.1】声明一个长度为6个字符的变量编号，并赋值。DECLARE编号CHAR(6)SELECT编号=010101,编程基础知识,第11章SQLServer的程序设计,编程基础知识2全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不局限于某一程序，任何程序均可调用。全局变量通常存储一些SQLServer2005的配置设定值和效能统计数据。全局变量时应注意以下几点：(1)全局变量不是由用户的程序定义的，它们是在服务器级定义的。(2)用户只能使用预先定义的全局变量。(3)引用全局变量时，必须以标识符开头。(4)局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。3.注释符在Transact-SQL中可使用两类注释符：(1)ANSI标准的注释符-用于单行注释。(2)与C语言相同的程序注释符号，即/*/，/*用于注释文字的开始，*/用于注释文字的结尾，可在程序中标识多行文字的注释。,第11章SQLServer的程序设计,算符主要分算术运算符、赋值运算符、位运算符、关系运算符、逻辑运算符以及字符串连接运算符，见表11-2,SQLServer中的运算符,第11章SQLServer的程序设计,SQLServer2005提供的函数分为两大类内部函数和用户自定义函数。内部函数内部函数的作用是用来帮助用户获得系统的有关信息、执行有关计算、实现数据转换以及统计功能等。SQL所提供的内部函数又分为系统函数、日期函数、字符串函数、数学函数、集合函数等几种,常用函数,第11章SQLServer的程序设计,系统函数,表11-3列出一些常用的系统函数及其功能。,第11章SQLServer的程序设计,【例11.2】查询主机名称。在SQLServerManagementStudio查询窗口中运行以下代码：SELECTHOST_NAME()GO【例11.3】返回book1表中书名字段的长度。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECTCOL_LENGTH(book1，书名)AS书名长度FROMbook1GO,系统函数,第11章SQLServer的程序设计,日期函数用来显示日期和时间的信息。它们处理datatime和smalldatatime的值，并对其进行算术运算。表11-4列出了所有的日期函数。,日期函数,第11章SQLServer的程序设计,【例11.4】查询服务器当前的系统日期和时间。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECT当前日期=GETDATE()，月=MONTH(GETDATE()，日=DAY(GETDATE()，年=YEAR(GETDATE();GO,第11章SQLServer的程序设计,字符串函数用于对字符串进行连接、截取等操作，表11-6列出了常用的字符串函数。,字符串函数,第11章SQLServer的程序设计,【例11.5】STUFF函数的练习，从原始字符串中删除4个字符，然后再插入另一个字符串。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOPrintSTUFF(ccccadrkuekgoj,4,3,AAA)GO,字符串函数,第11章SQLServer的程序设计,数学函数用来对数值型数据进行数学运算。表11-7列出了常用的数学函数,数学函数,第11章SQLServer的程序设计,集合函数也称为统计函数，它对一组值进行计算并返回一个数值。聚合函数经常与SELECT语句的子句一起使用，表11-8列出了常用的聚合函数及其功能。,集合函数,第11章SQLServer的程序设计,用户可以使用CREATEFUNCTION语句编写自己的函数，以满足特殊需要。可用用户自定义函数来传递0个或多个参数，并返回一个简单的数值分为3种，分别是标量用户自定义函数、直接表值用户定义函数和多语句表值用户自定义函数1创建标量用户自定义函数标量用户自定义函数返回一个简单的数值，如int、char、decimal等其语法格式如下：CREATEFUNCTIONowner_name.function_name(parameter_nameASscalar_parameter_data_type=default，n)RETURNSscalar_return_data_typeWITH，nASEEGINFunction_bodyRETURNscalar_expressionEND,用户自定义函数,第11章SQLServer的程序设计,【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOCREATEFUNCTIONAvgdingji_book1(出版社nchar(20)RETURNSFLOATASBEGINDECLARE平均定价FLOATSET平均定价=(SELECTAVG(定价)FROMbook1WHERE出版社=出版社)RETURN平均定价END使用下面语句对刚创建的函数进行操作。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECTdbo.Avgdingji_book1(中国长安)AS平均成绩GO,用户自定义函数,第11章SQLServer的程序设计,表值函数返回一个Table型数据，对直接表值用户定义函数而言，返回的结果只是一系列表值，没有明确的函数体。其语法格式为：CREATEFUNCTIONowner_name.function_name(parameter_nameASscalar_parameter_data_type=default，.n)RETURNSTABLEWITH，.nASRETURN(select-statement)【例11.8】创建一个函数返回同一个出版社所出书的部分信息。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOCREATEFUNCTION书的信息(出版社nchar(20)RETURNSTABLEASRETURN(SELECT书名，定价，出版社FROMbook1WHERE出版社=出版社)使用下面语句对刚创建的函数进行操作：在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECT*FROMdbo.书的信息(海南)GO,创建直接表值用户定义函数,第11章SQLServer的程序设计,多语句表值用户自定义函数是以BEGIN语句开始，END语句结束的函数体，这些语句可将行插入返回的表中。其语法格式为：CREATEFUNCTIONowner_name.function_name(parameter_nameASscalar_parameter_data_type=default，.n)RETURNSreturn_variableTABLEWITH，.nASBEGINFunction_bodyRRTURNEND其中，return_variable：指一个TABLE类型的变量用于存储和累积返回的表中的数据行。其余参数与标量型用户自定义函数相同。,创建多语句表值用户自定义函数,第11章SQLServer的程序设计,【例11.9】创建一个函数返回定价高于一定价格的书的信息。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOCREATEFUNCTIONmoney_higher(highermoneymoney)RETURNSmoney_higherTABLE(编号nvarchar(255)，书名nvarchar(255)，定价money，出版社nvarchar(255)ASBEGININSERTmoney_higherSELECTbookin.编号，书名，定价，出版社FROMbookin，book1WHEREbookin.编号=book1.编号and定价highermoneyRETURNENDGO使用下面语句对刚创建的函数进行操作：在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECT*FROMdbo.money_higher(1000)GO,创建多语句表值用户自定义函数,第11章SQLServer的程序设计,批处理是包含一个或多个Transact-SQL语句的组，它将一次性地发送到SQLServer中执行，应用程序将这些语句作为一个单元一次性地提交给SQLServer，并由SQLServer编译成一个执行计划，然后作为一个整体来执行【例11.10】批处理示例分析。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOCREATEVIEWabcASSELECT*FROMbook1GOSELECT*FROMbook2GO在SQLServerManagementStudio查询窗口中运行以下代码，观察系统给出的信息。DECLAREAAINTGOSELECTAA=44GO,批处理,第11章SQLServer的程序设计,1IF.ELSE语句利用IF.ELSE语句能够对一个条件进行测试，并根据测试的结果来执行相应的操作。ELSE语句是可选的。其语法为：IF逻辑表达式语句块1ELSE语句块2【例11.11】查询是否有书的定价高于8000元的书，如果有则输出该书的信息包括作者姓名，如果没有就输出“不存在高于8000元的书”。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGODECLARE定价money，messagevarchar(250)SET定价=8000IFEXISTS(SELECT*FROMbook1WHERE定价定价)BEGINSELECTDISTINCTbook1.编号,书名,定价,作者姓名FROMbook1，teacherWHEREbook1.编号=teacher.编号and定价定价ENDELSESETmessage=不存在高于8000元的书PRINTmessage,流控语句,第11章SQLServer的程序设计,2BEGINENDBEGIN和END用来定义语句块，必须成对出现。它将多个SQL语句括起来，相当于一个单一语句。常用于下列情况：(1)WHILE循环需要包含多条语句。(2)CASE函数的元素需要包含多条语句。(3)IF.ELSE语句中需要包含多条语句。BEGIN.EDN语句的语法格式如下：EEGINSQL语句1SQL语句2.ENDBEGIN.END语句块通常与其他流控语句综合使用，BEGIN和END分别表示语句块的开始和结束，它们必须成对使用,流控语句,第11章SQLServer的程序设计,3WHILE、BREAK和CONTINUEWHILE语句用来实现循环结构，其语法为：WHILE逻辑表达式语句块功能：当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。BREAK语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。例如，WHILE逻辑表达式1BENIN语句1IFWHILE逻辑表达式2CONTINUE语句2END,流控语句,第11章SQLServer的程序设计,4CASE表达式CASE表达式用于多条件分支选择CASE表达式有简单的CASE表达式和搜索型CASE表达式两种。1)简单的CASE表达式CASE表达式WHEN表达式THEN表达式.ELSE表达式END2)搜索型CASE表达式CASEWHEN逻辑表达式THEN.ELSE表达式END,流控语句,第11章SQLServer的程序设计,【例11.12】从book1表中，选取书名、出版社，如果出版社为中国长安则输出“中国最有实力的出版社之一”，如果是海南出版社，则输出“海外影响力最强的出版社之一”。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECT书名，出版社=CASE出版社WHEN中国长安THEN中国最有实力的出版社之一WHEN海南THEN海外影响力最强的出版社之一ENDFROMbook1【例11.13】从book1表中查询所有书的定价情况，凡定价为空的输出“未录入定价”，小于30元输出“价格适中”，30100元输出“价格偏高”，100300元输出“价格高”，300以上的输出“价格特别高”。在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOSELECT书名，出版社，定价情况=CASE定价WHEN定价ISNULLTHEN未录入定价WHEN定价0and定价=30and定价=100and定价=300THEN价格特别高ENDFROMbook1,流控语句,第11章SQLServer的程序设计,5RETURNRETURN语句实现无条件退出批命令、存储过程或触发器。RETURN整型表达式6WAITFOR其语法为：WAITFORDELAYTIME|TIMETIME【例11.14】等待2小时2分零2秒后才执行SELECT语句。WAITFORDELAY02:02:02SELECT*FROMbook17GOTO【例11.15】求1+2+3+100的总和。在SQLServerManagementStudio查询窗口中运行以下代码：DECLAREsumSMALLINT，iSMALLINTSETi=1Setsum=0BEG:IF(i4BEGINROLLBACKTRANSACTIONPRINT此编号的书已超过9本，不能再次插入ENDELSEBEGINCOMMITTRANSACTIONPRINT此编号的书还未超过9本，你的操作插入成功END验证结果在SQLServerManagementStudio查询窗口中运行以下代码：USEbookSELECT*FROMbook2WHERE编号=200701,事务操作,第11章SQLServer的程序设计,在SQLServerManagementStudio查询窗口中运行以下代码：USEbookGOBEGINTRANSACTIONINSERTbook2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200777,SQL2005,高等教育,2007-11-24,30)INSERTbook2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200778,周末,高等教育,2007-11-25,40)INSERTbook2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200779,今日一线,高等教育,2007-11-26,50)INSERTbook2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200780,大家谈,高等教育,2007-11-27,50)INSERTbook2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200781,高等数学,高等教育,2007-11-28,50)DECLAREcountnumINTSETcountnum=(SELECTCOUNT(*)FROMbook2WHERE编号=200701)IFcountnum4BEGINROLLBACKTRANSACTIONPRINT此编号的书最多只能录入4本,已超过最大数,你不能成功录入ENDELSEBEGINCOMMITTRANSACTIONPRINT此编号的书还未超过4本，你的操作录入成功END,事务操作,第11章SQLServer的程序设计,开发问题如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题，这些问题包括以下几种情况。1)丢失或覆盖更新2)未确认的相关性(脏读)3)不一致的分析(不能重复读)4)幻读,锁,第11章SQLServer的程序设计,SQLServer2005自动将资源锁定在适合任务的级别。锁定在较小的粒度(例如行)可以增加并发但需要较大的开销，因为，如果你锁定许多行，则需要控制更多的锁。锁定在较大的粒度(例如表)需要维护的锁较少，要求的开销较低。但，因为锁定整个表会限制其他事务对表中任意部分进行访问，所以，就并发而言是相当昂贵的。几种常用的锁模式。1共享锁用于不更改或不更新数据的操作(只读操作)，如SELECT语句共享锁允许并发事务读取(SELECT)一个资源2更新锁用于可更新的资源中，防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。3排他锁用于数据修改的操作有很多，例如，INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新，可以使用排他锁,SQLServer中的锁定,第11章SQLServer的程序设计,【例11.19】使用对象资源管理器浏览系统中的锁。【例11.20】使用sp_lock系统存储过程显示SQLServer中当前持有的所有锁的信息。在SQLServerManagementStudio查询窗口中运行以下代码：USEmasterGOEXECsp_lockGO,SQLServer中的锁定,第11章SQLServer的程序设计,封锁机制的引入能解决并发用户的数据不一致性问题，但也会引起事务间的死锁问题。死锁的主要原因是由于两个或更多的事务竞争资源而直接或间接地相互等待而造成的要求每个事务一次就将要使用的数据全部加锁，否则就不能继续执行。(1)预先规定一个顺序，所有事务都按这个顺序实行加锁，这样也不会发生死锁。例如，通过SETDEADLOCKPRIORITY语句设置会话的优先级，如果设置为LOW说明该会话的优先级较低，再出死锁时，首先中断该会话的事务。(2)允许死锁发生，系统采用某些方式诊断当前系统中是否有死锁发生,死锁,第11章SQLServer的程序设计,游标(Cursor)是一种数据访问机制，它允许用户访问单独的数据行，而并非对整个行集合进行操作(通过使用SELECT，UPDATE或者DELETE语句进行)游标的定义游标是一个与Transact-SQL的SELECT语句相关联的符号名，它使用户可逐行访问由SQLServer返回的结果集。游标包括以下两个部分。(1