第十章 SQL Server的程序设计.ppt

第十章SQLServer的程序设计,SQLServer数据库应用技术,2,本章主要内容,10.1批处理与流程控制10.2事务处理10.3锁机制10.4游标,3,10.1批处理与流程控制,通常,服务器端的程序使用SQL语句来编写.一般而言,一个服务器端的程序是由以下一些部分组成:批注释程序中使用的变量改变批中语句的执行顺序的流程控制语言错误和消息的处理,4,10.1批处理与流程控制,一.批处理批处理就是单个或多个Transact-SQL语句的集合，由用户或应用程序一次性发送给SQLServer解释并执行批处理内的所有语句指令。建立批处理如同编写SQL语句，区别在于它是多条语句同时执行的，用GO语句作为一个批处理的结束。,5,一.批处理,使用批时的几点限制:1.大多数的create命令要在单个批中执行,但createdatabase,createtable和createindex例外2.用set语句改变的选项在批结束时生效3.在同一个批中不能改变一个表再立即引用其新列.,6,【例10.1】一个批处理的例子,usestudentgocreateviewteacher_info_viewasselectteacher_id,name,tech_title,salaryfromteacher_infogoselect*fromteacher_info_viewgo,createview必须是批处理中唯一的语句,一.批处理,7,【例10.2】错误例题,usestudentgocreateviewstud_info_viewasselectstud_id,namefromstud_infoinsertintostud_info_viewvalues(0501010211,黄铭)go,一.批处理,8,【例10.3】无效批处理,declarestud_varintgoselectstud_var=25goprintstud_vargo,一.批处理,9,二.流程控制,使用T-SQL语言编程的时候，常常要利用各种流程控制语句去进行顺序、分支控制转移、循环等操作。T-SQL提供了一组流程控制语句，包括：条件控制语句、无条件转移语句、循环语句和返回状态值给调用例程的语句1.BEGINEND语句块2.IFELSE语句3.CASE表达式4.WHILE语句5.WAITFOR语句6.GOTO语句7.RETURN语句,10,二.流程控制-beginend,其语法如下：BEGIN命令行|程序块ENDBEGINEND用来设定一个程序块，将在BEGINEND内的所有程序视为一个单元执行。BEGINEND经常在条件语句（如IFELSE）或循环等控制流程的语句中使用。在BEGINEND中可嵌套另外的BEGINEND来定义另一程序块。,11,二.流程控制-ifelse,IFELSE语句用来实现选择结构，其语法格式如下:IF(条件表达式)命令行|程序块ELSE命令行或程序块其中:可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IFELSE用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。如果不使用程序块，IF或ELSE只能执行一条命令。IFELSE可以进行嵌套,最多嵌套32级。,12,二.流程控制-ifelse,【例10.4】,if(selectavg(grade)fromstud_gradewhereleft(stud_id,8)=04010101)=60beginprintpassend,13,二.流程控制-ifelse,【例10.5】统计2004级计算机应用技术专业02班的学生人数不超过5人，则向数据表stud_info中插入新的学生记录,declarep_totalintbeginselectp_total=count(*)fromstud_infowhereleft(stud_id,8)=04010102if(p_total560)updatestud_infosetmark=mark-5whereyear(birthday)=1986end,15,二.流程控制-ifelse,【例10.6】从数据表stud_grade中读出学生陈红的成绩,将百分制转换成等级制显示.,declarescorenumeric(4,1),stepchar(1)beginselectscore=gradefromstud_gradewherename=陈红if(score=90andscore=80)setstep=Belseif(score=70)setstep=Celseif(score=60)setstep=Delsesetstep=Eprintstepend,16,二.流程控制-case表达式,CASE函数可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE函数按照使用形式的不同，可以分为简单CASE表达式和搜索CASE表达式。【注意】1.执行CASE子句时，只运行第一个匹配的子句。2.CASE表达式不是语句，它不能单独执行，而只能作为语句的一部分来使用。,17,二.流程控制-case表达式,1.简单CASE表达式将一个测试表达式与一组简单表达式进行比较，如果某个简单表达式与测试表达式的值相等，则返回相应结果表达式的值。其语法格式如下：CASE测试表达式WHEN测试值1THEN结果表达式1.WHEN测试值nTHEN结果表达式nELSE结果表达式mEND,18,二.流程控制-case表达式,【例10.7】从学生表stud_info中，选取stud_id，gender，如果gender为“男”则输出“M”，如果为“女”输出“F”。,selectstud_id,sex=casegenderwhen男thenMwhen女thenFendfromstud_info,19,二.流程控制-case表达式,2.搜索CASE表达式与简单表达式不同的是，搜索表达式中，CASE关键字后面不跟任何表达式，在各WHEN关键字后面跟的都是逻辑表达式，其语法格式如下:CASEWHEN逻辑表达式1THEN结果表达式1WHEN逻辑表达式nTHEN结果表达式nELSE结果表达式mEND,20,二.流程控制-case表达式,【例10.8】从stud_grade表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。,selectstud_id,name,score=casewhengradeisnullthen未考whengrade=60andgrade=70andgrade=90then优秀endfromstud_grade,21,二.流程控制-while语句,其语法如下：WHILE(条件表达式)BEGIN命令行或程序块BREAKCONTINUE命令行或程序块ENDWHILE命令在设定的条件成立时，会重复执行命令行或程序块。CONTINUE命令可以让程序跳过CONTINUE命令之后的语句，回到WHILE循环的第一行，继续进行下一次循环。BREAK命令则让程序完全跳出循环，结束WHILE命令的执行。WHILE语句也可以嵌套。,22,二.流程控制-while语句,【例10.9】以下程序计算1100之间所有能被3整除的数的个数及总和。,declarexsmallint,ysmallint,numssmallintsetx=0sety=1setnums=0while(y=100)beginif(y%3=0)beginsetx=x+ysetnums=nums+1endsety=y+1endprintstr(x)+,+str(nums),23,二.流程控制-waitfor,其语法如下：WAITFORDELAY|TIMEWAITFOR命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。其中时间必须为DATETIME类型的数据，但不能包括日期。各关键字含义如下：1DELAY：用来设定等待的时间段，最多可达24小时2TIME：用来设定等待结束的时间点,24,二.流程控制-waitfor,【例10.10】等待1小时2分零3秒后才执行SELECT语句。【例】等到下午15点零8分后才执行SELECT语句。,waitfordelay01:02:03select*fromstud_info,waitfortime15:38:00select*fromstud_info,25,二.流程控制-goto,GOTO语句是无条件转移语句，语法格式为：GOTO标识符GOTO命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。作为跳转目标的标识符可为数字与字符的组合。但必须以“：”结尾。在GOTO命令行，标识符后不必跟“：”,26,二.流程控制-goto,【例10.11】求123100的总和。,declaresumsmallint,ismallintseti=1setsum=0beg:if(i=100)beginsetsum=sum+i;seti=i+1gotobegendprintsum,27,二.流程控制-return,语法如下：RETURN（整数值）RETURN命令用于结束当前程序的执行，返回到上一个调用它的程序或其它程序。在括号内可指定一个返回值。如果没有指定返回值，SQLServer系统会根据程序执行的结果返回一个内定值，如右图：,如果运行过程产生了多个错误，SQLServer系统将返回绝对值最大的数值；如果此时用户定义了返回值，则以返回用户定义的值。RETURN语句不能返回NULL值。,0程序执行成功-1找不到对象-2数据类型错误-3死锁-4违反权限原则-5语法错误-6用户造成的一般错误-7资源错误如磁盘空间不足-8非致命的内部错误-9已达到系统的极限-10-11致命的内部不一致性错误-12表或指针破坏-13数据库破坏-14硬件错误,28,10.2事务处理,一.事务概述二.事务管理三.事务模式四.事务日志,29,一.事务概述,一个事务是由一系列的对数据库的查询操作和更新操作构成的。事务是数据库系统中执行的一个工作单位，它是由用户定义的一组操作序列。一个事务可以是一组SQL语句、一条SQL语句或整个程序，一个应用程序可以包括多个事务。事务的开始与结束可以由用户显式控制。如果用户没有显式地定义事务，则由DBMS按照缺省规定自动划分事务。在SQL语言中，定义事务的语句有三条：,30,一.事务概述,BEGINTRANSACTIONCOMMITROLLBACKBEGINTRANSACTION表示事务的开始；COMMIT表示事务的提交，即将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，此时事务正常结束；ROLLBACK表示事务的回滚，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的更新操作全部撤销，再回滚到事务开始时的状态。,31,一.事务概述,【例10.12】将student数据库中学生基本信息表stud_info的学号stud_id由0401010811修改为0401010111,begintranstud_transactionupdatestud_infosetstud_id=0401010111wherestud_id=0401010108updatestud_gradesetstud_id=0401010111wherestud_id=0401010108committranstud_transaction/*对于显示定义的事务,最后必须提交*/,32,一.事务概述,事务是由有限的数据库操作序列组成，但并不是任意的数据库操作序列都能成为事务，为了保护数据的完整性，一般要求事务具有以下四个特征：1原子性一个事务是一个不可分割的工作单位，事务在执行时，应该遵守“要么不做，要么全做”（nothingorall）的原则，即不允许事务部分的完成。即使因为故障而使事务未能完成，它执行的部分结果要被取消。,33,一.事务概述,2一致性事务对数据库的作用是数据库从一个一致状态转变到另一个一致状态。所谓数据库的一致状态是指数据库中的数据满足完整性约束。例如，银行企业中，“从帐号A转移资金额R到帐号B”是一个典型的事务，这个事务包括两个操作，从帐号A中减去资金额R和在帐号B中增加资金额R，如果只执行其中一个操作，则数据库处于不一致状态，帐务会出现问题。也就是说，两个操作要么全做，要么全不做，否则就不能成为事务。可见事务的一致性与原子性是密切相关的。,34,一.事务概述,3隔离性如果多个事务并发地执行，应像各个事务独立执行一样，一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。并发控制就是为了保证事务间的隔离性。4持久性指一个事务一旦提交，它对数据库中数据的改变就应该是持久的，即使数据库因故障而受到破坏，DBMS也应该能够恢复。总结:事务的这4个特性一般简称为事务的ACID特性,35,二.事务管理语句,(1)建立事务begintransaction事务名称|tran_name_variablewithmarkdescription(2)commit语句标志事务结束committrantransaction_name|tran_name_variable或commitwork,36,二.事务管理语句,(3)rollback语句rollbacktrantransaction_name|tran_name_variable|savepoint_name|savepoint_name或rollbackwork(4)设置保存点savetransavepoint_name,37,二.事务管理语句,补充:两个可用于事务管理的全局变量error:给出最近一次执行的出错语句引发的错误号,error为0表示未出错.rowcount:给出受事务中已执行语句所影响的数据行数,38,二.事务管理语句,【例10.13】,declaretrannamevarchar(20)selecttranname=student_manager1begintrantrannamegoupdatestud_gradesetgrade=grade*0.90wherecourse_idlike%06gocommittrantranname,39,二.事务管理语句,【例】,begintraninsertstud_gradevalues(0401010113,王小明,0401010106,80)savetranmy_savedeletefromstud_gradewherename=王小明rollbacktranmy_savecommittran,40,二.事务管理语句,【例10.14】使用事务处理方式对表stud_grade执行更新操作,成功则提交事务,失败则取消事务,begintranstudent_manager2updatestud_gradesetgrade=round(sqrt(grade)*10,0)wherecourse_idlike%06iferror!=0rollbacktranstudent_manager2elsecommittranstudent_manager2,41,三.事务模式,1.显示事务模式可以由用户在其中定义事务的启动和结束.2.隐式事务模式指在当前事务提交或回滚后,自动启用新事务.setimplicit_tranactionson|off3.自动事务模式,42,四.事务日志,用来记录事务的每一次对数据库的更新操作。1.事务日志的内容2.事务日志文件（logging）,43,10.3锁机制,数据库是一个共享资源，允许多个用户程序并行地存取数据库。若对这种并发操作不加以控制就会破坏数据的一致性。,44,一.锁的概念,锁作为一种安全机制，用于控制多个用户的并发操作，防止用户读取正在由其他用户更改的数据或者多个用户同时修改同一数据，确保事务的完整性和数据的一致性。所谓封锁就是当一个事务在对某个数据对象（可以是数据项、记录、数据集、以至整个数据库）进行操作之前，必须获得相应的锁，以保证数据操作的正确性和一致性。锁定机制的主要属性是锁的粒度和锁的类型。,45,一.锁的概念-锁的粒度,根据对数据的不同处理，封锁的对象可以是这样一些逻辑单元：字段、记录、表、数据库等。锁对象的大小称之为锁粒度（LockGranularity）.SQLServer提供了多种粒度的锁，允许一个事务锁定不同类型的资源。锁的粒度越小，系统允许的并发用户数目就越多，数据库的利用率就越高，但封锁机构复杂，系统开销也就越大。管理锁定所需要的系统资源越多。反之，则相反。为了减少锁的成本，应该根据事务所要执行的任务，合理选择锁的粒度，将资源锁定在适合任务的级别范围内。由于同时封锁一个记录的概率很小，一般数据库系统都在记录级上进行封锁，以获得更高的并发度。,46,一.锁的概念-锁的粒度,按照粒度增加的顺序，不同粒度的锁可以锁定的资源如表10.1所示。表10.1不同粒度的锁可以锁定的资源,47,一.锁的概念-锁类型,SQLServer使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。常用的锁模式有：1.排它锁(ExclusiveLock):用于数据修改操作排它锁又称写锁，简称为X锁，其采用的原理是禁止并发操作。当事务T对某个数据对象R实现X封锁后，其他事务要等T解除X封锁以后，才能对R进行封锁。这就保证了其他事务在T释放R上的锁之前，不能再对R进行操作。,48,一.锁的概念-锁类型,2.共享锁(ShareLock)：用于只读取数据的操作共享锁又称读锁，,简称为S锁，其采用的原理是允许其他用户对同一数据对象进行查询，但不能对该数据对象进行修改。当事务T对某个数据对象R实现S封锁后，其他事务只能对R加S锁，而不能加X锁，直到T释放R上的S锁。这就保证了其他事务在T释放R上的S锁之前，只能读取R，而不能再对R作任何修改。3修改(Update)锁：用于可更新的资源中，防止多个会话在读取、锁定及随后可能进行的资源更新时发生常见形式的死锁。,49,一.锁的概念-两段锁协议,在运用X锁和S锁这两种基本封锁对数据对象加锁时，还需要约定一些规则，如何时申请X锁或S锁，持锁时间和何时释放等。我们称这些规则为封锁协议（LockingProtocol）。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。两段封锁协议是最常用的一种封锁协议，理论上已经证明使用两段封锁协议产生的是可串行化调度(一个事务的运行次序在并行调度执行的结果等价于某一串行调度执行的结果，则称这种调度是可串行化的调度)。两段锁协议是指每个事务的执行可以分为两个阶段：加锁阶段（生长阶段）和解锁阶段（衰退阶段）。,50,一.锁的概念-两段锁协议,加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。,51,一.锁的概念-两段锁协议,两段封锁法可以这样来实现：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。,52,二.查看锁的信息,1.使用企业管理器:管理-当前活动-锁/进程ID或锁/对象2.用系统存储过程sp_locksp_lockspid1=spid1,spid2=spid2参数说明:spid1=spid1:指定进程的标识号,该标识号存储在master.dbo.sysprocesses中.如过不指定,则显示所有锁的信息.,53,三.死锁,封锁技术的引入能有效解决并发用户的数据一致性问题，但因此可能会引起进程间的死锁问题。（1）引起死锁的主要原因是:两个进程已各自锁住一个页，但又要求访问被对方锁住的页。更一般的情况是，一个事务独占了其他事务正在申请的资源，且若干个这样的事务形成一个等待圈。,54,三.死锁,（2）死锁的避免死锁一旦发生，系统效率将会大大下降，因而要尽量避免死锁的发生。同操作系统避免死锁的方法类似，在数据库环境下，常用的方法有：（1）要求每个事务一次就将要使用的数据全部加锁，否则就不能继续执行。（2）预先规定一个封锁顺序，所有事务都按这个顺序实行封锁，这样也不会发生死锁。（3）允许死锁发生，系统采用某些方式诊断当前系统中是否有死锁发生。SQLServer能自动发现并解除死锁。当发现死锁时，它会选择其进程累计的CPU时间最少者所对应的用户作为“牺牲者”(令其夭折)，以让其他进程能继续执行。然后将被中断的事务回滚,同时SQLServer发送错误号1205(即error=1205)给牺牲者。,55,10.4游标,一.游标的定义及其优点二.使用游标三.游标示例,56,一.游标的定义及其优点,通常情况下，关系数据库中的操作总会对整个记录集产生影响，而在实际应用中，应用程序有时只需要每次处理一条或一部分记录。在这种情况下，就需要使用游标在服务器内部处理结果集.游标可视为一种特殊的指针，它与某个查询结果相联系,可以指向结果集的任意位置,以便对指定位置的数据进行处理.使用游标可以在查询数据的同时对数据进行处理.游标的优点:p253,57,二.使用游标,使用游标的步骤(1)声明游标(2)打开游标(3)从游标的结果集中读取数据(4)对游标中的数据逐行操作(5)关闭游标(6)释放游标,58,二.使用游标-声明游标,游标与局部变量一样，也要先声明后使用。SQLServer中，使用游标的第一步是声明游标，声明的内容主要包括游标名称、数据来源、选择条件、游标属性。命令格式：DECLAREcursor_namescrollCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_name,.n命令说明：（1）scroll:说明所声明的游标可以前滚,后滚,可使用所用的提取选项(first,last,prior,next).如果省略scroll,则只能使用next提取选项,59,二.使用游标-声明游标,（2）select_statement表示一个SELECT查询语句，它查询的表称为游标的基表，查询的结果称为游标结果集。在游标声明的select_statement内必须有from子句,且不允许使用关键字COMPUTE、COMPUTEBY和INTO。（3）READONLY表示所声明的游标为只读的，不得用于DELETE、UPDATE语句。不选此项时，则为可修改游标。（4）UPDATE表示所声明的游标为可修改游标，后面的OFcolumn_name,.n指定允许修改的列，无OFcolumn_name,.n选项时表示允许修改所有列。,60,二.使用游标-打开游标,打开游标就是执行相应的SELECT语句的查询操作，检索多行数据，把所有满足查询条件的数据行组成一个集合，称为游标活动集（ActiveSet）或结果集，并把游标指针置于其首端。命令格式：OPENcursor_name注意:p254(1),(2),61,二.使用游标-推进游标指针并取当前记录,使用游标是指将游标指向（或称“定位于”）游标结果集的某一行，对该行的数据进行提取或修改。游标指向的那一行称为游标的当前行。当游标声明被打开后,游标指针位于结果集的第一行之前.使用游标提取数据只能一次提取结果集的单个行，并可以将游标所指行（当前行）的各列数据转移到指定的变量中。这样，其他T-SQL语句就可以引用这些变量来访问已提取的数据值。使用游标语法如下:,62,二.使用游标-推进游标指针并取当前记录,FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROM游标名称INTO变量名,n命令说明：（1）NEXT：读取当前游标指针所指向行的下一行数据，并将游标指向该下一行。如果FETCHNEXT是对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。,63,二.使用游标-推进游标指针并取当前记录,（2）PRIOR：读取当前游标指针所指向行的上一行数据，并将游标指向该上一行。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标仍指向第一行。（3）FIRST：读取游标结果集的第一行数据并将其作为当前行。（4）LAST：读取游标结果集的最后一行数据并将其作为当前行。,64,二.使用游标-推进游标指针并取当前记录,（5）ABSOLUTEn|nvar：表示读取游标结果集的第n行数据。如果n或nvar为正数，则返回从游标结果集第一行开始计算的正数第n行数据，并将返回的行变成新的当前行。如果n或nvar为负数，则返回从游标结果集第末行开始计算的倒数第n行数据，并将返回的行变成新的当前行。如果n或nvar为0，则没有行返回。n必须为整型常量且nvar必须为smallint、tinyint或int类型数据。,65,二.使用游标-推进游标指针并取当前记录,（6）RELATIVEn|nvar：如果n或nvar为正数，读取当前行之后的第n行并将返回的行变成新的当前行。如果n或nvar为负数，读取当前行之前的第n行并将返回的行变成新的当前行。如果n或nvar为0，读取当前行。如果对游标的第一次提取操作时将FETCHRELATIVE的n或nvar指定为负数或0，则没有行返回。n必须为整型常量，局部变量nvar必须为smallint、tinyint或int型。（7）INTOvariable_name,.n：表示允许将提取操作的列数据存放到局部变量列表中。,66,二.使用游标-推进游标指针并取当前记录,注意:有两个全局变量可以提供关于游标活动的信息1.F