SQLServer的程序设计.ppt

《SQLServer的程序设计.ppt》由会员分享，可在线阅读，更多相关《SQLServer的程序设计.ppt（40页珍藏版）》请在三一办公上搜索。

1、第11章 SQL Server的程序设计,教学提示：SQL Server程序设计是读者学习SQL Server数据库的一个重要环节，它对以后程序开发有着直接的决定因素。本章从最简单的Transcat-SQL的语法入手由浅入深地讲解函数、事务、锁和游标等，它们是灵活应用Transcat-SQL语句的关键，在程序设计和开发中起着重要的作用。教学目标：通过本章的学习，读者应该掌握编程的基础知识、基本语句；理解事务、锁和游标等基本原理；能对事务、锁和游标进行简单地运行。,第11章 SQL Server的程序设计,Transact-SQL的语法规则Transact-SQL的语法规则见表11-1,编程基础

2、知识,第11章 SQL Server的程序设计,SQL Server的变量在Transact-SQL语句中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。1局部变量局部变量是一个能够有特定数据类型的对象，它的作用范围仅局限于程序内部，局部变量可以作为计数器来计算循环执行的次数，或是控制循环执行的次数。局部变量被引用时，要在其名称前加上标识符，而且必须先用DECLARE命令定义后才可以使用，其说明形式如下：DECLARE variable_name datatype，variable_name datatype其语法如下：SELECT variable_name=v

3、alueSET variable_name=value【例11.1】声明一个长度为6个字符的变量编号，并赋值。DECLARE 编号CHAR(6)SELECT 编号=010101,编程基础知识,第11章 SQL Server的程序设计,编程基础知识 2全局变量全局变量是SQL Server系统内部使用的变量，其作用范围并不局限于某一程序，任何程序均可调用。全局变量通常存储一些SQL Server 2005的配置设定值和效能统计数据。全局变量时应注意以下几点：(1)全局变量不是由用户的程序定义的，它们是在服务器级定义的。(2)用户只能使用预先定义的全局变量。(3)引用全局变量时，必须以标识符开头。

4、(4)局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。3.注释符在Transact-SQL中可使用两类注释符：(1)ANSI标准的注释符-用于单行注释。(2)与C语言相同的程序注释符号，即/*/，/*用于注释文字的开始，*/用于注释文字的结尾，可在程序中标识多行文字的注释。,第11章 SQL Server的程序设计,算符主要分算术运算符、赋值运算符、位运算符、关系运算符、逻辑运算符以及字符串连接运算符，见表11-2,SQL Server中的运算符,第11章 SQL Server的程序设计,SQL Server 2005提供的函数分为两大类内部函数和用户自定义函数。

5、内部函数内部函数的作用是用来帮助用户获得系统的有关信息、执行有关计算、实现数据转换以及统计功能等。SQL所提供的内部函数又分为系统函数、日期函数、字符串函数、数学函数、集合函数等几种,常 用 函 数,第11章 SQL Server的程序设计,系统函数,表11-3列出一些常用的系统函数及其功能。,第11章 SQL Server的程序设计,【例11.2】查询主机名称。在SQL Server Management Studio查询窗口中运行以下代码：SELECT HOST_NAME()GO【例11.3】返回book1表中书名字段的长度。在SQL Server Management Studio查询窗

