存储过程、触发器.ppt

《存储过程、触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程、触发器.ppt（30页珍藏版）》请在三一办公上搜索。

1、第6章 存储过程、触发器,6.1 存储过程,6.2 触发器,存储过程的类型,(1)系统存储过程 系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_，例如常用的显示系统对象信息的sp_help存储过程，它们为检索系统表的信息提供了方便快捷的方法。系统存储过程允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。常用的系统存储过程，请见附录。(2)本地存储过程 本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，其名称不能以sp_为前缀。(3)临时存储过程 临时存储过程属于本地存

2、储过程。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。如果本地存储过程的名称前有两个“#”，该过程就是全局临时存储过程，这种存储过程可以在所有用户会话中使用。(4)远程存储过程 远程存储过程指从远程服务器上调用的存储过程。(5)扩展存储过程 在SQL Server2000环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server2000系统中，并且按照使用存储过程的方法执行。,用户存储过程的创建与执行,用户存储过程只能定义在当前数据库中，可以使用SQL命令语句或SQL Server的企业管理

3、器创建存储过程。缺省情况下，用户创建的存储过程归数据库所有者拥有，数据库的所有者可以把许可授权给其他用户。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/*执行的操作*/说明：参数procedur

4、e_name用于指定存储过程名，必须符合标识符规则，且对于数据库及其所有者必须唯一；创建局部临时过程，可以在 procedure_name 前面加一个“#”；创建全局临时过程，可以在 procedure_name 前加“#”。参数number为可选的整数，用于区分同名的存储过程，以便用一条 DROP PROCEDURE 语句删除一组存储过程；,用户存储过程的创建与执行,FOR REPLICATION用于说明不能在订阅服务器上执行为复制创建的存储过程，该选项不能和 WITH RECOMPILE一起使用。参数sql_statement代表过程体包含的T-SQL 语句，参数n2说明一个存储过程可以包

5、含多条 T-SQL 语句。对于存储过程要注意下列几点：(1)用户定义的存储过程只能在当前数据库中创建（临时过程除外，临时过程总是在 tempdb 中创建）。(2)成功执行 CREATE PROCEDURE 语句后，过程名称存储在 sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本存储在 syscomments 中。(3)自动执行存储过程 QL Server 启动时可以自动执行一个或多个存储过程。这些存储过程必须由系统管理员在 master 数据库中创建，并在 sysadmin 固定服务器角色下作为后台过程执行。这些过程不能有任何输入参数。(4)sql_statem

6、ent的限制 除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL外，其它SET 语句均可在存储过程内使用。,用户存储过程的创建与执行,如下语句必须使用对象所有者名对数据库对象进行限定：CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 及DBCC语句。权限。CREATE PROCEDURE的权限默认授予sysadmin固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员。sysadmin 固定服务器角色

7、成员和 db_owner 固定数据 库角色成员可以将 CREATE PROCEDURE 权限转让给其他用户。注意：存储过程的定义不能跨越批处理。2存储过程的执行 通过EXEC命令可以执行一个已定义的存储过程。语法格式：EXEC UTE return_status=procedure_name;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT,.n WITH RECOMPILE,用户存储过程的创建与执行,说明：参数return_status为可选的整型变量，保存存储过程的返回状态，EXECUTE语句使用该变量前，必

8、须对其定义。参数procedure_name 和number用于调用已定义的一组存储过程中的某一个，procedure_name代表了存储过程的组名，number用于指定组中的存储过程。定义存储过程组的目的是以便用一条DROP PROCEDURE 语句删除一组存储过程，对过程分组后，不能删除组中的单个过程。参数procedure_name_var代表存储过程名。parameter为CREATE PROCEDURE 语句中定义的参数名；value为存储过程的实参；variable为变量，用于保存OUTPUT参数返回的值。DEFAULT关键字表示不提供实参，而是使用对应的默认值。n：表示实参可有多

