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

第7章存储过程和触发器
存储过程
存储过程的类型
(1) 系统存储过程
系统存储过程是由系统提供的存储过程,作为命令执行各种操作。
(2) 本地存储过程
本地存储过程是指在用户数据库中创建的存储过程,这种存储过程完成特定数据库操作任务,其名称不能以sp_为前缀。
(3) 临时存储过程
临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”,该存储过程就称为局部临时存储过程,这种存储过程只能在一个用户会话中使用。
(4) 远程存储过程
远程存储过程指从远程服务器上调用的存储过程。
(5) 扩展存储过程
在SQL Server环境之外执行的动态链接库称为扩展存储过程,其前缀是sp_。使用时需要先加载到SQL Server系统中,并且按照使用存储过程的方法执行。
用户存储过程的创建与执行
在用户存储过程的定义中不能使用下列对象创建语句:
CREATE VIEW
CREATE DEFAULT
CREATE RULE
CREATE PROCEDURE
CREATE TRIGGER

如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩,然后调用该存储过程步骤如下:
定义如下存储过程
USE XSCJ
Go
CREATE PROCEDURE student_grade
AS
SELECT ,,,
FROM XS,XS_KC,KC
WHERE = AND =
Go
存储过程
使用存储过程的优点:
(1) 存储过程在服务器端运行,执行速度快。
(2) 存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。
(3) 确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。
(4) 自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务。
存储过程
调用存储过程
EXEC student_grade
GO
通过上例了解了存储过程的使用,下面介绍创建和执行存储过程的语法格式。
1) 创建存储过程
语法格式:
CREATE PROC [ EDURE ] procedure_name [ ; number ]
/*定义过程名*/
[ { ***@parameter data_type } /*定义参数的类型*/
[ VARYING ] [ = default ] [ OUTPUT ] ] /*定义参数的属性*/
[ ,...n1 ]
[ WITH  { PILE | ENCRYPTION | PILE , ENCRYPTION }]
/*定义存储过程的处理方式*/
[ FOR REPLICATION ]
AS sql_statement [ ...n2 ] /*执行的操作*/
存储过程
对于存储过程要注意下列几点:
(1) 用户定义的存储过程只能在当前数据库中创建(临时过程除外,临时过程总是在 tempdb 中创建)。
(2) 成功执行 CREATE PROCEDURE 语句后,过程名称存储在 sysobjects 系统表中,而 CREATE PROCEDURE 语句的文本存储在 ments 中。
(3) 自动执行存储过程
SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在 master 数据库中创建,并在 sysadmin 固定服务器角色下作为后台过程执行。
(4) sql_statement的限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL外,其它SET 语句均可在存储过程内使用。
(5) 权限。CREATE PROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员。
存储过程
2)存储过程的执行
通过EXEC命令可以执行一个已定义的存储过程。
语法格式:
[ EXEC [ UTE ] ] 
{  [ ***@return_status = ]
{ procedure_name [ ;number ] | ***@procedure_name_var }
[ [ ***@parameter = ] { value | ***@variable [ OUTPUT ] | [ DEFA