数据库技术与应用第5章(Transact-SQL、存储过程等).ppt

1,本章主要内容:T-SQL的基本语法 存储过程(难点)触发器(难点),第5章 Transact-SQL、存储过程和触发器,2,5.1 Transact-SQL语言,5.1.1 数据类型,数据类型见表5-1。下面做一些必要的说明。1.整型(Bigint、Int、Smallint、tinyint)整型数参与任何算术运算的结果只保留数值的整数部分。DECLARE n1 int,n2 int,n3 int-变量的声明SET n1=14SET n2=3SET n3=n1/n2-n3=4SELECT n1 AS n1,n2 AS n2,n3 AS n3-起别名,3,5.1.1 数据类型,2.浮点型(float、real、Decimal、Numerc)float和real属于近似数据类型。特点是表示范围大,但任何一个数都仅是一个近似值。适合用于科学计算。Decimal和Numerc是精确数值类型，表示的是一个精确值。适合用于财务金融（如：工资）。,5.1.1 数据类型,4,5.1.1 数据类型,Decimal和Numeric的表示范围和精度及所占内存都一样，为与ANSI标准兼容SQL Server支持这两种表示。Decimal(p,s):p表示总位数(不包括小数点)，介于138之间，默认值为18。s表示小数点后的位数，默认值是0。参数间关系：0=sp。注：最好在WHERE子句中不要引用float类型的字段。,5.1.1 数据类型,5,5.1.1 数据类型,3.字符型 Char、Varchar、Text；Nchar、Nvarchar、Ntext（双字节字符型）Text 和Ntext用于存储大块文字。,4.Unicode Unicode是双字节编码标准，一个字符占2个字节。,5.1.1 数据类型,6,5.1.1 数据类型,5.日期时间型Datetime、Smalldatetime 日期格式有：X/Y/Z X-Y-X X.Y.Z XYZ 中XYZ可以是年月日或月日年，XYZ只能是年月日。,declare d1 datetimeset d1=19881001select d1,5.1.1 数据类型,7,5.1.1 数据类型,6.货币数据类型Money、SmallMoney相当于带货币符号的Decimal类型。精确到0.0001货币单位。Money:存储 8 字节，范围 922,337,203,685,477.5808 到 922,337,203,685,477.5807smallmoney:存储4 字节，范围 214,748.3648 到 214,748.3647,5.1.1 数据类型,8,5.1.1 数据类型,7.Bit位类型（也称 逻辑类型）可以取值为 1、0 或 NULL 的整数数据类型。用0表示false，1表示true。特点：(1)不允许建索引；(2)几个位字段可以占用同一个字节。,5.1.1 数据类型,9,5.1.1 数据类型,8.二进制类型（Binary、Varbinary、image）存储二进制数据。常用于存储音频视频图像等字节流式数据。数据表字段中仅存放指针，指向数据实际存放地址。,5.1.1 数据类型,10,5.1.1 数据类型,9.其他类型（Sql_variant、table）Sql_variant:可存储SQL Server支持的所有数据类型(text、ntext、timestamp和Sql_variant除外)值的数据类型。方便SQL Server的开发工作。Table:用于存储对表或视图处理后的结果集。只能用于Transact-SQL用户自定义函数的返回值。,5.1.1 数据类型,11,5.1.2 标识符、变量和运算符,1.标识符 标识符是指用户定义的数据库对象的名称以及变量名称。分为标准标识符和定界标识符。命名规则：长度为1128个字符,不区分大小写字母。可由：汉字、字母、数字、_、$、#字符构成。,12,5.1.2 标识符、变量和运算符,以符号开头的标识符有专门的用途：以开始的标识符，表示局部变量或参数。以#开始的标识符，表示是局部数据库对象。例如，局部临时表或过程等。以两个#开始的标识符，表示全局临时对象。,5.1.2 标识符、变量和运算符,13,5.1.2 标识符、变量和运算符,2.变量 变量分为局部变量和全局变量。(1)局部变量用户定义,在DECLARE语句中声明在SET或SELECT语句中赋值,局部变量声明:DECLARE 变量名 数据类型,变量名 数据类型,5.1.2 标识符、变量和运算符,14,5.1.2 标识符、变量和运算符,SET 变量名1=表达式|(SELECT 子查询),SELECT 变量名1=表达式1|(SELECT 子查询),变量名2=表达式|(SELECT 子查询),SELECT 变量名1=字段表达式1,变量名2=字段表达式2 FROM 表名 WHERE 条件-查询赋值，与查询语句结合使用,局部变量赋值:,或(一条语句为多个变量赋值),或,5.1.2 标识符、变量和运算符,15,5.1.2 标识符、变量和运算符,说明：当子查询结果为多个值时，保留最后那个值赋给变量。如果“子查询”没有返回值，则局部变量值为NULL。一个SET语句只能为一个变量赋值，而一个SELECT语句可为多个变量赋值。,5.1.2 标识符、变量和运算符,16,5.1.2 标识符、变量和运算符,5.1.2 标识符、变量和运算符,17,5.1.2 标识符、变量和运算符,例2：定义变量score、sno、cno,类型分别为 int、char、char,并赋值给它们。,DECLARE score int，sno char(6)DECLARE cno char(6)-变量声明SELECT sno=060111,cno=0002-变量赋值SELECT score=成绩-查询赋值 FROM 选课 WHERE 学号=sno and 课号=cnoSELECT score AS 成绩-查询变量值,5.1.2 标识符、变量和运算符,18,5.1.2 标识符、变量和运算符,(2)全局变量 系统提供、系统赋值 值报告系统活动信息和连接信息 可以在脚本中引用全局变量由SQL Server系统定义和维护，用户不能给全局变量赋值或直接更改其中的值。全局变量的名字前有两个,即。,5.1.2 标识符、变量和运算符,19,5.1.2 标识符、变量和运算符,全局变量对于检查SQL Server环境的某些信息或条件是有用的。例如：查看SQL Server版本号。SELECT version,5.1.2 标识符、变量和运算符,例如：trancount返回当前活动的事务数。IF(trancount0)PRINT 有事务在运行 ELSE PRINT 无事务在运行,20,3.运算符运算符的类型：算术运算符 比较运算符 字符串连接运算符 逻辑运算符,5.1.2 标识符、变量和运算符,5.1.2 标识符、变量和运算符,21,(1)算术运算符：+、-、*、/、%(求余)(2)比较运算符:=、(不等于)、=、,5.1.2 标识符、变量和运算符,(3)字符串连接运算符：。例如：print asd+dfg+1234 结果为：asddfg1234 空串()作为单个空格处理。例如：abc+efg，结果为abc egf.,5.1.2 标识符、变量和运算符,22,5.1.2 标识符、变量和运算符,(4)逻辑运算符（见表5-6）,5.1.2 标识符、变量和运算符,23,5.1.2 标识符、变量和运算符,表5-6(续),5.1.2 标识符、变量和运算符,24,(5)运算符优先级 括号算术运算符(字符串连接符)比较运算符逻辑运算符赋值（由高到低）,5.1.2 标识符、变量和运算符,5.1.2 标识符、变量和运算符,25,SQL Server提供了非常丰富的函数供用户使用，同时也允许用户定义自己的函数。,5.1.3 函数,5.1.3 函数,26,1.Transact-SQL提供的系统函数有：,5.1.3 函数,5.1.3 函数,27,函数分为三大类：行集函数（也称表值函数）可以用在SQL语句中“表”出现的任何地方引用行集函数。聚合函数(第4 章介绍过)：也称计合函数。,5.1.3 函数,标量值函数：最常用的一类函数。通过有参或无参调用它，可以使其执行并返回单个值。,5.1.3 函数,28,5.1.3 函数,系统提供的函数，参考教材。不再例举。,5.1.3 函数,29,5.1.3 函数,2.用户自定义函数用户可以根据应用需要定义自己的函数。自定义函数分三种：标量函数、内嵌表值函数、多语句表值函数。下面我们介绍前2种。(1)标量函数标量函数指函数返回单个值(字符串数值等).语法格式：见下页。,5.1.3 函数,30,5.1.3 函数,CREATE FUNCTION 函数名（形式参数定义表）RETURNS 函数返回值数据类型ASBEGIN 函数体 RETURN 返回值表达式 END,5.1.3 函数,31,5.1.3 函数,例3 创建一个标量函数，返回某学生的平均分数。学号作为函数参数。,5.1.3 函数,CREATE FUNCTION get_avg(sno char(6)RETURNS int ASBEGIN DECLARE temp int SELECT temp=AVG(成绩)FROM 选课 WHERE 学号=sno RETURN temp END,32,5.1.3 函数,可以用下列语句调用get_avg函数：SELECT dbo.get_avg(030101)AS 030101平均成绩注意：调用时必须给出用户名dbo。它是函数创建者。,(2)内嵌表值函数 内嵌表值函数指 函数返回值是一个表.语法格式：见下页。,5.1.3 函数,33,5.1.3 函数,CREATE FUNCTION 函数名（形式参数定义表）RETURNS TABLE ASRETURN(SELECT查询语句),函数返回值是一个查询表。,5.1.3 函数,34,5.1.3 函数,例4 创建一个表值函数，返回平均分数大于或等于指定分数的学生学号和平均分数。函数参数为给定的一个“分数”。,5.1.3 函数,CREATE FUNCTION get_all_avg(score int)RETURNS TABLE ASRETURN SELECT 学号,AVG(成绩)AS 平均成绩 FROM 选课 GROUP BY 学号 HAVING AVG(成绩)=score,注意：表达式AVG(成绩)必须给出别名。,35,5.1.3 函数,函数的调用：查询平均成绩大于80分的学生信息。SELECT 学生.学号,姓名,平均成绩FROM get_all_avg(80),学生 WHERE get_all_avg.学号=学生.学号,5.1.3 函数,36,5.1.3 函数,(3)删除用户自定义函数 DROP FUNCTION 函数名(4)修改用户自定义函数 ALTER FUNCTION 函数名 参数定义与代码,5.1.3 函数,37,流程控制语句用于控制SQL语句、语句块、存储过程或触发器的执行流程。主要的流程控制语句有：见下页表。,5.1.4 流程控制语句,5.1.4 流程控制语句,38,5.1.4 流程控制语句,5.1.4 流程控制语句,39,其他Transact-SQL语句还有：(1)/*/:注释语句,用于多行注释(2)-(注释语句):用于单行或嵌套注释(3)DECLARE：变量声明语句。(4)EXECUTE：存储过程执行语句。(5)PRINT:终端输出语句,5.1.4 流程控制语句,5.1.4 流程控制语句,40,1.BEGIN END语句 该语句将多条SQL语句封装在一起，构成一个语句块。主要语句块就可以在IF/ELSE、WHILE等语句中作为一个整体来执行。语法格式：,5.1.4 流程控制语句,BEGIN 若干SQL语句END,5.1.4 流程控制语句,41,5.1.4 流程控制语句,2.IFELSE语句 语法格式：,IF 条件 语句|语句块1 ELSE 语句|语句块2,5.1.4 流程控制语句,42,5.1.4 流程控制语句,例5 查询选修0001课的学生成绩，如有大于90分以上的，则将其姓名显示出来；若无人大于90分，则显示“成绩优秀者为0个”。,IF EXISTS(SELECT*FROM 选课 WHERE 课号=0001 AND 成绩=90)SELECT 姓名 FROM 学生 JOIN 选课 ON 学生.学号=选课.学号 WHERE 课号=0001 AND 成绩=90ELSE PRINT 成绩优秀者为0个,5.1.4 流程控制语句,43,3.WHILE语句语法格式：,5.1.4 流程控制语句,5.1.4 流程控制语句,44,5.1.4 流程控制语句,例6 引用已建函数get_all_avg，分别求出平均成绩大于60、70、80、90的学生成绩信息。,5.1.4 流程控制语句,45,5.1.4 流程控制语句,DECLARE j intSET j=60WHILE j100BEGIN SELECT*,j as 平均成绩 大于 FROM get_all_avg(j)SET j=j+10END,5.1.4 流程控制语句,46,*4.WAITFOR语句WAITFOR语句可以指定在某一时间点或时间间隔后执行SQL语句、语句块、存储过程或事务。,5.1.4 流程控制语句,语法格式：WAITFOR DELAY time|TIME time,time 格式为：hh:mm:ss。,5.1.4 流程控制语句,47,例7 对学生选课关系(1)设置在9:00执行一次查询操作；(2)再设置在2小时以后再执行一次查询操作，查看学生选课情况。,5.1.4 流程控制语句,BEGIN WAITFOR TIME 9:00-等到9点 SELECT 学号,课号 FROM 选课ENDGOBEGIN WAITFOR DELAY 2:00-延迟2小时 SELECT 学号,课号 FROM 选课END,5.1.4 流程控制语句,48,5.RETURN语句RETURN无条件退出语句。可在任何时候用于从过程、批处理或语句块中退出。不执行位于 RETURN 之后的语句。,5.1.4 流程控制语句,格式：RETURN 整数表达式,RETURN语句可以返回整数值。一般用于表示存储过程或应用程序的执行状态。如，所有系统存储过程返回 0 值表示成功，返回非零值则表示失败。注意：当用于存储过程时，RETURN 不能返回空值。,5.1.4 流程控制语句,49,6.CASE 表达式CASE 表达式用于多分支结构，有两种语法格式。(1)简单CASE表达式,5.1.4 流程控制语句,CASE 表达式WHEN 表达式1 THEN 结果表达式1 WHEN 表达式n THEN 结果表达式nELSE 结果表达式n+1END,5.1.4 流程控制语句,50,说明：可以是常量、属性名、函数、子查询和算术运算符、字符串运算符等组合的有意义的式子。是在多个中选择一个表达式，它的值作为“CASE表达式”的结果值。当所有匹配都不成立，CASE则返回NULL。可以只有 一个WHEN子句。是一个表达式，可以用在可以出现的任何地方。,5.1.4 流程控制语句,5.1.4 流程控制语句,51,5.1.4 流程控制语句,5.1.4 流程控制语句,52,例8 将百分制分数按优、良、中、及格和不及格五个等级记分制的形式输出。,5.1.4 流程控制语句,SELECT 学号,课号,成绩=CASE 成绩/10WHEN 6 THEN 及格WHEN 7 THEN 中WHEN 8 THEN 良WHEN 9 THEN 优WHEN 10 THEN 优ELSE 不及格ENDFROM 选课,说明：整数除整数得到整数（系统自动取整）验证：Select 10/2,5.1.4 流程控制语句,53,(2)搜索型CASE表达式,5.1.4 流程控制语句,CASEWHEN 条件表达式1 THEN 结果表达式1WHEN 条件表达式n THEN 结果表达式nELSE 结果表达式n+1END,功能：得到一个满足条件的值。如下图所示。,5.1.4 流程控制语句,54,5.1.4 流程控制语句,5.1.4 流程控制语句,55,例9 将百分制分数按优、良、中、及格和不及格五个等级记分制的形式输出。,5.1.4 流程控制语句,SELECT 学号,课号,成绩=CASE WHEN 成绩/10=6 THEN 及格 WHEN 成绩/10=7 THEN 中 WHEN 成绩/10=8 THEN 良 WHEN 成绩/10=9 THEN 优 WHEN 成绩/10=10 THEN 优 WHEN 成绩/10 IS NULL THEN 无成绩 ELSE 不及格 ENDFROM 选课,5.1.4 流程控制语句,56,7.批处理和脚本(1)批处理批处理是作为一个组一起提交并执行的若干Transact-SQL语句。批处理可以交互地运行，或作为脚本的一部分运行。一个脚本可以包含多个Transact-SQL批处理。,5.1.4 流程控制语句,5.1.4 流程控制语句,57,使用GO语句定义一个批处理。使用GO语句表示一个批处理的结束。GO 不是通用的Transact-SQL语句，是只有SQL Server查询分析器和osql实用程序接受的语句。,5.1.4 流程控制语句,5.1.4 流程控制语句,58,SQL Server如何处理批处理 SQL Server统一优化、编译和执行一个批处理中的语句，但是，这些语句并不构成一个事务，不必作为一个可恢复单元执行。用户定义的变量作用域限制在一个批处理，所以变量不能在GO语句后引用。,5.1.4 流程控制语句,5.1.4 流程控制语句,59,例如：USE 教学数据库GOCREATE VIEW 成绩_V1(学号,平均成绩)AS SELECT 学号,AVG(成绩)FROM 选课 GROUP BY 学号 GOSELECT*FROM 成绩_V1GO,5.1.4 流程控制语句,5.1.4 流程控制语句,60,批处理规则CREATE PROCEDURE、CREATE VIEW、CREATE TRIGGER、CREATE RULE、CREATE DEFAULT这些语句不能在一个批处理中联合使用，并且也不能与其他语句联合使用。即在一个批处理中，只能有一个CREATE语句，其他语句都只能视为该语句的的一部分。,5.1.4 流程控制语句,5.1.4 流程控制语句,61,不能在一个批中修改表结构，然后在同一个批中引用刚修改的列。如果EXECUTE语句是批处理的第1句，则可以省略，否则执行任何不是批处理中第1句的存储过程都必须包含EXECUTE关键字。一个批处理中用到的变量必须在其中定义，不能引用其他批处理中定义的变量。,5.1.4 流程控制语句,5.1.4 流程控制语句,62,(2)脚本 脚本是作为一个文件保存的一个或多个Transact-SQL语句。脚步可以在SQL Server分析器或任何文本编辑器中编写和保存，以.sql为扩展名。脚本必须在SQL Server分析器中执行。脚本主要用于：重新创建数据库或数据对象，或重复执行一些语句。,5.1.4 流程控制语句,5.1.4 流程控制语句,63,5.2.1 存储过程的概念,1.存储过程的定义,存储过程是存放在服务器上的预先编译好的一组完成特定功能的Transact-SQL语句集。,5.2 存储过程,5.2.1 存储过程的概念,64,5.2.1 存储过程的概念,2.存储过程的特点 使用存储过程可以减少网络数据流量。,用户不是在网络上发送几百条SQL语句，而是通过发送一条调用过程语句来执行一个复杂的操作，这样减少了服务器和客户机之间传递的请求数。,存储过程的概念,增强代码的重用性和共享性。存储过程可以被多个应用程序多次调用，实行代码的重用性和共享性。,65,5.2.1 存储过程的概念,使用存储过程可以加快系统的运行速度。存储过程第一次执行后，执行计划就被存放在高速缓存中。以后执行时，不必再执行优化处理，从而加快了系统运行速度。,5.2.1 存储过程的概念,存储过程提高了数据的安全性。系统管理员可以只授权用户访问存储过程的权限，而不授予用户访问存储过程中涉及的到的表的权限。从而有效地保护了数据表中存储过程涉及不到的数据的安全性。,66,5.2.1 存储过程的概念,3.存储过程的分类 系统存储过程 系统存储过程(由前缀sp_标识)存放在master数据库中。可以作为命令直接执行。其中大部分存储过程都可以在任何用户数据库中执行。用户存储过程：用户自定义的存储过程。,5.2.1 存储过程的概念,67,5.2.1 存储过程的概念,扩展存储过程 扩展存储过程(主要由前缀xp标识)是指在SQL Server环境外部执行的DLL。但是，它们可以被加载到SQL Server系统中，并按照存储过程方式执行。远程存储过程远程存储过程是指从远程服务器上调用的存储过程。,5.2.1 存储过程的概念,68,5.2.2 存储过程创建和执行,1.创建存储过程语句,CREATE PROCEDURE 存储过程名;num 参数1 数据类型=默认值 OUTPUT,其他参数 WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION ASBEGIN SQL语句系列END,5.2.2 存储过程创建和执行,69,5.2.2 存储过程创建和执行,说明;num：可选的整数。用来对同名若干过程编号，如 proc_s;1,proc_s;2,proc_s;3等。采用相同名字加编号区别若干过程的优点是删除时方便，如，DROP PROCEDURE proc_s一条命令把3个过程都删了。,5.2.2 存储过程创建和执行,70,5.2.2 存储过程创建和执行,default：参数的默认值。如果定义了默认值，不必指定该参数的值就可调用。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符(%、_、)。OUTPUT：表示该参数是返回参数。参数可将信息返回给调用过程。Text、ntext、Image参数可用作OUTPUT参数。,5.2.2 存储过程创建和执行,71,5.2.2 存储过程创建和执行,RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION RECOMPILE：表明该过程在运行时重新编译。ENCRYPTION：表示系统加密syscomments表中包含CREATE PROCEDURE语句文本的条目。加密后即使是过程创建者 本人也无法查看过程定义文本。,5.2.2 存储过程创建和执行,72,5.2.2 存储过程创建和执行,例如：USE 教学数据库GOCREATE PROC dbo.proc_sASSELECT*FROM 学生 WHERE 所属系 LIKE 计算机%GO,5.2.2 存储过程创建和执行,73,5.2.2 存储过程创建和执行,2.执行存储过程语句,EXECUTEreturn_status=存储过程名;number|存储过程名变量参数1=值1,参数n=值n|变量 OUTPUT|DEFAULT WITH RECOMPILE,5.2.2 存储过程创建和执行,74,5.2.2 存储过程创建和执行,参数说明:return_status,为一整型变量,用于保存存储过程的返回状态值。存储过程名变量：表示存储过程名称的变量。,5.2.2 存储过程创建和执行,变量 output default:用来保存过程中的 OUTPUT参数的返回值。DEFAULT提供参数的默认值,75,5.2.2 存储过程创建和执行,3.创建无参数的存储过程例10 创建一个存储过程。完成显示所有学生的平均成绩。,CREATE PROC SC_proc;1 AS SELECT 学号,AVG(成绩)as 平均成绩 FROM 选课 GROUP BY 学号,5.2.2 存储过程创建和执行,76,5.2.2 存储过程创建和执行,4.创建带有输入参数的存储过程例11 创建一个存储过程。显示指定学号的学生的平均成绩。学号作为参数.,CREATE PROC SC_proc;2sno char(6)AS SELECT 学号,AVG(成绩)as 平均成绩 FROM 选课WHERE 学号 LIKE sno GROUP BY 学号,5.2.2 存储过程创建和执行,77,5.2.2 存储过程创建和执行,调用存储过程sc_proc;2方法1:declare temp1 char(6)set temp1=030101 exec 教学数据库.dbo.sc_proc;2 temp1方法2:exec sc_proc;2 030101,5.2.2 存储过程创建和执行,78,5.2.2 存储过程创建和执行,5.创建带有输出参数的存储过程例12 创建存储过程。完成求指定学号的学生的平均成绩，并将该成绩保存在变量avg中。,CREATE PROC SC_proc;3sno char(6),avg int OUTPUT ASSELECT avg=AVG(成绩)FROM 选课WHERE 学号=sno GROUP BY 学号,5.2.2 存储过程创建和执行,79,5.2.2 存储过程创建和执行,执行存储过程SC_proc;3:DECLARE temp CHAR(6),avg_out INTSET temp=030101EXEC SC_proc;3 temp,avg_out OUTPUTPRINT 030101的平均成绩为:+CAST(avg_out AS CHAR(3),5.2.2 存储过程创建和执行,80,5.2.2 存储过程创建和执行,6.返回存储过程的执行状态每个存储过程的执行,都将自动返回一个整数状态值,用于告诉用户调用存储过程的执行情况。调用程序可以根据返回值作相应的处理。用户可以用大于0或-1-99的来定义自己的返回状态值，以表示不同的执行结果。一般而言，0表示存储过程执行成功，-1-99之间的数表示过程执行失败。,5.2.2 存储过程创建和执行,81,5.2.2 存储过程创建和执行,例13 检查给定学号的学生有无不及格的记录，有则返回5,无则返回0并输出该学生的学习记录。如果没有提供参数学号，则返回15.,CREATE PROC SC_PROC;4sno CHAR(6)=NULLASIF sno IS NULL RETURN 15/*未给出该生学号*/,创建返回执行状态的存储过程,5.2.2 存储过程创建和执行,82,5.2.2 存储过程创建和执行,ELSE IF EXISTS(SELECT*FROM 选课 WHERE 学号=sno AND 成绩60)RETURN 5/*该生有不及格成绩时*/ELSE BEGIN SELECT*FROM 选课 WHERE 学号=sno RETURN 0/*该生无不及格，返回其选课信息*/END,ELSE IF EXISTS(SELECT*FROM 选课 WHERE 学号=sno AND 成绩60)RETURN 5/*该生有不及格成绩时*/ELSE BEGIN SELECT*FROM 选课 WHERE 学号=sno RETURN 0/*该生无不及格，返回其选课信息*/END,ELSE IF EXISTS(SELECT*FROM 选课 WHERE 学号=sno AND 成绩60)RETURN 5/*该生有不及格成绩时*/ELSE BEGIN SELECT*FROM 选课 WHERE 学号=sno RETURN 0/*该生无不及格，返回其选课信息*/END,5.2.2 存储过程创建和执行,83,5.2.2 存储过程创建和执行,执行：（学号取030101或060111，各执行一次；学号未给出，再执行一次）DECLARE return_status INTEXEC return_status=sc_proc;4 030101IF return_status=15 PRINT 缺少输入参数-学号ELSE IF return_status=5 PRINT 该生有不及格成绩记录!Else PRINT 该生无不及格成绩!,5.2.2 存储过程创建和执行,84,5.2.3 存储过程的处理,存储过程的处理,5.2.3 存储过程的处理,85,5.2.3 存储过程的处理,存储过程的初次处理包括：创建存储过程和预编译。创建存储过程：存储过程被创建时，查询处理器对该过程进行语法分析，检查是否合乎语法规范，通过语法检查后将该存储过程的源代码存放到当前数据库的系统表syscomments中，同时在sysobjects中登记该存储过程的名字。注：绝不要直接删除系统表syscomments中的项。,5.2.3 存储过程的处理,86,5.2.3 存储过程的处理,2.预编译(或再编译)存储过程第1次执行时，SQL Server查询优化器分析存储过程中的SQL语句，并创建含有快速访问数据的计划。在查询优化器把编译的计划放在存储过程高速缓存中后，系统执行存储过程。,5.2.3 存储过程的处理,87,5.2.3 存储过程的处理,重编译 在某些应用中,数据库的逻辑结构可能发生改变（如属性列被修改）或新增了索引等，这样存储过程的查询计划就可能不是最优的，有必要重新编译，以便获得新的优化的查询计划。重编译有三种方法。(1)在创建存储过程时采用RECOMPILE CREATE PROC WITH RECOMPILE这样，每次执行时，系统都会重新编译优化。,5.2.3 存储过程的处理,88,5.2.3 存储过程的处理,(2)在执行存储过程时采用RECOMPILE EXEC 存储过程名 参数表 WITH RECOMPILE 在执行存储过程时，重新编译优化。(3)调用系统存储过程sp_recompile 命令:sp_recompile 表名运行该命令的结果是指定表的存储过程在下一次运行时将被重新编译.,5.2.3 存储过程的处理,89,5.2.4 存储过程的查看、修改和删除,1.查看存储过程方法1：可以通过系统存储过程来查看用户定义的存储过程。,Sp_helptext 存储过程名,5.2.4 查看、修改和删除存储过程,90,5.2.4 存储过程的查看、修改和删除,方法2：查询分析器中，找到所要查看的存储过程，单击鼠标右键，打开快捷菜单，选择“编辑”菜单命令。,5.2.4 查看、修改和删除存储过程,91,5.2.4 存储过程的查看、修改和删除,方法3：如果想查看存储过程的一般信息，可使用：Sp_help 存储过程名,查看、修改和删除存储过程,92,5.2.4 存储过程的查看、修改和删除,2.修改存储过程（建议可视化操作完成）,在查询分析器中使用下列命令：ALTER PROC OWNER.存储过程名,可视化操作：选定存储过程，在其上打开快捷菜单，选择“编辑”菜单命令，打开编辑窗口。修改完成后，再重新执行一次即可。,或,查看、修改和删除存储过程,93,*5.2.5 系统存储过程,常用的系统存储过程如下表,5.2.5 系统存储过程,94,*5.2.5 系统存储过程,5.2.5 系统存储过程,95,*5.2.5 系统存储过程,例：查看与选课表相关的所有对象。sp_depends 选课,5.2.5 系统存储过程,96,5.3 触发器,SQL Server提供了两种主要机制来强制实施数据库的完整性：约束和触发器。触发器可以实现比check约束更为复杂的完整性约束。,5.3 触发器,5.3 触发器,97,触发器：是一种特殊的存储过程，在用户试图更新触发器保护的数据时自动执行。触发器:(1)总是与一个表相连。触发器是在单个表或视图上定义，这个表称为触发器表。每个表上可以建若干触发器。(2)是自动激活的。当对一个表中数据执行插入、删除、修改操作时，如果对该表上这个操作定义了触发器，则该触发器自动执行，这是不可阻挡的。,5.3.1 什么是触发器,5.3 触发器,98,(3).不能直接调用与标准的存储过程不同，触发器不能直接调用，也不能传递或接受参数。(4).是一个事务触发器和激活它的语句作为一个事务处理，可以从触发器中的任何位置撤销。触发器可以包括ROLLBACK TRANSACTION语句，激活触发器的语句可以看成隐含事务的开始。,5.3.1 什么是触发器,5.3 触发器,99,Deleted和inserted 表:当用户对触发器表执行更新操作时，SQL Server自行为每个触发器创建和管理这两个表。它们是逻辑表，存放在内存中，用户不能直接对这两个表进行修改。这两个表的结构与触发器表的结构相同。触发工作完成后，与触发器相关的这两个表将被删除。,5.3.1 什么是触发器,5.3 触发器,100,1.inserted 表inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。inserted表中的行是触发器表中新行的副本。,5.3.1 什么是触发器,5.3 触发器,101,2.deleted 表deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传送到deleted 表，deleted 表和触发器表一般没有相同的行。,5.3.1 什么是触发器,5.3 触发器,102,5.3.2 创建触发器,1.语法格式CREATE TRIGGER 触发器名ON 表-给定建立触发器的表WITH ENCRYPTION-加密存储触发器定义FOR|AFTER|INSTEAD OF 触发时间INSERT,DELETE,UPDATE-触发事件AS 触发器要完成的操作-触发后的动作,5.3.1 创建触发器,103,5.3.2 创建触发器,说明：AFTEER：在触发操作执行完后触发。FOR关键字：与AFTER相同。INSERTED OF：代替触发动作(触发语句)进行操作，并在处理约束之前触发。WITH ENCRYPTION：加密存储触发器存放在syscomments中的文本。,5.3.1 创建触发器,104,5.3.2 创建触发器,注意：创建触发器的权限默认为表的所有者，且不能将该权限转让给其它用户。不能在临时表上创建触发器，但在触发器中可引用临时表。触发器允许嵌套。最大嵌套数32。,5.3.1 创建触发器,105,触发器中不允许有下列语句：ALTER DATABASE,CREATE DATABASE,DISK INIT,DISK RESIZE,DROP DATABASE,LOAD DATABASE等等。,5.3.2 创建触发器,5.3.1 创建触发器,106,5.3.2 创建触发器,2.INSERT触发器插入触发器的执行过程：首先执行INSERT语句的插入操作。将新行插入到触发器表和inserted表中。然后执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。,5.3.1 创建触发器,107,5.3.2 创建触发器,例14 建立一个INSERT触发器。每当在“选课”表中插入一条记录时，检查学号在学生表中是否存在，若不存在，拒绝插入，否则允许。,5.3 触发器,108,5.3.2 创建触发器,CREATE TRIGGER sc_insertON 选课 FOR INSERT AS BEGIN DECLARE sno CHAR(6)Select sno=学号 FROM inserted IF NOT EXISTS(SELECT*FROM 学生 WHERE 学号=sno)BEGIN PRINT 该生不存在！ROLLBACK TRAN END END,5.3 触发器,109,5.3.2 创建触发器,测试：INSERT INTO 选课(学号,课号,成绩)VALUES(000001,011,88),5.3 触发器,110,5.3.2 创建触发器,create trigger s_sno_numeric on dbo.学生 for insert,update asbegin decl