存储过程-触发器-函数

表，来确定该触发器的操作是否应该执行和如何执行。
④在inserted表中的记录，总是触发器表中一行或多行记录的 冗余。
第29页
DELETE触发器

当触发一个DELETE触发器时，被删除的记录放 在一个特殊的deleted表中。

deleted表是一个逻辑表，用来保存已经从表中删
除的记录。 该deleted表允许参考原来的DELETE语句删除的 已经记录在日志中的数据。
创建存储过程
在创建存储过程之前应该考虑到以下几个方面：
1．在一个批处理中Create Procedure 语句不能与其它
SQL 语句合并在一起。 2．数据库所有者具有默认的创建存储过程的权限它可把 该权限传递给其它的用户。 3．存储过程作为数据库对象其命名必须符合命名规则。 4．只能在当前数据库中创建属于当前数据库的存储过程
4.执行存储过程Query_Study (含有输出参数)
DECLARE @sn char(20) DECLARE @dept char(10) EXEC Query_Study 'S10',@sn OUTPUT,@dept OUTPUT Print @sn --或者select @sn print @dept
第8章 存储过程、触发器 和函数
本章概述 本章要点 本章内容
第1页
本章概述




如何提高Transact-SQL语句的执行效率呢？如何加强数据 库中数据完整性的机制呢？这些问题的解决都依赖于数据库 的编程对象。典型的数据库编程对象包括视图、存储过程、 触发器、函数等。 存储过程是一个可重用的代码模块，可以高效率地完成指定 的操作。 触发器是一种特殊类型的存储过程，可以实现自动化的操作。 用户定义函数是由用户根据应用程序的需要而定义的可以完 成特定操作的函数。 本章将全面介绍存储过程、触发器、用户定义函数等特点和 使用方式。

在执行触发器操作过程中，如果检测到错误发生，则整个 触发事件语句和触发器操作的事务自动回滚。
第24页
DML触发器的类型

按照触发器事件类型的不同，可以把MSSQL Server 2008系统提供的DML触发器分成3种类型， 即 INSERT类型 UPDATE类型 DELETE类型
第25页
创建DML触发器
FOR INSERT,UPDATE,DELETE
AS SELECT * FROM student

该触发器建立完毕后，当执行如下操作时将会显示数据表s 中的全部记录。
EXEC InsertRecordDefa @sno = 'S11', @sn = '张建峰', @age = 17, @sex = '男'

在创建存储过程时，应该指定所有的输入参数、执行数据 库操作的编程语句、返回至调用过程或批处理表明成功或 失败的状态值、捕捉和处理潜在错误的错误处理语句。
第8页
创建存储过程

创建存储过程，需确定存储过程的三个组成部分:
1．所有的输入参数以及传给调用者的输出参数 2．被执行的针对数据库的操作语句包括调用其它 存储过程的语句 3．返回给调用者的状态值以指明调用是成功还是 失败

第30页
UPDATE触发器

修改一条记录就等于插入一条新记录同时删除一条旧记 录。
UPDATE语句也可以看成是由删除一条记录的DELETE 语句和增加一条记录的INSERT语句组成。 当在某一个有UPDATE触发器表的上面修改一条记录时， 表中原来的记录移动到deleted表中，修改过的记录插 入到了inserted表中。 触发器可以检查deleted表和inserted表以及被修改的表， 以便确定是否修改了多个行和应该如何执行触发器的操 作。
第3页
本章内容
8.1 8.2 8.3 8.4 存储过程 触发器 用户定义函数 本章小结
第4页
8.1 存储过程

存储过程可以提高应用程序的设计效率和增强系 统的安全性。本节将全面介绍存储过程的特点、
类型、创建、执行等内容。
第5页
存储过程的特点和类型

存储过程是一个可重用的代码模块，可以高效率
地完成指定的操作。
第2页
本章要点

存储过程的特点、类型和作用


使用CREATE PROCEDURE语句创建存储过程
存储过程的执行方式 DML触发器的特点和创建方式 DML触发器的工作原理 使用CREATE TRIGGER语句创建DML触发器 DDL触发器的特点和创建方式 用户定义函数的类型和特点 使用CREATE FUNCTION语句创建用户定义函数
CREATE TRIGGER trigger_name
ON table_name | view_name
WITH ENCRYPTION { FOR | AFTER | INSTEAD OF } {[DELETE] [ , ] [ INSERT ] [ , ] [ UPDATE ] } AS sql_statement
使用sp_helptext系统存储过程
•使用sp_helptext系统存储过程查看定义的 存储过程信息
•exec sp_helptext ‘存储过程名’
第28页
DML触发器的工作原理
①向表中插入数据时，INSERT触发器触发执行。
②当INSERT触发器触发时，新的记录增加到触发器表中和 inserted表中。 ③ inserted表是一个逻辑表，保存了所插入记录的备份，允 许用户参考INSERT语句中数据。触发器可以检查inserted
存储过程的执行过程

存储过程创建之后，在第一次执行时需要 经过语法分析阶段、解析阶段、编译阶段 和执行阶段。
第20页
查看存储过程的信息

