第9章 存储过程和触发器专用课件.pptVIP

第9章 存储过程和触发器 本章导读： 存储过程和触发器都不是标准的SQL语句，不同的数据库管理系统在具体实现时语法会有所不同。存储过程是一组Transact-SQL语句和可选控制语句的预编译集合，它是按名存储并运行于服务器上，独立于表的数据库对象。而触发器是一种在关系表上定义，伴随用户修改相关数据而自动执行的特殊存储过程，主要用于强化复杂的规则和要求。 知识要点： 存储过程 触发器 9.1 存储过程 存储过程是存储在服务器上的一组预编译好的T-SQL代码。存储过程可以通过输入参数接收调用程序的实参输入，也可以通过输出形参将运行结果返回给调用程序，还可以通过状态参数判断存储过程的执行成功与否。用户自定义的存储过程名存储于系统表sysobjects中，而存储过程中定义的文本内容存储于系统表syscomments中。 9.1.1 存储过程概述 9.1.2 存储过程的创建 9.1.1 存储过程概述 存储过程可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序调用。SQL Server支持5种类型的存储过程： 1．系统存储过程 SQL Server提供了大量的系统存储过程，很多管理活动都是通过系统存储过程实现的。系统存储过程名以sp_为前缀，存储在master数据库中，用户可以在任何数据库中执行系统存储过程。另外，用户可以在master数据库中定义sp_为前缀的自定义系统存储过程。 2．用户存储过程 是指用户自行创建并存储在用户数据库中的存储过程。为了与系统存储过程相区别，一般不要将用户存储过程名定义为以sp_为前缀的名称。如果用户自定义的存储过程和系统存储过程同名，那么用户存储过程永远不执行。 9.1.1 存储过程概述 3．临时存储过程 临时存储过程分为局部临时存储过程和全局临时存储过程。 局部临时存储过程名称以#为前缀，存放在tempdb数据库中，只由创建并连接的用户使用，当该用户断开连接时将自动删除局部临时存储过程。 全局临时存储过程名称以##为前缀，存放在tempdb数据库中，允许所有连接的用户使用，在所有用户断开连接时自动被删除。 4．远程存储过程 位于远程服务器上的存储过程。 5．扩展存储过程 扩展存储过程：利用外部语言（如C语言编写的存储过程，以弥补SQL Server的不足之处，扩展新的功能，扩展存储过程名以xp_为前缀。 9.1.2 存储过程的创建 SQL Server提供了两种创建存储过程的方法：使用企业管理器和使用T-SQL语句的Create Procedure命令。 1．使用Transact-SQL语句 在SQL Server中，使用T-SQL语句的Create Procedure命令创建存储过程，其格式如下： create proc[edure] 存储过程名[;分组编号] [{@形式参数 数据类型} [ = 默认值 ] [output] [varying]] [,...n] [with { recompile | encryption | recompile,encryption}] [for replication] as sql语句 [...n] 9.1.2 存储过程的创建 功能：在当前数据中创建指定名称的存储过程。 说明： （1）存储过程名：必须符合标识符规则，且对于数据库及其所有者必须唯一，如果要创建局部临时存储过程，需在过程名前加一个编号符（#）；如果要创建全局临时存储过程，需在过程名前加两个编号符（##）； （2）分组编号：是可选的整数，用来对一组同名的存储过程进行分组编号，以便于一条drop procedure语句即可删除一组同名的存储过程； （3）@形式参数：可以定义多个形式参数（形参），形参有输入参数和输出参数之分； （4）数据类型：是指明形参的数据类型，包括text，ntext和image均可作为形参的数据类型，当形参是输入参数时，不能使用cusor数据类型； 9.1.2 存储过程的创建 （5）默认值：定义输入参数时，可以赋值默认值，默认值必须是常量或者null，输入参数默认值可以使用like关键字及其通配符（%，_，[]，[^]），当输入参数赋值默认值时，调用存储过程可以省略实参，否则调用时，必须提供实际参数（实参）值； （6）output：表示形参是输出参数，输出参数可以将返回值传递给调用过程，缺省output时表示参数是输入参数； （7）varying：指明输出参数支持的结果集是由存储过程动态构造，内容可以变化，当形参数据类型定义为cursor（游标）类型，必须同时指明varying选项； （8）recompile：表示每次重新编译存储过程，而encryption表示加密存储过程文本； （9）for replication：表示创建的存