第09章存储过程和触发器

1、第第9章章 存储过程存储过程1、存储过程的特点点、存储过程的特点点（1）提高数据库的执行速度 （2）能实现模块化程序设计（3）减少网络流量 （4）提供系统安全性 2、存储过程的类型、存储过程的类型（1）系统存储过程 SQL server 系统自带的（以sp_开头），具有执行系统过程权限的用户直接调用系统存储过程。Eg：列出有关sysobject中每个对象的信息 use master exec sp_help9.1 存储过程的概念存储过程的概念2、存储过程的类型、存储过程的类型（2）扩展存储过程 即外挂程序，用于扩展SQL server 2005 的功能，是可以动态装载并执行的动态链接库（DLL

2、）Eg：列出C盘上的文件和文件夹清单 exec xp_cmdshell dir c:（3）用户定义存储过程 由用户根据实际问题的需要所创建的存储过程。在命名时不要以sp_和xp_开头，以区别系统存储过程和扩展存储过程。9.1 存储过程的概念存储过程的概念9.29.2创建和执行存储过程创建和执行存储过程1 1、存储过程的建立、存储过程的建立 1 1）格式）格式 CREATE PROCEDURE CREATE PROCEDURE 架构名称架构名称.存储过程名存储过程名 WITH encryption|recompile WITH encryption|recompile AS AS SQL 2 2

3、）存储过程的各选项设置规则）存储过程的各选项设置规则 WithWith：指定一些选项 recompilerecompile：表明SQL server 不会缓存该存储过程的计划，该过程将在每次运行时重新编译。 EncryptionEncryption：表示SQL server 加密存储过程的文本 SQL ：指定过程要执行的操作，可以包含多条SQL语句9.29.2创建和执行存储过程创建和执行存储过程1 1、存储过程的建立、存储过程的建立EgEg：在：在library library 数据库中，创建一个返回图书类别数据库中，创建一个返回图书类别的存储过程的存储过程GetBookCategoryGet

4、BookCategory。 USE Library USE Library GO GO CREATE PROCEDURE GetBookCategory CREATE PROCEDURE GetBookCategory AS AS SELECT SELECT 类编号类编号, , 类名称类名称 FROM BookCategory FROM BookCategory2 2、执行存储过程、执行存储过程 USE Library USE Library GO GO EXECUTE GetBookCategory EXECUTE GetBookCategory【例例】创建一个存储过程创建一个存储过程pro

5、c_student1proc_student1用于显示学号为用于显示学号为“J0402J0402”的学生基本信息（包括学生学号、姓名、性别的学生基本信息（包括学生学号、姓名、性别、系）。、系）。CREATE PROCEDURE CREATE PROCEDURE c_student1 c_student1 ASAS SELECT SELECT 学号学号, ,姓名姓名, ,性别性别, ,系系 FROM sFROM s WHERE WHERE 学号学号= =J0402J0402GOGO9.29.2创建和执行存储过程创建和执行存储过程

6、1.1.修改存储过程修改存储过程ALTER PROCEDURE ALTER PROCEDURE 已创建的存储过程名已创建的存储过程名 AS AS 修改后的存储过程语句体修改后的存储过程语句体Eg：修改前例中定义的存储过程getbookcategoryUSE LibraryGOALTER PROCEDURE GetBookCategoryAS SELECT 类编号, 类名称FROM BookCategoryORDER BY 类编号GO9.3 9.3 修改存储过程修改存储过程1 1）格式）格式 DROP PROC 存储过程名 ,.n 2 2功能功能从当前数据库中删除一个或多个存储过程或过程组。Eg

7、：删除前面创建的存储过程getbookcategory。USE LibraryUSE LibraryGOGODROP PROC GetBookCategoryDROP PROC GetBookCategoryGOGO2、删除存储过程、删除存储过程9.3 9.3 修改存储过程修改存储过程1 1）语法格式）语法格式 parameter_name data_type=defaultoutputparameter_name data_type=defaultoutput2 2）使用输入参数）使用输入参数Eg：根据读者的编号，查询读者的当前借书记录。CREATE PROCEDURE BorrowInfo

