存储过程和触发器.docx

《存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《存储过程和触发器.docx（10页珍藏版）》请在三一文库上搜索。

1、第7章存储过程和触发器7.1 存储过程7.1.1 存储过程的类型(1)系统存储过程 系统存储过程是由系统提供的存储过程,作为命令执行各种操作.(2)本地存储过程 本地存储过程是指在用户数据库中创立的存储过程,这种存储过程完成特定数据库操作任务,其名称 不能以sp为前缀.(3)临时存储过程临时存储过程属于本地存储过程.如果本地存储过程的名称前面有一个“#,该存储过程就称为局部临时存储过程,这种存储过程只能在一个用户会话中使用.(4)远程存储过程远程存储过程指从远程效劳器上调用的存储过程.(5)扩展存储过程在SQL Serve环境之外执行的动态链接库称为扩展存储过程,其前缀是sp_.使用时需要先加

2、载到 SQL Server系统中,并且根据使用存储过程的方法执行.7.1.2 用户存储过程的创立与执行在用户存储过程的定义中不能使用以下对象创立语句：CREATE VIEWCREATE DEFAULTCREATE RULECREATE PROCEDURECREATE TRIGGER1.通过SQL命令创立和执行存储过程如果要通过SQL命令定义一个存储过程查询 XSC瞰据库中每个同学各门功课的成绩,然后调用该存储过程步骤如下：定义如下存储过程USE XSCJ GoCREATE PROCEDURBtudent_gradeAS SELECT XS.号,XS姓名,KC课程名,XS_KCt绩FROM XS

3、,XS_KC,KCWHERE X除号=XS_KC号 AND XS_KO程号=KC课程号Go使用存储过程的优点：(1)存储过程在效劳器端运行,执行速度快.(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译 好的二进制代码执行,提升了系统性能.(3)保证数据库的平安.使用存储过程可以完成所有数据库操作,并可通过编程方式限制上述操作对数据库信息访问的权限.(4)自动完成需要预先执行的任务.存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方 便了用户的使用,可以自动完成一些需要预先执行的任务.调用存储过程EXEC stu

4、dent_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 /* 执行的操

5、作 */对于存储过程要注意以下几点：(1)用户定义的存储过程只能在当前数据库中创立(临时过程除外,临时过程总是在tempdb中创立).(2)成功执行 CREATEPROCEDUR晤句后,过程名称存储在 sysobjects系统表中,而 CREATEPROCEDUR赠句的文本存 储在 syscomments 中.(3)自动执行存储过程SQL Server启动时可以自动执行一个或多个存储过程.这些存储过程必须由系统治理员在master数据库中创立,并在sysadmin固定效劳器角色下作为后台过程执行.(4) sql_statement 的限制除了 SET SHOWPLAN_TEX下口 SET SH

6、OWPLAN_ALL卜,其它 SET语句均可在存储过程内使用.(5)权限.CREATE PROCEDURES限默认授予 sysadmin固定效劳器角色成员,db_owner和db_ddladmin固定数据库角色成 员.2)存储过程的执行通过EXE8r令可以执行一个已定义的存储过程.语法格式：EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,n WITH RECOMPILE 存储过程的执行要注意以下几点：(1)如果存

7、储过程名的前三个字符为sp_ SQL Server会在Master数据库中寻找该过程.如果没能找到合法的过程名,SQLServer会寻找所有者名称为 dbo的过程.(2)参数可以通过 value 或 parameter_name = value 提供.(3)执行存储过程时,假设语句是批处理中的第一个语句,那么不一定要指定EXECUTE关键字.3)举例(1)设计简单的存储过程【例7.1从XSC跋据库的三个表中查询,返回学生学号、姓名、课程名、成绩、学分.该存储过程不使用任何参数.USE XSCJ/*检查是否已存在同名的存储过程,假设有,删除. */IF EXISTS (SELECT name F

8、ROM sysobjectsWHERE name = student_info AND type = P)DROP PROCEDURE student_info GO/* 创立存储过程 */CREATE PROCEDURE student_infoAS SELECT 装号,姓名,课程名,成绩,学分 FROM XS a INNER JOIN XS_KC bON a.学号 =b.学号 INNER JOIN KC tON b.课程号=t.课程号GO(2)使用带参数的存储过程【例7.2从XSC跋据库的三个表中查询某人指定课程的成绩和学分.该存储过程接受与传递参数精确匹配的值.IF EXISTS (SE

9、LECT name FROM sysobjectsWHERE name = student_info1 AND type = P)DROP PROCEDURE student_info1 GOCREATE PROCEDURE student_info1name char (8),cname char(16)AS SELECT罪号,姓名,课程名,成绩,学分FROM XS a INNER JOIN XS_KC bON a.学号 =b.学号 INNER JOIN KC tON b.课程号=t.课程号WHERE a.生名=name and t.课程名=cname GO(3)使用带有通配符参数的存储过程

