第5章 SQL程序设计与开发

数据库原理与设计第5章SQL程序设计与开发批处理与脚本SQL程序设计基础流程控制语句游标SQL程序的调试与错误处理SQL程序实例第6章SQL程序设计与开发在数据库应用的客户端适当使用批处理具有以下优点：减少数据库服务器与客户端之间的数据传输次数，消除过多的网络流量。减少数据库服务器与客户端之间传输的数据量。缩短完成逻辑任务或事务所需的时间。较短的事务不会长期占有数据库资源，能尽快释放锁，有效避免死锁。增加逻辑任务处理的模块化，提高代码的可复用度，减少维护工作量。批处理与脚本将一个批发送给SQLServer2000

服务器用户执行过程语句引擎将SQL语句发送给SQL语句执行器SQLServer服务器PL/SQL引擎SQL

语句

执行器过程语句

执行器执行SQL语句将结果发送给用户批处理的执行只要批处理中的语句没有任何语法错误，就可以经过编译建立执行计划。

（1）不能建立执行计划的批处理在下面的示例中，批处理中存在语法错误，不能建立执行计划，其中Pubs是SQLServer自带的测试数据库。

USEpubsCREATETABLETestBatch(ColaINTPRIMARYKEY,ColbCHAR(3))INSERTINTOTestBatchVALUES(1,'aaa')INSERTINTOTestBatchVALUES(2,'bbb')INSERTINTOTestBatchVALUSE(3,'ccc')/*语法错误，VALUES拼写错误*/SELECT*FROMTestBatchGO批处理的执行下面的示例没有语法错误，可以建立执行计划。在执行过程中，由于第3个INSERT语句产生主键重复的错误，因此该INSERT语句与之后的SELECT语句不能被执行。由于前两个INSERT语句成功地执行并且提交，因此它们在发生运行时错误之后被保留下来。

USEpubsCREATETABLETestBatch(ColaINTPRIMARYKEY,ColbCHAR(3))INSERTINTOTestBatchVALUES(1,'aaa')INSERTINTOTestBatchVALUES(2,'bbb')INSERTINTOTestBatchVALUES(1,'ccc')/*主键重复*/SELECT*FROMTestBatch/*返回行1和2的记录*/GO编写批处理的规则①不能在同一个批处理中更改表，然后引用新列。②不能在删除一个对象之后，立即在同一个批处理中引用该对象。③不能在定义一个CHECK约束后，立即在同一个批处理中使用该约束。④CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句，在一个批处理中只能提交一个。⑤如果批处理中的第一句是执行某些存储过程的EXECUTE语句，则EXECUTE关键字可以省略不写。如果EXECUTE语句不是批处理中的第一条语句，则需要EXECUTE关键字。脚本

Transact-SQL语句的集合称为脚本。

Transact-SQL脚本存储为文件，带有sql扩展名。把编写好的SQL语句（例如，创建数据库对象、调试通过的SQL语句集合）保存起来，以便下一次执行同样（或类似）操作时，调用这些语句集合。这样可以省去重新编写调试SQL语句的麻烦，提高工作效率。脚本文件可以调入查询分析器查看内容或再次被执行，也可以通过记事本等浏览器查看内容。SQL程序设计基础

1．SQL程序基本成分

2．SQL程序编写规范变量Transact-SQL中的变量分为局部变量和全局变量。局部变量的声明格式为：

DECLARE@local_variable

data_type[,@local_variable

data_type…..]如：

DECLARE@empidvarINTSET@empidvar=1234SELECT*FROMEmployeesWHEREEmployeeid=@empidvarDECLARE@pub_idCHAR(4),@hire_dateDATETIMESET@pub_id='0877'SET@hire_date='1/01/93'SELECT@pub_id='0877',@hire_date='1/01/93'/*使用SELECT赋值也可以*/SELECTFname,LnameFROMEmployeeWHEREPub_id=@pub_idANDHire_date>=@hire_date

