Transact-SQL简介、存储过程和触发器.ppt

第六章 Transact-SQL简介、存储过程和触发器,6.1Transact-SQL简介 6.2存储过程 6.3触发器,6.1 Transact-SQL简介,6.1.1 批处理、脚本和注释 6.1.2 常 量 和 变 量 6.1.3 流 程 控 制 语 句,、常规对象的标识符规则,）第一个字符必须是下列字符之一：字母a-z和A-Z， 以及来自其他语言的字母字符、下划线_、或者 数字符号。 说明：以符号开始的标识符表示局部变量或参数。 以一个数字符号开始的标识符表示临时表或过程。 以双数字符号开始的标识符表示全局临时对象。 ）后续字符可以是：所有的字母、十进制数字、符号 美元符号（$）、数字符号或下划线。 说明：标识符不能是t_sql的保留字。 不允许嵌入空格或其他特殊字符。 当标识符用于t_sql语句时，必须用双引号或括号 分隔不符合规则的标识符。,、数据库对象的命名规则,格式如下：,server_name.database_name.owner_name.object_name 其中：server_name：对象所在的服务器名称 database_name：对象所在的数据库名称 owner_name：表示对象的所有者 object_name：表示对象的名称,6.1.1 批处理、脚本和注释,批处理就是一个或多个Transact-SQL语句的集合，用户或应用程序一次将它发送给SQL Server，由SQL Server编译成一个执行单元，此单元称为执行计划，执行计划中的语句每次执行一条。 批处理的结束标记是：GO。,1 批处理 建立批处理如同编写SQL语句，区别在于它是多条语句同时执行的，用GO语句作为一个批处理的结束。 2 脚本 脚本是批处理的存在方式，将一个或多个批处理组织到一起就是一个脚本 。 脚本可以在查询分析器中执行，查询分析器是编辑、调试和使用脚本的最好环境。,3注释 1)单行注释： 使用两个连在一起的减号“ ”作为注释符 语法格式为： 注释文本 2)块注释：使用“/* */”作为注释符 块注释的语法格式为： /*注释文本*/ 或： /* 注释文本 */,例： use northwind go -多行注释的第一行 -多行注释的第二行 select * from employee go /*多行注释的第一行。 多行注释的第二行。*/ select * from products,6.1.2常量和变量,常量和变量是程序设计中不可缺少的元素。变量又分为局部变量和全局变量，局部变量是一个能够保存特定数据类型实例的对象，是程序中各种类型数据的临时存储单元，用在批处理内SQL语句之间传递数据。全局变量是系统给定的特殊变量。,1 常量 Transact-SQL的常量主要有以下几种。 字符串常量 数值常量 日期常量,2 全局变量 全局变量是SQL Server系统提供并赋值的变量。用户不能定义全局变量，也不能用SET语句来修改全局变量。通常是将全局变量的值赋给局部变量，以便保存和处理。事实上，在SQL Server中，全局变量是一组特定的函数，它们的名称是以开头，而且不需要任何参数，在调用时无需在函数名后面加上一对圆括号，这些函数也称为无参数函数。,全局变量在程序中的应用,例：利用全局变量CONNECTIONS显示到系统的当前时期 和时间为止，用户登录SQL Server的次数,select getdate( ) as today date and time select connections as login attempts,CONNECTIONS:返回自上次启动Microsoft SQL Server 以来连接或试图连接的次数。,3 局部变量 局部变量是用户在程序中定义的变量，一次只能保存一个值，它仅在定义的批处理范围内有效。局部变量可以临时存储数值。局部变量名总是以符号开始，最长为128个字符。 使用DECLARE语句声明局部变量，定义局部变量的名字、数据类型，有些还需要确定变量的长度。,4 变量的声明与赋值 声明变量的语句格式： DECLARE 局部变量名 数据类型 注：不能把局部变量指定为text或image类型，使用DECLARE声明一个局部变量后，这个变量的值将被初始化为null。 变量的赋值语句格式为： SET 局部变量名=值 | 表达式 注：表达式可以是任意的SQL SERVER表达式。,例：计算两个变量的值的和，然后输出其结果。 DECLARE x int DECLARE y int DECLARE z int SET x =10 SET y =10 SET z = x + y Print z,例：使用DECLARE语句定义一个名为ming的局部变 量，在st数据库中的student表中检索所有姓 “王”的学生信息。,use st declare ming varchar(30) set ming=王% select sno,sname,ssex,sage,sdept from student where sname like ming,例：创建一个局部变量，并赋一个任意字符串作为局部 变量的值。,declare char_var char(20) set char_var =hello,everybody! Select char_var as char_var变量值为,6.1.3 流程控制语句,流程控制语句是组织较复杂Transact-SQL语句的语法元素，在批处理、存储过程、脚本和特定的检索中使用。它们包括条件控制语句、无条件转移语句和循环语句等。,主要的流程控制语句： BEGINEND：定义语句块 IFELSE：若指定条件为真，执行一个分支，否则执行另一个分支 WHILE：当指定条件为真时重复一些语句 CASE：允许表达式按照条件返回不同的值 BREAK：退出最内层的WHILE循环 CONTINUE：重新开始WHILE循环 WAITFOR：为语句的执行设置延迟,1 BEGINEND语句块 BEGIN和END用来定义语句块，必须成对出现。它将多个SQL语句括起来，相当于一个单一语句，其语法格式如下。 BEGIN 语句1或语句块1 语句2或语句块2 END,2 IF.ELSE语句 IFELSE语句用来实现选择结构，其语法格式如下。 IF 布尔表达式 语句1或语句块1 ELSE 语句2或语句块2 ,3 WHILE语句 WHILE语句用来实现循环结构，其语法格式如下： WHILE 逻辑表达式 语句块 当逻辑表达式为真时，执行循环体，直到逻辑表达式为假。 BREAK语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。,例：若IF条件为真或为假时要执行的语句只有一条（默认时，一条语句就是一个语句块），则可以不使用BEGINEND。 DECLARE x int,y int,z int SET x=40 SET y=30 IF(x y) SET z= x - y ELSE SET z= y - x SET x=0 PRINT x PRINT y PRINT z,执行结果为 0 30 10,例：若希望条件为假时，在ELSE语句中执行其后续的两条语句，则必须使用BEGINEND将这两条语句包括起来，使其成为一个语句块。 DECLARE x int,y int,z int SET x=40 SET y=30 IF(x y) SET z= x - y ELSE BEGIN SET z= y - x SET x=0 END,PRINT x PRINT y PRINT z 执行结果为 40 30 10,例：计算1+2+3+100的和 DECLARE i int，sum int SET i=1 SET sum=0 WHILE i=100 BEGIN SET sum= sum+ i SET i= i +1 END PRINT sum,4、函数,SQL系统中提供了许多函数，用户可以利用这些函数完成特定的运算和操作。常用的函数包括：系统函数、字符串函数、日期和时间函数、数学函数、转换函数。除此之外用户还可以根据自己的需要利用CREATE FUNCTION命令创建函数。,、创建用户自定义的函数的语法形式：,CREATE FUNCTION function_name (parament_name as data_type) RETURNS return_data_type BEGIN Function_body RETURN expression END,说明： function_name:用户自定义函数的名称。 parament_name:用户自定义函数的参数。 data_type:参数的类型。 return_data_type:用户自定义函数的返回值类型。,、调用用户自定义函数的基本语法,database_name owner_name.function_name(argument_expr,),说明： argument_expr:表示实际参数。 在调用返回数值的用户自定义函数时，一定要在函数名的 前面加上用户名，否则会出现“函数名不是可以识别的函 数名”的错误提示信息。,例：创建函数sumsaler（）统计销售人员总数。,use sales go create function sumsaler() returns int begin return(select count(saleID) from salers) end 用SELECT语句调用sumsaler()函数。 select str(dbo.sumsaler()+人 as 销售员总数,例：创建函数fun1（）求两个整数之和。,create function fun1 (x as int, y as int) returns int begin return(x+y) end 用SELECT语句调用sumsaler()函数。 select str(dbo.fun1(10,20),6.2存储过程,存储过程（stored procedure）是一组事先编译好的Transact-SQL代码。存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。,使用存储过程的好处： 1、执行速度快。 2、模块化的程序设计。 3、减少网络通信量。 4、保证系统的安全性。,1使用T-SQL语句创建存储过程 创建存储过程使用CREATE PROC EDURE语句。 语法格式如下： CREATE PROCEDURE 存储过程名 参数名 数据类型=defaultOUTPUT ,n AS SQL语句,6.2.1存储过程的创建,Default：表示参数的默认值。 Output：表明参数是输出参数。 执行存储过程的SQL语句是EXECUTE，其语法格式为： EXECUTE存储过程名 实参，OUTPUT，n 存储过程可以嵌套，SQL Server 2000 最多可以允许嵌套32层存储过程。,例1：带有复杂查询的存储过程：查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。 CREATE PROCEDURE S_grade1 AS SELECT sname,cname,grade FROM Student s JOIN sc ON s.sno=sc.sno JOIN course c ON o=o WHERE Sdept=计算机系 执行此存储过程：EXEC S_grade1,例2：带有输入参数的存储过程：查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。 CREATE PROC student_grade2 dept char(20) AS SELECT Sname,sdept,cname,grade FROM student s JOIN sc ON s.sno=sc.sno JOIN course c ON o=o WHERE sdept=dept,注：当存储过程有输入参数并且没有为输入参数指定默认值时，在调用此存储过程时，必须要为此输入参数指定一个常量值。 执行例2的存储过程，查询信息系学生的修课情况： EXEC student_grade2 信息系,例3、带有多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，若没有指定课程，则默认为数据库。 CREATE PROC S_GRADE2 Sname char(20)，Cname char(20)= 数据库 AS SELECT Sname,Cname,Grade FROM Student ,Scourse,Course WHERE Sname= Sname and Cname= Cname and Student.Sno=Scourse.Sno And Course.Cno=Scourse.Cno,执行带多个参数的存储过程时，参数的传递方式有两种： 1、按参数位置传递：执行存储过程的EXEC语句中的实参的排列顺序必须与定义存储过程时定义的参数的顺序一致 EXEC S_GRADE2 张三， VB 2、按参数名传递：执行存储过程的EXEC语句中要指明定义存储过程时定义的参数的名字以及此参数的值，而不关心参数的定义顺序 EXEC S_GRADE2 Sname =张三， Cname =VB,例4、带有多个输入参数并均指定默认值的存储过程：查询指定系，指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值为计算机，默认的性别为男，默认的年龄为20。 CREATE PROC S_GRADE3 dept char(20)= 计算机 ， sex char(2)= 男，age int=20 AS SELECT * FROM Student WHERE Sdept = dept and Ssex = sex and Sage=age,例5、带有输出参数的存储过程：计算两个数得到积，将结果作为输出参数返回给调用者。 CREATE PROCEDURE PROC1 var1 int, var2 int, var3 int output AS SET var3 = var1 * var2 执行此存储过程的示例： DECLARE res int EXEC PROC1 5,7, res output PRINT res 结果为：35,例6、创建一个存储过程，向学生表中插入数据。 CREATE PROC insert_student sno char (10),sname char(10),ssex char (4),sage int,sdept char(20) as insert into student values (sno,sname,ssex,sage,sdept) 执行此存储过程 exec insert_student 1001,李明,男,20,计算机系,6.2.2查看存储过程信息,1使用T-SQL语句查看存储过程 （1）可以使用sp_helpText 命令查看创建存储过程的文本信息。例如： use master go sp_helptext sp_who go,系统返回信息是：,（2）可以使用sp_help查看存储过程的一般信息。例如： use master go sp_help proc1 go,6.2.3修改存储过程,SQL Server提供了在不改变存储过程使用许可和名字的情况下，对存储过程进行修改的语句。 语法格式为： ALTER PROCEDURE 存储过程名 参数名 数据类型=defaultOUTPUT ,n AS SQL语句 注：也可以使用企业管理器进行系统存储过程的修改。,例6、将例1定义的存储过程修改，使之能查询任何指定系的学生的修课情况。 ALTER PROC S_Grade1 dept char(20) AS SELECT Sname,Cname,Grade FROM Student JOIN Scourse ON Student.Sno=Scourse.Sno JOIN Course ON Course.Cno=Scourse.Cno WHERE Sdept =dept,6.2.5删除存储过程,1使用T-SQL语句删除存储过程 删除存储过程使用DROP PROCEDURE语句。 语法格式为： DROP PROCEDURE 存储过程名 ,n 例如： use student go drop procedure S_grade1 go,6.3触发器,触发器是一种特殊的存储过程，他不需要由用户调用执行，而是当用户对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的，触发器通常用于保证业务规则的数据完整性，其主要优点是用户可以用编程的方法来实现复杂的处理逻辑和商业规则，增强了数据完整性约束规则。,6.3.1创建触发器,1使用T-SQL语句创建触发器 创建触发器使用CREATE TRIGGER语句。 语法格式如下： CREATE TRIGGER 触发器名ON 表名 WITH ENCRYPTION FOR|AFTER|INSTEAD OF DELETE,INSERT,UPDATE AS SQL语句,其中： “触发器名称”必须是唯一的 ON子句用于指定在其上执行触发器的表 AFTER：指定触发器只有在引发触发器执行的SQL语句指定的操作都已成功执行，并且所有的引用级联操作和约束检查也成功完成后，才执行此触发器，也叫后触发性触发器 FOR 如果仅指定FOR关键字，则AFTER是默认设置 INSTEAD OF：指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。（注：只有SQL Server2000才支持）也叫前触发器,例1：创建带有提示信息的触发器，每当用户在course表中执行插入操作时，向用户产生一条提示信息。 create trigger tri_insert_course on course for insert as print 在course表中插入数据 只是为了提示信息用 执行如下语句测试触发器的作用： Insert into course(cno,cname) values(d01,数据库),例2、创建一个触发器，当向表student中修改一条记录时，自动显示student表中的记录。 CREATE TRIGGER change_display ON student FOR update AS SELECT * FROM student,例3、创建限制删除的触发器，限制删除SC表中不及格学生的成绩记录。 create trigger tri_del_grade on SC for delete as if exists(select * from DELETED where delete