[工学]第九章 存储过程与触发器.ppt

第9章 存储过程、触发器 9.1 存储过程 问题 要把完成某功能的SQL做成类似C语言的函数，供需要时调用，如何做？ 1.什么是存储过程？ 存储过程是SQL Server服务器上一组预编译的Transact-SQL语句，它们被集合在一起用于完成某项任务，它可以接受参数、返回参数值，并且可以嵌套调用。 9.1.1 存储过程概述 9.1.1 存储过程概述 2. 存储过程的功能特点 模块化编程 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。 执行速度快 存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。 减少网络通信量 有了存储过程后，在网络上只要一条语句就能执行一个存储过程。 保证系统安全性 通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。 3.SQL Server存储过程的类型包括： 系统存储过程 用户定义存储过程 临时存储过程 扩展存储过程 9.1.1 存储过程概述 系统存储过程： 是指由系统提供的存储过程，主要存储在master数据库中并以sp_为前缀，。 通过系统存储过程，SQL Server中的许多管理性或信息性的活动都可以完成(例如使用sp_depends、sp_helptext可以了解数据库对象、数据库信息) 。 尽管系统存储过程被放在master数据库中，仍可以在其他数据库中对其进行调用(调用时，不必在存储过程名前加上数据库名)。 用户自定义存储过程： 是由用户创建并能完成某一特定功能(例如查询用户所需数据信息)的存储过程。 它处于用户创建的数据库中，存储过程名前没有前缀sp_。 9.1.1 存储过程概述 存储过程和视图的比较 视图 存储过程 语句 只能是SELECT语句 可以包含程序流、逻辑以及SELECT语句 输入、 返回结果 不能接受参数，只能返回结果集 可以有输入输出参数，也可以有返回值 典型应用 多个表的连接查询 完成某个特定的较复杂的任务 9.1.1 存储过程概述 1. 创建和执行不带参数的存储过程 （1）使用企业管理器创建存储过程 （2）使用CREATE PROCEDURE语句创建存储过程 CREATE PROC[EDURE] [所有者.]存储过程名 AS SQL 语句[,...n] 执行： [EXEC[UTE]] 存储过程名 9.1.2 存储过程的创建与使用 【例1】创建并执行存储过程proc_reader，查询读者姓名及其所在单位名称。 CREATE PROC proc_reader AS SELECT Reader.ReaderName,Department.DepartmentName FROM Reader, Department WHERE Reader.DepartmentNo=Department.DepartmentNo GO EXEC proc_reader 存储过程创建后，存储过程的名称存放在sysobject表中，文本存放在syscomments表中。 9.1.2 存储过程的创建与使用 2. 创建和执行带参数的存储过程 （1）使用CREATE PROCEDURE创建带参数存储过程 CREATE PROC[EDURE] [所有者.]存储过程名 [@参数 数据类型 [=default][OUTPUT]] [,...n] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] [FOR REPLICATION] AS SQL 语句[,...n] （2）执行带参数存储过程 [EXEC[UTE]] [@整型变量=]存储过程名|@存储过程变量 [[@参数=]参量值|@变量 [OUTPUT]|[DEFAULT]][,...n] [WITH RECOMPLILE] 9.1.2 存储过程的创建与使用 在SQL查询分析器中输入命令： DECLARE @readerno char(10) EXEC proc_bookborrowed @readerno= 101‘ 运行的结果： CREATE PROC proc_bookborrowed (@readerno char(10)) AS SELECT Reader.ReaderName, Borrow.BookNo,Book.Bookname FROM Reader,Borrow,Book WHERE Reader.ReaderNo=Borrow.ReaderNo AND Book.BookNo=Borrow.BookNo AND Reader.ReaderNo = @readerno