第13讲 存储过程与触发器New.pptVIP

2004-9-10 Transact-SQL程序设计之存储过程和触发器 主要内容： 本章主要讲解存储过程与触发器的建立和使用。通过本章的学习，读者应该掌握以下内容： 存储过程与触发器的功能和特点 存储过程与触发器的建立、修改和使用 存储过程和触发器在维护数据完整性中的作用 存储过程 什么是存储过程？ 存储过程（stored procedure）是一组事先编译好的Transact-SQL代码。存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。 使用存储过程的好处： （1）执行速度快 在执行时不必再重复语法检查和性能优化 经过第一次调用之后，就驻留在内存中，不必再经过编译和优化 （2）模块化的程序设计 可以被调用无数次。 用户可以独立于应用程序而对存储过程进行修改。 可以按照独特的功能模式设计不同的存储过程 （3）减少网络通信量 （4）保证系统的安全性 可以设置用户通过存储过程对某些关键数据进行访问 存储过程分类 系统存储过程 系统提供的存储过程，可以作为命令执行各种操作，名以‘sp_’作为前缀 本地存储过程 本地存储过程是用户根据需要，在自己创建的数据库中所创建的存储过程 ，名称不能以‘sp_’作为前缀 临时存储过程 用户自定义的存储过程，名称以‘#’或‘##’作为前缀，分局部临时存储过程和全局临时存储过程，都可在任何数据库中执行，当前用户退出后，对象释放。 远程存储过程 远程服务器上调用的存储过程，现在只用在分布式查询中。 扩展存储过程 扩展存储过程是 SQL Server 可以动态装载并执行的动态链接库 (DLL) ，名称以‘xp_’作为前缀 创建存储过程 1．使用T-SQL语句创建存储过程 语法格式如下： CREATE PROC[EDURE] 存储过程名 [WITH RECOMPILE|ENCRYPTION] AS SQL语句 其中： RECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过程的备份 ENCRYPTION：系统表syscomments包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的项。若要隐藏或加密存储过程定义，请使用此关键字。 例如：查询选修了课程的学生信息 use stuinfo go create procedure stu_information as select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno go exec stu_information use stuinfo go if exists (select name from sysobjects where name = stu_information and type = p) drop procedure stu_information go create procedure stu_information as select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno go 注意：判断一个标量是否为空: if 标量 is NULL create function s_t2() returns int as begin select * from t2 return 1 end 错误：函数中含有的 SELECT 语句无法向客户端返回数据。 有参数的存储过程 create procedure stu_info @cno smallint as select student.sno,sname,cno,grade,@cno from student,sc where student.sno=sc.sno and cno=@cno go exec stu_info @cno=‘1’ 或者 exec stu_info ‘1’ 数值类型单引号可以不写 use pubs go create proc s1_jobs @j_id smallint as select * from jobs select * from jobs where job_id=@j_id go exec s1_jobs 11 go 存储过程特征： （1）存储过程可以接收参数，并以输出参数的形式返回多个参数给调用存储过程的过程或批处理。 （2）存储过程也可以容纳存储过程，可以在对数据库进行查询、修改的编程语句中调用其