网络数据库_14关系型数据库的存储过程与触发器

1、存储过程存储过程触发器触发器基本概念基本概念n 存储过程是事先编好的、存储在数据存储过程是事先编好的、存储在数据库中的程序，是一组预先编译过的库中的程序，是一组预先编译过的SQLSQL语句组语句组成的成的SQL SeverSQL Sever数据库应用程序。这些程序用数据库应用程序。这些程序用来完成对数据库的指定操作。它能够检索系统来完成对数据库的指定操作。它能够检索系统信息，管理数据库或执行其他任务。信息，管理数据库或执行其他任务。客户端应用客户端应用（不使用存储过程）（不使用存储过程）客户端应用客户端应用（使用存储过程）（使用存储过程）. .Start transactionStart tr

2、ansaction. .INSERT dataINSERT dataUPDATE dataUPDATE dataDELETE dataDELETE dataEnd transactionEnd transaction. . .Start transactionStart transaction. .C a l l S t o r e d C a l l S t o r e d procedureprocedureEnd transactionEnd transaction. .DBMSDBMSServerServerDBMSDBMSServerServerProcedure:Procedure

3、:INSERT dataINSERT dataUPDATE dataUPDATE dataDELETE dataDELETE data(a)(a)(b)(b) 不使用不使用存储过程时，存储过程时，所有的数据所有的数据处理都在客处理都在客户端完成；户端完成；而使用存储而使用存储过程时，可过程时，可以使数据处以使数据处理在服务器理在服务器端完成。端完成。存储过程的处理流程对象信息进入 sysobjects 和 syscomments系统表 编译过的执行计划存放在缓冲区里面编译优化语法分析使用存储过程具有以下优点：使用存储过程具有以下优点：u降低网络通讯量降低网络通讯量u执行速度快执行速度快u具有安

4、全控制机制具有安全控制机制u能减轻程序设计工作量能减轻程序设计工作量存储存储过程过程类型类型系统存储过程系统存储过程用户存储过程用户存储过程临时存储过程临时存储过程扩展存储过程扩展存储过程系统存储过程n SQL Server本身提供了一些存储过程，用于管理SQL Server和显示有关数据库和用户的信息，我们称之为系统存储过程。用户存储过程n 用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。创建存储过程语法创建存储过程语法CREATE PROC EDURE procedure_name CREATE PROC EDU

5、RE procedure_name parameter data_type parameter data_type VARYING = default OUTPUT VARYING = default OUTPUT ,.n ,.n WITH WITH RECOMPILE | ENCRYPTION | RECOMPILE , RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ENCRYPTION AS sql_statement .n AS sql_statement .n 存储过程举例存储过程举例例一ncreate proc sname_procn

6、sno char(10),sname char(20) outputnasnselect sname=sname from student where sno=snondeclare sname1 char(20)nexec sname_proc sno=090660101,sname=sname1 outputnselect sname1开发语言调用存储过程开发语言调用存储过程n开发语言如何调用存储过程呢?n第一步:首先建立数据库连接n第二步:调用存储过程开发语言调用存储过程开发语言调用存储过程ncreate proc dbo.age_procnage char(20) outputnasn

7、select age=sage from student where sno=090660101Java 调用存储过程开发语言调用存储过程开发语言调用存储过程nimport java.sql.*; n public class test_connection n public static void main(String args) throws Exception n String id=null,name=null; n n String url=jdbc:odbc:testodbcjdbc; n tryn Class.forName(sun.jdbc.odbc.JdbcOdbcDriv

8、er); /加载驱动System.out.println(hhhhhhhh11111111111111111);nConnection conn=DriverManager.getConnection(url,sa,123456); /使用SQL-SERVER2000nSystem.out.println(hhhhhhhh2222222222); nSystem.out.println(hhhhhhhh333333333);nSystem.out.println(hhhhhhhh4444444444444);n CallableStatement cstmt = conn.prepareCal

