第10章存储过程与触发器1.pptVIP

SQL Server 2005数据库应用技术（第二版） 第10章 存储过程与触发器（1） 本次课的学习内容 理解存储过程,了解存储过程的用途 创建存储过程的语法与示例(了解输入与输出参数的使用) 存储过程的更改与删除 执行存储过程的方式 10-1 存储过程 导入: 为了查询XS数据库中信息系的学生所修总学分信息，可以使用下列查询： USE XS SELECT 学号，姓名，系名，总学分 FROM XSDA WHERE 系名=’信息’ ORDER BY 总学分 DESC 问题: 如果网络上有5000个用户执行同一查询 ,结果会怎样? 解决方法: 将代码以一定的形式存放在服务器中，而不是存储在客户机上 。 10-1 存储过程 10-1-1 存储过程基础 存储过程就是在SQL Server数据库中存放的查询，是存储在服务器中的一组预编译过的T-SQL语句，而不是在客户机上的前端代码中存放的查询。 1.存储过程的优点 存储过程除减少网络通信流之外，还有如下优点： （1）存储过程在服务器端运行，执行速度快。 （2）存储过程是预编译过的，当第一次调用以后，就驻留在内存中，以后调用时不必再进行编译，因此，它的运行速度比独立运行同样的程序要快。 10-1 存储过程 优点： （3）简化数据库管理。例如，如果需要修改现有查询，而查询存放在用户机器上，则要在所有的用户机器上进行修改。而如果在服务器中集中存放查询并作为存储过程，则只需要在服务器上改变一次。 （4）存储过程可以接受参数。允许零个或多个输入参数，也允许零个或多个输出参数。 （5）可作为安全机制使用。即使无对应权限的用户，也可授予权限执行存储过程。 注意：并不是每一个发往服务器的查询都要使用存储过程，只有用户经常运行这个查询，才应考虑将该查询生成为存储过程。 10-1 存储过程 2.存储过程的分类 ： 按编写的语言来分：T-SQL存储过程与CLR(公共语言运行)存储过程。 T-SQL 存储过程又可分为： 用户定义存储过程 系统存储过程。 10-1 存储过程 10-1-2 创建存储过程 定义一个存储过程时，首先要确定以下几点： （1）存储过程的名字。 （2）该存储过程所要完成的功能，这是一个存储过程的主体。 （3）是否需要输入参数，是否需要输出参数来输出结果。 10-1 存储过程 10-1-2 创建存储过程 创建存储过程的方法有: 1.使用模板创建存储过程: SSMS/VIEW/模板资源管理器/存储器/创建存储过程 2.用SSMS管理平台创建 对象资源管理器/数据库/可编程性/存储过程/新建存储过程 10-1 存储过程 10-1-2 创建存储过程 3. 用T-SQL语句创建存储过程 语法格式： CREATE PROC[EDURE][Schema_name.] procedure_name [@parameter data_type [=default][OUTPUT]][,…] [WITH procedure_option] AS sql_statement 注: WITH procedure_option各个选项请详见P201 10-1 存储过程 说明： （1）用户自定义存储过程只能在当前数据库中创建。 （2）=default用于指定存储过程输入参数的默认值。默认值必须是常量或NULL，可以包含通配符%、_、[]和[^]。如果在定义时带了“=default”参数，则执行存储过程时根据情况可不提供实参。 （3）在定义用户存储过程时，不能使用如下对象创建语句 CREATE VIEW、CREATE DEFAULT、CREATE RULE CREATE PROCEDURE、CREATE TRIGGER （4）CREATE PROCEDURE必须是批处理中的第一条语句。存储过程的定义也不能跨越批处理。 （5）用相应架构名称限定存储过程所引用的对象名称，从而确保从存储过程中访问来自不同架构的表、视图或其它对象。 创建存储过程示例 1、创建简单的存储过程 例： USE XS GO IF OBJECT_ID ( dbo.Getxsda, P ) IS NOT NULL DROP PROCEDURE dbo.Getxsda GO CREATE PROCEDURE dbo.Getxsda AS SELECT * FROM XSDA GO --DECLARE @return_value int --EXEC @return_value = [dbo].[GetXSDA] --SELECT Return Value = @return_value 创建存储