10、【例7.3从三个表的联接中返回指定学生的学号、姓名、所选课程名称及该课程的成绩.该存储过程在参数中使用了模 式匹配,如果没有提供参数,那么使用预设的默认值.USE XSCJIF EXISTS (SELECT name FROM sysobjectsWHERE name = st_info AND type = P)DROP PROCEDURE st_infoGO CREATE PROCEDURE st_infoname varchar(30)=刘AS SELECT罪号,a.姓名,c.课程名,b.成绩FROM XS a INNER JOIN XS_KC bON a.学号 =b.学号 INNER

11、JOIN KC cON c.课程号=b.课程号WHERE 姓名 LIKE name GO(4)使用带OUTPUT参数的存储过程【例7.4】用于计算指定学生的总学分,存储过程中使用了一个输入参数和一个输出参数.USE XSCJ GOIF EXISTS(SELECT name FROM sysobjectsWHERE name = totalcredit AND type = P)DROP PROCEDURE totalcreditGO USE XSCJGO CREATE PROCEDURE totalcredit name varchar(40),total int OUTPUTAS SELEC

12、T total= SUM(：分)FROM XS,XS_KC,KCWHERE 姓名=name AND X翳号=XS_KC 号GROUP BY X群号 GO(5)使用OUTPUT游标参数的存储过程OUTPUT游标参数用于返回存储过程的局部游标.【例7.5在XSC敷据库的XS表上声明并翻开一个游标.USE XSCJIF EXISTS (SELECT name FROM sysobjectsWHERE name = st_cursor and type = P)DROP PROCEDURE st_cursor GOCREATE PROCEDURE st_cursor st_cursor CURSOVA

13、RYING OUTPUTASSET st_cursor = CURSOR FORWARD_ONLY STATIC FORSELECT *FROM XSOPEN st_cursor GO(6)使用 WITH ENCRYPTION项WITH ENCRYPTION?句对用户隐藏存储过程的文本.【例7.6创立加密过程,使用 sp_helptext系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments表中 获取关于该过程的信息.IF EXISTS (SELECT name FROM sysobjectsWHERE name = encrypt_this AND type = P)DR

14、OP PROCEDURE encrypt_this GOUSE XSCJ GOCREATE PROCEDURE encrypt_thisWITH 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 ma

15、ster GOCREATE PROCEDURE sp_showtableTABLE 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.idWHERE tab.name LIKE TABLE GOUSE XSCJEXEC sp_showtable xs% GO7.1.3用户存储过程的编辑修改语法格式：ALTER PROC EDURE proced

16、ure_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_info1name char(8),cname char(16)AS SELECT罪号,姓名,课程名,成绩,学分FROM XS a INNER join

17、XS_KC bON a.学号 =b.学号 INNER JOIN KC tON b.课程号=t.课程号WHERE aH 名=name and t.课程名=cnameGO【例7.9创立名为select_students的存储过程,默认情况下,该过程可查询所有学生信息,随后授予权限.USE XSCJGO IF EXISTS(SELECT name FROM sysobjects WHERE name = select_students AND type = P)DROP PROCEDURE select_students GO /*假设该存储过程已存在,那么删除 */USE XSCJ GOCREAT

