sql server实用教程郑阿齐 第07章

7.1.1 存储过程的类型
(1) 系统存储过程 系统存储过程是由系统提供的存储过程，作为命令执行各种操作。 (2) 本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成 特定数据库操作任务，其名称不能以sp_为前缀。 (3) 临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一 个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一 个用户会话中使用。 (4) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。 (5) 扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前 缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过 程的方法执行。
7.1 存储过程
调用存储过程
EXEC student_grade GO 通过上例了解了存储过程的使用，下面介绍创建和执行存储过程的 语法格式。 1) 创建存储过程 语法格式: 语法格式 CREATE PROC [ EDURE ] procedure_name [ ; number ] /*定义过程名*/ [ { @parameter data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUTPUT ] ] /*定义参数的属性*/ [ ,...n1 ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] /*定义存储过程的处理方式*/ [ FOR REPLICATION ] AS sql_statement [ ...n2 ] /*执行的操作*/
7.2.1 利用SQL命令创建触发器
2．触发器中使用的特殊表 ．
inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执 行，新的记录插入到触发器表和inserted表中。 deleted逻辑表：用于保存已从表中删除的记录，当触发一个 DELETE触发器时，被删除的记录存放到deleted逻辑表中。
7.1.2 用户存储过程的创建与执行
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
7.2.1 利用SQL命令创建触发器
1 .语法格式 语法格式
CREATE TRIGGER trigger_name ON { table | view } /*指定操作对象*/ [ WITH ENCRYPTION ] /*说明是否采用加密方式*/ { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] /*说明该触发器不用于复制*/ AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ ...n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] }] /*两个IF子句用于说明触发器执行的条件*/ sql_statement [ ...n ] /*一条或若干条SQL语句*/ } }
7.1.4 用户存储过程的删除
使用DROP PROCEDURE 语句可永久地删除存储过程。在此之前，必须确认该 存储过程没有任何依赖关系。 语法格式： 语法格式： DROP PROCEDURE { procedure } [ ,...n ] procedure指要删除的存储过程或存储过程组的名称；n： 表示可以指定多个存储过程同时删除。 【例7.10】删除 XSCJ数据库中的student_info1 存储过程。 】 USE XSCJ GO DROP PROCEDURE student_info1
【例7.8】对存储过程student_info1进行修改。 】
USE XSCJ GO ALTER 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
7.1.3 用户存储过程的编辑修改
【例7.9】创建名为 select_students 的存储过程，默认情 】 况下，该过程可查询所有学生信息，随后授予权限。
USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = ' select_students ' AND type = 'P') DROP PROCEDURE select_students /*若该存储过程已存在， 则删除*/ GO USE XSCJ GO CREATE PROCEDURE select_students /*创建存储过程*/ AS SELECT * FROM XS ORDER BY 学号 GO
7.1 存储过程
2．利用企业管理器创建用户存储过程 ．
第1步 在SQL Server 企业管理器窗口中，选择相应的服务器、数据 步 库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如 图7.1所示的快捷菜单。
第2步 选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口 步 中输入定义的存储过程，如图7.2所示，然后选择“确定”按钮。
7.2.1 利用SQL命令创建触发器
4．举例 ．
【例7.11】对于XSCJ数据库，如果在XS表中添加或更改数据， 】 则将向客户端显示一条信息。 /*使用带有提示消息的触发器*/ USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO CREATE TRIGGER reminder ON XS FOR INSERT, UPDATE AS RAISERROR (4008, 16, 10) GO
7.1 存储过程
3) 举例
(1) 设计简单的存储过程 【例7.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、 】 课程名、成绩、学分。该存储过程不使用任何参数。 USE XSCJ /*检查是否已存在同名的存储过程，若有，删除。*/ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'student_info' AND type = 'P') DROP PROCEDURE student_info GO /*创建存储过程*/
7.2.1 利用SQL命令创建触发器
3.注意事项 注意事项 (1) 触发器中不允许包含以下 T-SQL 语句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、 LOAD LOG 、RESTORE LOG 、DISK INIT、DISK RESIZE和 RECONFIGURE (2) 触发器不能返回任何结果，为了阻止从触发器返回结果， 不要在触发器定义中包含 SELECT 语句或变量赋值。
7.1 存储过程
2）存储过程的执行 ）
通过EXEC命令可以执行一个已定义的存储过程。 语法格式： 语法格式： [ EXEC [ UTE ] ] { [ @return_status = ] { procedure_name [ ;number ] | @procedure_name_var } [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ] [ WITH RECOMPILE ] ｝
7.2.1 利用SQL命令创建触发器
【例7.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录 】 时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在， 若有一项为否，则不允许插入。 USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'check_trig' AND type = 'TR') DROP TRIGGER check_trig GO CREATE TRIGGER check_trig ON XS_KC FOR INSERT AS SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学号 FROM XS b) OR a.课程号 NOT IN (SELECT c.课程号 FROM KC c) BEGIN RAISERROR ('违背数据的一致性.', 16, 1) ROLLBACK TRANSACTION END
(2) 使用带参数的存储过程
【例7.2】从XSCJ数据库的三个表中查询某人指定课程的成绩和学 】 分。该存储过程接受与传递参数精确匹配的值。