6、口中运行以下代码：USE bookGOSELECT COL_LENGTH(book1，书名)AS 书名长度FROM book1GO,系统函数,第11章 SQL Server的程序设计,日期函数用来显示日期和时间的信息。它们处理datatime和 smalldatatime的值，并对其进行算术运算。表11-4列出了所有的日期函数。,日期函数,第11章 SQL Server的程序设计,【例11.4】查询服务器当前的系统日期和时间。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOSELECT 当前日期=GETDATE()，月=MONTH(GETD

7、ATE()，日=DAY(GETDATE()，年=YEAR(GETDATE();GO,第11章 SQL Server的程序设计,字符串函数用于对字符串进行连接、截取等操作，表11-6列出了常用的字符串函数。,字符串函数,第11章 SQL Server的程序设计,【例11.5】STUFF函数的练习，从原始字符串中删除4个字符，然后再插入另一个字符串。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOPrint STUFF(ccccadrkuekgoj,4,3,AAA)GO,字符串函数,第11章 SQL Server的程序设计,数学函数用来对数值型

8、数据进行数学运算。表11-7列出了常用的数学函数,数学函数,第11章 SQL Server的程序设计,集合函数也称为统计函数，它对一组值进行计算并返回一个数值。聚合函数经常与SELECT语句的子句一起使用，表11-8列出了常用的聚合函数及其功能。,集合函数,第11章 SQL Server的程序设计,用户可以使用CREATE FUNCTION语句编写自己的函数，以满足特殊需要。可用用户自定义函数来传递0个或多个参数，并返回一个简单的数值 分为3种，分别是标量用户自定义函数、直接表值用户定义函数和多语句表值用户自定义函数 1创建标量用户自定义函数标量用户自定义函数返回一个简单的数值，如int、ch

9、ar、decimal等其语法格式如下：CREATE FUNCTIONowner_name.function_name(parameter_name AS scalar_parameter_data_type=default，n)RETURNS scalar_return_data_typeWITH，nASEEGIN Function_body RETURN scalar_expressionEND,用户自定义函数,第11章 SQL Server的程序设计,【例11.7】创建一个自定义函数，返回特定出版社所出书的平均定价。在SQL Server Management Studio查询窗口中运行以

10、下代码：USE bookGOCREATE FUNCTION Avgdingji_book1(出版社 nchar(20)RETURNS FLOATASBEGINDECLARE 平均定价 FLOATSET 平均定价=(SELECT AVG(定价)FROM book1WHERE 出版社=出版社)RETURN 平均定价END使用下面语句对刚创建的函数进行操作。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOSELECT dbo.Avgdingji_book1(中国长安)AS 平均成绩GO,用户自定义函数,第11章 SQL Server的程序设计,表

11、值函数返回一个Table型数据，对直接表值用户定义函数而言，返回的结果只是一系列表值，没有明确的函数体。其语法格式为：CREATE FUNCTION owner_name.function_name(parameter_name AS scalar_parameter_data_type=default，.n)RETURNS TABLEWITH，.nASRETURN(select-statement)【例11.8】创建一个函数返回同一个出版社所出书的部分信息。在SQL Server ManagementStudio查询窗口中运行以下代码：USE bookGOCREATE FUNCTION 书的

12、信息(出版社nchar(20)RETURNS TABLEASRETURN(SELECT 书名，定价，出版社FROM book1WHERE 出版社=出版社)使用下面语句对刚创建的函数进行操作：在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOSELECT*FROM dbo.书的信息(海南)GO,创建直接表值用户定义函数,第11章 SQL Server的程序设计,多语句表值用户自定义函数是以BEGIN语句开始，END语句结束的函数体，这些语句可将行插入返回的表中。其语法格式为：CREATE FUNCTION owner_name.function

13、_name(parameter_name AS scalar_parameter_data_type=default，.n)RETURNS return_variable TABLE WITH，.nASBEGINFunction_bodyRRTURNEND其中，return_variable：指一个TABLE类型的变量用于存储和累积返回的表中的数据行。其余参数与标量型用户自定义函数相同。,创建多语句表值用户自定义函数,第11章 SQL Server的程序设计,【例11.9】创建一个函数返回定价高于一定价格的书的信息。在SQL Server ManagementStudio查询窗口中运行以下代码

14、：USE bookGOCREATE FUNCTION money_higher(highermoney money)RETURNS money_higher TABLE(编号nvarchar(255)，书名nvarchar(255)，定价money，出版社nvarchar(255)ASBEGININSERT money_higherSELECT bookin.编号，书名，定价，出版社FROM bookin，book1WHERE bookin.编号=book1.编号and 定价 highermoneyRETURNENDGO使用下面语句对刚创建的函数进行操作：在SQL Server Managem

15、ent Studio查询窗口中运行以下代码：USE bookGOSELECT*FROM dbo.money_higher(1000)GO,创建多语句表值用户自定义函数,第11章 SQL Server的程序设计,批处理是包含一个或多个Transact-SQL语句的组，它将一次性地发送到SQL Server中执行，应用程序将这些语句作为一个单元一次性地提交给SQL Server，并由SQL Server编译成一个执行计划，然后作为一个整体来执行【例11.10】批处理示例分析。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOCREATE VIEW

16、 abcASSELECT*FROM book1GOSELECT*FROM book2GO在SQL Server Management Studio查询窗口中运行以下代码，观察系统给出的信息。DECLARE AA INTGOSELECT AA=44GO,批 处 理,第11章 SQL Server的程序设计,1IF.ELSE语句利用IF.ELSE语句能够对一个条件进行测试，并根据测试的结果来执行相应的操作。ELSE语句是可选的。其语法为：IF 逻辑表达式语句块1ELSE语句块2【例11.11】查询是否有书的定价高于8000元的书，如果有则输出该书的信息包括作者姓名，如果没有就输出“不存在高于800

17、0元的书”。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGODECLARE 定价money，message varchar(250)SET 定价=8000IF EXISTS(SELECT*FROM book1WHERE 定价 定价)BEGINSELECT DISTINCT book1.编号,书名,定价,作者姓名FROM book1，teacherWHERE book1.编号=teacher.编号and 定价 定价ENDELSE SET message=不存在高于8000元的书PRINT message,流 控 语 句,第11章 SQL Se

18、rver的程序设计,2BEGINENDBEGIN和END用来定义语句块，必须成对出现。它将多个SQL语句括起来，相当于一个单一语句。常用于下列情况：(1)WHILE循环需要包含多条语句。(2)CASE函数的元素需要包含多条语句。(3)IF.ELSE语句中需要包含多条语句。BEGIN.EDN语句的语法格式如下：EEGINSQL语句1SQL语句2.ENDBEGIN.END语句块通常与其他流控语句综合使用，BEGIN和END分别表示语句块的开始和结束，它们必须成对使用,流 控 语 句,第11章 SQL Server的程序设计,3WHILE、BREAK和CONTINUEWHILE语句用来实现循环结构，

19、其语法为：WHILE逻辑表达式语句块功能：当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。BREAK语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。例如，WHILE逻辑表达式1BENIN语句1IF WHILE逻辑表达式2CONTINUE语句2END,流 控 语 句,第11章 SQL Server的程序设计,4CASE表达式CASE表达式用于多条件分支选择CASE表达式有简单的CASE表达式和搜索型CASE表达式两种。1)简单的CASE表达式CASE 表达式WHEN 表达式 THEN 表达式.ELSE 表达式END2)搜索型CASE表达式CASEWHE

20、N 逻辑表达式 THEN.ELSE表达式END,流 控 语 句,第11章 SQL Server的程序设计,【例11.12】从book1表中，选取书名、出版社，如果出版社为中国长安则输出“中国最有实力的出版社之一”，如果是海南出版社，则输出“海外影响力最强的出版社之一”。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOSELECT 书名，出版社=CASE 出版社WHEN 中国长安 THEN 中国最有实力的出版社之一WHEN 海南 THEN 海外影响力最强的出版社之一ENDFROM book1【例11.13】从book1表中查询所有书的定价情况

21、，凡定价为空的输出“未录入定价”，小于30元输出“价格适中”，30100元输出“价格偏高”，100300元输出“价格高”，300以上的输出“价格特别高”。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOSELECT 书名，出版社，定价情况=CASE 定价WHEN 定价IS NULL THEN 未录入定价WHEN 定价0 and 定价=30 and 定价=100 and 定价=300 THEN 价格特别高ENDFROM book1,流 控 语 句,第11章 SQL Server的程序设计,5RETURNRETURN语句实现无条件退出批命令、存

22、储过程或触发器。RETURN整型表达式6WAITFOR其语法为：WAITFORDELAY TIME|TIME TIME【例11.14】等待2小时2分零2秒后才执行SELECT语句。WAITFOR DELAY 02:02:02SELECT*FROM book17GOTO【例11.15】求 1+2+3+100的总和。在SQL Server Management Studio查询窗口中运行以下代码：DECLARE sum SMALLINT，i SMALLINTSET i=1Set sum=0BEG:IF(i=100)BEGINSET sum=sum+iSET i=i+1GOTO BEGENDPRIN

23、T sum,流 控 语 句,第11章 SQL Server的程序设计,事务的基本概念事务是作为单个逻辑工作单元执行的一系列操作.这一系列的操作或者都被执行或者都不被执行 两个银行账号之间转账的例子 事务作为一个逻辑工作单元有4个属性，称为ACID(原子性、一致性、隔离性和持久性)属性。(1)原子性：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。(2)一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。(3)隔离性：由并发事务所作的修改必须与任何其他并发

24、事务所作的修改隔离，保证事务查看数据时数据所处的状态，只能是另一并发事务修改它之前的状态或者是另一事务修改它之后的状态，而不能中间状态的数据。(4)持久性：事务完成之后对系统的影响是永久性的。,事 务 处 理,第11章 SQL Server的程序设计,事务组织结构的一般形式如下。(1)定义一个事务的开始：BEGIN TRANSACTION。(2)提交一个事务所：COMMIT TRANSACTION。(3)回滚事务：ROLLBACK TRANSACTION。1BEGIN TRANSACTION功能：标记一个显式本地事务的起始点，语法如下：BEGIN TRANSACTION transaction

25、_name2COMMIT TRANSACTION功能:标志一个成功的隐式事务或用户定义事务的结束，语法如下：COMMIT TRANSACTION transaction_name3ROLLBACK TRANSACTION功能：将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点，语法如下：ROLLBACK TRANSACTION transaction_name,事务操作,第11章 SQL Server的程序设计,【例11.16】COMMIT TRANSACTION提交事务简单示例。定义一事务向book2表中插入3条记录，并提交完成。在SQL Server Management Studi

26、o查询窗口中运行以下代码：USE bookGO-开始事务BEGIN TRANSACTIONINSERT book2(编号，ISBN号，书名，出版社，出版日期，定价)VALUES(200701，200777，SQL2005，高等教育，2007-11-24，30)INSERT book2(编号，ISBN号，书名，出版社，出版日期，定价)VALUES(200701，200778，周末，高等教育，2007-11-25，40)INSERT book2(编号，ISBN号，书名，出版社，出版日期，定价)VALUES(200701，200779，今日一线，高等教育，2007-11-26，50)-提交事务COM

27、MIT TRANSACTION验证结果在SQL Server ManagementStudio查询窗口中运行以下代码：USE bookSELECT*FROM book2WHERE 编号=200701,事务操作,第11章 SQL Server的程序设计,【例11.17】使用ROLLBACK TRANSACTION回滚事务简单示例，定义一事务向book2表中插入3条记录，并回滚撤销。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGO-开始事务BEGIN TRANSACTIONINSERT book2(编号,ISBN号,书名,出版社,出版日期,定价

28、)VALUES(200701,200777,SQL2005,高等教育,2007-11-24,30)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200778,周末,高等教育,2007-11-25,40)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200779,今日一线,高等教育,2007-11-26,50)-回滚事务ROLLBACK TRANSACTION验证结果在SQL Server Management Studio查询窗口中运行以下代码：USE bookSELECT*FROM

29、 book2WHERE 编号=200701,事务操作,第11章 SQL Server的程序设计,【例11.18】定义一事务向book2表中插入多条记录，并检验若一种编号的书超过4本，则回滚事务，即插入无效，否则成功提交。在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOBEGIN TRANSACTIONINSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200777,SQL2005,高等教育,2007-11-24,30)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)

