SQL Server数据库应用与开发 李德有 彭德林 SQL 2005第6章新.ppt

第6章 存储过程 主要内容 存储过程及其创建 执行存储过程 管理存储过程的方法 第六章 存储过程 6.1存储过程概述 6.2存储过程的创建 6.3存储过程的维护 6.1存储过程概述 6.1.1 存储过程简介 SQL Server提供了一种方法：可以将一些预先编译的SQL语句集中起来由SQL Server数据库服务器来完成，以实现某个任务，这就是存储过程。 6.1存储过程概述 6.1.2 存储过程优点 1.存储过程是SQL Server数据库服务器上一组预先编译好的Transact-SQL语句。 2.存储程以一个名称存储在数据库中，可作为一个独立的数据库对象；可作为一个单 元供用户在应用程序中调用。 3.存储过程可以接收和输出参数，返回执行存储过程的状态值。 4.一个存储过程可以调用另一个存储过程。 5.存储过程会返回执行情况的状态代码给调用它的程序。 6.1存储过程概述 6.1.2 存储过程优点 1.存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。存储过程已经经过编译，无须经过SQL语句的再次分析，可以直接执行。根据经验，存储过程在数据库上运行，平均要比SQL语句执行速度快上2~10倍。 2.存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。 3.存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。 4.存储过程主要是在服务器上运行，减少对客户机的压力。 6.1存储过程概述 6.1.2 存储过程优点 5.存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。 6.存储过程可以在单个存储过程中执行一系列SQL语句。 7.存储过程可以减轻服务器的负担。当用户的操作是针对于数据数据库对象的操作时，使用存储过程，只要直接发送存储过程的调用命令即可，从而增加了网络流量，降低网络负担。如果使用单条调用语句的方式，网络上就必须传输大量的SQL语句。 6.1存储过程概述 6.1.3存储过程分类 1.系统存储过程 系统存储过程就是系统创建的存储过程，是在SQL Server安装成功后，就已经存储在系统数据库中了，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_”为开头，并存放在“sys”构架中，为数据库管理者所有。 6.2存储过程的创建 6.2.1 使用Create procedure语句 CREATE PROC[ EDURE ] procedure_name[ ;number ] ?? [{ @parameter data_type } [ VARYING ] [ =default ][ OUTPUT ]] ?? [ ,...n ] ??[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION }] [ FOR REPLICATION ] ??AS sql_statement [ ...n ] 6.2存储过程的创建 例6-1：创建一个存储过程exp1，查询学生表中所有内容的。 USE 教务管理 GO CREATE PROCEDURE exp1 AS SELECT * FROM 学生 GO 6.2存储过程的创建 6.2.5 创建及使用存储过程注意事项 2.尽量少用可选参数 在频繁使用可选参数之前，请仔细考虑。通过执行额外的工作会很轻易地影响性能，而根据为任意指定执行输入的参数集合，这些工作时不需要的。您可以通过对每种可能的参数组合使用条件编码来解决此问题，但这相当费时并会增大出错的几率。 6.3存储过程的维护 6.3.1 存储过程修改 ALTER PROC[EDURE] procedure_name [;number] [ {@parameter data_type } [VARYING] [= default] [OUTPUT]] [,...n] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION}] [FOR REPLICATION] AS sql_statement [...n] 6.3存储过程的维护 6.3.1 存储过程修改 例6-11：修改存储过程exp1，使其按学号排序。 ALTER PROCEDURE exp1 AS SELECT * FROM 学生 ORDER BY 学号 GO 注意：如果在ALERT PROCEDURE中使用了WITH ENCTYPTION保留字，那么在查看修改后的存储过程源代码时，是看不到的。 6.3存储过程的维护 6.3.2 存储过程删除 使用命