SQLServer2005数据库实践教程第4章管理触发器与存储过程.ppt

SQL Server 2005数据库实践教程——开发与设计篇 主编：钱 哨 本章你将学习到： 了解存储过程以及触发器的概念 了解触发器的功能和类型 掌握如何使用企业管理器创建和管理存储过程和触发器 掌握如何使用T-SQL语句创建和管理存储过程和触发器 第一节、管理存储过程 本节你将学习和了解到： 1、讲述存储过程如何创建并收集相关信息 2、系统如何对存储过程进行处理 3、如何对存储过程传入和传出数据 一、存储过程的基本概念 存储过程（Stored Procedure） 是存放在SQL SEVER中的预先编译好了的SQL程序。由于存储过程是预先编译好了的，因此他们具有各种类型查询的最好性能。 1、存储过程是以一个名称存储在数据库中，可以作为一个独立的数据对象，也可以作为一个单元在数据库中被用户调用。 2、存储过程可以接收和输出数据、参数以及返回执行存储过程的状态值，还可以嵌套使用。 3、存储过程提供了标准的SQL语言所没有的高级特性，其传递参数和执行逻辑表达式的功能，有助于应用程序设计者处理复杂的数据任务。 4、存储过程是工作在服务器上的，从而有效的减少C/S频繁访问的数据流量，减少数据操作所需要的网络带宽和数据流量 5、存储过程使得开发者不必在客户端开发大量的程序代码，同时在数据库的安全性上面得到提高。 2、存储过程的运行特点 存储过程是存放在SQL SEVER中的特别快的存储对象，当首次运行存储过程的时候，它将按照以下的方式进行： （1）该过程被分解成为部件对象。 （2）检查数据库中对象（表、视图）是否存在，这被称为是分解。 （3）分解成功后，该过程名称被存放在sysbject表中，创建存储过程的代码被放在syscomments表中 （4）编译，并且编译过程中将创建如何运行查询蓝本。该蓝本被称作是查询计划或查询树，查询树存放在sysprocedures表中。 （5）存储过程首次执行时候读出查询计划并完全编译成为过程计划。今后的数据操作中将按照这样的计划执行，从而节约了每次执行存储过程的语法检查、分解和编译查询树的执行时间。 （6）存储过程的最大好处是：一旦执行了存储过程，过程计划将存储到cache中，这样在下次运行该存储过程的时候，将直接从cache中读取并运行，大大提高了查询的速度 存储过程的好处（2） 1、存储过程封装事务，一旦封装完毕，该封装可以用于多个应用，从而有一致的数据接口。这样带来的好处就是：如果改变过程的功能，只需要在一个地方修改，而不必要对每一个应用进行修改。 2、通过存储过程可以传入参数并返回参数 3、执行速度快，工作效率高；规范化程序设计。 4、提高系统的安全性能。 创建存储过程的规则 引用的对象必须在创建存储过程前就存在。 不能在单个存储过程中再创建同名的存储过程 存储过程最多有255个参数 存储过程不能够执行下列的语句： create procedure,rule,view 存储过程创建的文本不可以超过64kb 二、使用管理平台创建存储过程 1、创建存储过程 三、使用T-SQL管理存储过程 1、创建存储过程（语法如下）： CREATE PROC[EDURE] procedure_name[;number]/指定的存储过程名称//number用来对同名过程进行分组，以便用一条drop procedure就可以将同组的过程一并除去/ [{@parameter data_type}/指定存储过程的参数名称以及类型/ [VARYING][=default][OUTPUT]/指定输出参数支持的结果集（仅适合于带游标的参数）//默认数值//用来指定参数是可以返回的，可以将该信息返回给调用的过程/ ][,…n] [WITH {RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION}] [FOR REPLICATION]/指定每执行一次都要重新编译//SQL需要加密syscomments表中的内容//该存储过程只能够在数据数据复制的时候使用，本选项不能够和with recompile联合使用/ AS sql_statement[…n]/该项包含T-SQL/ 存储过程编程例子 例1.在school数据库中创建一个存储过程，用于返回工资收入高于教师平均收入的教师情况，并按照他们的工资降序排列 use school --如果存在同名的存储过程，先删除之 if exists(select name from sysobjects where name=higher_sal) drop procedure higher_sal go --新建存储过程 create procedure hig