数据库原理以及应用 第8课-存储过程的使用.ppt

创建和管理存储过程 创建存储过程 执行存储过程 修改、删除和查看存储过程 存储过程的定义 存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。用户可以重复调用这些存储过程，实现它所定义的操作。 存储过程的分类 存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。 系统存储过程 系统存储过程是指安装SQL Server时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。 扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如C或C++等）创建自己的扩展过程。 用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。 存储过程的优点 存储过程提供了处理复杂任务的能力 存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。 增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量。 加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。 加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。 例1 创建一存储过程p1 ，要求该存储过程返回学生学号、姓名和性别。 1. 建立不带参数的存储过程 use sampledb go create procedure p1 As Select sno,sname,sex From student go 执行不带参数的execute 语句 Execute p1 【例2】建立一个存储过程p2 ，根据学生学号查询该学生的信息。在执行p2时，给出‘学号’ （输入参数） 创建存储过程p2： use sampledb go CREATE PROCEDURE p2 @p2_sno varchar(8) AS SELECT * from student where sno=@p2_sno go 执行p2： exec p2这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。 如将上例中： @p2_sno varchar(8) 替换为： @p2_sno varchar(8)= ‘2011%’ 【例3】建立一个存储过程p2 ，根据学生学号查询该学生的信息， ‘学号’参数的默认值为‘2011%’。 创建存储过程p2： use sampledb go CREATE PROCEDURE p2 @p2_sno varchar(8)= 2011% AS SELECT * from student where sno like @p2_sno go 执行p2： exec p2 exec p2 创建带输出参数的存储过程 OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时SQL调用脚本必须使用OUTPUT关键字。 【例4】创建一个实现2个数的加法计算并将运算结果作为输出参数的存储过程P3。 创建P3： create procedure P3 @m1 int, @m2 int, @re