Ch08-存储过程和触发器

1、网络数据库开发技术存储过程和触发器 张蕾1什么是存储过程SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。2存储过程的类型 (1) 系统存储过程 系统存储过程是由系统提供的存储过程，作为命令执行各种操作。 (2) 本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3) 临时存储过程 临时存储过程属于本地存储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。(4

2、) 远程存储过程 远程存储过程指从远程服务器上调用的存储过程。(5) 扩展存储过程 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。3存储过程使用存储过程的优点： (1) 存储过程在服务器端运行，执行速度快。 (2) 存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。 (3) 确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。 (4) 自动完成

3、需要预先执行的任务。存储过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。4函数有返回值，而存储过程的返回值仅说明该过程是否成功完成。存储过程与函数的比较函数与存储过程基本类似，调用时函数要用表达式方式，而存储过程不能直接调用，必须采用“EXEC 存储过程名”或“CALL 存储过程名”。5创建存储过程 在SQL Server中，可以使用三种方法创建存储过程 ：使用创建存储过程向导创建存储过程。利用SQL Server 企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过

4、程。 6用户存储过程的创建与执行 在用户存储过程的定义中不能使用下列对象创建语句：CREATE VIEWCREATE DEFAULT CREATE RULE 1通过SQL命令创建和执行存储过程如果要通过SQL命令定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩定义如下存储过程USE XSCJGoCREATE PROCEDURE student_gradeAS SELECT XS.学号,XS.姓名,KC.课程名, XS_KC.成绩FROM XS,XS_KC,KCWHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号Go7存储过程 调用存储过程EXEC stu

5、dent_gradeGO1) 创建存储过程语法格式:CREATE PROC | PROCEDURE procedure_name /*定义过程名*/ parameter data_type /*定义参数的类型*/ VARYING = default OUTPUT /*定义参数的属性*/ ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION /*定义存储过程的处理方式*/AS sql_statement .n2 /*执行的操作*/8存储过程对于存储过程要注意下列几点：(1) 用户定义的存储过程只能在当前数据库中创建。(2) 成功执行

6、CREATE PROCEDURE 语句后，过程名称存储在sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本存储在 syscomments 中。(3) 自动执行存储过程SQL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在 master 数据库中创建，并在 sysadmin 固定服务器角色下作为后台过程执行。 (4)一个存储过程的最大尺寸为128M。(5)创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。9存储过程2）存储过程的执行通过EXEC命令可以执行一个已定义的存储过程。语法格式： EXEC UTE r

7、eturn_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) 执行存储过程时，若语句是批处理

8、中的第一个语句，则不一定要指定EXECUTE 关键字。10存储过程3) 举例(1) 设计简单的存储过程例从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩、学分。该存储过程不使用任何参数。USE XSCJCREATE PROCEDURE student_info/*创建存储过程*/ASSELECT a.学号, 姓名, 课程名, 成绩, 学分 FROM XS a INNER JOIN XS_KC b ON a.学号 = b.学号 INNER JOIN KC t ON b.课程号= t.课程号 GO11存储过程(2) 使用带参数的存储过程例从XSCJ数据库的三个表中查询某人指定课程的

9、成绩和学分。该存储过程接受与传递参数精确匹配的值。USE XSCJCREATE 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.课程名=cnameGO12存储过程(3) 使用带有通配符参数的存储过程例从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在

10、参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。USE XSCJCREATE 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 nameGO 13存储过程(4) 使用带OUTPUT参数的存储过程例用于计算指定学生的总学分，存储过程中使用了一个输入参数和一个输出参数。USE XSCJGOIF EXISTS(SELECT na

11、me FROM sysobjects WHERE name = totalcredit AND type = P)DROP PROCEDURE totalcreditGOUSE XSCJGOCREATE PROCEDURE totalcredit name varchar(40), total int OUTPUTASSELECT total= SUM(学分)FROM XS,XS_KC,KCWHERE 姓名=name AND XS.学号= XS_KC.学号 GROUP BY XS.学号GO14存储过程(5) 使用OUTPUT游标参数的存储过程OUTPUT 游标参数用于返回存储过程的局部游标。例

