存储过程和触发器

第7章 存储过程和触发器 7.1 存储过程 7.1.1 存储过程的类型 (1) 系统存储过程 系统存储过程是由系统提供的存储过程，作为命令执行各种操作。 (2) 本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3) 临时存储过程临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。(4) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。(5) 扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。7.1.2 用户存储过程的创建与执行 在用户存储过程的定义中不能使用下列对象创建语句：CREATE VIEWCREATE DEFAULT CREATE RULE CREATE PROCEDURE CREATE TRIGGER1通过SQL命令创建和执行存储过程如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，然后调用该存储过程步骤如下：定义如下存储过程USE XSCJ GoCREATE PROCEDURE student_gradeAS SELECT XS.学号,XS.姓名,KC.课程名, XS_KC.成绩FROM XS,XS_KC,KCWHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号 Go使用存储过程的优点： (1) 存储过程在服务器端运行，执行速度快。 (2) 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 (3) 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。 (4) 自动完成需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。调用存储过程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 /*执行的操作*/对于存储过程要注意下列几点：(1) 用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。(2) 成功执行 CREATE PROCEDURE语句后，过程名称存储在sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本存储在 syscomments 中。(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 | 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/*检查是否已存在同名的存储过程，若有，删除。*/IF EXISTS (SELECT name FROM sysobjects WHERE name = student_info AND type = P) DROP PROCEDURE student_info GO /*创建存储过程*/CREATE PROCEDURE student_infoAS 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 XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = student_info1 AND type = P) DROP PROCEDURE student_info1 GOCREATE 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 (3) 使用带有通配符参数的存储过程【例7.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = st_info AND type = P) DROP PROCEDURE st_infoGO CREATE PROCEDURE st_infoname varchar(30) = 刘%AS SELECT a.学号,a.姓名,c.课程名,b.成绩FROM XS a INNER JOIN XS_KC b ON a.学号 =b.学号 INNER JOIN KC c ON c.课程号= b.课程号 WHERE 姓名 LIKE name GO (4) 使用带OUTPUT参数的存储过程【例7.4】用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。USE XSCJ GO IF EXISTS(SELECT name FROM sysobjects WHERE name = totalcredit AND type = P)DROP PROCEDURE totalcreditGO USE XSCJGO CREATE PROCEDURE totalcredit name varchar(40), total int OUTPUTAS SELECT total= SUM(学分)FROM XS,XS_KC,KCWHERE 姓名=name AND XS.学号= XS_KC.学号 GROUP BY XS.学号 GO (5) 使用OUTPUT游标参数的存储过程OUTPUT 游标参数用于返回存储过程的局部游标。【例7.5】在 XSCJ数据库的XS表上声明并打开一个游标。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = st_cursor and type = P)DROP PROCEDURE st_cursor GOCREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUTAS SET st_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM XS OPEN st_cursor GO(6) 使用 WITH ENCRYPTION 选项WITH ENCRYPTION 子句对用户隐藏存储过程的文本。【例7.6】创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects WHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_this GOUSE XSCJ GOCREATE PROCEDURE encrypt_this WITH ENCRYPTIONAS SELECT * FROM XS GO (7) 创建用户定义的系统存储过程【例7.7】创建一个过程，显示表名以 xs 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 kc 开头的所有表及对应的索引。IF EXISTS (SELECT name FROM sysobjectsWHERE name = sp_showtable AND type = P) DROP PROCEDURE sp_showtable GOUSE master GOCREATE PROCEDURE sp_showtable TABLE varchar(30) = kc%AS SELECT AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE LIKE TABLE GO USE XSCJEXEC sp_showtable xs% GO7.1.3 用户存储过程的编辑修改 语法格式：ALTER PROC EDURE procedure_name ; number parameter data_type VARYING 0= default OUTPUT ,.n1 WITH RECOMPILE | ENCRYPTION| RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n2 【例7.8】对存储过程student_info1进行修改。USE XSCJGO 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.9】创建名为 select_students 的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。USE XSCJGO IF EXISTS(SELECT name FROM sysobjects WHERE name = select_students AND type = P) DROP PROCEDURE select_studentsGO /*若该存储过程已存在，则删除*/USE XSCJ GOCREATE PROCEDURE select_students /*创建存储过程*/AS SELECT *FROM XS ORDER BY 学号 GO使用DROP PROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROP PROCEDURE procedure ,.n 【例7.10】删除 XSCJ数据库中的student_info1 存储过程。USE XSCJ GODROP PROCEDURE student_info1procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。 语法格式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.2.1 利用SQL命令创建触发器2触发器中使用的特殊表 inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。 deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。 3使用触发器的限制使用触发器有下列限制：(1) CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 (2) 触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。 (3) 如果指定触发器所有者名限定触发器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。 (5) 一个表的外键在 DELETE、UPDATE 操作上定义了级联，不能在该表上定义 INSTEAD OF 7.2.1 利用SQL命令创建触发器(7)在触发器内可以指定任意的 SET 语句,所选择的 SET 选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。(8) 触发器中不允许包含以下 T-SQL 语句：CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、DISK INIT、DISK RESIZE和RECONFIGURE(9) 触发器不能返回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含 SELECT 语句或变量赋值。4权限CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。5举例【例7.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/*使用带有提示消息的触发器*/USE XSCJ IF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminder GOCREATE TRIGGER reminder ON XSFOR INSERT, UPDATE AS RAISERROR (4008, 16, 10) GO【例7.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = check_trig AND type = TR) DROP TRIGGER check_trig GOCREATE TRIGGER check_trigON XS_KCFOR INSERTAS 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【例7.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。USE XSCJ GOCREATE TRIGGER update_trigON XS_KC FOR update AS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF (COLUMNS_UPDATED() & 3) 0BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION END GO6. INSTEAD OF触发器的设计如果视图的数据来自于多个基表，则必须使用INSTEAD OF 触发器支持引用表中数据的插入、更新和删除操作。如果视图的列为以下几种情况之一： （1）基表中的计算列。（2）IDENTITY INSERT 为 OFF 的基表中的标识列。（3）具有 timestamp 数据类型的基表列。 该视图的INSERT语句必须为这些列指定值，INSTEAD OF 触发器在构成将值插入基表的 INSERT 语句时会忽略指定的值。【例7.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEAD OF INSERT触发器的使用。USE XSCJCREATE TABLE books( BookKey int IDENTITY(1,1), BookName nvarchar(10) NOT NULL, Color nvarchar(10) NOT NULL, ComputedCol AS (BookName +Color), Pages int ) GO/*建立一个视图，包含基表的所有列*/CREATE VIEW View2AS SELECT BookKey, BookName ,Color, ComputedCol, PagesFROM books GO/*在View2视图上创建一个 INSTEAD OF INSERT 触发器*/CREATE TRIGGER InsteadTrig on View2INSTEAD OF INSERTAS BEGIN/* 实际插入时，INSERT语句中不包含Book