8、CREATE PROCEDURE BorrowInfoReaderID int ReaderID int ASASSELECT Borrow.SELECT Borrow.图书编号图书编号, Books., Books.图书名图书名, Borrow., Borrow.借阅日期借阅日期, , Borrow.Borrow.应还日期应还日期FROM Borrow INNER JOINFROM Borrow INNER JOINBooks ON Borrow.Books ON Borrow.读者编号读者编号 = Books.= Books.图书编号图书编号WHERE Borrow. WHERE Borr

9、ow. 读者编号读者编号=ReaderID=ReaderID1、带参数的存储过程、带参数的存储过程9.4 9.4 存储过程中参数的使用存储过程中参数的使用2 2）使用输入参数）使用输入参数 两种传递参数的方法： 通过位置传递：在执行存储过程的语句中直接给出参数 的值。 eg ：execute borrowinfo 2 通过参数名传递：在执行存储过程的语句中，用“参数名 =参数值” 的形式给出参数值。 eg ： execute borrowinfo readerID=21、带参数的存储过程、带参数的存储过程9.4 9.4 存储过程中参数的使用存储过程中参数的使用3 3）使用输出参数）使用输出参数

10、 语法格式 parameter_name data_type=default output eg ：根据读者的编号，查询读者的到期的图书个数 CREATE PROC GetOverDateNum ReaderID int,BookNum int OUTPUT AS SELECT BookNum=COUNT(*) FROM Borrow Where 读者编号=ReaderID AND DATEDIFF(day,应还日期,GETDATE()01、带参数的存储过程、带参数的存储过程9.4 9.4 存储过程中参数的使用存储过程中参数的使用2 2）使用输出参数）使用输出参数 两种传递参数的方法： 通过位

11、置传递参数 eg ：declare num int execute getoverdatenum 2，num output select 到期册数是：,num 通过参数名传递 eg ：declare num int execute getoverdatenum readerID=2， booknum=num output select 到期册数是：,num1、带参数的存储过程、带参数的存储过程9.4 9.4 存储过程中参数的使用存储过程中参数的使用在存储过程中除了返回输出参数以外，还可以有返回值，用来显示存储过程的执行情况。Eg ：根据指定图书编号，查找其是否借出，并将其值返回。CREATE

12、PROCEDURE IsBorrowedBookID int ASDECLARE BookState bitSELECT BookState=是否借出FROM BooksWHERE 图书编号 = BookID RETURN BookState2、通过、通过return返回值返回值9.4 9.4 存储过程中参数的使用存储过程中参数的使用DECLARE IsBorrowed bitEXEC IsBorrowed=IsBorrowed 1IF IsBorrowed=0 PRINT 未借出ELSE PRINT 已借出【例例】建立存储过程建立存储过程sc_look_deletesc_look_delet

13、e，查询某个同学的所有课程成绩，查询某个同学的所有课程成绩，如果存在不及格课程，则删除不及格成绩记录，否则显示所有课程成绩。如果存在不及格课程，则删除不及格成绩记录，否则显示所有课程成绩。CREATE PROCEDURE sc_look_deleteCREATE PROCEDURE sc_look_deletelooklook学号学号 varchar(6)varchar(6)AS AS IF EXISTS(SELECT IF EXISTS(SELECT 学号学号 FROM sc FROM sc WHERE WHERE 学号学号=look=look学号学号 and and 成绩成绩60)60)

14、DELETE FROM sc DELETE FROM sc WHERE WHERE 学号学号=look=look学号学号 AND AND 成绩成绩6060ELSEELSE SELECT SELECT 学号学号, ,课程号课程号, ,成绩成绩 FROM sc WHERE FROM sc WHERE 学号学号=look=look学号学号【例例】现在我们就来修改刚才创建好的存储过程现在我们就来修改刚才创建好的存储过程proc_student3proc_student3，用，用于显示指定学号的学生各门课程的最高成绩，执行该存储过程返回学号于显示指定学号的学生各门课程的最高成绩，执行该存储过程返回学号为

15、为J0401J0401的学生的最高成绩。的学生的最高成绩。完整的完整的ALTER PROCEDUREALTER PROCEDURE语句如下：语句如下：ALTER PROCEDURE c_student3num char(6), max smallint outputASBEGIN SELECT max=max(成绩) FROM s JOIN sc ON s.学号=sc.学号 WHERE s.学号=numEND1 1格式格式 SP_RENAME 原存储过程名，新存储过程名2 2功能功能将存储过程名更改为新存储过程名。【例例】创建存储过程的创建存储过程的sg sg ，更名为，更名为s

16、tudent_procstudent_proc。方法一：使用方法一：使用SQLSQL命令命令SP_RENAME sg,student_proc注意注意: : 更改对象名的任一部分都可能破坏脚本和存储过程。更改对象名的任一部分都可能破坏脚本和存储过程。方法二：在方法二：在Management StudioManagement Studio重命名存储过程重命名存储过程重命名存储过程重命名存储过程 触发器是特殊的存储过程，它也定义了一组触发器是特殊的存储过程，它也定义了一组Transact-SQLTransact-SQL语句，用于完成某项任务。语句，用于完成某项任务。 触发器的主要作用是能强制数据完

17、整性，保证数据一致性触发器的主要作用是能强制数据完整性，保证数据一致性，主要表现为：，主要表现为：l强化约束强化约束 l保证参照完整性保证参照完整性 l级联运行级联运行 l跟踪变化跟踪变化 l创建触发器时需指定：创建触发器时需指定： 名称、在其上定义触发器的表、触名称、在其上定义触发器的表、触发器将何时激发、激活触发器的数据修改语句。发器将何时激发、激活触发器的数据修改语句。 第第10章章 触发器触发器10.1触发器的概念触发器的概念1 1格式格式CREATE TRRIGER 架构的名称.触发器名 ON 表名|视图WITH encryption -对文本进行加密。FOR| AFTER | IN

18、STEAD OF delete,insert,updateAS SQL 语句2 2功能功能AFTERAFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。INSTEAD OFINSTEAD OF：指定执行触发器而不是执行“触发 SQL 语句”，从而替代“触发语句”的操作。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。9.2.2创建触发器创建触发器【例例9.269.26】在学生选课表在学生选课表scsc上创建一个触发器上创建一个触发器trigger_student1trigger_student1

19、，该触发器被，该触发器被INSERTINSERT操作触发，当用户向操作触发，当用户向scsc表插入一条新记录时，判断该记录的学号在学生基本信息表表插入一条新记录时，判断该记录的学号在学生基本信息表s s中中是否存在，如果存在插入成功，否则插入失败。是否存在，如果存在插入成功，否则插入失败。CREATE TRIGGER trigger_student1ON sc AFTER INSERTAS BEGINIF(SELECT count(*) FROM inserted JOIN s ON inserted.学号=s.学号)=0 BEGIN ROLLBACK TRAN PRINT 插入记录无效！ E

20、NDEND9.2.2创建触发器创建触发器1 1COLUMNS_UPDATEDCOLUMNS_UPDATED（）（）COLUMNS_UPDATED()用于测试是否插入或更新了所涉及的列，仅用于 INSERT 或 UPDATE 触发器中。COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。2 2UPDATE (column)UPDATE (column)测试在指定的列上进行的 INSERT 或 UPDATE 操作，如果UPDATE(column)返回 TRUE 值，则指定的列上插入了显式值或隐性 (NULL) 值。9.2.3 COLUMNS_UPDATE

21、D()和和UPDATE (column)函数函数【例例9.319.31】修改修改s s表的学号属性列，并且通过使用表的学号属性列，并且通过使用 COLUMNS_UPDATED() COLUMNS_UPDATED() 功能，测试所做的更改。修改在功能，测试所做的更改。修改在scsc表中相表中相对应的记录。如，将对应的记录。如，将s s表中的学号表中的学号JO401JO401，修改为，修改为G0401,G0401,则则scsc表表中的原学号为中的原学号为JO401JO401的学号，相应更改为的学号，相应更改为G0401G0401。（注意，要使。（注意，要使此项实验成功，必须删除此项实验成功，必须删

22、除s s表与表与scsc表的外键约束。）表的外键约束。）【例例9.329.32】创建触发器创建触发器sinsertsinsert，当向，当向S S表添加一名学生信表添加一名学生信息时，则触发向息时，则触发向scsc表增加一条记录。学号为新增学号，课程号表增加一条记录。学号为新增学号，课程号为为C01C01。【例例9.339.33】在数据库学生选课中创建触发器在数据库学生选课中创建触发器check_trigcheck_trig，当向当向scsc表插入一条记录时，检查该记录的学号在表插入一条记录时，检查该记录的学号在s s表中是否存在表中是否存在，检查课程号在，检查课程号在c c表中是否存在，若有

23、一项为否，则不允许插入表中是否存在，若有一项为否，则不允许插入。9.2.3 COLUMNS_UPDATED()和和UPDATE (column)函数函数【例9.34】创建一触发器check_delete，删除s表中的记录时，同时删除sc表中的相应记录。【例9.35】为S表创建一个新触发器delete_stu，当删除表S中的一条学生记录时，检查SC表中是否存在相同学生的选课成绩，如果有，则不允许删除此记录。【例9.36】在学生选课数据库中创建视图I_v，包含姓名,性别,课程号,成绩。接着在视图上创建instead of触发器insertsview，要求向视图插入数据时，相应的数据表中也插入了对应

24、的数据。（注意，避免与触发器sinsert产生冲突。）9.2.3 COLUMNS_UPDATED()和和UPDATE (column)函数函数. .格式格式RAISERROR ( msg_id | msg_str ,严重级别, 状态. .功能功能（1）msg_id是存储于 sysmessages 表中的用户定义的错误信息号。用户定义错误信息的错误号应大于 50000。（2）msg_str是一条特殊消息，此错误信息最多可包含 400 个字符。如果该信息包含的字符超过 400 个，则只能显示前 397 个并将添加一个省略号以表示该信息已被截断。（3）与消息关联的严重级别。用户可以使用从 0 到 1

25、8 之间的严重级别。19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。若要使用 19 到 25 之间的严重级别，必须选择 WITH LOG 选项。（4）状态是从 1 到 127 的任意整数，默认为 1，表示有关错误调用状态的信息。9.2.4 RAISERROR1 1格式格式ALTER TRRIGER 架构的名称.触发器名 ON 表名|视图WITH encryption -对文本进行加密。FOR| AFTER | INSTEAD OF delete,insert,updateAS SQL 语句2 2功能功能修改触发器，各选项的功能与创建触发器的命令一样。9.2.5

26、修改触发器修改触发器【例例9.379.37】现在我们就来修改刚才在现在我们就来修改刚才在【例例9.269.26】中创建中创建好的触发器好的触发器trigger_student1trigger_student1，该触发器被，该触发器被INSERTINSERT操作触发操作触发，当用户向，当用户向scsc表插入一条新记录时，判断该记录的学号在学表插入一条新记录时，判断该记录的学号在学生基本信息表生基本信息表s s中是否存在，如果存在插入成功，否则插入中是否存在，如果存在插入成功，否则插入失败；同时判断该记录的课程号在课程表失败；同时判断该记录的课程号在课程表C C中是否存在，如中是否存在，如果存在插入成功，否则插入失败果存在插入成功，否则插入失败. .ALTER TRIGGE