12、在 XSCJ数据库的XS表上声明并打开一个游标。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = st_cursor and type = P)DROP PROCEDURE st_cursorGOCREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUTASSET st_cursor = CURSOR FORWARD_ONLY STATIC FORSELECT * FROM XSOPEN st_cursorGO15存储过程 (6) 使用 WITH ENCRYPTION

13、 选项WITH ENCRYPTION 子句对用户隐藏存储过程的文本。例创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects WHERE name = encrypt_this AND type = P) DROP PROCEDURE encrypt_thisGOUSE XSCJGOCREATE PROCEDURE encrypt_this WITH ENCRYPTIONASSELECT * FROM XSGO16存储过程(7)

14、 创建用户定义的系统存储过程例 创建一个过程，显示表名以 xs 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 kc 开头的所有表及对应的索引。IF EXISTS (SELECT name FROM sysobjectsWHERE name = sp_showtable AND type = P) DROP PROCEDURE sp_showtableGOUSE masterCREATE PROCEDURE sp_showtable TABLE varchar(30) = kc%AS SELECT AS TABLE_NAME, AS IND

15、EX_NAME, indid AS INDEX_IDFROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE LIKE TABLEGO USE XSCJEXEC sp_showtable xs%GO17存储过程2利用企业管理器创建用户存储过程第1步 在SQL Server 企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图所示的快捷菜单。 第2步 选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过程，如图所示，然后选

16、择“确定”按钮。 18用户存储过程的编辑修改 语法格式：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 例对存储过程student_info1进行修改。USE XSCJGOALTER PROCEDURE student_info1 name char(8),cname char(16)

17、 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.课程名=cnameGO19用户存储过程的删除 使用DROP PROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROP PROCEDURE procedure ,.n 例删除 XSCJ数据库中的student_info1 存储过程。USE XSCJGODROP PROCEDURE stu

18、dent_info1procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。 20存储过程的安全性示例假定EMP表是某用户USER1的私有表，用户SCOTT是开发者，最终用户GREEN，现在要求GREEN只能通过SCOTT创建的存储过程HIRE_EMP存取EMP表,该存储过程查询或插入雇员记录.从USER1用户环境下,为SCOTT用户授予对EMP表的相应权限。解：GRANT SELECT，INSERT，UPDATE，DELETE ON EMP TO SCOTT；SCOTT一旦创建完HIRE_EMP过程,给GREEN用户授予对该过程的EXECUTE权限. G

19、RANT EXECUTE ON HIRE_EMP TO GREEN;21使用系统存储过程来查看存储过程 sp_help：用于显示存储过程的参数及其数据类型 sp_help objname= name 参数name为要查看的存储过程的名称。 sp_helptext：用于显示存储过程的源代码 sp_helptext objname= name 参数name为要查看的存储过程的名称。sp_depends：显示和存储过程相关的数据库对象 sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的

20、存储过程列表22触发器触发器是一种特殊的存储过程，它在特定语言事件发生时自动执行，通常用于实现强制业务规则和数据完整性。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。23 触发器主要优点触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。 触发器可以通过数据库中的相关表进行层叠更改。 触发器可以强制限制，这些限制比用 CHECK 约束所定义的更复杂。 24利用SQL命令创建触发器 语法格式CREATE TRIGGER trigger_na

21、me 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

22、.n /*两个IF子句用于说明触发器执行的条件*/sql_statement .n /*一条或若干条SQL语句*/ 25利用SQL命令创建触发器2触发器中使用的特殊表 inserted 逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。 deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。 3使用触发器的限制使用触发器有下列限制：(1) CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 (2) 触发器只能在当前的数据库中创建，但触发器可以

23、引用当前数据库的外部对象。 (3) 如果指定触发器所有者名限定触发器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。 (5) 一个表的外键在 DELETE、UPDATE 操作上定义了级联，不能在该表上定义 INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器。26利用SQL命令创建触发器 (7) 在触发器内可以指定任意的 SET 语句，所选择的 SET 选项在触发器执行期间有效，并在触发器执行完后恢复到以前的设置。 (8) 触发器中不允许包含以下 T-SQL 语句：

24、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 固定数据库角色成员，并且不可转让。27利用SQL命令创建触发

25、器5举例例对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。USE XSCJ /*使用带有提示消息的触发器*/IF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGOCREATE TRIGGER reminder ON XSFOR INSERT, UPDATE AS RAISERROR (4008, 16, 10)GO注：4008为用户自定义消息，可用sp_addmessage将自定义消息添加到sysmessages表中。28利

