SQL Server 2005数据库实用技术 高职应用技术专业 单光庆 第9章 存储过程与触发器.ppt

尚辅网 第九章 存储过程与触发器 本章目录 9.1 存储过程 9.2 触发器 9.3实训 9.1 存储过程 存储过程是数据库对象之一。在SQL Server中，我们可以将经常用到的一段程序(俗称子程序)放在数据库中，这样的子程序称为存储过程。 9.1.1 存储过程概述 1、存储过程的基本概念 存储过程(Stored Procedure)是SQL Server服务器上一组预先定义且编译好的T-SQL语句，它以一个名字存储在数据库中，可以作为一个独立的数据库对象，也可以作为一个单元。用户需要时，可直接调用它。由于存储过程的执行是在服务器上进行的，因此速度较快，而且提高了网络性能。 2、存储过程的类型 SQL Server提供了三种类型的存储过程：系统存储过程、用户自定义存储过程和扩展存储过程。在不同情况下根据需要执行不同的存储过程。 （1）系统存储过程 系统存储过程是由系统提供的存储过程，它定义在系统数据库master中，以sp_为前缀，主要用来从系统表中获取信息，为系统管理员管理SQL Server提供帮助，为用户查看数据库和用户信息提供方便，可以作为命令执行各种操作。例如使用sp_help存储过程可查看数据库对象信息。常用的系统存储过程见附录E。 （2）用户自定义存储过程 用户自定义存储过程是指用户根据自身需要，为完成某一特定功能，在自己的普通数据库中创建的存储过程。它包括本地存储过程、临时存储过程和远程存储过程。 ① 本地存储过程 本地存储过程是指用户根据自己的需要，在用户数据库中创建的存储过程。 ② 临时存储过程 临时存储过程属于本地存储过程，通常分为局部临时存储过程和全局临时存储过程。如果创建的存储过程其名称是以“#”作为第一个字符，则称为局部临时存储过程， ③ 远程存储过程 远程存储过程是指从远程服务器上调用的存储过程。 （3）扩展存储过程 在SQL server环境之外执行的动态链接称为扩展存储过程，其前缀是xp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。只有系统数据库才有扩展存储过程，它是比存储过程更为“低级”的工具。扩展存储过程就像DLL链接库一样，可以“扩展”SQL Server的功能。 3、存储过程的优点 存储过程可以嵌套使用，支持代码重用。 存储过程可以接受与使用参数动态执行其中的SQL语句。 存储过程由于在创建时已经过编译，每次运行时不必重新编译，因此比一般的SQL语句执行速度快。 存储过程具有安全特性和所有权链接，以及可以附加到它们的证书。 存储过程允许模块化程序设计。 存储过程可以减少网络通信流量。 存储过程可以强制应用程序的安全性。 4、建立存储过程注意事项 几乎任何可以写批处理的T-SQL代码都可以用于创建存储过程，但要注意以下规则： 名字必须符合SQL Server命名规则。 创建存储过程中引用的对象必须在创建前就存在。 存储过程最多只能有255个参数。 存储过程不能有下面的SQL创建语句：CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER和CREATE VIEW。 若在存储过程中使用了SELECT *，底层表又加入了新的列，新的列在存储过程运行时将无法显示。 创建存储过程的文本不能超过64KB。 创建存储过程只能在当前数据库中创建。 9.1.2 创建存储过程 用户存储过程只能定义在当前数据库中。默认情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。 在SQL Server中创建存储过程有两种方法，一种是使用SQL Server Management Studio管理器创建存储过程，另一种是使用CREATE PROCEDURE语句创建存储过程，下面分别介绍这两种方法。 1、用Transact-SQL语句创建存储过程 Transact-SQL语句中用CREATE PROCEDURE命令创建存储过程，其语法格式如下： CREATE PROC[EDURE][owner.]procedure_name[;number] [{@parameter data_type} [VARYING][=default][OUTPUT] ][,...n] [WITH {RECOMPILE|ENCRIPTION|RECOMPILE,ENCRYPTION}] [FOR REPLICATION] AS sql_statement[...n] 2、使用SQL Server Management Studio创建存储过程 【例9-1】假设在“xsgl”数据库中建立一个名为“xsgl_