30、VALUES(200701,200778,周末,高等教育,2007-11-25,40)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200779,今日一线,高等教育,2007-11-26,50)DECLARE countnum INTSET countnum=(SELECT COUNT(*)FROM book2 WHERE 编号=200701)IF countnum 4BEGIN ROLLBACK TRANSACTIONPRINT 此编号的书已超过9本，不能再次插入ENDELSEBEGINCOMMIT TRANSACTIONPRINT 此

31、编号的书还未超过9本，你的操作插入成功END验证结果在SQL Server Management Studio查询窗口中运行以下代码：USE bookSELECT*FROM book2WHERE 编号=200701,事务操作,第11章 SQL Server的程序设计,在SQL Server Management Studio查询窗口中运行以下代码：USE bookGOBEGIN TRANSACTIONINSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200777,SQL2005,高等教育,2007-11-24,30)INSERT book2(

32、编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200778,周末,高等教育,2007-11-25,40)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200779,今日一线,高等教育,2007-11-26,50)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200780,大家谈,高等教育,2007-11-27,50)INSERT book2(编号,ISBN号,书名,出版社,出版日期,定价)VALUES(200701,200781,高等数学,高等教育,20

33、07-11-28,50)DECLARE countnum INTSET countnum=(SELECT COUNT(*)FROM book2 WHERE 编号=200701)IF countnum 4BEGIN ROLLBACK TRANSACTIONPRINT 此编号的书最多只能录入4本,已超过最大数,你不能成功录入ENDELSEBEGINCOMMIT TRANSACTIONPRINT 此编号的书还未超过4本，你的操作录入成功END,事务操作,第11章 SQL Server的程序设计,开发问题如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题，这些问