运算符SQLServer提供赋值运算符、算术运算、逻辑运算、位运算、比较运算、字符串连接运算符等。赋值运算符“=”用于将表达式的值赋给某个变量。算术运算符在两个表达式上执行数学运算，包括加法（+）、减法（−）、乘法（*）、除法（/）、取模（%）等运算，加减运算也可用于datetime和smalldatetime日期类型。位运算符可以在两个表达式之间执行位操作，包括按位与（&）、按位或（|）、按位异或（^）。表达式的数据类型可以是整型或与整型兼容的数据类型。比较运算符用于测试两个表达式之间值的关系，包括=、>、<、>=、<=、<>、！<、！>，比较运算的结果是布尔类型。逻辑运算符用于对某个条件进行测试。字符串连接运算符（+）可以进行字符串连接，例如，‘ABC’+‘DEF’的运算结果为‘ABCDEF’。当一个复杂表达式包含若干运算符时，运算符按照优先级顺序执行，先执行优先级高的运算符，后执行优先级低的运算符。函数SQLServer在标准SQL的基础上，提供了丰富的函数，包括日期函数、字符串函数、数学函数、聚合函数、系统函数等。例如可以使用下面的语句得到当前日期、并进行日期处理。

SELECTcurr_date=GETDATE( ),cur_mon=MONTH(GETDATE( )),cur_day=DAY(getdate( )),pre_date=DATEADD(day,-1,GETDATE( )),next_date=DATEADD(day,1,GETDATE( ))SELECTLTRIM('fivespacesareatthebeginningofthisstring.')--去掉左面空格SELECTpub_id,SUBSTRING(pr_info,1,10)ASpr_infoFROMpub_infoWHEREpub_id='1756'SELECTSTUFF('abcdef',2,3,'ijklmn')自己定义函数（1）

CREATEFUNCTIONCubicVolume(@CubeLengthNUMERIC(4,1),@CubeWidthNUMERIC(4,1),@CubeHeightNUMERIC(4,1))/*输入参数长、宽、高及其数据类型*/RETURNSNUMERIC(12,3)/*函数返回参数类型*/ASBEGINRETURN(@CubeLength*@CubeWidth*@CubeHeight)/*函数返回值*/END自己定义函数（2）

CREATEFUNCTIONCUBICVOLUME_2(@CubelengthNUMERIC(4,1),@CubewidthNUMERIC(4,1),@CubeheightNUMERIC(4,1))RETURNSNUMERIC(12,3)ASBEGINDECLARE@VolumeNUMERIC(12,3);/*定义变量*/IF((@CubeLength>0)AND(@CubeWidth>0)AND(@CubeHeight>0))SELECT@Volume=@CubeLength*@CubeWidth*@CubeHeight

ELSESELECT@Volume=-1;/*条件分支流程*/RETURN(@Volume)ENDSQL程序编写规范对变量和数据库对象等标识符采用有意义的命名编写代码时养成合理的大小写习惯对存储过程、游标等数据库对象命名时，采用适当的前缀和后缀代码采用缩进方式在程序中增加适当的注释流程控制语句关

键

字描

述BEGIN...END定义语句块BREAK退出最内层的WHILE循环CONTINUE重新开始WHILE循环GOTOlabel从label所定义的label之后的语句处继续进行处理IF...ELSE定义条件以及当一个条件为FALSE时的操作RETURN无条件退出WAITFOR为语句的执行设置延迟WHILE当特定条件为TRUE时重复语句语句块：BEGIN…END

USEpubsIF(SELECTCOUNT(*)FROMdeleted,salesWHEREsales.title_id=deleted.title_id)>0BEGINROLLBACKTRANSACTIONPRINT'Youcan'tdeleteatitlewithsales.'ENDBEGIN…END关键字之间封装了一系列的SQL语句，形成一个语句块，代表一组一起执行的SQL语句。BEGIN…END的语法结构如下：

BEGINSQL语句1SQL语句2……END条件执行：IF...ELSE语句IF...ELSE语句的语法结构如下：

IF布尔表达式

{SQL语句|SQL语句块}[ELSE

{SQL语句|SQL语句块}]IF...ELSE语句允许嵌套，可以在其他IF之后或在ELSE下面，嵌套另一个IF语句，嵌套层数没有限制。

条件执行：IF...ELSE语句（2）

USEpubsIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')<$15BEGINPRINT'thefollowingtitlesareexcellentmod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'

ENDELSEPRINT'Averagetitlepriceismorethan$15.'条件执行：IF...ELSE语句（3）USEpubsIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')<$15BEGINPRINT'Thefollowingtitlesareexcellentmod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'ENDELSEIF(SELECTAVG(price)FROMtitlesWHEREtype='mod_cook')>$15BEGINPRINT'Thefollowingtitlesareexpensivemod_cookbooks:'PRINT''SELECTSUBSTRING(title,1,35)ASTitleFROMtitlesWHEREtype='mod_cook'END多分支CASE表达式简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索函数计算一组布尔表达式以确定结果。简单CASE表达式的语法结构如下：

CASE表达式

WHEN表达式THEN表达式

......

[ELSE表达式]

ENDCASE搜索函数的语法结构如下：

CASE

WHEN布尔表达式THEN表达式

......

[ELSE表达式]

END多分支CASE表达式

USEpubsGOSELECTCategory=CASEtypeWHEN'popular_comp'THEN'PopularComputing'WHEN'mod_cook'THEN'ModernCooking'WHEN'business'THEN'Business'WHEN'psychology'THEN'Psychology'WHEN'trad_cook'THEN'TraditionalCooking'ELSE'Notyetcategorized'END,CAST(titleASvarchar(25))AS'ShortenedTitle',priceASPriceFROMtitlesWHEREpriceISNOTNULLORDERBYtype,priceCOMPUTEAVG(price)BYtype--CAST函数的功能是将某种数据类型的表达式显式转换为另一种数据类型循环：WHILE语句WHILE语句的语法结构如下：

WHILE布尔表达式

{SQL语句|SQL语句块}

[BREAK]

{SQL语句|SQL语句块}

[CONTINUE]WHILE语句也允许嵌套。BREAK语句使程序从最内层的WHILE循环中退出。CONTINUE语句使WHILE循环重新开始执行，忽略CONTINUE关键字后的语句。循环：WHILE语句（2）USEpubsGOWHILE(SELECTAVG(price)FROMtitles)<$30BEGINUPDATEtitlesSETprice=price*2SELECTMAX(price)FROMtitlesIF(SELECTMAX(price)FROMtitles)>$50BREAKELSECONTINUEENDPRINT'Toomuchforthemarkettobear'循环：WHILE语句（3）

DECLARE@countersmallintSET@counter=1WHILE@counter<5BEGINSELECTRAND(@counter)Random_NumberSETNOCOUNTONSET@counter=@counter+1SETNOCOUNTOFFENDGO非条件执行：GOTO语句GOTO语句的语法结构如下：

标签: --定义标签

GOTO标签 --改变执行

USEpubsGODECLARE@tablename

sysnameSET@tablename=N'authors'

table_loop: --定义标签

