实验六管理存储过程

1、实验六 存储过程与触发器一、存储过程【创建存储过程】：CREATE PROCEDURE OWNER.PROCEDURE NAME AS <SQL块> 如：Use pubsGoCreate procedure author_informationAs select au_lname,au_fname,title,pub_name from authors a join titleauthor ta on a.au_id=ta.au_id join titles t on t.title_id=ta.title_id join publishers p on t.pub_id=p.pu

2、b_idGo【管理存储过程】：Ø 可以使用sp_helptext命令查看创建存储过程的文本信息。Use pubsGoSp_helptext author_informationGoØ 可以用sp_help查看存储过程的一般信息。Use pubsGoSp_help author_informationGoØ 可以使用系统存储过程sp_rename修改存储过程的名字。Use pubsGoSp_rename author_information ,authors_informationGoØ 也可以使用企业管理浏览存储过程的信息，具体方法是：² 从树

3、型结构上选中存储过程所在的数据库节点，展开该节点；2 / 11² 选中数据库节点下的存储过程节点，则右边的列表列出了数据库中目前所有的存储 过程；² 选中存储过程，右击，执行属性命令，则系统将弹出如图所示对话框。 ² 可以在对话框中修改存储过程内容，并保存修改。² 如果想知道某个表被存储过程引用的情况，可以使用sp_depends，Sp_depends authors² 如果想知道某个存储过程引用表的情况，则可以使用Sp_depends procedure_name【执行存储过程】：对于存储过程的调用，应使用EXECUTE或EXEC关键字。Us

4、e pubsGo Exec authors_informationGo【删除存储过程】：Ø DROP PROCEDURE procedure,nUse pubsGoDrop procedure authors_informationGoØ 也可以使用企业管理器来删除存储过程，方法是：² 从树型结构上选中存储过程所在的数据库节点，展开该节点；² 选中数据库节点下的存储过程节点，则右边的列表列出了数据库中目前所有的存储过程；选中要删除的存储过程，右击，执行删除命令，则系统将弹出如图所示对话框。单击全部除去按钮，将删除所选中的存储过程。【修改存储过程】：SQL

5、 server允许在不改变存储过程使用许可，不改变名字的情况下，对存储过程进行定义的修改。Alter procedure procedure_nameAS sql_statement 二、触发器 触发器（triegger）是一种特殊的存储过程，它与表格紧密相连，可以看作是表格定义的一部分。当使用UPDATE、INSERT或DELETE等语句对表进行修改操作时，DBA常扩触发器来实现自动触发的处理方法。当用户修改指定表或视图中的数据时，触发器将会自动执行。触发器基于一个表创建，但是可能针对多个表进行操作。所以触发器常被用来实现复杂的商业规则。 例如：在pubs数据库晨，存放着出版商publish

6、ers的信息，出版物的信息titles、出版特与作者关联的信息titleauthor以及作者作息authors。现在，有一条出版商的信息被删除了，则所以由该出版商的出版物都应该将pub_id修改为NULL，或者删除有关的出版物信息。同样titleauthor表中的信息也应该相应地得到修改。这样关联到三张表的一致性维护问题，可以使用触发器来实现。在publishers表上设置一个DELETE触发器，当删除一条publishers信息时，触发器自动执行，对titles表和titleauthor表进行修改。 在SQL server中一张表可以有多个触发器。用户可以针对INSERT、UPDATE或DE

7、LETE语句分别设置触发器，也可以针对一张表上的特定操作设置多个触发器。解发器可以容纳非常复杂的Transact-SQL语句。但是，不管触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回退。 触发器在服务器将特定的操作（UPDATE、INSERT、DELETE）执行结束后才执行。如果在执行特定数据库操作的过程中，发生了系统错误，则触发器不会被触发。这种触发器类型是默认的类型，好AFTER类型。 在SQL server 2000中引进了一种新的触发器类型：INSTEAD OF类型。这种类型的触发器取代了触发该

8、触发器执行的SQL代码。换句话说，它将覆盖该代码引起的变化。通常这种类型的触发器用在需要维护数据一致性的地方。 触发器主要提供下列功能：Ø 级联修改数据库中的所有相关表Ø 撤消或回退违反引用完整性的操作，防止非法修改数据Ø 执行比核查约束CHECK更复杂的约束操作Ø 查找在数据修改前后的表状态之间的差别，并根据差别分别采取相应的措施Ø 在一张表的同一类型的操作（UPDATE、INSERT、DELETE）上设置多个触发器，从而可以针对同样的修改语句执行不同的多种操作注意：u 只有表的拥有者才可以在表上创建或删除触发器，这样权限不许转授。u 尽管可

