计算机操作系统课件复习资料-第8章.ppt

《计算机操作系统课件复习资料-第8章.ppt》由会员分享，可在线阅读，更多相关《计算机操作系统课件复习资料-第8章.ppt（53页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第8章章 存储过程存储过程存储过程存储过程l存储过程是为了实现某个特定任务，由一组预存储过程是为了实现某个特定任务，由一组预先编译好的先编译好的SQL语句组成，将其放在服务器上，由语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库用户通过指定存储过程的名字来执行的一种数据库对象。对象。l存储过程类型存储过程类型 n系统存储过程系统存储过程n扩展存储过程扩展存储过程n用户自定义存储过程用户自定义存储过程系统存储过程存储以系统存储过程存储以SP_为前缀为前缀,是由是由SQL Server2005自己创建、管理和使用的一种特自己创建、管理和使用的一种特殊的存储过程，不能对其

2、进行修改或删除。殊的存储过程，不能对其进行修改或删除。如如Sp_helpdb、Sp_renamedb等。等。由用户自行创建的存储过程，可以输入参数、由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以向客户端返回表格或结果、消息等，也可以返回输出函数。返回输出函数。第第8章章 存储过程存储过程l8.1 创建管理简单存储过程创建管理简单存储过程 l8.2 创建管理带参的存储过程创建管理带参的存储过程 第第8章章 存储过程存储过程l8.1 创建管理简单存储过程创建管理简单存储过程 l8.2 创建管理带参的存储过程创建管理带参的存储过程 8.1 创建管理简单的存储过程创建管

3、理简单的存储过程 l8.1.1 无参数无参数存储过程的创建存储过程的创建l8.1.2 无参数无参数存储过程的执行存储过程的执行l8.1.3 查查看存看存储过储过程程l8.1.4 修改存储过程修改存储过程 l8.1.5 编译存储过程编译存储过程l8.1.6 删删除存除存储过储过程程8.1.1 无参数无参数存储过程的创建存储过程的创建l1）语法格式：）语法格式：CREATE PROC EDURE procedure_nameAS sql_statement .n l2）语法注释：语法注释：procedure_name：新新建建存存储储过过程程的的名名称称，其其名名称称必必须须符符合合标标识识符符命

4、命名名规规则则，且且对对于于数数据据库库及及其其所所有有者必须唯一。者必须唯一。sql_statement：指指存存储储过过程程中中的的任任意意数数目目和和类类型型的的Transact-SQL语句。语句。8.1.1 无参无参存储过程的创建存储过程的创建l例例 1：在在 student数数 据据 库库 中中，创创 建建 一一 个个 名名 称称 为为pr_searchScore的的存存储储过过程程，该该存存储储过过程程将将查查询询出出选选修修表表中中“降龙十八掌降龙十八掌”课程的成绩信息。课程的成绩信息。l代码如下：代码如下：CREATE PROC AS SELECT a.Lid,Sid,scor

5、e FROM T_opt a,T_lesson b WHERE a.Lid=b.Lid and lname=降龙十八掌降龙十八掌 pr_searchScore创建存储过程的注意事项创建存储过程的注意事项n1)存存储储过过程程是是数数据据库库对对象象，其其名名称称必必须须遵遵守守标标识符命名规则。识符命名规则。n2)不不能能将将 CREATE PROCEDURE 语语句句与与其其它它 SQL 语句组合到单个批处理中。语句组合到单个批处理中。n3)创创建建存存储储过过程程时时，应应指指定定所所有有输输入入参参数数和和向向调调用用过过程程或或批批处处理理返返回回的的输输出出参参数数、执执行行数数据据

6、库库操操作作的的编编程程语语句句和和返返回回至至调调用用过过程程或或批批处处理理以以表表明成功或失败的状态值。明成功或失败的状态值。8.1 创建管理简单的存储过程创建管理简单的存储过程 l8.1.1 无参数无参数存储过程的创建存储过程的创建l8.1.2 无参数无参数存储过程的执行存储过程的执行l8.1.3 查查看存看存储过储过程程l8.1.4 修改存储过程修改存储过程 l8.1.5 编译存储过程编译存储过程l8.1.6 删删除存除存储过储过程程8.1.2 无参数无参数存储过程的执行存储过程的执行l对对 存存 储储 在在 服服 务务 器器 上上 的的 存存 储储 过过 程程，可可 以以 使使 用

