TransactSQL简介存储过程和触发器课件

第六章Transact-SQL简介、存储过程和触发器6.1Transact-SQL简介

6.2存储过程

6.3触发器6.1Transact-SQL简介

6.1.1批处理、脚本和注释6.1.2常量和变量6.1.3流程控制语句1批处理 建立批处理如同编写SQL语句，区别在于它是多条语句同时执行的，用GO语句作为一个批处理的结束。2脚本 脚本是批处理的存在方式，将一个或多个批处理组织到一起就是一个脚本。 脚本可以在查询分析器中执行，查询分析器是编辑、调试和使用脚本的最好环境。

3注释1)单行注释：使用两个连在一起的减号“––”作为注释符语法格式为：––注释文本

2)块注释：使用“/**/”作为注释符块注释的语法格式为：/*注释文本*/或：/*注释文本*/6.1.2常量和变量

常量和变量是程序设计中不可缺少的元素。变量又分为局部变量和全局变量，局部变量是一个能够保存特定数据类型实例的对象，是程序中各种类型数据的临时存储单元，用在批处理内SQL语句之间传递数据。全局变量是系统给定的特殊变量。1常量Transact-SQL的常量主要有以下几种。字符串常量数值常量日期常量3局部变量 局部变量是用户在程序中定义的变量，一次只能保存一个值，它仅在定义的批处理范围内有效。局部变量可以临时存储数值。局部变量名总是以@符号开始，最长为128个字符。 使用DECLARE语句声明局部变量，定义局部变量的名字、数据类型，有些还需要确定变量的长度。4变量的声明与赋值声明变量的语句格式：DECLARE@局部变量名数据类型注：不能把局部变量指定为text或image类型，使用DECLARE声明一个局部变量后，这个变量的值将被初始化为null。变量的赋值语句格式为：SET@局部变量名=值|表达式注：表达式可以是任意的SQLSERVER表达式。6.1.3流程控制语句流程控制语句是组织较复杂Transact-SQL语句的语法元素，在批处理、存储过程、脚本和特定的检索中使用。它们包括条件控制语句、无条件转移语句和循环语句等。

主要的流程控制语句：BEGIN…END：定义语句块IF…ELSE：若指定条件为真，执行一个分支，否则执行另一个分支WHILE：当指定条件为真时重复一些语句CASE：允许表达式按照条件返回不同的值BREAK：退出最内层的WHILE循环RETURN：重新开始WHILE循环WAITFOR：为语句的执行设置延迟1BEGIN…END语句块BEGIN和END用来定义语句块，必须成对出现。它将多个SQL语句括起来，相当于一个单一语句，其语法格式如下。BEGIN

语句1或语句块1 语句2或语句块2 …END3WHILE语句 WHILE语句用来实现循环结构，其语法格式如下：WHILE逻辑表达式语句块 当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。 BREAK语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。例：若IF条件为真或为假时要执行的语句只有一条（默认时，一条语句就是一个语句块），则可以不使用BEGIN…END。DECLARE@xint,@yint,@zintSET@x=40SET@y=30IF(@x>@y)SET@z=@x-@yELSESET@z=@y-@xSET@x=0PRINT@xPRINT@yPRINT@z执行结果为

03010例：若希望条件为假时，在ELSE语句中执行其后续的两条语句，则必须使用BEGIN…END将这两条语句包括起来，使其成为一个语句块。DECLARE@xint,@yint,@zintSET@x=40SET@y=30IF(@x>@y)SET@z=@x-@yELSEBEGINSET@z=@y-@xSET@x=0END例：计算1+2+3+…+100的和DECLARE@iint，@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT@sum6.2存储过程

存储过程（storedprocedure）是一组事先编译好的Transact-SQL代码。存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。使用存储过程的好处：1、执行速度快。2、模块化的程序设计。3、减少网络通信量。4、保证系统的安全性。Default：表示参数的默认值。Output：表明参数是输出参数。执行存储过程的SQL语句是EXECUTE，其语法格式为：[EXECUTE]存储过程名[实参[，OUTPUT][，…n]]

存储过程可以嵌套，SQLServer2000最多可以允许嵌套32层存储过程。例1：带有复杂查询的存储过程：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。CREATEPROCEDURES_grade1ASSELECTsname,cname,gradeFROMStudentsJOINscONs.sno=sc.snoJOINcoursecONo=oWHERESdept=‘计算机系’执行此存储过程：EXECS_grade1例3、带有多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，若没有指定课程，则默认为‘数据库’。CREATEPROCS_GRADE2@Snamechar(20)，@Cnamechar(20)=‘数据库’ASSELECTSname,Cname,GradeFROMStudent,Scourse,CourseWHERESname=@SnameandCname=@CnameandStudent.Sno=Scourse.SnoAndCourse.Cno=Scourse.Cno

