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

第十章存储过程和触发器
存储过程
触发器
第十章存储过程和触发器
 
本章以本书的“图书销售系统”为例,主要介绍了如何在SQL Server 2000数据库中建立、修改存储过程和触发器。
存储过程
存储过程简介
存储过程是一组预先编译好的SQL代码。它是SQL Sever上的一个非常有用的特性,是一个独立的数据库对象,可以通过调用命令(EXECUTE)执行存储过程,而不必发送大量Transact-SQL代码,因此大大减少了网络流量,或用户的应用程序调用。
存储过程可用于向用户返回数据、向表中插入新数据、修改、执行系统和管理任务等。
存储过程有如下优点:
(1)行速度快
存储过程在创建时经过语法检查和性能优化,在第一次被调用后就驻留在内存中,下次执行不再经过编译。当某操作需大量使用或重复执行,存储过程将比Transact-SQL 代码要快。
(2)模块化的程序设计
存储过程在创建后,即存储在数据库中,可被应用程序无数次调用。用户可按不同功能的程序模块,设计不同的存储过程以供使用。
(3)减少网络流量
存储过程是一组Transact-SQL语句,但其是作为一个独立的单元来使用,在调用时,只需一个执行语句即可实现。
(4)安全性
可以授权无直接执行存储过程语句权限的用户执行该存储过程。
(5)可以将复杂的任务划分为多个简单的商业逻辑
(6)存储过程对某些需要周期性处理的任务特别有用。
(7)存储过程在客户端和服务器端提供了良好的并发性。
在下例情况下,将使用到存储过程。
(1)不需要任何客户端信息而在服务器端完成的操作
(2)涉及到大量行的操作、运算
(3)频繁的、周期性的操作
(4)那些被多个模块共同执行的操作。
创建存储过程
语法:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { ***@parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ PILE | ENCRYPTION | PILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
 
参数解释:
procedure_name
新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。
要创建局部临时过程,可以在 procedure_name 前面加一个编号符(#procedure_name),要创建全局临时过程,可以在 procedure_name 前面加两个编号符(##procedure_name)。完整的名称(包括# 或##)不能超过 128 个字符。指定过程所有者的名称是可选的。
;number
是可选的整数,用来对同名的过程分组,以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应在 procedure_name 前后使用适当的定界符。
***@parameter
过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2100 个参数。
使用@ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。
【注意】调用存储过程时,必须给出参数值,参数可为空格,但不能为空(NULL)
data_type
参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。
【说明】对于可以是 cursor 数据类型的输出参数,没有最大数目的限制。
VARYING
指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。
default
参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 L