7、用EXECUTE命令或其名称执行它，语法格式：命令或其名称执行它，语法格式：EXEC UTE procedure_namel对上例的存储过程对上例的存储过程pr_searchScore的执行语句如下：的执行语句如下：EXEC pr_searchScore 或者或者 pr_searchScore8.1 创建管理简单的存储过程创建管理简单的存储过程 l8.1.1 无参数存储过程的创建l8.1.2 无参数存储过程的执行l8.1.3 查看存储过程l8.1.4 修改存储过程 l8.1.5 编译存储过程l8.1.6 删除存储过程8.1.3 查看存储过程查看存储过程 1.1.使用对象资源管理器查看存储过程使

8、用对象资源管理器查看存储过程 2.2.使用系统存储过程查看存储过程信息使用系统存储过程查看存储过程信息 1 1）使用）使用sp_helptextsp_helptext查看存储过程的文本信息：查看存储过程的文本信息：sp_helptext sp_helptext 存储过程名存储过程名 2 2）使使用用sp_dependssp_depends查查看看存存储储过过程程的的相相关关性性：sp_depends sp_depends 存储过程名存储过程名 3 3）使用）使用sp_helpsp_help查看存储过程的一般信息查看存储过程的一般信息.sp_help sp_help 存储过程名存储过程名8.1

9、创建管理简单的存储过程创建管理简单的存储过程 l8.1.1 无参数无参数存储过程的创建存储过程的创建l8.1.2 无参数无参数存储过程的执行存储过程的执行l8.1.3 查查看存看存储过储过程程l8.1.4 修改存储过程修改存储过程 l8.1.5 编译存储过程编译存储过程l8.1.6 删删除存除存储过储过程程8.1.4 修改存储过程修改存储过程 l修改存储过程语法格式为：修改存储过程语法格式为：ALTER PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,EN

10、CRYPTION AS Sql_statement8.1.4 修改存储过程修改存储过程 l例例2：修改存储过程：修改存储过程pr_searchScore,查询出成绩表中查询出成绩表中降龙十八掌降龙十八掌课程所有低于课程所有低于100分的成绩。分的成绩。ALTER PROC As SELECT a.Lid,Sid,score FROM T_opt a,T_lesson b WHERE a.Lid=b.Lid and lname=降降龙龙十十八八掌掌 and score 100Pr_searchScore8.1 创建管理简单的存储过程创建管理简单的存储过程 l8.1.1 无参数无参数存储过程的创建

11、存储过程的创建l8.1.2 无参数无参数存储过程的执行存储过程的执行l8.1.3 查查看存看存储过储过程程l8.1.4 修改存储过程修改存储过程 l8.1.5 编译存储过程编译存储过程l8.1.6 删删除存除存储过储过程程8.1.5 编译存储过程编译存储过程l 在使用了一次存储过程后，可能会因为某些原因，在使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译。行重新编译。8.1.5 编译存储过程编译存储过程lSQ

12、L Server提供三种重新编译存储过程的方法提供三种重新编译存储过程的方法：1)1)在建立存储过程时设定重新编译在建立存储过程时设定重新编译语法格式：语法格式：CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement2)2)在执行存储过程时设定重编译在执行存储过程时设定重编译 语法格式：语法格式：EXECUTE procedure_name WITH RECOMPILEWITH RECOMPILE3)3)通过使用系统存储过程设定重编译通过使用系统存储过程设定重编译 语法格式为：语法格式为：EXEC sp_recompile

13、 procedure_name8.1 创建管理简单的存储过程创建管理简单的存储过程 l8.1.1 无参数无参数存储过程的创建存储过程的创建l8.1.2 无参数无参数存储过程的执行存储过程的执行l8.1.3 查查看存看存储过储过程程l8.1.4 修改存储过程修改存储过程 l8.1.5 编译存储过程编译存储过程l8.1.6 删删除存除存储过储过程程8.1.6 删除存储过程删除存储过程 1).1).使用使用SSMSSSMS对象资源管理器删除存储过程对象资源管理器删除存储过程 2).2).使用使用DROP PROCEDUREDROP PROCEDURE语句删除存储过程语句删除存储过程 DROP DRO