执行带多个参数的存储过程时，参数的传递方式有两种：1、按参数位置传递：执行存储过程的EXEC语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致EXECS_GRADE2’张三’，’VB’2、按参数名传递：执行存储过程的EXEC语句中要指明定义存储过程时定义的参数的名字以及此参数的值，而不关心参数的定义顺序EXECS_GRADE2@Sname=’张三’，@Cname

=’VB’例4、带有多个输入参数并均指定默认值的存储过程：查询指定系，指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值为‘计算机’，默认的性别为‘男’，默认的年龄为20。CREATEPROCS_GRADE3@deptchar(20)=‘计算机’

，@sexchar(2)=‘男’，@ageint=20

ASSELECT*FROMStudentWHERESdept=@deptandSsex=@sexandSage>=@age

例5、带有输出参数的存储过程：计算两个数得到积，将结果作为输出参数返回给调用者。CREATEPROCEDUREPROC1@var1int,@var2int,@var3intoutputASSET@var3=@var1*@var2执行此存储过程的示例：DECLARE@resintEXECPROC15,7,@resoutputPRINT@res结果为：35例6、创建一个存储过程，向学生表中插入数据。

CREATEPROCinsert_student@snochar(10),@snamechar(10),@ssexchar(4),@sageint,@sdeptchar(20)asinsertintostudent,@sage,@sdept)执行此存储过程execinsert_student‘1001’,’李明’,’男’,’20’,’计算机系’6.2.2查看存储过程信息1．使用T-SQL语句查看存储过程（1）可以使用sp_helpText命令查看创建存储过程的文本信息。例如：usemastergosp_helptextsp_whogo系统返回信息是：（2）可以使用sp_help查看存储过程的一般信息。例如：usemastergosp_helpproc1go6.2.3修改存储过程

SQLServer提供了在不改变存储过程使用许可和名字的情况下，对存储过程进行修改的语句。语法格式为：ALTERPROC[EDURE]存储过程名[{@参数名数据类型}[=default][OUTPUT]][,…n]ASSQL语句注：也可以使用企业管理器进行系统存储过程的修改。例6、将例1定义的存储过程修改，使之能查询任何指定系的学生的修课情况。ALTERPROCS_Grade1@deptchar(20)ASSELECTSname,Cname,GradeFROMStudentJOINScourseONStudent.Sno=Scourse.SnoJOINCourseONCourse.Cno=Scourse.CnoWHERESdept=@dept6.2.5删除存储过程1．使用T-SQL语句删除存储过程删除存储过程使用DROPPROCEDURE语句。语法格式为：DROPPROCEDURE存储过程名[,…n]例如：usestudentgodropprocedureS_grade1go6.3触发器

触发器是一种特殊的存储过程，他不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的，触发器通常用于保证业务规则的数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束规则。6.3.1创建触发器1．使用T-SQL语句创建触发器创建触发器使用CREATETRIGGER语句。语法格式如下：CREATETRIGGER触发器名ON表名[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASSQL语句其中：“触发器名称”必须是唯一的ON子句用于指定在其上执行触发器的表

AFTER：指定触发器只有在引发触发器执行的SQL语句指定的操作都已成功执行，并且所有的引用级联操作和约束检查也成功完成后，才执行此触发器，也叫后触发性触发器

FOR如果仅指定FOR关键字，则AFTER是默认设置INSTEADOF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。（注：只有SQLServer2000才支持）也叫前触发器例1：创建带有提示信息的触发器，每当用户在course表中执行插入操作时，向用户产生一条提示信息。createtriggertri_insert_courseoncourseforinsertasprint’在course表中插入数据’——只是为了提示信息用执行如下语句测试触发器的作用：Insertintocourse(cno,cname)values(‘d01’,‘数据库')例2、创建一个触发器，当向表student中修改一条记录时，自动显示student表中的记录。CREATETRIGGERchange_displayONstudentFORupdateASSELECT*FROMstudent例3、创建限制删除的触发器，限制删除SC表中不及格学生的成绩记录。createtriggertri_del_gradeonSCfordeleteasifexists(select*fromDELETEDwheredeleted.grade<60)ROLLBACK在CREATETRIGGER语句中不能使用SELECT语句返回对表格查询的数据，因为触发器不接受用户应用程序传递的参数，从而也无法向用户应用程序返回查询表格数据所得到的结果。在创建触发器的语句中，禁止使用下列T-SQL语句：ALTERDATABASE ALTERPROCEDUREALTERTABLE ALTERTRIGGERALTERVIEW CREATEDATABASECREATEDEFAULT CREATEINDEXCREATE