18、E PROCEDURE select_students /* 创立存储过程 */AS SELECT *FROM XSORDER BY 学号 GO使用 DROP PROCEDURE语句可永久地删除存储过程.在此之前,必须确认该存储过程没有任何依赖关系.语法格式：DROP PROCEDURE procedure ,.n 【例7.10删除XSC跋据库中的student_info1存储过程.USE XSCJ GODROP PROCEDURE student_info1procedure指要删除的存储过程或存储过程组的名称；n：表示可以指定多个存储过程同时删除.语法格式CREATE TRIGGER tr

19、igger_nameON 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_

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

21、据库中创立,但触发器可以引用当前数据库的外部对象.(3)如果指定触发器所有者名限定触发器,要以相同的方式限定表名.在同一 CREATE TRIGGER句中,可以为多种操作(如INSERT和UPDATE定义相同的触发器操作.一个表的外键在 DELETE UPDATE操作上定义了级联,不能在该表上定义INSTEADOF 7.2.1利用SQL命令创立触发器(7)在触发器内可以指定任意的SET语句,所选才I的SET选项在触发器执行期间有效,并在触发器执行完后恢复到以前的设置.(8)触发器中不允许包含以下T-SQL语句：CREATE DATABASE ALTER DATABASE LOAD DATABA

22、SE RESTORE DATABASE DROP DATABASE LOAD LOG、RESTORE LOG、DISK INIT DISK RESIZE口 RECONFIGURESELECT语句或变量赋值.(9)触发器不能返回任何结果,为了阻止从触发器返回结果,不要在触发器定义中包含4 .权限CREATETRIGGER权限默认授予定义触发器的表所有者、sysadmin固定效劳器角色成员、db_owner和db_ddladmin固定数据库角色成员,并且不可转让.5 .举例【例7.11】对于XSC敷据库,如果在XS表中添加或更改数据,那么将向客户端显示一条信息./*使用带有提示消息的触发器*/US

23、E XSCJIF EXISTS (SELECT name FROM sysobjectsWHERE name = reminder AND type = TR)DROP TRIGGER reminder GOCREATE TRIGGER reminder ON XSFOR INSERT, UPDATEAS RAISERROR (4008, 16, 10) GO【例7.12在数据库XSC片创立一触发器,当向 XS_KC1插入一记录时,检查该记录的学号在XS表是否存在,检查课程号在KC表中是否存在,假设有一项为否,那么不允许插入.USE XSCJIF EXISTS (SELECT name FRO

24、M sysobjectsWHERE name = check_trig AND type = TR)DROP TRIGGER check_trig GOCREATE TRIGGER check_trigON XS_KCFOR INSERTAS SELECT *FROM inserted aWHERE a学号 NOT IN (SELECT bt 0BEGIN RAISERROR连背数据的一致性.,16, 1)ROLLBACK TRANSACTION END GO6 . INSTEAD OF虫发器的设计如果视图的数据来自于多个基表,那么必须使用INSTEAD OF触发器支持引用表中数据的插入、更新

25、和删除操作.如果视图的列为以下几种情况之一：(1)基表中的计算列.(2) IDENTITY INSERT OFF的基表中的标识列.(3)具有timestamp数据类型的基表列.该视图的INSERTS句必须为这些列指定值,INSTEAD OF触发器在构成将值插入基表的INSERT语句时会忽略指定的值.【例7.14】在XSC跋据库中创立表、视图和触发器,以说明 INSTEAD OF INSERT发器的使用.CREATE TABLE books(BookKey int IDENTITY(1,1),BookName nvarchar(10) NOT NULL,Color nvarchar(10) NO

26、T 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/*实际插入时,INSERTS句中不包含 BookKey字段和.ComputedCol.字

27、段的值*/INSERT INTO booksSELECT 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 , UPD

28、ATE 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】修改XSC敷据库中在XS表上定义的触发器reminder oUSE XSCJALTER TRIGGER reminder ON XSFOR UPDATEAS RAISERROR (执行的操作是修改,16, 10) GO7.2.4触发器的删除1.利用SQL命令删除触发器语法格式：DROP TRIGGER trigger ,.n 说明：trigger ：指要删除的触发器名称,包含触发器所有者名.n：表示可以指定多个触发器.【例7.16删除触发器reminder oUSE XSCJIF EXISTS (SELECT name FROM sysobjectsWHERE name = reminder AND type = TR)DROP TRIGGER reminder GO