第六章-SQL-编程技术

第六章SQL编程技术16.1SQL编程基础6.1.1批处理批处理是一个以“ＧＯ”为结束的语句集．这些语句是作为一个组来执行并一起提交的．

脚本是一系列顺序执行的批处理．脚本文件的文件类型为.sql注意：ＧＯ不是ＳＱＬ语句．2例：给出含有三个批处理的脚本CREATETABLE学院( 学院名称char(12)PRIMARYKEY, 院长char(8), 学院地址char(16), 办公电话char(8), 编制人数smallint)GOINSERTINTO学院VALUES('计算机学院','刘洋','一号楼4','88889999',50)INSERTINTO学院VALUES('商学院','梁山','五号楼302','99998888',60)GOSELECT*FROM学院GO36.1.2变量1.变量的类型局部变量--以@引导,由用户定义.其作用域定义的辖域中.全局变量--以@@引导,由系统提供并赋值.其作用域为全局.46.1.2变量2.变量的声明语句格式:DECLARE@变量名数据类型[,@变量名数据类型……]例:声明变量DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINT56.1.2变量3.变量的赋值语句格式一:SET@变量名称=表达式语句格式二:SELECT@变量名称=表达式

语句格式三:

SELECT

@变量名称=字段(或函数)FROM表(或视图)名

WHERE<条件表达式>

6举例例1:DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINTSET@Name=‘李华’SET@Sex=‘女’SET@Age=20例2:DECLARE@NameCHAR(8)SELECT@Name=‘李华’7举例例3:DECLARE@NameCHAR(8),@SexCHAR(2),@AgeSMALLINTSELECT@Name=姓名,@Sex=性别,@Age=YEAR(GETDATE())-YEAR(出生日期)FROM学生WHERE学号='00150236'SELECT@Name,@Sex,@AgeGO显示结果86.1.3控制流语句包含条件控制语句、无条件转移语句、循环语句等。控制语句说明IF...ELSE条件语句GOTO无条件转移语句WHILE循环语句CONTINUE用于重新开始下一次循环BREAK用于退出最内层的循环RETURN无条件返回96.1.3控制流语句１.BEGIN…END功能：将一组SQL语句作为一个语句块。语句格式：BEGIN<SQL语句序列>END与IF语句和WHILE语句配合使用106.1.3控制流语句2.IF…ELSE语句语句格式：IF<条件表达式>

<SQL语句序列1>ELSE

<SQL语句序列2>1112举例USE教学数据库GODECLARE@gavgsmallintSELECT@gavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')IF(@gavg>80)PRINT'大学英语课程的平均成绩高于80分'ELSE

BEGIN

PRINT'大学英语课程的平均成绩低于80分'select'平均成绩:',@gavg

END136.1.3控制流语句3.循环语句语句格式：WHILE<条件表达式><SQL语句序列1>[BREAK][<SQL语句序列1>][CONTINUE]

146.1.3控制流语句15求出大学英语课程的平均成绩，当该分数小于８0分时，循环做,给所有成绩高于平均分的成绩加１分，当最高分大于１００时停止加分．并显示加的分数．该分数大于８0分时，循环停止并显示加的分数16举例DECLARE@gavgsmallint,@COUNsmallintSET@COUN=0SELECT@gavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')WHILE(@gavg<80)

BEGINSET@COUN=@COUN+1UPDATE选课set成绩=成绩+1where课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')AND成绩>@gavgIF(SELECTmax(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语'))>100BREAK

