第4章 存储过程和触发器.ppt

存储过程和触发器 计算机科学与工程学院 2013 年9月 存储过程的概述 存储过程：几种存储在sql server中的sql 语句和流程控制语句的预编译集合，用以实现某种任务。 使用存储过程的好处： （1）减少客户端代码的重复。只需要创建一次存储过程并将其存储在数据库中，以后即可在客户端程序中多次调用该存储过程。 （2）允许更快的执行。如果某操作需要大量的Sql代码或重复执行，使用存储过程将比在酷暑段执行那个SQ代码的执行速度要快。存储过程在服务器段经过预编译，生成查询计划，可以直接执行，二在哭护短每次运行SQL语句时，都要从客户端重复发送，在SQL SERVER执行这些语句时，都要对其进行那个编译和优化。 （3）减少网络流量。如：一个需要百行SQL代码的操作由一条执行过程代码的单独语句就可以实现，而不需要在网络中发送百行代码。 存储过程的概述 使用存储过程的好处： （3）简化数据库管理。如，要修改某种查询，如果查询重复放在客户机上，则要在所有客户机上修改查询，而使用存储过程可以集中修改； （4）可作为安全机制的使用。直接发送sql语句可能会泄露密码等，而采用存储过程只需传递必要的参数即可。 存储过程的分类 系统存储过程：存储在master数据库中，用于执行sql server的某些管理功能、显示数据库和用户的信息。系统存储过程以sp_开头，可以在任何数据库中执行。 用户存储过程：用户自己创建并存储在用户数据库中的存储过程。 临时存储过程分为两种： 　（1）本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且只有创建它的用户才能执行它; 　（2）全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。 存储过程的分类 远程存储过程：在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行一个远程存储过程。 扩展存储过程：扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。 存储过程的创建 1 创建存储过程的格式 CREATE PROCEDURE 存储过程名 [{@参数名 数据类型}[=default][OUTPUT] ][,…n] [With {recompile|encryption|recompile,encryption}] AS SQL语句 存储过程名：必须符合标识符命名规则； 编号：可选整数，用于对同名的存储过程分组，以便用一条drop procedure语句将同组的存储过程一起删除； @参数：过程中的参数，在创建过程的语句中可以声明一个或多个参数。用户必须在执行存储过程时提供每个所声明参数的值。参数名称前必须使用@符号，每个过程的参数仅用于该过程本身。在其他过程中可以使用相同的参数名称。参数只能用于代替常量，而不能代替表名、列名或其他数据库对象的名称 存储过程的创建 1 创建存储过程的格式（续） 数据类型：参数的数据类型，所有数据类型都可以作为存储过程的参数。但是cursor数据类型只能用于output参数。如果指定的数据类型为cursor，也必须同时指定varying 和output关键字。 Varying：指定作为输出参数支持的结果集，仅适用于游标参数。 默认值：参数的默认值，如果定义了默认值，可以不必指定参数的值即可执行存储过程。默认值通常为null或常量。 Output：表明该参数是返回参数，使用该参数可以将信息返回给调用过程，text,ntext 和image参数可用于output参数。 Recompile:表明不保存该存储过程的执行计划，该存储过程将在运行时重新编译。 Encryption:指定sql server对syscomments表中包含本create procedure语句文本的条目进行加密，在执行’exec sp_helptext 触发器名/存储过程名’时看不到定义触发器/存储过程的语句。 AS:用于指定该存储过程要执行的操作。 Sql语句：存储过程中要包含的sql 语句。 存储过程的创建 例：创建一个存储过程，可以将所有学生的入学成绩增加10% create procedur