34、题包括以下几种情况。1)丢失或覆盖更新2)未确认的相关性(脏读)3)不一致的分析(不能重复读)4)幻读,锁,第11章 SQL Server的程序设计,SQL Server 2005自动将资源锁定在适合任务的级别。锁定在较小的粒度(例如行)可以增加并发但需要较大的开销，因为，如果你锁定许多行，则需要控制更多的锁。锁定在较大的粒度(例如表)需要维护的锁较少，要求的开销较低。但，因为锁定整个表会限制其他事务对表中任意部分进行访问，所以，就并发而言是相当昂贵的。几种常用的锁模式。1共享锁用于不更改或不更新数据的操作(只读操作)，如SELECT语句共享锁允许并发事务读取(SELECT)一个资源2更新锁用

35、于可更新的资源中，防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。3排他锁用于数据修改的操作有很多，例如，INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新，可以使用排他锁,SQL Server中的锁定,第11章 SQL Server的程序设计,【例11.19】使用对象资源管理器浏览系统中的锁。【例11.20】使用sp_lock系统存储过程显示SQL Server中当前持有的所有锁的信息。在SQL Server Management Studio查询窗口中运行以下代码：USE masterGOEXEC sp_lockGO,SQL Server

36、中的锁定,第11章 SQL Server的程序设计,封锁机制的引入能解决并发用户的数据不一致性问题，但也会引起事务间的死锁问题。死锁的主要原因是由于两个或更多的事务竞争资源而直接或间接地相互等待而造成的要求每个事务一次就将要使用的数据全部加锁，否则就不能继续执行。(1)预先规定一个顺序，所有事务都按这个顺序实行加锁，这样也不会发生死锁。例如，通过SET DEADLOCK PRIORITY语句设置会话的优先级，如果设置为LOW说明该会话的优先级较低，再出死锁时，首先中断该会话的事务。(2)允许死锁发生，系统采用某些方式诊断当前系统中是否有死锁发生,死锁,第11章 SQL Server的程序设计,

