tj第六章SQL编程技术.ppt

1,第六章SQL编程技术,2,6.1SQL编程基础,6.1.1批处理批处理是一个以“”为结束的语句集这些语句是作为一个组来执行并一起提交的脚本是一系列顺序执行的批处理脚本文件的文件类型为.sql注意：不是语句,3,例：给出含有三个批处理的脚本,CREATETABLE学院(学院名称char(12)PRIMARYKEY,院长char(8),学院地址char(16),办公电话char(8),编制人数smallint)GOINSERTINTO学院VALUES(计算机学院,刘洋,一号楼4,88889999,50)INSERTINTO学院VALUES(商学院,梁山,五号楼302,99998888,60)GOSELECT*FROM学院GO,4,6.1.2变量,1.变量的类型局部变量-以引导,由用户定义.其作用域定义的辖域中.全局变量-以引导,由系统提供并赋值.其作用域为全局.,5,6.1.2变量,2.变量的声明语句格式:DECLARE变量名数据类型,变量名数据类型例:声明变量DECLARENameCHAR(8),SexCHAR(2),AgeSMALLINT,6,6.1.2变量,3.变量的赋值语句格式一:SET变量名称=表达式语句格式二:SELECT变量名称=表达式语句格式三:SELECT变量名称=字段(或函数)FROM表(或视图)名WHERE,7,举例,例1:DECLARENameCHAR(8),SexCHAR(2),AgeSMALLINTSETName=李华SETSex=女SETAge=20例2:DECLARENameCHAR(8)SELECTName=李华,8,举例,例3:DECLARENameCHAR(8),SexCHAR(2),AgeSMALLINTSELECTName=姓名,Sex=性别,Age=YEAR(GETDATE()-YEAR(出生日期)FROM学生WHERE学号=00150236SELECTName,Sex,AgeGO,显示结果,9,6.1.3控制流语句,包含条件控制语句、无条件转移语句、循环语句等。,10,6.1.3控制流语句,.BEGINEND功能：将一组SQL语句作为一个语句块。语句格式：BEGINEND与IF语句和WHILE语句配合使用,11,6.1.3控制流语句,2.IFELSE语句语句格式：IFELSE,12,13,举例,USE教学数据库GODECLAREgavgsmallintSELECTgavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)IF(gavg80)PRINT大学英语课程的平均成绩高于80分ELSEBEGINPRINT大学英语课程的平均成绩低于80分select平均成绩:,gavgEND,14,6.1.3控制流语句,3.循环语句语句格式：WHILEBREAKCONTINUE,15,6.1.3控制流语句,16,举例:,求出大学英语课程的平均成绩当该分数小于0分时，循环做,给所有成绩高于平均分的成绩加分，当最高分大于时停止加分并显示加的分数该分数(平均成绩)大于0分时，循环停止并显示加的分数,17,举例,DECLAREgavgsmallint,COUNsmallintSETCOUN=0SELECTgavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)WHILE(gavggavgIF(SELECTmax(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)100BREAKENDPRINTCOUN,18,举例,DECLAREgavgsmallint,COUNsmallintSETCOUN=0SELECTgavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)PRINTgavgWHILE(gavggavgIF(SELECTmax(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)=100BREAKELSESELECTgavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称=大学英语)ENDPRINTCOUN,警告:聚合或其它SET操作消除了空值。74（所影响的行数为4行）警告:聚合或其它SET操作消除了空值。警告:聚合或其它SET操作消除了空值。（所影响的行数为4行）警告:聚合或其它SET操作消除了空值。-275（所影响的行数为1行）,19,4.RETURN语句语句格式：RETURN整数功能：无条件所在批处理、存储过程或触发器。可以返回一个整数值,6.1.3控制流语句,20,举例,题目：根据给定的学号（param）检查学生的平均成绩，若75，将返回状态代码1，将返回状态代码2。USE教学数据库CREATEPROCEDUREcheckavgparamvarchar(10)ASIF(SELECTAVG(成绩)FROM选课WHERE学号=param)75RETURNELSERETURN,21,执行存储过程：,declareaasmallintexecaa=checkavg00150236ifaa=print小于75elseprint大于75,22,6.1.4EXECUTE语句,功能：执行函数、存储过程语法格式：EXECUTEoutput,23,6.1.5注释,.单行注释（两个减号）.多行注释/*/,24,6.1.6程序设计举例,题目：转帐，若帐户的余额大于等于100元，从帐户上支取100元，存入帐户.否则，不执行转帐操作。,begintransactionupdate帐户set余额=余额-100where帐户=AIF(SELECT余额from帐户where帐户=A)=0beginprint金额不够。转帐失败！rollbacktransactionendelsebeginupdate帐户set余额=余额+100where帐户=Bprint转帐成功！committransactionend,25,事务概述,事务是作为单个逻辑工作单元执行的一系列操作。事务的属性（ACID)原子性：原子工作单元一致性：保证数据的一致性隔离性：并发事务之间所做的修改要隔离并发操作：几个用户程序同时读写一个数据的情况持久性：对系统的影响要持久,26,事务操作,语法格式：BEGINTRANSACTION事务开始COMMITTRANSACTION提交ROLLBACKTRANSACTION回滚说明：如果没有明确给出BEGINTRANSACTION语句，则SQLServer是将每个SQL语句都当成一个事务进行执行,27,6.3存储过程,6.3.1存储过程的概念是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。存储过程的分类系统存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）用户自定义的存储过程,28,使用存储过程的优势,模块化编程：创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。快速执行：存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。安全机制通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,29,6.3.2创建存储过程,创建过程语法格式：CREATEPROCEDURE数据类型，OUTPUTAS执行过程语法格式：EXECUTE或EXEC变量=值删除存储过程语法格式：DROPPROCEDURE,30,例：检索某个学生(学号为01111111）的成绩单，包括姓名、课程名、分数。存储过程名为sc_grade,IFEXISTS(SELECTnameFROMsysobjectsWHEREname=sc_gradeANDtype=P)DROPPROCEDUREsc_gradeGOCREATEPROCEDUREsc_gradeSNOCHAR(8)ASSELECT姓名,课程名称,成绩FROM学生S,课程C,选课SCWHERES.学号=SC.学号ANDC.课程号=SC.课程号ANDS.学号=SNOGO,31,selectleft(name,15)+space(3)name,xtypefromsysobjectswherextypein(U,PK,FK,CK,P,TR)andSUBSTRING(name,1,3)dt_,namextype-check_trigTRScroeProcPt1UPK_t1_17036CCPKt2U系科UPK_系科PK选课1U选课11U选课UPK_选课PK课程UPK_课程PKstudentUPK_student_3EPKstudent3UPK_student3_4PKcheckavgP教师UPK_教师PK学生UPK_学生PKdtpropertiesUpk_dtpropertiesPK（所影响的行数为24行）,32,执行过程,EXECsc_grade01111111或EXECsc_gradesno=01111111,33,例在“教学”数据库建立一个名为ScroeProc的存储过程，它带有两个输入参数并返回两个输出参数和一个返回值。程序清单如下：USE教学GO-存储过程ScroeProc若存在，则删除之IFEXISTS(SELECTnameFROMsysobjectsWHEREname=ScroeANDtype=P)DROPPROCEDUREScroeProcGO,34,-创建存储过程ScroeProc-输入参数：Dno输入系科号；Cname输入课名-输出参数：Avg接受平均分CREATEPROCEDUREScroeProcDnochar(4),Cnamevarchar(30),AvgdecimalOUTPUTAS/*声明和初始化一个局部变量，用于保存系统函数ERROR的返回值*/DECLAREErrorSaveintSETErrorSave=0,35,-执行一个选择查询，统计指定系的某门课的平均成绩-通过两个输入参数接受系号和课程名称-通过一个输出参数Avg接受平均分SELECTAvg=AVG(成绩)FROM选课scINNERJOIN学生stONsc.学号=st.学号INNERJOIN课程COONsc.课程号=co.课程号where系科号=DnoAND课程名称=CnameIF(ERROR0)SETErrorSave=ERRORselectErrorSave,36,/*执行一个修改查询，将统计指定系的某门课的高于平均分的加1分*/UPDATE选课set成绩=成绩+1where学号in(select学号stFROM选课scINNERJOIN学生stONsc.学号=st.学号INNERJOIN课程COONsc.课程号=co.课程号where系科号=DnoAND课程名称=Cname)IF(ERROR0)SETErrorSave=ERRORRETURNErrorSave,37,执行存储过程:/*声明变量，用于保存返回值和输出结果*/DECLARERetCodeint,AvgGradedecimal/*执行存储过程，并指定输入参数和输出参数*/EXECUTERetCode=ScroeProc15,数据结构,AvgGradeOUTPUT,38,-0（所影响的行数为1行）（所影响的行数为51行）-840（所影响的行数为1行）,39,6.4数据库触发器,6.4.1触发器的概念是一种实现复杂完整性约束的特殊存储过程，是能够在符合条件是自动触发的SQL程序。1.触发器的特点2.触发器的优点,40,6.4.1触发器的概念,若触发器所依赖的表中有约束，则在执行时，约束优于触发器，而且如果在操作中触发器和约束发生冲突，触发器将不执行。触发器操作是一个事务操作。,41,6.4.1触发器的概念,在下列情况下可以考虑使用触发器：强制比CHECK约束复杂的数据完整性使用自定义的错误信息和执行复杂的错误处理实现多张表的级联修改比较数据库修改前后数据的状态维护非规范数据,42,6.4.2创建触发器,语法格式：CREATETRIGGEROnFOR|AFTER|INSTEADOFINSERT|UPDATE|DELETEAS删除触发器语法格式：DROPTRIGGER,43,语句解释：,AFTER:指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。INSTEADOF:指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。INSERT,UPDATE,DELETE:是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。,44,使用触发器的限制,(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。,45,使用触发器的限制,(5)一个表的外键在DELETE、UPDATE操作上定义了级联，不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。(6)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE,46,6.4.3触发器的工作原理,当触发器被触发时，系统会创建两个专用临时表：inserted表和deleted表。这两个表由系统来维护，不允许用户直接对这两个表进行修改（可以读）。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。,47,6.4.3触发器的工作原理,insterted表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同时，也将其副本存入insterted表中。因此，在insterted表中的行总是与触发表中的新行相同。,48,6.4.3触发器的工作原理,deleted表：存放由于DELETE或UPDATE语句的执行而导致要从该触发表中删除的所有行。也就是说，把触发表中要删除或要更新的旧行移到deleted表中。因此，deleted表和触发表不会有相同的行。,49,6.4.3触发器的工作原理,对INSERT操作，只在insterted表中保存所插入的新行，而deleted表中无一行数据。对于DELETE操作，只在deleted表中保存被删除的旧行，而insterted表中无一行数据。对于UPDATE操作，可以将它考虑为DELETE操作和INSERT操作的结果，所以在inserted表中存放着更新后的新行值，deleted表中存放着更新前的旧行值。,50,例:在学生表上创建触发器reminder，若在学生表中添加、更改和删除数据，则将向客户端显示信息。,USE教学IFEXISTS(SELECTnameFROMsysobjectsWHEREname=reminderANDtype=TR)DROPTRIGGERreminderGOCREATETRIGGERreminderON学生FORINSERT,UPDATE,DELETEASprint注意：学生表数据被修改。SELECT*FROMINSERTEDSELECT*FROMDELETEDGO,51,insertinto学生(学号,姓名)values(9999,dd),UPDATE学生SET姓名=WWWWWHERE学号=9999,deletefrom学生where学号=9999,52,举例：创建一触发器，当向成绩表插入一记录时，检查该记录的学号在学生表是否存在，检查课程号在课程表中是否存在，若有一项为否，则不允许插入。,Use教学GOalterTRIGGERcheck_trigON成绩forINSERTASIFEXISTS(SELECT*FROMinsertedaWHEREa.学号NOTIN(SELECT学号FROM学生)ORa.课程编号NOTIN(SELECT课程编号FROM课程)BEGIN-SELECT*FROM成绩-SELECT*FROMinsertedprint违背数据的一致性！ROLLBACKTRANSACTIONENDSELECT*FROM成绩GO,53,insertinto成绩values(3012,C6,90)SELECT*FROM成绩WHERE课程编号LIKE%6,执行插入操作：,insertinto成绩values(5012,C6,90),服务器:消息547，级别16，状态1，行1INSERT语句与COLUMNFOREIGNKEY约束FK_成绩_学号_6B24EA82冲突。该冲突发生于数据库教学，表学生,column学号。语句已终止。,54,分别删除了成绩表、课程表、学生表的联系,insertinto成绩values(5012,C6,90),55,6.2游标及其应用,6.2.1游标的概念如果要求每次只显示表格中的一行，该如何处理？这在将T-SQL嵌入到其他高级语言（如VC、VB、Delphi等）的编程中经常用到。,56,6.2.1游标的概念,游标(cursor)是一个存储区域，用来存放结果集。游标的指针，可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。游标提供了对一个结果集进行逐行处理的能力：在结果集中定位特定行从结果集的当前位置检索行支持对结果集中当前位置的行进行数据处理（修改/删除）,57,6.2.2游标的用法,声明游标打开游标处理数据（读取/修改/删除）可以和其他T-S