SQL Server 2005数据库实用教程,了解存储过程的定义、优势及执行方法 掌握触发器的设计、实现及管理方法 能够创建、执行、修改及删除存储过程 能够使用触发器解决问题,知识目标,技能目标,9.1 存储过程 9.2 存储过程的定义和执行 9.3 存储过程的管理 9.4触发器 9.5 触发器的管理 9.6 实训 存储过程和触发器的创建和应用,9.1 存储过程 9.1.1 什么是存储过程 存储过程是一组Transact-SQL语句，将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。存储过程经编译后存储在数据库服务器中，可以接受参数并返回状态值和参数值。它们只需编译一次，以后即可多次执行。因为 Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。,9.1.2 为什么使用存储过程 开发SQL Server数据库时，T-SQL程序语言是介于应用程序与SQL Server数据库之间的程序接口（Programming Interface）。使用T-SQL程序时，有两种不同的方法可存储和执行程序。一种是本书之前使用的查询窗口，读者可在此窗口输入T-SQL程序代码；另一种就是保存为存储过程，鉴于存储过程的特点，使T-SQL程序代码更为灵活，以供日后使用。 使用存储过程和数据库的好处是，可以充分利用数据库资源，减少程序代码，程序员的工作将更简便，写出来的代码也更简洁明了。使用存储过程的优点有： （1）能实现模块化程序设计。存储过程是根据实际功能的需要创建的一个程序模块，并被存储在数据库中。以后用户要完成该功能，只要在程序中直接调用该存储过程即可，而无需再编写重复的程序代码。存储过程可由数据库编程方面的专门人员创建，并可独立于程序源代码而进行修改和扩展。 （2）提高性能。在服务器端执行，速度快。执行一次后，其执行规划就驻留在高速缓冲存储器，以后再次执行，就直接运行已经编译好的二进制代码，提高了系统性能。 （3） 提供安全机制。管理员可以不授予用户访问存储过程中涉及的表的权限，而只授予执行存储过程的权限。这样，既可以保证用户通过存储过程操纵数据库中的数据，又可以保证用户不能直接访问存储过程中涉及的表。用户通过存储过程来访问表，所能进行的操作是有限制的，从而保证了表中数据的安全性。 （4）减少网络流量。一个需要数百行T-SQL代码的操作，如果将其创建成存储过程，那么使用一条调用存储过程的语句就可完成该操作。这样就可避免在网络上发送数百行代码，从而减少了网络负荷。,9.1.3 存储过程的类型 存储过程可以分为系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程等，在不同情况下需要执行不同的存储过程。 （1）系统存储过程：这些存储过程存储在master数据库中，其前缀是sp_。它允许系统管理员执行修改系统表的数据库管理任务，可以在任何一个数据库中执行。如图9-1所示。 图9-1 系统数据库中的存储过程,（2）本地存储过程：是指在用户数据库中创建的存储过程，完成特定的数据库操作任务，其前缀不能是sp_。 （3）临时存储过程：属于本地存储过程。如果存储过程名称前面有“”，说明其是局部临时存储过程，这种存储过程只能在一个用户会话中使用；如果其前面有“”，说明其是全局临时存储过程，可以在所有用户会话中使用。 （4）远程存储过程：从远程服务器上调用。 （5）扩展存储过程：在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是sp_。使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法执行。 图9-1显示的是master系统数据库的内容，在“可编程性”中有“存储过程”、“扩展存储过程”及“数据库触发器”等。只有系统数据库才有扩展存储过程，它是比存储过程更为“低级”的工具，可使用C语言等创建。扩展存储过程就像DLL链接库一样，可以扩展SQL Server的功能。,9.2 存储过程的定义和执行 9.2.1 创建简单存储过程 简单存储过程类似于给一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。 创建存储过程的基本语法如下： CREATE PROCEDURE 存储过程名称 WITH ENCRYPTION WITH RECOMPILE AS SQL 语句 参数说明： WITH ENCRYPTION：对存储过程进行加密。 WITH RECOMPILE：对存储过程重新编译。,【例9.1】创建一个查询学生“汪洋”的借书信息的“借书查询”存储过程。 创建代码如下： USE 学生图书管理系统 GO CREATE PROC 借书查询 AS SELECT 姓名, 图书编号,借书日期,还书日期 FROM 学生信息 JOIN 租借信息 ON 学生信息.借书证号=租借信息.借书证号 WHERE 姓名=汪洋,9.2.2 执行存储过程 存储过程创建完成后，可以使用EXECUTE语句来执行存储过程。 语法格式如下： EXECUTE 存储过程名称 参数值, 若EXECUTE语句是批处理的第一条语句时，可以省略EXECUTE。 【例9.2】执行【例9.1】中的存储过程“借书查询”，结果如图9-2所示。 执行代码如下： USE 学生图书管理系统 GO EXEC 借书查询,9.2.3 带参数的存储过程 带参数的存储过程提供了参数，大大提高了系统开发的灵活性。向存储过程提供输入、输出参数的目的是通过参数向存储过程输入和输出信息来扩展存储过程的功能。通过使用参数，可以多次使用同一存储过程，并按用户要求查找所需要的结果。在程序中调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。一个存储过程中最多可以有1024个参数。 1带输入参数的存储过程 一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 语法格式如下： CREATE PROCEDURE 存储过程名称 参数名 数据类型=默认值 ，n WITH ENCRYPTION WITH RECOMPILE AS SQL 语句,在【例9.1】中的“借书查询”这个存储过程只能对“汪洋”这个特定的学生进行查询，如果要查询任何指定学生的借书信息，就要在 “借书查询”存储过程中使用输入参数。 【例9.3】创建某个班某个学生借书信息的存储过程。 USE 学生图书管理系统 GO CREATE PROC 借书查询2 班级 varchar（10） =2002-02, 姓名 varchar（10）=汪洋 AS SELECT 姓名, 图书编号,借书日期,还书日期 FROM 学生信息 JOIN 租借信息 ON 学生信息.借书证号=租借信息.借书证号 WHERE 班级=班级 AND 姓名=姓名 执行带输入参数的存储过程时，SQL Server提供了两种传递参数的方法，分别是使用参数名传递参数值和按参数位置传递参数值。,（1） 按位置传递。这种方法是在执行存储过程的语句中直接给出参数的值。当有多个参数时，给出的参数值的顺序与创建存储过程的语句中的参数顺序相一致，即参数传递的顺序就是参数定义的顺序。按位置传递参数时，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时使用关键字“DEFAULT”作为参数值的占位。 执行的语句为： EXEC 借书查询2 2002-03,王红 （2） 通过参数名传递。这种方法是在执行存储过程的语句中，使用“参数名=参数值”的形式给出参数值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值。 EXEC 借书查询2 姓名=王红,班级=2002-03 （3） 使用默认参数值。执行存储过程时，不输入参数传值。 EXEC 借书查询2 系统在执行存储过程时，自动将默认值“2002-02”传给输入参数班级，“汪洋”传给输入参数姓名，该执行结果将显示“2002-02”班的学生“汪洋”的借书信息。,2带通配符输入参数的存储过程 【例9.4】创建存储过程查询姓“王”的学生的借书信息。 创建代码如下： USE 学生图书管理系统 GO CREATE PROC 借书查询3 姓名 varchar（10）=王% AS SELECT 姓名, 图书编号,借书日期,还书日期 FROM 学生信息 JOIN 租借信息 ON 学生信息.借书证号=租借信息.借书证号 WHERE 姓名 LIKE 姓名 执行代码如下： EXEC 借书查询3 或 EXEC 借书查询3 王%,3带输出参数的存储过程 通过定义输出参数，可以从存储过程中返回一个或多个值。定义输出参数需要在参数定义后加OUTPUT关键字。 语法格式如下： CREATE PROCEDURE 存储过程名称 参数名 数据类型 =默认值 OUTPUT ，n WITH ENCRYPTION WITH RECOMPILE AS SQL 语句,【例9.5】创建查询某个学生所借图书的总价钱的存储过程。 创建语句如下： USE 学生图书管理系统 GO CREATE PROC 借书总价钱 姓名 varchar（10）,总价 money OUTPUT AS SELECT 总价=sum（定价） FROM 学生信息 as a JOIN 租借信息 as b ON a.借书证号=b.借书证号 JOIN 图书信息 as c ON b.图书编号=c.图书编号 WHERE 姓名=姓名,在程序中调用存储过程时，SQL Server提供了两种传递参数的方法。OUTPUT变量必须在定义存储过程和使用该变量时都进行定义。定义时的参数名和调用时的变量名不一定相同，不过数据类型和参数的位置必须匹配。 （1） 按位置传递参数。 DECLARE 总价钱 money EXEC 借书总价钱 汪洋,总价钱 OUTPUT SELECT 汪洋所借图书的总价钱是：,总价钱 （2） 通过参数名传递。 DECLARE 总价钱 money EXEC 借书总价钱 总价=总价钱 OUTPUT,姓名=汪洋 SELECT 汪洋所借图书的总价钱是：,总价钱,9.3 存储过程的管理 9.3.1 查看存储过程的定义 在SQL Server Management Studio的“对象资源管理器”中，选中要查看的存储过程，右击，在快捷菜单中选择“属性”，如图9-3所示，弹出“存储过程属性”窗口，如图9-4所示。 图9-3 “借书查询”存储过程属性,也可以通过系统存储过程sp_helptext查看存储过程的定义，通过sp_help查看存储过程的参数，通过sp_depends查看存储过程的相关性。 【例9.6】利用系统存储过程查看“借书查询”的相关信息。 代码如下： EXEC sp_helptext 借书查询 EXEC sp_help 借书查询 EXEC sp_depends 借书查询,9.3.2 修改存储过程 在SQL Server Management Studio的“对象资源管理器”中，选中要修改的存储过程，右击，在快捷菜单中选择“修改”，出现修改代码窗口进行修改即可。 使用ALTER PROCEDURE语句修改存储过程，语法格式如下： ALTER PROCEDURE 存储过程名称 WITH ENCRYPTION WITH RECOMPILE AS SQL语句,【例9.7】修改存储过程“借书查询”，使其按班级和姓名来查询，并修改为加密存储过程。 USE 学生图书管理系统 GO ALTER PROC 借书查询 WITH ENCRYPTION AS SELECT 姓名, 图书编号,借书日期,还书日期 FROM 学生信息 join 租借信息 on 学生信息.借书证号=租借信息