37、游标(Cursor)是一种数据访问机制，它允许用户访问单独的数据行，而并非对整个行集合进行操作(通过使用SELECT，UPDATE 或者DELETE 语句进行)游标的定义游标是一个与Transact-SQL的SELECT 语句相关联的符号名，它使用户可逐行访问由SQL Server返回的结果集。游标包括以下两个部分。(1)游标结果集(Cursor Result Set)：由定义该游标的SELECT语句返回的行集合。(2)游标位置(Cursor Position)：指向这个行集合某一行的当前指针,游 标,第11章 SQL Server的程序设计,1)声明游标声明游标的语法形式为：DECLAREc

38、ursor_name CURSORFOR select_statementFORREAD ONLY|UPDATE OF column_name_list，2)打开游标打开游标的语法形式：OPEN crusor_name3)使用FETCH语句，从结果集中检索单独的行读取游标中的数据的语法形式：FETCHNEXT|FRIOR|FIRST|LASTFROMcursor_nameINTO fetch_target-list4)使用游标修改数据5)关闭游标关闭游标的语法形式：CLOSE cursor_name6)释放游标释放游标的语法形式：DWALLOCATE CURSOR cursor_name,游标

39、的使用,第11章 SQL Server的程序设计,【例11.21】基本游标使用示例。声明一个名为Crsbook1的游标，该游标从book1表中检索所有记录。通过本例可以学习从声明游标到最后释放游标的基本过程。在SQL Server Management Studio查询窗口中运行以下代码：使用DECLARE CURSOR语句声明游标。USE bookGODECLARE Crsbook1 CURSORFOR SELECT*FROM book1 使用OPEN语句打开游标：OPEN CrsBook1使用FETCH语句，从游标中检索行：FETCH NEXT FROM CrsBook1,游标的使用,第11章 SQL Server的程序设计,将当前记录的出版社更改为中国商业行业：UPDATE book1 SET 出版社=中国商业行业 WHERE CURRENT OF CrsBook1如果要删除当前记录，可以使用如下命令：DELETTE FROM book1 WHERE CURRENT OF CrsBook1使用CLOSE语句关闭游标：CLOSE CrsBook1使用DEALLOCATE语句释放游标：DEALLOCATE CrsBook1,游标的使用,