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

1、创建和管理存储过程 创建存储过程 执行存储过程 修改、删除和查看存储过程存储过程的定义 存储过程存储过程是为完成特定的功能而汇集在一起的一组一组SQL程序语句程序语句，经编译后存储在数据库中的SQL程序。用户可以重复调用这些存储过程，实现它所定义的操作。例：存储过程P_1create procedure p_1 As Select * From student存储过程名完成特定的功能的一组一组SQL程序语句程序语句存储过程的分类 存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。 系统存储过程系统存储过程 系统存储过程是指安装SQL Server时由系统创建的存储过程。存

2、储在master数据库中，其前缀为前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。 扩展存储过程扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为前缀为xp_。它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如C或C+等）创建自己的扩展过程。 用户定义的存储过程用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。 存储过程的优点 存储过程提供了处理复杂任务的能力 存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使

3、用十分复杂的SQL语句处理复杂任务。 增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量。 加快系统运行速度加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。 加强系统安全性加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程

4、的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。例：存储过程P_1create procedure p_1 As Select * From student 创建格式：创建格式： create procedure create procedure 过程名过程名as as beginbegin sql sql 语句组语句组endend 执行格式：执行格式：execute execute 过程名过程名 存储过程的格式例例1 创建一存储过程p1 ，要求该存储过程返回学生学

5、号、姓名和性别。 1. 建立不带参数的存储过程use sampledbgo create procedure p1As Select sno,sname,sex From studentgo 执行不带参数的execute 语句Execute p1存储过程的参数 参数类型：参数类型： 输入参数输入参数：允许调用者：允许调用者向过程传递数据值向过程传递数据值 输出参数输出参数：允许存储过程：允许存储过程向调用者返回值向调用者返回值 创建格式：创建格式：create procedure create procedure 过程名过程名 parameter parameter 类型类型=缺省值缺省值 o

6、utput output,parameter ,parameter 类型类型=缺省值缺省值 output outputAs As sqlsql语句组语句组 执行格式：执行格式：execute execute 过程名过程名 参数参数【例【例2】建立一个存储过程p2 ，根据学生学号查询该学生的信息。在执行p2时，给出学号 （输入参数）创建存储过程p2：use sampledbgo CREATE PROCEDURE p2 p2_sno varchar(8)ASSELECT * from student where sno=p2_snogo执行p2：exec p2 20110001 这种类型的存储过程

7、存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。 如将上例中： p2_sno varchar(8) 替换为： p2_sno varchar(8)= 2011%【例【例3】建立一个存储过程p2 ，根据学生学号查询该学生的信息， 学号参数的默认值为2011%。创建存储过程p2：use sampledbgo CREATE PROCEDURE p2 p2_sno varchar(8)= 2011% ASSELECT * from student whe

8、re sno like p2_snogo执行p2：exec p2 20110001 exec p2创建带输出参数的存储过程 OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之输出参数必须位于所有输入参数之后。后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时在调用该过程时SQL调用脚本必须使用调用脚本必须使用OUTPUT关键字。关键字。【例【例4】创建一个实现2个数的加法计算并将运算结果作为输出参数的存储过程P3。创建P3： create procedure P3 m1 int, m2 int, result int outpu

9、t as set result=m1+m2go执行P3(必须定义一个变量answer，并使用OUTPUT关键字指定它为调用输出参数。)Declare answer intExecute P3 8,10, answer outputprint answer【例【例5】创建一个存储过程P4, 根据课程号（输入参数）计算该课程的平均成绩（输出参数）。创建P4：if exists (select name from sysobjects where name=p4 and type=p) drop proc p4 -如果p4存在则删除它goCREATE PROCEDURE p4 p4cno varch

10、ar(3)=%2, sumgrade int outputAS select sumgrade=avg(grade) from sc where cno like p4cno执行P4(必须定义一个变量a1，并使用OUTPUT关键字指定它为调用输出参数。)declare a1 intexec p4 002,a1 outputprint 该课程平均成绩为+cast(a1 as char(10)修改删除存储过程 修改修改 ：alteralter procedure procedure 过程名过程名 as as sql sql语句组语句组 删除删除 ：drop procedure drop proce

11、dure 过程名过程名例：例：drop procedure p4drop procedure p4查看存储过程 查看查看 ：exec sp_helptextsp_helptext 过程名过程名例：例：use sampledbexec sp_helptext p4重新编译存储过程 在某些情况下，可能需要改变数据库的逻辑结构（如：为表新增列），或者为表新增索引。为了使该存储过程能够根据数据库的改变重新优化，或从新的索引中受益，这就要求SQL Server在执行存储过程时对它重新编译，因为除非重新启动SQL Server，否则，存储过程访问数据表的原始查询不会自动优化。重新编译存储过程的重新编译存储

12、过程的2种方法：种方法：方法方法 在创建存储过程时，使用CREATE PROCEDURE中的RECOMPILE重编译选项。具体语法如下： CREATE PROCEDURE. WITH RECOMPILE 每次执行时都被重新编译和优化,并创建新的查询计划。例6(方法1) 创建一个带重编译选项的存储过程p6 ，要求该存储过程返回学生学号、姓名和性别。 1. 建立存储过程p6, 使用RECOMPILE重编译选项use sampledbgo create procedure p6 WITH RECOMPILEAs Select sno,sname,sex From studentGo 执行p6Execute p6重新编译存储过程的重新编译存储过程的2种方法：种方法：方法方法 执行存储过程时重编译。在EXECUTE语句中使用WITH RECOMPILE选项，让SQL Server在执行一个存储过程时，重新编译该存储过程。其语法如下：EXECUTE 过程名 参数 WITH RECOMPILE例6