14、P PROCEDUREPROCEDURE语语句句可可以以一一次次从从当当前前数数据据库中将一个或多个存储过程或过程组删除：库中将一个或多个存储过程或过程组删除：DROP PROCEDUREDROP PROCEDURE 存储过程名称存储过程名称,n,n例：删除存储过程例：删除存储过程pr_searchScorepr_searchScore DROP PROCEDURE pr_searchScore DROP PROCEDURE pr_searchScore第第8章章 存储过程存储过程l8.1 创建管理简单存储过程创建管理简单存储过程 l8.2 创建管理带参数的存储过程创建管理带参数的存储过程 8

15、.2 创建管理带参数的存储过程创建管理带参数的存储过程l8.2.1 创建带参数存储过程的语法创建带参数存储过程的语法 l8.2.2 带输入参数的存储过程带输入参数的存储过程 l8.2.3 带输出参数的存储过程带输出参数的存储过程 l8.2.4 存储过程的返回值存储过程的返回值创建创建带带参数参数存储过程的语法存储过程的语法 1）语法：CREATE PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS Sql_statement创建创建

16、带带参数参数存储过程的语法存储过程的语法2）语法注释：parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。OUTPUT：表明参数是输出参数。该选项的值可以返回给EXECUTE。8.2 创建管理带参数的存储过程创建管理带参数的存储过程l8.2.1 创建带参数存储过程的语法创建带参数存储过程的语法 l8.2.2 带输入参数的存储过程带输入参数的存储过程 l8.2.3 带输出参数的存储过程带输出参数的存储过程 l8.2.4 存储过程的返回值存储过程的返回值8.2.2 带输入参数的存储过程带输入参数的存储过程 l例例3：在：在student数据库中创建一个存储过程数据库

17、中创建一个存储过程pr_searchStuScore，查询出指定的某位学生的所，查询出指定的某位学生的所有成绩。有成绩。CREATE PROC pr_searchStuScoreAsSelect Lid,a.Sid,Score From T_opt a,T_student bWhere a.Sid=b.Sid and sname=sname nchar(10)sname nchar(10)sname8.2.2 带输入参数的存储过程带输入参数的存储过程 执行带输入参数的存储过程：执行带输入参数的存储过程：EXEC UTE return_status=procedure_name paramete

18、r=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 执行存储过程执行存储过程pr_searchStuScore：EXECUTE pr_searchStuScore 黄蓉黄蓉 EXEC pr_searchStuScore snamesname=黄蓉黄蓉8.2.2 带输入参数的存储过程带输入参数的存储过程 执行带输入参数的存储过程：执行带输入参数的存储过程：EXEC UTE return_status=procedure_name parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE

19、执行存储过程执行存储过程pr_searchStuScore：declare Sname nchar(10)set Sname=黄蓉黄蓉 EXEC pr_searchStuScore SnameSname 8.2.2 带输入参数的存储过程带输入参数的存储过程l例4：创建存储过程pr_searchStuLesson，根据指定的学生姓名和课程名查询成绩。CREATE PROC pr_searchStuLessonAS SELECT Lname,Sname,score FROM T_opt a,T_student b,T_lesson c WHERE a.Sid=b.Sid and b.Lid=c.L

20、idsname nchar(10)and b.Sname=snameand c.Lname=lname,lname nchar(20)8.2.2 带输入参数的存储过程带输入参数的存储过程1)按位置传递参数值按位置传递参数值 在执行存储过程的语句中，在执行存储过程的语句中，直接给出参数的值直接给出参数的值。当有多。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的书写顺序一致。中的参数的书写顺序一致。2)通过参数名传递参数值通过参数名传递参数值 在执行存储过程的语句中，使用在执行存储过程的语句中，使用【参数名参数名=参数值参数值】

21、的形的形式给出参数值。其优点是参数可以以任意顺序给出。式给出参数值。其优点是参数可以以任意顺序给出。8.2.2 带输入参数的存储过程带输入参数的存储过程3)在输入参数中使用默认值在输入参数中使用默认值 在执行有参数的存储过程时，如果没有指定参在执行有参数的存储过程时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时不给出参数也能正确运行，则在创建存储过程时给输入参数指定默认值。时给输入参数指定默认值。8.2.2 带输入参数的存储过程带输入参数的存储过程l按位置传递参数值exec pr_searchSt