9、以在触发器中引用视图或临时表，但不能在视图或临时表或系统表上创建触发器。u 使用UPDATE语句可以一次对多个数据进行修改，但不管修改了多少数据，触发器都中触发一次。u 在执行修改语句过程中，触发器的执行是修改语句事务的一部分。所以，如果触发器执行不成功，则整个修改事务将会回退。u 当使用约束、规则、默认值就可以实现预定的数据完整性时，应优先考虑使用这三种措施u TRUNCATE TABLE 虽然在功能上与DELETE操作类似，但是 TRUNCATE TABLE不会触发DELETE触发器运行。【创建触发器】：CREATE TRIGGER trigger_name ON table|view A

10、S <SQL块> Use NorthwindIf exists (select name from sysobjects Where name=tr_procedure_update AND type=TRDrop trigger tr_product_updateGoCreate trigger tr_product_update ON productsFor UPDATEDeclare msg varchar(100)Select msg=str(rowcount)+”employees updated by this statement”Print msgReturnGo这个

11、触发器在用户针对products表执行UPDATE执行，返回共修改了多少行数据。其中msg是一个变量，数据类型为varchar(100),rowcount是一个系统存储过程。它返回当前被修改的行数。创建触发器的语句CREATE TRIGGER必须写在批处理的第一行，否则系统将会返回错误信息。在CREATE TRIGGER语句中，不能使用SELECT语句返回针对表格查询的数据，因为触发器不接收用户应用程序传递的参数，从而也无法向用户应用程序返回查询表格数据所得到的结果。注意：由于系统表怕存储数据的特殊性和重要性，所以建议用户不要自己在系统表上建立触发器。【管理触发器】： 触发器是特殊的存储过程，

12、所以适用于存储过程的管理方式，都适用于触发器。所以用户完全可以使用sp_helptext，sp_help，sp_depens等系统存储过程，以及使用企业管理器来浏览触发器的有关信息，也可以使用sp_helptrigger来浏览指定表格上，指定类型的触发器的信息，语法是： Sp_helptrigger table_name, type如：use northwind GoSp_helptrigger products, delete提示：如果不设置type的值，则返回定义在该表上的所有触发器的信息。【删除触发器】：Ø DROP TRIGGER trigger ,n当用户删除某个表格时，所

13、有建立在该表上的触发器都将被删除。步骤为： 选中要创建触发器的表所在的数据库，展开该节点； 展开该数据库节点下的表节点； 选中指定的表，右击，从快捷菜单中展开全部任务子菜单，执行管理触发器命令，将弹出如图所示对话框； 从名称下拉菜单里选择要删除的触发器； 单击删除按钮，删除该触发器； 单击确定按钮完成操作。【修改触发器】：Alter trigger trigger_nameON table . Transact-SQL编程：全局变量以两个为标记，如CONNECTION局部变量以一个为标记，如A定义局部变量：DECLARE local_varible1 char(4), local_varibl

14、e2 char(8)显示局部变量：SELECT local_varible1 ， SELECT local_varible2给局部变量赋值：SET local_varible=expressionTransact-SQL语句中以BEGIN和END为程序块的关键字。无条件退出语句：RETURN.实验范例：1、用存储过程查询缺成绩的学生学号和课程号create procedure pro1as select sname,cno from student,sc where student.sno=sc.sno and grade is null2、用存储过程查询指定课程选课的学生人数最高成绩以及最低

15、成绩和平均成绩create procedure pro2 As Select count(cno), avg(grade) From sc Group by sno3、用存储过程查询指定课程选课的学生人数、最高成绩、最低成绩和平均成绩create procedure pro3 cno char(4) As Select count(sno),max(grade),min(grade),avg(grade) From sc Where cno=cno Group by cno Exec pro3 24、用存储过程求某系学生选修的课程号及成绩create procedure pro4 sdept

16、char(15),sno char(9) AsSelect cno,grade From student,sc Where student.sno=sc.sno and sdept=sdept and student.sno=sno5、用存储过程查询某门课程成绩大于80分的学生姓名create procedure pro5 cno char(4) As Select sname From student,sc Where student.sno=sc.sno and cno=cno and grade>806、创建sc1表，结构与sc一样，然后创建触发器，当SC表添加数据时，SC1表也同时添加create table sc1(sno char(9), Cno char(4), Grade int, Primary key(sno,cno), Foreign key (sno) references student(sno), Foreign key (cno) references cours