9、个。关键字WITH RECOMPILE指定强制编译。存储过程的执行要注意下列几点：(1)如果存储过程名的前三个字符为 sp_，SQL Server 会在 Master 数据库中寻找该过程。如果没能找到合法的过程名，SQL Server 会寻找所有者名称为 dbo 的过程。(2)参数可以通过 value 或 parameter_name=value 提供。(3)执行存储过程时，若语句是批处理中的第一个语句，则不一定要指定EXECUTE 关键字。,用户存储过程的创建与执行,3存储过程的几种情况(1)不使用任何参数的存储过程【例6.1】从XSCJ数据库的三个表中查询，返回学生学号、姓名、课程名、成绩

10、、学分。该存储过程不使用任何参数。/*创建存储过程*/CREATE PROCEDURE student_info AS SELECT a.学号,姓名,课程名,成绩,t.学分 FROM XS a INNER JOIN XS_KC b ON a.学号=b.学号 INNER JOIN KC t ON b.课程号=t.课程号 student_info 存储过程可以通过以下方法执行：EXECUTE student_info 或者 EXEC student_info 如果该过程是批处理中的第一条语句，则可使用：student_info,用户存储过程的创建与执行,(2)使用带参数的存储过程【例6.2】从XS

11、CJ数据库的三个表中查询某人指定课程的成绩和学分。该存储过程接受与传递参数精确匹配的值。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 student_info1 存储过程有多种执行方式，下面列出了一部分：EXECUTE student_info1 王林,计算机基础

12、 或者 EXECUTE student_info1 name=王林,cname=计算机基础 或者 EXECUTE student_info1 cname=计算机基础,name=王林 或者 EXEC student_info1 王林,计算机基础 或者 EXEC au_info cname=计算机基础,name=王林,用户存储过程的创建与执行,(3)使用带有通配符参数的存储过程【例6.3】从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩。该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。CREATE PROCEDURE st_infoname varcha