22、uLesson 黄蓉,打狗棒法l通过参数名传递参数值exec pr_searchStuLesson sname=黄蓉,lname=打狗棒法或exec pr_searchClaLesson lname=打狗棒法,sname=黄蓉8.2.2 带输入参数的存储过程带输入参数的存储过程l例5：对存储过程pr_searchStuLesson进行修改，指定课程默认值为打狗棒法，指定学生默认值为黄蓉。ALTER PROC pr_searchStuLesson sname nchar(10)=黄蓉,lname nchar(20)=打狗棒法AS SELECT Lname,Sname,score FROM T_o

23、pt a,T_student b,T_lesson c WHERE a.Sid=b.Sid and b.Lid=c.Lid and b.Sname=sname and c.Lname=lnameGOEXEC pr_searchStuLesson 8.2 创建管理带参的存储过程创建管理带参的存储过程l8.2.1 创建带参存储过程的语法创建带参存储过程的语法 l8.2.2 带输入参数的存储过程带输入参数的存储过程 l8.2.3 带输出参数的存储过程带输出参数的存储过程 l8.2.4 存储过程的返回值存储过程的返回值8.2.3 带输出参数的存储过程带输出参数的存储过程l例例6：在在student数数

24、据据库库中中创创建建一一个个存存储储过过程程pr_avgScore,输输出某门课程的平均成绩。出某门课程的平均成绩。CREATE PROC pr_avgScorelname nchar(20)AsSelect avg(Score)from T_opt a,T_lesson bWhere a.Lid=b.Lid and lname=lnamel执行执行:declare result decimal(3,1)exec pr_avgScore 九阴真经九阴真经,select result as 平均成绩平均成绩,ageScore decimal(3,1)outputageScore=result o

25、utput8.2.3 带输出参数的存储过程带输出参数的存储过程l例例7：创建一个存储过程：创建一个存储过程pr_getTopStudent，查询平均，查询平均成绩最高的学生姓名。成绩最高的学生姓名。CREATE PROC pr_getTopStudentname nchar(10)OUTPUTASSELECT top 1 sname FROM T_student a join T_opt b on a.Sid=b.Sidgroup by Snameorder by avg(Score)DESCGOname=8.2.3 带输出参数的存储过程带输出参数的存储过程l执行执行:DECLARE resu

26、lt nchar(10)EXEC pr_getTopStudent result OUTPUTSELECT 平均成绩最高的学生是：平均成绩最高的学生是：+result8.2 创建管理带参的存储过程创建管理带参的存储过程l8.2.1 创建带参存储过程的语法创建带参存储过程的语法 l8.2.2 带输入参数的存储过程带输入参数的存储过程 l8.2.3 带输出参数的存储过程带输出参数的存储过程 l8.2.4 存储过程的返回值存储过程的返回值8.2.4 存储过程的返回值存储过程的返回值存储过程在执行后都会返回一个整型值。如存储过程在执行后都会返回一个整型值。如果执行成功，则返回果执行成功，则返回0；否则

27、返回；否则返回-1到到-99之间之间的随机数，也可以使用的随机数，也可以使用RETURN语句来指定一语句来指定一个存储过程的返回值。个存储过程的返回值。8.2.4 存储过程的返回值存储过程的返回值l例例8：在：在student数据库中创建一个存储过程，返回数据库中创建一个存储过程，返回学生人数。学生人数。create proc pr_numStudentas declare fanhuizhi int select fanhuizhi=count(sid)from T_student return fanhuizhicreate proc pr_numStudentfanhuizhi int

28、outputas select fanhuizhi=count(sid)from T_student8.2.4 存储过程的返回值存储过程的返回值l接收返回值必须要用变量来接收接收返回值必须要用变量来接收 declare jieshouzhi int exec jieshouzhi=pr_ numStudent print jieshouzhi8.2.4 存储过程的返回值存储过程的返回值l接收接收output的返回值也必须要用变量，如：的返回值也必须要用变量，如：declare jieshouzhi int exec pr_ numStudent_2 jieshouzhi output prin