9、l(call dbo.age_proc(?);n System.out.println(hhhhh5555);n cstmt.registerOutParameter(1, java.sql.Types.INTEGER);n System.out.println(hhhhh66666);n cstmt.execute();n System.out.println(MANAGER ID: + cstmt.getInt(1);n n/rs.close(); ncstmt.close(); ncatch(Exception e)System.out.println(hhhhhhhh);nn触发器概述

10、触发器概述n触发器是一类触发器是一类特殊的存储过程特殊的存储过程，被定义为在对，被定义为在对表或视图发出表或视图发出 UPDATEUPDATE、INSERT INSERT 或或 DELETE DELETE 语语句时句时自动执行自动执行。n触发器是与表紧密相连，是与某个表关联的。触发器是与表紧密相连，是与某个表关联的。也就是说，如果在这个表上发生了也就是说，如果在这个表上发生了UPDATEUPDATE、INSERT INSERT 或或 DELETE DELETE 操作，就会触发相应的触操作，就会触发相应的触发器进行工作。发器进行工作。触发器优点触发器优点n1 1强制比强制比checkcheck更

11、复杂的约束更复杂的约束n2 2使用自定义的错误提示信息使用自定义的错误提示信息n3 3实现数据库中多张表的级联修改实现数据库中多张表的级联修改n4 4比较数据库修改前后的数据状态比较数据库修改前后的数据状态触发器触发器分类n一一DML触发器触发器qAFTER触发器触发器qINSTEAD OF触发器触发器n二二DDL触发器触发器DMLDML触发器分类触发器分类nAFTERAFTER触发器触发器q触发器在触发它们的语句完成后执行。触发器在触发它们的语句完成后执行。q如果该语句因错误（如违反约束或语法错误）而失败，触发如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。器将不会执行。q

12、不能为视图指定不能为视图指定 AFTER AFTER 触发器。触发器。q在在 SQL Server 2005 SQL Server 2005 中中 AFTER AFTER 是默认触发器。是默认触发器。nINSTEAD OFINSTEAD OF触发器触发器q该触发器代替触发操作执行。该触发器代替触发操作执行。q可在表和视图上指定可在表和视图上指定 INSTEAD OF INSTEAD OF 触发器。触发器。q只能为每个触发操作（只能为每个触发操作（INSERTINSERT、UPDATE UPDATE 和和 DELETEDELETE）定义一）定义一个个 INSTEAD OF INSTEAD OF

13、触发器。触发器。创建触发器语法创建触发器语法CREATE TRIGGER trigger_name CREATE TRIGGER trigger_name ON table | view ON table | view WITH ENCRYPTION WITH ENCRYPTION BEFOR | AFTER | INSTEAD OF INSERT DELETE, BEFOR | AFTER | INSTEAD OF INSERT DELETE, UPDATE UPDATE AS AS sql_statement sql_statement DML触发器创建 当创建一个触发器时必须指定如下选项

14、：当创建一个触发器时必须指定如下选项： （1）名称；）名称；（2）在其上定义触发器的表；）在其上定义触发器的表；（3）触发器将何时激发；）触发器将何时激发；（4）激活触发器的数据修改语句，有效选项为）激活触发器的数据修改语句，有效选项为 INSERT、UPDATE 或或 DELETE，多个数据修改语句可激活同一个触，多个数据修改语句可激活同一个触发器；发器；（5）执行触发操作的编程语句。）执行触发操作的编程语句。 触发器应用举例触发器应用举例ncreate trigger sno_triggernon studentnafter updatenasn if update(sno) nbegin

15、nraiserror(you can not update sno,16,2)n rollbackn endndrop trigger sno_trigger触发器应用举例触发器应用举例ncreate trigger delete_sc_snonon scninstead of deletenasnif exists(select * from sc)nbeginnraiserror(you can not delete,16,2)n rollbackn endInserted和deleted表n触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。n这两张表存储在高速缓

16、存中，SQL Server 自动创建和管理这两个表。n这两个表的结构和与当前触发器所关联的表的结构一样。n可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；n只有在触发器中才可以使用这两个表，且不能直接对表中的数据进行更改。How an INSERT Trigger WorksINSERT statement to a table with an INSERT Trigger DefinedINSERT Order Details VALUES(10525, 2, 19.00, 5, 0.2)OrderID105221052310524ProductID10417U

17、nitPrice31.009.6530.00Quantity7924Discount 5 19.002 0.210523Insert statement logged10523219.0050.2TRIGGER Actions ExecuteOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7924Discount 5 19.002 0.210523Trigger Code:USE NorthwindCREATE TRIGGER OrdDet_InsertON Order

18、 DetailsFOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDProductID UnitsInStock 12341

19、5106520215INSERT Statement to a Table with an INSERTTrigger DefinedINSERT Statement LoggedTrigger Actions ExecutedHow a DELETE Trigger WorksDELETE Statement to a table with a DELETE Trigger DefinedDELETE Statement to a table with a DELETE Trigger Defined4Dairy Products Cheeses0 x15DELETE statement l

20、oggedCategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoft drinks, coffeesSweet and savory Desserts, candies, Picture0 x150 x150 x15 0 x15CheesesDairy Products4DELETE CategoriesWHERE CategoryID = 4USE NorthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Disco

21、ntinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDProductID Discontinued 12340000Trigger Actions Execute2 1UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDDELETE Statement to a Table with a DELETEStatement DefinedD

22、ELETE Statement LoggedTrigger Actions ExecutedHow an UPDATE Trigger WorksUPDATE Statement to a table with an UPDATE Trigger DefinedUPDATE EmployeesSET EmployeeID = 17WHERE EmployeeID = 2UPDATE Statement logged as INSERT and DELETE StatementsEmployeeID LastName FirstNameTitleHireDate1234DavolioBarrLe

23、verlingPeacockNancyAndrewJanetMargaretSales Rep.RSales Rep.Sales Rep.2FullerAndrew Vice Pres.17FullerAndrew Vice Pres.2FullerAndrew Vice Pres.TRIGGER Actions ExecuteUSE NorthwindGOCREATE TRIGGER Employee_UpdateON EmployeesFOR UPDATEASIF UPDATE (EmployeeID)BEGIN TRANSACTIONRAISERROR (Transaction cannot be processed.* Employee ID number cannot be modified., 10, 1)ROLLBACK TRANSACTIONASIF UPDATE (EmployeeID)BEGIN TRANSACTIONRAISERROR (Transaction cannot be processed.* Employee ID number cannot be modified., 10, 1)ROLLBACK TRAN