IF(@@FETCH_STATUS<>-2)BEGINSELECT@tablename=RTRIM(UPPER(@tablename))EXEC("SELECT"""+@tablename+"""=COUNT(*)FROM"+@tablename)PRINT""ENDFETCHNEXTFROMtnames_cursorINTO@tablenameIF(@@FETCH_STATUS<>-1)GOTOtable_loop--改变执行

GO调度执行：WAITFORBEGINWAITFORTIME'22:20'EXECUTEupdate_all_statsEND。语法结构如下：

WAITFOR{DELAY'时间'|TIME'时间'}其中DELAY指定等待的时间间隔，最长可达24小时；TIME指定等待到的时间点，即触发的具体时间；时间可以是datetime

数据类型，格式为hh:mm:ss，不指定日期。在晚上10:20执行存储过程update_all_stats。游标SELECT语句返回所有满足条件的完整记录集，在数据库应用程序中常常需要处理结果集的一行或多行。游标（CURSOR）是结果集的逻辑扩展，可以看作是指向结果集的一个指针，通过使用游标，应用程序可以逐行访问并处理结果集。使用游标时，应先声明，然后打开，接着使用；使用完后关闭、释放资源。游标声明游标：DECLARECURSOR语句打开游标：OPEN语句读取数据：FETCH语句关闭游标：CLOSE语句释放游标：DEALLOCATE语句游标使用实例

USEMSBEGINDECLARE@CnoVARCHAR(5)

--变量：课程编号

DECLARE@ClnameVARCHAR(30) --变量：班级名称

DECLARE@clnoVARCHAR(6) --变量：班级编号

DECLARE@avgscoreNUMERIC(10,2) --变量：平均成绩

DECLARE@CtermINT --变量：学期

DECLAREclass_cursorCURSORFORSELECTclname,clno,dbo.termConvert('2005-2006/2',clno)FROMclass --声明班级游标

/*其中termConvert函数是自定义函数，可以将如“2006-2007/2”的学期表述的字符串方式转换为如1、2、3等表述的数字方式。如2005年入学的同学的“2006-2007/2”学期是其在校的第4学期*/OPENclass_cursor --打开班级游标

FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@Cterm--读取游标数据

WHILE@@FETCH_STATUS=0--检测游标数据是否读取完，如果还有数据，继续循环

BEGINSET@avgscore=(SELECTISNULL(avg(score),0)FROMsca,studentb,classc,coursedWHEREa.sno=b.snoANDb.clno=c.clnoANDb.clno=@clnoANDo=oANDd.Cterm=@Cterm)

游标使用实例（2）IF@avgscore>0/*根据班级平均成绩是否为0判断，该班的成绩是否登记，如果为0，表明没有登记该班的在2005-2006/2学期的成绩*/BEGINPRINT'2005-2006/2'+'学期'+@CLname+'各门课总平均成绩为'+str(@avgscore,5,1)--每个学生的平均成绩和获得的学分

PRINT'该班每个学生的平均成绩如下：'SELECTe.sname,d.avgscore,totalCreditFROM(SELECTa.sno,AVG(score)avgscore,SUM(dbo.CreditConvert(score,CCredits))totalCreditFROMstudenta,scb,coursecWHEREa.sno=b.snoANDo=oANDc.cterm=@CtermGROUPBYa.sno)d,studente,classfWHEREe.sno=d.snoANDe.clno=f.clnoANDf.clno=@clnoENDELSEPRINT'2005-2006/2'+'学期'+@CLname+'成绩没有登记'FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@CtermENDCLOSEclass_cursorDEALLOCATEclass_cursor

ENDSQL程序的错误类型语法错误是指不符合Transact-SQL规范的错误，这类错误会导致SQL语句不能编译。不熟悉语法规范经常会导致这类错误的发生。还有一些错误虽然没有语法错误，也不会使SQL语句执行失败，但确实不能实现预设的功能，这是一种逻辑错误。逻辑错误不仅会发生在程序开发阶段，也常常会出现在程序实际运行阶段。只有非常清楚程序需要实现的功能，才能避免此类错误。如果发生了逻辑错误，可以跟踪程序的执行流程，逐行、逐段地检查程序执行结果，发现产生问题的原因。运行时错误是指SQL程序在执行过程中出现的意想不到的错误，如由于锁表影响对数据表的更改、修改数据库对象时违反数据完整性与一致性的约束等。这类错误导致了一些SQL语句执行失败。采取必要的边界处理和错误处理，可以减少此类错误的发生次数。SQL程序的错误处理1．定位错误发生的位置2．判定错误原因3．简化程序以便调试学分转换函数①功能要求：将学生考试成绩转换成学分的功能。如果考试通过获得该课程的学分，否则获得学分为0。②入口参数：成绩和课程学分，③返回：返回应得学分。

CREATEFUNCTIONCreditConvert(@scoreNUMERIC(3,1),@CCreditsNUMERIC(3,1))--@score：考试成绩

--@CCredits：课程规定学分

RETURNSNUMERIC(5,2)--应得学分

ASBEGINRETURNCASESIGN(@score-60)WHEN1THEN@CCreditsWHEN0then@CCreditsWHEN-1then0ENDEND学期转换函数②入口参数：学年和入学年份③返回：数字表示的学期。

--termConvert

功能：学期转换

CREATEfunctiontermConvert(@tremCHAR(11),@clnoCHAR(6))--@trem

学年，格式如：2006-2007/2--@clno

班级编号，格式如：020001，前2位代表入学年份

RETURNSINT--在校第几学期

ASBEGINRETURNCONVERT(NUMERIC,SUBSTRING(@trem,1,4))-CONVERT(NUMERIC,'20'+SUBSTRING(@clno,1,2)))*2+CONVERT(NUMERIC,SUBSTRING(@trem,11,1))END统计平均成绩

CREATEPROCEDUREp_AverageScore@termvarchar(11)--入口参数：学期

--学期的格式为：XXXX-XXXX/X。

--前9位标别学年，最后一位表示本学年的第几学期。如2005-2006/2表示2005-2006学年的第2学期。

ASBEGINDECLARE@CnoVARCHAR(5)--变量：课程编号

DECLARE@ClnameVARCHAR(30)--变量：班级名称

DECLARE@clnoVARCHAR(6)--变量：班级编号

DECLARE@avgscoreNUMERIC(10,2)--变量：平均成绩

DECLARE@CtermINT--变量：学期

DECLAREclass_cursorCURSORFORSELECTCLname,CLno,dbo.termConvert(@term,clno)FROMclass--声明班级游标

/*其中termConvert函数是自定义函数，可以将如“2006-2007/2”的学期表述的字符串方式转换为如1、2、3等表述的数字方式。如2005年入学的同学的“2006-2007/2”学期是其在校的第4学期*/

统计平均成绩

OPENclass_cursor--打开班级游标

FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@Cterm--读取游标数据

WHILE@@FETCH_STATUS=0--检测游标数据是否读取完，如果还有数据，继续循环

BEGINSET@avgscore=(SELECTISNULL(avg(Score),0)FROMSCa,Studentb,Classc,CoursedWHEREa.SNo=b.SNoANDb.CLno=c.CLnoANDb.CLno=@clnoANDa.CNo=d.CNoANDd.Cterm=@Cterm)IF@avgscore>0/*根据班级平均成绩是否为0判断该班的成绩是否登记，如果为0，表明没有登记该班的在本学期的成绩*/BEGINPRINT@term+'学期'+@CLname+'各门课总平均成绩为'+str(@avgscore,5,1)--每个学生的平均成绩和获得的学分

PRINT'该班每个学生的平均成绩如下：'SELECTe.SName,d.avgscore,totalCreditFROM(SELECTa.SNo,AVG(score)avgscore,SUM(dbo.CreditConvert(score,CCredits))totalCreditFROMStudenta,SCb,CoursecWHEREa.SNo=b.SNoANDb.CNo=c.CNoANDc.Cterm=@CtermGROUPBYa.SNo)d,Studente,ClassfWHEREe.SNo=d.SNoANDe.CLno=f.CLnoANDf.CLno=@clnoENDELSEPRINT@term+'学期'+@CLname+'成绩没有登记'FETCHNEXTFROMclass_cursorINTO@CLname,@clno,@CtermENDCLOSEclass_cursor

DEALLOCATEclass_cursor

END统计不同分数段的人数和平均成绩

CREATEPROCEDUREp_SatSore

@cnoCHAR(5) --入口参数：班级编号

@clnoCHAR(6) --入口参数：课程编号

ASBEGINDECLARE@socre1INT --待统计分数段上限

DECLARE@socre2INT --待统计分数段下限

DECLARE@numINT --待统计分数段人数

DECLARE@CLNAMEVARCHAR(30) --班级名称

DECLARE@CNAMEVARCHAR(50) --课程名称

--查询课程名称和班级名称

SET@CLNAME=(SELECTCLNAMEFROMCLASSWHERECLNO=@clno)SET@CNAME=(SELECTCNAMEFROMCOURSEWHERECNO=@cno)PRINT@CLNAME+'<'+@CNAME+'>'+'考试成绩按照分数段统计情况'--设置被统计分数段的初值

SET@socre1=100SET@socre2=90WHILE(@socre1>=60)BEGINSET@num=(SELECTcount(*)FROMSCa,Classb,StudentcWHEREb.CLno=c.CLnoANDa.SNo=c.SNoANDb.C