ENDPRINT@COUN17举例DECLARE@gavgsmallint,@COUNsmallintSET@COUN=0SELECT@gavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')PRINT@gavgWHILE(@gavg<80)BEGINSET@COUN=@COUN+1UPDATE选课set成绩=成绩+1where课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')AND成绩>@gavgIF(SELECTmax(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语'))>=100BREAKELSESELECT@gavg=AVG(成绩)FROM选课WHERE课程号=(SELECT课程号FROM课程WHERE课程名称='大学英语')

ENDPRINT@COUN184.RETURN语句语句格式：RETURN[整数]功能：无条件所在批处理、存储过程或触发器。可以返回一个整数值6.1.3控制流语句19举例题目：根据给定的学号（@param）检查学生的平均成绩，若>75，将返回状态代码1，将返回状态代码2。USE教学数据库CREATEPROCEDUREcheckavg@paramvarchar(10)ASIF(SELECTAVG(成绩)FROM选课WHERE学号=@param)>75RETURN１ELSERETURN０20执行存储过程：declare@aasmallintexec@aa=checkavg'00150236'if@aa=０print'小于75'elseprint'大于75'216.1.4EXECUTE语句功能：执行函数、存储过程语法格式：EXEC[UTE]<过程名><参数>［output］226.1.5注释１.单行注释－－（两个减号）２.多行注释/**/236.1.6程序设计举例题目：转帐，若Ａ帐户的余额大于等于100元，从Ａ帐户上支取100元，存入Ｂ帐户.否则，不执行转帐操作。begintransactionupdate帐户set余额=余额-100where帐户='A'IF(SELECT余额from帐户where帐户='A')<=0beginprint'金额不够。转帐失败！'rollbacktransactionendelsebeginupdate帐户set余额=余额+100where帐户='B'print'转帐成功！'committransactionend24事务概述事务是作为单个逻辑工作单元执行的一系列操作。事务的属性（ACID)原子性：原子工作单元一致性：保证数据的一致性隔离性：并发事务之间所做的修改要隔离并发操作：几个用户程序同时读写一个数据的情况持久性：对系统的影响要持久25事务操作语法格式：BEGINTRANSACTION事务开始COMMITTRANSACTION提交ROLLBACKTRANSACTION回滚说明：如果没有明确给出BEGINTRANSACTION语句，则SQLServer是将每个SQL语句都当成一个事务进行执行266.3存储过程6.3.1存储过程的概念

是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。存储过程的分类系统存储过程扩展存储过程（提供从SQLServer到外部程序的接口，以便进行各种维护活动）用户自定义的存储过程27使用存储过程的优势模块化编程：创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。快速执行：存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。减少网络通信量有了存储过程后，在网络上只要一条语句就能执行一个存储过程。安全机制通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。286.3.2创建存储过程创建过程语法格式：CREATEPROCEDURE<过程名>［<参数>数据类型，…］[OUTPUT]AS<SQL语句序列>执行过程语法格式：EXECUTE或EXEC<过程名>［变量=］值删除存储过程语法格式：

DROPPROCEDURE<过程名>29例：检索某个学生(学号为‘01111111’）的成绩单，包括姓名、课程名、分数。存储过程名为sc_gradeIFEXISTS(SELECTnameFROMsysobjectsWHEREname='sc_grade'ANDtype='P')DROPPROCEDUREsc_gradeGOCREATEPROCEDUREsc_grade@SNOCHAR(8)ASSELECT姓名,课程名称,成绩FROM学生S,课程C,选课SCWHERES.学号=SC.学号ANDC.课程号=SC.课程号ANDS.学号=@SNOGO30执行过程EXECsc_grade'01111111'或EXECsc_grade@sno='01111111'31例在“教学”数据库建立一个名为ScroeProc的存储过程，它带有两个输入参数并返回两个输出参数和一个返回值。程序清单如下：USE教学GO--存储过程ScroeProc若存在，则删除之IFEXISTS(SELECTnameFROMsysobjectsWHEREname=’Scroe’ANDtype=’P’)DROPPROCEDUREScroeProcGO32--创建存储过程ScroeProc--输入参数：@Dno输入系科号；@Cname输入课名--输出参数：@Avg接受平均分；@Max接受最高分CREATEPROCEDUREScroeProc@Dnochar(4),@Cnamevarchar(30),@AvgdecimalOUTPUT,@MaxdecimalOUTPUTAS/*声明和初始化一个局部变量，用于保存系统函数@@ERROR的返回值*/DECLARE@ErrorSaveintSET@ErrorSave=033--执行一个选择查询，统计指定班级某门课的平均成绩--通过两个输入参数接受系号和课程名称--通过一个输出参数@Avg接受平均分SELECT@Avg=AVG(成绩)FROM选课scINNERJOIN学生stONsc.学号=st.学号INNERJOIN课程COONsc.课程号=co.课程号GROUPBY系科号,课程名称HAVING系科号=@DnoAND课程名称=@CnameIF(@@ERROR<>0)SET@ErrorSave=@@ERROR34/*执行一个选择查询，统计某门课的最高分*//*通过两个输入参数接受系科号和课程名称*//*通过一个输出参数@Max接受最高分*/SELECT@Max=Max(成绩)FROM选课scINNERJOIN学生stONsc.学号=st.学号INNERJOIN课程COONsc.课程号=co.课程号GROUPBY系科号,课程名称HAVING系科号=@DnoAND课程名称=@CnameIF(@@ERROR<>0)SET@ErrorSave=@@ERRORRETURN@ErrorSave35/*声明变量，用于保存返回值和输出结果*/DECLARE@RetCodeint@AvgGradedecimal,@MaxGradedecimal/*执行存储过程，并指定输入参数和输出参数*/EXECUTE@RetCode=ScroeProc’15’,’数据结构’,@AvgGradeOUTPUT,@MaxGradeOUTPUTselect@AvgGrade,@MaxGrade366.4数据库触发器6.4.1触发器的概念是一种实现复杂完整性约束的特殊存储过程，是能够在符合条件是自动触发的SQL程序。1.触发器的特点

2.触发器的优点376.4.1触发器的概念若触发器所依赖的表中有约束，则在执行时，约束优于触发器，而且如果在操作中触发器和约束发生冲突，触发器将不执行。触发器操作是一个事务操作。386.4.1触发器的概念在下列情况下可以考虑使用触发器：强制比CHECK约束复杂的数据完整性使用自定义的错误信息和执行复杂的错误处理实现多张表的级联修改比较数据库修改前后数据的状态维护非规范数据396.4.2创建触发器语法格式：CREATETRIGGER<触发器名>

On<表名>

FOR

|AFTER|INSTEADOFINSERT|UPDATE|DELETEAS<SQL语句序列>删除触发器语法格式：DROPTRIGGER<触发器名>

40语句解释：AFTER:指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。INSTEADOF:指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。INSERT,UPDATE,DELETE:是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。41使用触发器的限制(1)CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATETRIGGER语句中，可以为多种操作（如INSERT和UPDATE）定义相同的触发器操作。

42使用触发器的限制(5)一个表的外键在DELETE、UPDATE操作上定义了级联，不能在该表上定义INSTEADOFDELETE、INSTEADOFUPDATE触发器。(6)触发器中不允许包含以下T-SQL语句：CREATEDATABASE、ALTERDATABASE、LOADDATABASE、RESTOREDATABASE、DROPDATABASE、LOADLOG、RESTORELOG、DISKINIT、DISKRESIZE和RECONFIGURE

436.4.3触发器的工作原理当触发器被触发时，系统会创建两个专用临时表：inserted表和deleted表。这两个表由系统来维护，不允许用户直接对这两个表进行修改（可以读）。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。446.4.3触发器的工作原理insterted表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同时，也将其副本存入insterted表中。因此，在insterted表中的行总是与触发表中的新行相同。456.4.3触发器的工作原理deleted表：存放由于DELETE或UPDATE语句的执行而导致要从该触发表中删除的所有行。也就是说，把触发表中要删除或要更新的旧行移到deleted表中。因此，deleted表和触发表不会有相同的行。466.4.3触发器的工作原理对INSERT操作，只在insterted表中保存所插入的新行，而deleted表中无一行数据。☆对于DELETE操作，只在deleted表中保存被删除的旧行，而insterted表中无一行数据。对于UPDATE操作，可以将它考虑为DELETE操作和INSERT操作的结果，所以在inserted表中存放着更新后的新行值，deleted表中存放着更新前的旧行值。

47例:在学生表上创建触发器reminder，若在学生表中添加、更改和删除数据，则将向客户端显示信息。USE教学

IFEXISTS(SELECTnameFROMsysobjects WHEREname='reminder'ANDtype='TR')DROPTRIGGERreminderGOCREATETRIGGERreminderON学生 FORINSERT,UPDATE,DELETEASprint'注意：学生表数据被修改。'SELECT*FROMINSERTEDSELECT*FROMDELETEDGO48INSERTED表DELETED表insertinto学生(学号,姓名)values('9999','dd')UPDATE学生SET姓名='WWWW'WHERE学号='9999'DELETED表INSERTED表deletefrom学生where学号='9999'49举例：创建一触发器，当向成绩表插入一记录时，检查该记录的学号在学生表是否存在，检查课程号在课程表中是否存在，若有一项为否，则不允许插入。

Use教学GOCREATETRIGGERcheck_trigON成绩FORINSERTASIFEXISTS(SELECT* FROMinserteda WHEREa.学号NOTIN(SELECT学号FROM学生)ORa.课程编号NOTIN(SELECT课程编号FROM课程))BEGIN RAISERROR(‘违背数据的一致性！！！！',10,1) ROLLBACKTRANSACTIONENDGO50insertinto成绩values('3012','C6',90)SELECT*FROM成绩WHERE课程编号LIKE'%6'执行插入操作：insertinto成绩values('5012','C6',90)服务器:消息547，级别16，状态1，行1INSERT语句与COLUMNFOREIGNKEY约束'FK__成绩__学号__6B24EA82'冲突。该冲突发生于数据库'教学'，表'学生',column'学号'。语句已终止。51分别删除了成绩表、课程表、学生表的联系insertinto成绩values('5012','C6',90)526.2游标及其应用6.2.1游标的概念

如果要求每次只显示表格中的一行，该如何处理？——这在将T-SQL嵌入到其他高级语言（如VC、VB、Delphi等）的编程中经常用到。536.2.1游标的概念游标(cursor)是一个存储区域，用来存放结果集。游标的指针，可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。游标提供了对一个结果集进行逐行处理的能力：在结果集中定位特定行从结果集的当前位置检索行支持对结果集中当前位置的行进行数据处理（修改/删除）546.2.2游标的用法声明游标打开游标处理数据（读取/修改/删除）——可以和其他T-SQL语句配