29、t jieshouzhil注意：注意：1)return返回的是整数，返回的是整数，output可以返回任何数据。可以返回任何数据。2)如果让如果让return返回非整数值，在创建存储过程时不返回非整数值，在创建存储过程时不会出错，但是运行存储过程时将会出错。会出错，但是运行存储过程时将会出错。练习练习1l在在student数据库中创建一个带参数的存储过程数据库中创建一个带参数的存储过程pr_SelectStu，查询出学生表中是否存在，查询出学生表中是否存在XXX(姓名姓名)同学，如果不存在，则返回值为同学，如果不存在，则返回值为1，否则查询该学生，否则查询该学生的班级是否的班级是否XX班（班级

30、编号），如是，则返回值为班（班级编号），如是，则返回值为2，否则返回值为，否则返回值为0。练习练习1create proc pr_SelectStusname nchar(10),sclass nchar(10)asif sname not in(select Sname from T_student)return(1)else if exists(select*from T_student where sname=sname and Cid=sclass)return(2)else return(0)练习练习1l执行存储过程语句如下执行存储过程语句如下:Declare fanhui intE

31、xec fanhui=pr_SelectStu 黄蓉黄蓉,10010911If fanhui=0Print 黄蓉是黄蓉是10010911班的学生班的学生If fanhui=1Print 没有黄蓉这个学生没有黄蓉这个学生If fanhui=2Print 黄蓉不是黄蓉不是10010911班的学生班的学生练习练习2l例：建立一个带参的存储过程，用于向例：建立一个带参的存储过程，用于向student数据库中的数据库中的T_student表中添加一条表中添加一条记录，在添加记录前先查看是否存在此学生记录，在添加记录前先查看是否存在此学生的学号，如不存在则插入该学生信息，存储的学号，如不存在则插入该学生信

32、息，存储过程返回值为过程返回值为0，如已存在，则不进行插入记，如已存在，则不进行插入记录操作，存储过程返回值为录操作，存储过程返回值为-1。练习练习2l实现的实现的T-SQL语句语句:CREATE PROC pr_InsertStu Id char(10),Name nchar(10),class nchar(10)，group nchar(20)练习练习2AS IF EXISTS(SELECT*FROM T_student WHERE Sid=Id)Return-1 ELSE BEGIN INSERT T_student(Sid,Sname,Cid,Gname)VALUES(Id,name,

33、class,group)RETURN 0 END练习练习2l 存储过程的执行如下：存储过程的执行如下：DECLARE result int Exec result=pr_InsertStu 1001091204,裘千仞裘千仞,软件软件1101 IF result=0 PRINT 记录插入成功记录插入成功 IF result=-1 PRINT 学号已存在，记录重复！学号已存在，记录重复！GO练习练习3l例例12：建立一个带参的存储过程：建立一个带参的存储过程pr_updateScore，用于修改，用于修改student数据库数据库T_opt表中某位同学的某门课程成绩。表中某位同学的某门课程成绩。

34、CREATE PROC pr_updateGrade sname nchar(8),lname nchar(20),score int AS UPDATE T_opt SET Score=score WHERE sid=(select Sid from T_student where Sname=sname)and Lid=(select Lid from T_lesson where Lname=lname)执行如下：执行如下：Exec pr_updateScore 杨康杨康，九阴真经九阴真经,130练习练习3课后练习课后练习1l在在student数据库中创建一个带参的存储过程数据库中创建一个带参的存储过程pr_updateStudent，其功能是修改某学生的姓，其功能是修改某学生的姓名。接受用户输入的学生学号、原姓名和新姓名，名。接受用户输入的学生学号、原姓名和新姓名，首先查询出首先查询出T_student表中是否存在该学生学号表中是否存在该学生学号和姓名，和姓名，如没有，则返回（如没有，则返回（-1），否则修改其），否则修改其姓名。姓名。课后练习课后练习2l建立一个带参的存储过程建立一个带参的存储过程pr_getScore，根，根据输入的学号查询某学生的最高成绩和平均据输入的学号查询某学生的最高成绩和平均成绩成绩,并将这两个成绩值返回。并将这两个成绩值返回。