在Microsoft SQL Server 2008系统中，可 以使用系统存储过程和目录视图查看有关
存储过程的信息。
第21页
8.2 触发器

Microsoft SQL Server 2008系统提供了两 种强制业务逻辑和数据完整性的机制，即 约束技术和触发器技术。
2.执行存储过程InsertRecord (带参调用)
EXEC InsertRecord ‘S1’, ‘王大利’, ‘男’, 18
,‘计算机系’ 或者：把值放入变量中，使用变量间接的提供参数值
执行存储过程
3.执行存储过程InsertRecordDefa (含默认值调用) EXEC InsertRecordDefa 'S10','高平', '女', 18
Encryption:指定对存储过程的正文进行加密，防止别的 用户看到编写的存储过程脚本。
定义无参数的存储过程
例1： 在study数据库中，创建一个名称为myproc1
的存储过程，该存储过程的功能是从数据表
studnet中查询所有男同学的信息。
GO CREATE PROCEDURE myproc1 AS SELECT * FROM s WHERE sex = '男' GO
Βιβλιοθήκη Baidu
定义具有参数的存储过程。
例2：创建一个名称为InsertRecord的存储过程，功能是 向数据表student中插入一条记录，新值由参数提供。 USE study GO CREATE PROCEDURE InsertRecord @sno char(6), @sn char(20), @age numeric(5), @sex char(2), @dept char(10) AS INSERT INTO s VALUES(@sno,@sn,@sex,@age,@dept) GO
CREATE PROCEDURE disRecord AS declare @sno char(6),@sn char(20),@sex char(2),@age smallint declare sp cursor for select s_no,s_name,s_sex ,year(getdate())year(s_birthday) from student where s_sex='女' open sp fetch sp into @sno ,@sn ,@sex ,@age while @@fetch_status=0 begin print @sno+@sn +@sex +convert(char,@age) fetch sp into @sno ,@sn ,@sex end close sp deallocate sp

定义能够返回值的存储过程。
例4：创建一个名称为Query_Study的存储过程， 其功能是从student表中根据学号查询某一同 学的姓名和系别。 CREATE PROCEDURE Query_Study @sno char(6), @sn char(20) OUTPUT, @dept char(10) OUTPUT AS SELECT @sn=sn, @dept=dept FROM student WHERE sno=@sno
创建存储过程的语法规则
CREATE PROCEDURE 存储过程名
@参数名 类型[=default] [output][,…n]
[with {recompile | encryption |recompile, encryption}]
AS sql_statement
Recompile:指定每次执行时都要重新编译

在Microsoft SQL Server 2008系统中，可以使 用Transact-SQL语言编写存储过程，也可以使用 CLR方式编写存储过程。使用CLR编写存储过程 是Microsoft SQL Server 2008系统与.NET框架
紧密集成的一种表现形式。
第6页
类型
在Microsoft SQL Server 2008系统中，提供了3 种基本的存储过程类型

用户定义的存储过程 扩展存储过程 系统存储过程。
第7页
使用CREATE PROCEDURE语句

在Microsoft SQL Server 2008系统中，可以使用 CREATE PROCEDURE语句创建存储过程。需要强调的 是，必须具有CREATE PROCEDURE权限才能创建存储 过程，存储过程是架构作用域中的对象，只能在本地数据 库中创建存储过程。

AFTER指定在对数据表的相关操作之后，触发器被触发。 若指定FOR关键字，则默认是AFTER设置。 INSTEAD OF指定执行触发器而不是执行语句
第26页

例：创建一个触发器，当向表student中插入一条记录时，自 动显示表中的记录。 CREATE TRIGGER Change_Display ON studnet

定义具有参数默认值的存储过程。
例3：创建一个名称为InsertRecordDefa的存储过程，其功能是 向student表中插入一条记录，新值由参数提供，若未提供系 别dept值时，由参数的默认值代替。 USE study GO CREATE PROCEDURE InsertRecordDefa @sno char(6), @sn char(20), @age numeric(5), @sex char(2), @dept char(10)='无' AS INSERT INTO student VALUES(@sno,@sn, @sex , @age, @dept)
即DML触发器和DDL触发器。
第23页
DML触发器

当数据库中发生数据操纵语言(DML)事件时将调用DML触 发器。 DML事件包括在指定表或视图中修改数据的INSERT、 UPDATE、DELETE语句。 在DML触发器中，可以执行查询其他表的操作，也可以


包含更加复杂的Transact-SQL语句。
删除存储过程
其语法规则为：
DROP PROCEDURE {procedure}} [,…n] 如：将存储过程mynewproc从数据库中删除。
drop procedure mynewproc
执行存储过程
使用EXECUTE 命令(可以简写为EXEC)
1.执行存储过程myproc(无参调用)
EXEC myproc
第22页
触发器的概念和类型

触发器是一种特殊类型的存储过程，它包括了大
量的Transact-SQL语句。但是触发器又与存储过
程不同，存储过程可以由用户直接调用执行，而 触发器不能被直接调用执行，它只能自动执行。

按照触发事件的不同，可以把Microsoft SQL Server 2008系统提供的触发器分成两种类型，