第7章 存储过程和触发器
7.1 存储过程
7.1.1 存储过程的类型
(1) 系统存储过程 系统存储过程是由系统提供的存储过程，作为命令执行各种操作。
(2) 本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种
存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。
(3) 临时存储过程
临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 (4) 远程存储过程
远程存储过程指从远程服务器上调用的存储过程。 (5) 扩展存储过程
在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。 7.1.2 用户存储过程的创建与执行
在用户存储过程的定义中不能使用下列对象创建语句： CREATE VIEW CREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER
1 / 16下载文档可编辑
1．通过SQL命令创建和执行存储过程
如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，然后调用该存储过程步骤如下： 定义如下存储过程 USE XSCJ Go
CREATE PROCEDURE student_grade
AS SELECT XS.学号,XS.姓名,KC.课程名, XS_KC.成绩
FROM XS,XS_KC,KC
WHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号 Go
使用存储过程的优点：
(1) 存储过程在服务器端运行，执行速度快。
(2) 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。
(3) 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。
(4) 自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。 调用存储过程
EXEC student_grade GO
通过上例了解了存储过程的使用，下面介绍创建和执行存储过程的语法格式。 1) 创建存储过程
2 / 16下载文档可编辑
语法格式:
CREATE PROC [ EDURE ] procedure_name [ ; number ] /*定义过程名*/ [ { @parameter data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUTPUT ] ] /*定义参数的属性*/ [ ,...n1 ]
[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }]
/*定义存储过程的处理方式*/
[ FOR REPLICATION ]
AS sql_statement [ ...n2 ] /*执行的操作*/ 对于存储过程要注意下列几点：
(1) 用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。
(2) 成功执行 CREATE PROCEDURE语句后，过程名称存储在sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本存储在 syscomments 中。 (3) 自动执行存储过程
SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在 master 数据库中创建，并在 sysadmin 固定服务器角色下作为后台过程执行。
(4) sql_statement的限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL外，其它SET 语句均可在存储过程内使用。
3 / 16下载文档可编辑
(5) 权限.CREATE PROCEDURE的权限默认授予sysadmin固定服务器角色成员,db_owner 和 db_ddladmin 固定数据库角色成员. 2）存储过程的执行
通过EXEC命令可以执行一个已定义的存储过程。 语法格式：
[ EXEC [ UTE ] ]
{ [ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var }
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ]
[ WITH RECOMPILE ] ｝
存储过程的执行要注意下列几点：
(1) 如果存储过程名的前三个字符为 sp_，SQL Server 会在 Master 数据库中寻找该过程。如果没能找到合法的过程名，SQL Server 会寻找所有者名称为 dbo 的过程。
(2) 参数可以通过 value 或 @parameter_name = value 提供。
(3) 执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE 关键字。 3) 举例
(1) 设计简单的存储过程
【例7.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学分。该存储过程不使用任何参数。 USE XSCJ
4 / 16下载文档可编辑
/*检查是否已存在同名的存储过程，若有，删除。*/ IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'student_info' AND type = 'P')
DROP PROCEDURE student_info GO /*创建存储过程*/ CREATE PROCEDURE student_info
AS SELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER JOIN XS_KC b
ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 GO (2) 使用带参数的存储过程
【例7.2】从XSCJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。 USE XSCJ
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'student_info1' AND type = 'P') DROP PROCEDURE student_info1 GO CREATE PROCEDURE student_info1
@name char (8),@cname char(16)
AS SELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER JOIN XS_KC b
ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号
WHERE a.姓名=@name and t.课程名=@cname GO
5 / 16下载文档可编辑
存储过程和触发器