26、用SQL命令创建触发器例在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = check_trig AND type = TR) DROP TRIGGER check_trigGOCREATE TRIGGER check_trig ON XS_KCFOR INSERTASSELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT b.学

27、号 FROM XS b) OR a.课程号 NOT IN (SELECT c.课程号 FROM KC c)BEGIN RAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTIONEND29利用SQL命令创建触发器例在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。USE XSCJGOCREATE TRIGGER update_trigON XS_KC FOR update AS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF (COLUMNS_UPDAT

28、ED() & 3) 0BEGINRAISERROR (违背数据的一致性., 16, 1) ROLLBACK TRANSACTION ENDGO30利用SQL命令创建触发器6. INSTEAD OF触发器的设计如果视图的数据来自于多个基表，则必须使用INSTEAD OF 触发器支持引用表中数据的插入、更新和删除操作。如果视图的列为以下几种情况之一： （1）基表中的计算列。（2）IDENTITY INSERT 为 OFF 的基表中的标识列。（3）具有 timestamp 数据类型的基表列。 该视图的INSERT语句必须为这些列指定值，INSTEAD OF 触发器在构成将值插入基表的 INSERT

29、语句时会忽略指定的值。31例在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)GOCREATE VIEW View2/*建立一个视图，包含基表的所有列*/AS SELECT BookKey, BookName ,Color, Compu

30、tedCol, PagesFROM booksGO/*在View2视图上创建一个 INSTEAD OF INSERT 触发器*/CREATE TRIGGER InsteadTrig on View2INSTEAD OF INSERTASBEGIN /*实际插入时，INSERT语句中不包含BookKey字段和ComputedCol值*/INSERT INTO books SELECT BookName ,Color, Pages FROM insertedENDGO32利用企业管理器创建触发器 步骤如下： 第1步 展开服务器组，然后展开服务器。第2步 展开“数据库”文件夹，展开将定义触发器的表所

31、属的数据库，然后单击“表”文件夹。第3步 选择将在其上创建触发器的表右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图所示。执行该命令后，进入如图所示的界面。 第4步 在“名称”中，单击 “新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。 33触发器的修改1利用SQL命令修改触发器语法格式：ALTER TRIGGER trigger_nameON ( table | view ) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLI

32、CATION ASsql_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 34触发器的修改例修改XSCJ数据库中在XS表上定义的触发器remind

33、er。USE XSCJALTER TRIGGER reminder ON XSFOR UPDATE AS RAISERROR (“执行的操作是修改”, 16, 10)GO2. 通过企业管理器修改触发器进入企业管理器，修改触发器的步骤与创建的步骤相同，进入界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。说明：trigger_name：指要更改的现有触发器。 如果原来的触发器定义是用 WITH ENCRYPTION 或 RECOMPILE 创建的，那么只有在ALTER TRIGGER中也包含这些选项时，这些选项才有效。其他参数含义参考创建触发器命令部分。35触发器的删除1

34、. 利用SQL命令删除触发器语法格式：DROP TRIGGER trigger ,.n 说明：trigger：指要删除的触发器名称，包含触发器所有者名。n：表示可以指定多个触发器。例 删除触发器reminder。USE XSCJIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminderGO2通过企业管理器删除触发器进入界面后在“名称”对应的下拉表中选择要删除的触发器名，然后选择“删除”按钮。36sp_help、sp_helptext和sp_depends具体用

35、途和语法形式 sp_help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help 触发器名称 sp_helptext：用于查看触发器的正文信息sp_helptext 触发器名称 sp_depends：用于查看指定触发器所引用的表或者指定的表涉及到的所有触发器。sp_depends 触发器名称sp_depends 表名37附例：用户登录验证CREATE PROC upUserLoginstrLoginName NVARCHAR(20),strLoginPwd NVARCHAR(20),blnReturn BIT OUTPUTAS- 定义一个临时用来保存密码的变量DECLARE strPwd NVARCHAR(20)BEGIN - 从表中查询当前用户的密码，赋值给 strPwd 变量，下面要对它进行比较 SELECT strPwd=uLoginPwd FROM uUser WHERE uLoginName=strLoginName IF strLoginPwd = strPwd BEGIN SET blnRetu