13、r(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 st_info 存储过程可以有多种执行形式，下面列出了一部分：EXECUTE st_info/*参数使用默认值*/或者 EXECUTE st_info 王%/*传递给name 的实参为王%*/或者 EXECUTE st_info 王张%(4)使用带OUTPUT参数的存储过程,用户存储过程的创建与执行,【例6.4】用于计算指定学生的总学分，

14、存储过程中使用了一个输入参数和一个输出参数。CREATE PROCEDURE totalcredit name varchar(40),total int OUTPUT AS SELECT total=SUM(KC.学分)FROM XS,XS_KC,KCWHERE 姓名=name AND XS.学号=XS_KC.学号 and XS_KC.课程号=KC.课程号 GROUP BY XS.学号 注意：OUTPUT 变量必须在创建表和使用该变量时都进行定义。定义时的参数名和调用时的变量名不一定要匹配，不过数据类型和参数位置必须匹。DECLARE t_credit char(20),total int

15、EXECUTE totalcredit 王林,total OUTPUT SELECT 王林,total(5)使用OUTPUT游标参数的存储过程 OUTPUT 游标参数用于返回存储过程的局部游标。【例6.5】在 XSCJ数据库的XS表上声明并打开一个游标。CREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUT AS SET st_cursor=CURSOR FORWARD_ONLY STATIC FOR SELECT*FROM XS OPEN st_cursor,用户存储过程的创建与执行,在如下的批处理中，声明一局部游标变量，执行上述

16、存储过程过程并将游标赋值给局部游标变量，然后通过该游标变量读取记录。DECLARE MyCursor CURSOR EXEC st_cursor st_cursor=MyCursor OUTPUT WHILE(FETCH_STATUS=0)BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor(6)使用 WITH ENCRYPTION 选项 WITH ENCRYPTION 子句对用户隐藏存储过程的文本。【例6.6】创建加密过程，使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 s

17、yscomments 表中获取关于该过程的信息。CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT*FROM XS,用户存储过程的创建与执行,通过系统存储过程sp_helptext可显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。执行如下语句：EXEC sp_helptext encrypt_this 结果集为提示信息：对象备注已加密。(7)创建用户定义的系统存储过程【例6.7】创建一个过程，显示表名以 xs 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 kc 开头的所有表及对应的索引。USE

18、 master GO CREATE 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 XSCJ EXEC sp_showtable xs%GO,用户存储过程的创建与执行,4利用企业管理器编辑用户存储过程 如果要通过企业管理器

19、界面定义一个存储过程查询XSCJ数据库中每个同学各门功课的成绩，步骤如下：第1步 在SQL Server 企业管理器窗口中，选择相应的服务器、数据库和存储过程图标（本例选择XSCJ数据库），单击鼠标右键，出现如图6.1所示的快捷菜单。第2步 选择新建存储过程，则出现编辑存储过程的属性窗口，在窗口中输入定义的存储过程，如图6.2所示，然后选择“确定”按钮。,图6.1 存储过程的快捷菜单 图6.2 编辑存储过程的属性窗口,用户存储过程的修改,使用ALTER PROCEDURE命令可修改已存在的存储过程并保留以前赋予的许可。语法格式：ALTER PROC EDURE procedure_name;n

20、umber parameter data_type VARYING 0=default OUTPUT,.n1 WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement.n2 说明：各参数含义与CREATE PROCEDURE相同。如果原来的过程定义是用 WITH ENCRYPTION 或 WITH RECOMPILE 创建的，那么只有在 ALTER PROCEDURE 中也包含这些选项时，这些选项才有效。ALTER PROCEDURE 权限默认授予 sysadmin 固定服务器角色成员、db_

21、owner 和 db_ddladmin 固定数据库角色成员和过程的所有者且不可转让。,用户存储过程的修改,用 ALTER PROCEDURE 更改后，过程的权限和启动属性保持不变。【例6.8】对存储过程student_info1进行修改。USE XSCJ GO ALTER PROCEDURE student_info1 name char(8),cname char(16)AS SELECT a.学号,姓名,课程名,成绩,t.学分 FROM XS a INNER join XS_KC b ON a.学号=b.学号 INNER JOIN KC t ON b.课程号=t.课程号 WHERE a.姓

22、名=name and t.课程名=cname GO【例6.9】创建名为 select_students 的存储过程，默认情况下，该过程可查询所有学生信息，随后授予权限。当该过程需更改为能检索计算机专业的学生信息时，用 ALTER PROCEDURE 重新定义该存储过程。,用户存储过程的修改,USE XSCJGOCREATE PROCEDURE select_students/*创建存储过程*/AS SELECT*FROM XS ORDER BY 学号GO修改存储过程select_studentsALTER PROCEDURE select_students WITH ENCRYPTIONAS

23、SELECT*FROM XS WHERE 专业名=计算机 ORDER BY 学号GO,用户存储过程的删除,当不再使用一个存储过程时，就要把它从数据库中删除。使用DROP PROCEDURE语句可永久地删除存储过程。在此之前，必须确认该存储过程没有任何依赖关系。语法格式：DROP PROCEDURE procedure,.n 说明：procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除。默认情况下，将 DROP PROCEDURE 权限授予过程所有者，该权限不可转让。db_owner 和 db_ddladmin 固定数据库角色成员和 sysadmin 固定服

24、务器角色成员可以通过在 DROP PROCEDURE 内指定所有者删除任何对象。若要查看过程名列表，可使用 sp_help系统存储过程。若要显示过程定义（存储在 syscomments 系统表内），可使用 sp_helptext。【例6.10】删除 XSCJ数据库中的student_info1 存储过程。USE XSCJ GO DROP PROCEDURE student_info1,6.2.1 利用SQL命令创建触发器,SQL命令创建触发器语法格式：CREATE TRIGGER trigger_name ON table|view/*指定操作对象*/WITH ENCRYPTION/*说明是否

25、采用加密方式*/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语句*/,6.2.1 利用SQL命令创建触发器,说明：参数t

26、rigger_name用于指定触发器名。触发器名必须符合标识符规则，并且在数据库中必须唯一，可以包含触发器所有者名。Table|view指在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以包含表或视图的所有者名。使用关键字WITH ENCRYPTION可防止将触发器作为 SQL Server 复制的一部分发布。bitwise_operator为用于比较运算的位运算符。updated_bitmask为整型的位屏蔽码，与实际更新或插入的列对应，例如：表t包含列 C0、C1、C2、C3 和 C4。假定该表上有 UPDATE 触发器，若要检查列 C0、C2、C4是否都有更新，可指定upda

27、ted_bitmask的值为00010101=0 x15；若要检查是否只有列 C1 有更新，可指定updated_bitmask的值为000000102。comparison_operator为比较运算符；column_bitmask为列屏蔽码，用来检查是否已更新或插入了对应列。参数sql_statement为触发器的T-SQL语句，当执行DELETE、INSERT 或 UPDATE操作时，对应的触发器操作将生效。n表示触发器中可以包含多条 T-SQL 语句。2触发器中使用的特殊表 执行触发器时，系统创建了两个特殊的逻辑表inserted表和deleted表，下面介绍一下这两个表的内容。ins

28、erted 逻辑表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中。,6.2.1 利用SQL命令创建触发器,deleted逻辑表：用于保存已从表中删除的记录，当触发一个DELETE触发器时，被删除的记录存放到deleted逻辑表中。修改一条记录等于插入一新记录，同时删除旧记录。当对定义了UPDATE触发器的表记录修改时，表中原记录移到deleted表中，修改过的记录插入到inserted表中。触发器可检查deleted表、inserted表及被修改的表。例如，若要检索 deleted、inserted 表中的所有记录，可使用如下语句：SELECT*

29、FROM deleted SELECT*FROM inserted deleted、inserted 逻辑表的查询方法与数据库表的查询方法相同。3使用触发器的限制 使用触发器有下列限制：(1)CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。(2)触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。(3)如果指定触发器所有者名限定触发器，要以相同的方式限定表名。(4)在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。,6.2.1 利用SQL命令创建触发器,(5)如果一个表的外键在

30、 DELETE、UPDATE 操作上定义了级联，则不能在该表上定义 INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器。(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)触发器不能返

31、回任何结果，为了阻止从触发器返回结果，不要在触发器定义中包含 SELECT 语句或变量赋值。如果必须在触发器中进行变量赋值，则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。4权限 CREATE TRIGGER 权限默认授予定义触发器的表所有者、sysadmin 固定服务器角色成员、db_owner 和 db_ddladmin 固定数据库角色成员，并且不可转让。,6.2.1 利用SQL命令创建触发器,【例6.11】对于XSCJ数据库，如果在XS表中添加或更改数据，则将向客户端显示一条信息。/*使用带有提示消息的触发器*/USE XSCJ GO CREATE TRIGG

32、ER reminder ON XSFOR INSERT,UPDATE AS RAISERROR(4008,16,10)GO 消息 4008 是sysmessages 中的用户定义消息。有关创建用户定义消息的方法请参考附录的sp_addmessage存储过程。,6.2.1 利用SQL命令创建触发器,【例6.12】在数据库XSCJ中创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表是否存在，检查课程号在KC表中是否存在，若有一项为否，则不允许插入。USE XSCJ GO CREATE TRIGGER check_trigON XS_KCFOR INSERT AS SELECT*F

33、ROM 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,6.2.1 利用SQL命令创建触发器,【例6.13】在XSCJ数据库的XS_KC表上创建一触发器，若对学号列和课程号列修改，则给出提示信息，并取消修改操作。通过调用 COLUMNS_UPDATED()函数，可快速测试对学号列和课程号列修改所做的更改。USE XSCJ GO CREATE TRIGGE

34、R update_trigON XS_KC FOR update AS/*检查学号列（C0）和课程号列（C1）是否被修改，如果有某些列被修改了，则取消修改操作。*/IF(COLUMNS_UPDATED()&3)0BEGIN RAISERROR(违背数据的一致性.,16,1)ROLLBACK TRANSACTION END GO,6.2.1 利用SQL命令创建触发器,5.INSTEAD OF触发器的设计 如果视图的数据来自于多个基表，则必须使用INSTEAD OF 触发器支持引用表中数据的插入、更新和删除操作。例如，若在一个多表视图上定义了 INSTEAD OF INSERT 触发器，视图各列的

35、值可能允许为空也可能不允许为空，若视图某列的值不允许为空，则 INSERT 语句必须为该列提供相应的值。如果视图的列为以下几种情况之一：（1）基表中的计算列。（2）IDENTITY INSERT 为 OFF 的基表中的标识列。（3）具有 timestamp 数据类型的基表列。该视图的INSERT语句必须为这些列指定值，INSTEAD OF 触发器在构成将值插入基表的 INSERT 语句时会忽略指定的值。【例6.14】在XSCJ数据库中创建表、视图和触发器，以说明INSTEAD OF INSERT触发器的使用。,6.2.1 利用SQL命令创建触发器,直接引用 books表的 INSERT 语句不

36、能为BookKey字段和.ComputedCol.字段提供值，例如：-正确的INSERT语句 INSERT INTO books(BookName,Color,Pages)VALUES(计算机辅助设计,红色,100)-查看INSERT 语句的执行结果:SELECT*FROM books-不正确的INSERT语句 INSERT INTO books VALUES(2,计算机辅助设计,红色,绿色,100)但对于引用 View2 视图的 INSERT 语句视图的每一列都指定值，例如：-对于视图View2，正确的 INSERT 语句 INSERT INTO View2(BookKey,BookName

37、,Color,ComputedCol,Pages)VALUES(4,计算机辅助设计,红色,绿色,100)-查看INSERT 语句的执行结果 SELECT*FROM View2 在执行视图的插入语句时，虽然将 BookKey和 ComputedCol字段的值传递到了InsteadTrig触发器，但触发器中的INSERT语句没有选择 inserted表BookKey和 ComputedCol字段的值。,6.2.2 利用企业管理器创建触发器,步骤如下：第1步 展开服务器组，然后展开服务器。第2步 展开“数据库”文件夹，展开将定义触发器的表所属的数据库，然后单击“表”文件夹。第3步 选择将在其上创建触

38、发器的表右击，出现快捷菜单，选择“所有任务”菜单项下的“管理触发器”子菜单项，如图6.3所示。执行该命令后，进入如图6.4所示的界面。,图6.3 企业管理器中定义触发器的快捷菜单 图6.4 企业管理器中定义触发器的界面,第4步 在“名称”中，单击“新建”，在“文本”框中输入触发器文本。若要检查语法，单击“检查语法”命令。,触发器的修改,1利用SQL命令修改触发器 语法格式：ALTER TRIGGER trigger_name ON(table|view)WITH ENCRYPTION(FOR|AFTER|INSTEAD OF)DELETE,INSERT,UPDATE NOT FOR REPLI

39、CATION 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,触发器的修改,【例6.15】修改XSCJ数据库中在XS表上定义的触发器reminder。USE XSCJ ALTER TRIGGER r

40、eminder ON XSFOR UPDATE AS RAISERROR(“执行的操作是修改”,16,10)GO 2.通过企业管理器修改触发器 进入企业管理器，修改触发器的步骤与创建的步骤相同，进入图6.2的界面后在“名称”对应的下拉表中选择要修改的触发器名即可进入触发器修改状态。,触发器的删除,【例6.16】删除触发器reminder。USE XSCJ IF EXISTS(SELECT name FROM sysobjects WHERE name=reminder AND type=TR)DROP TRIGGER reminder GO 2通过企业管理器删除触发器 进入图6.2的界面后在“名称”对应的下拉表中选择要删除的触发器名，然后选择“删除”按钮。习 题 1.试说明存储过程的特点及分类。2.举例说明存储过程的定义与调用。3.举例说明触发器的使用。,1.利用SQL命令删除触发器 语法格式：DROP TRIGGER trigger,.n 说明：trigger：指要删除的触发器名称，包含触发器所有者名。n：表示可以指定多个触发器。,