存储过程和触发器.docx

第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 FROM XS,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 FROM XS a INNER JOIN XS_KC b ON a.学号 =b.学号 INNER JOIN KC c 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,KC 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 tab.name AS TABLE_NAME, inx.name AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE tab.name 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基表中的计算列。2IDENTITY 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语句中不包含BookKey字段与putedCol.字段的值*/INSERT INTO books SELECT BookName ,Color, Pages FROM inserted END GO7.2.3 触发器的修改1利用SQL命令修改触发器语法格式：ALTER TRIGGER trigger_nameON ( table | view ) WITH ENCRYPTION   ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE          NOT FOR REPLICATION         AS sql_statement .n    | ( FOR | AFTER | INSTEAD OF ) INSERT , UPDATE          NOT FOR REPLICATION         AS IF UPDATE ( column )         AND | OR UPDATE ( column ) .n         | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask )         comparison_operator column_bitmask .n          sql_statement .n  【例7.15】修改XSCJ数据库中在XS表上定义的触发器reminder。USE XSCJALTER TRIGGER reminder ON XSFOR UPDATE AS RAISERROR (“执行的操作是修改, 16, 10) GO7.2.4 触发器的删除1. 利用SQL命令删除触发器语法格式：DROP TRIGGER trigger ,.n 说明：trigger：指要删除的触发器名称，包含触发器所有者名。n：表示可以指定多个触发器。【例7.16】 删除触发器reminder。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = 'reminder' AND type = 'TR') DROP TRIGGER reminder GO第 17 页