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

第8章 存储过程,存储过程,存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。存储过程类型 系统存储过程扩展存储过程用户自定义存储过程,系统存储过程存储以SP_为前缀,是由SQL Server2005自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如Sp_helpdb、Sp_renamedb等。,由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出函数。,第8章 存储过程,8.1 创建管理简单存储过程 8.2 创建管理带参的存储过程,第8章 存储过程,8.1 创建管理简单存储过程 8.2 创建管理带参的存储过程,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.1 无参数存储过程的创建,1）语法格式：CREATE PROC EDURE procedure_nameAS sql_statement.n,2）语法注释：procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。,8.1.1 无参存储过程的创建,例1：在student数据库中，创建一个名称为pr_searchScore的存储过程，该存储过程将查询出选修表中“降龙十八掌”课程的成绩信息。代码如下：CREATE PROC AS SELECT a.Lid,Sid,score FROM T_opt a,T_lesson b WHERE a.Lid=b.Lid and lname=降龙十八掌,pr_searchScore,创建存储过程的注意事项,1)存储过程是数据库对象，其名称必须遵守标识符命名规则。2)不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。3)创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.2 无参数存储过程的执行,对存储在服务器上的存储过程，可以使用EXECUTE命令或其名称执行它，语法格式：EXEC UTE procedure_name对上例的存储过程pr_searchScore的执行语句如下：EXEC pr_searchScore 或者 pr_searchScore,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.3 查看存储过程,1.使用对象资源管理器查看存储过程 2.使用系统存储过程查看存储过程信息 1）使用sp_helptext查看存储过程的文本信息：sp_helptext 存储过程名 2）使用sp_depends查看存储过程的相关性：sp_depends 存储过程名 3）使用sp_help查看存储过程的一般信息.sp_help 存储过程名,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.4 修改存储过程,修改存储过程语法格式为：ALTER PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS Sql_statement,8.1.4 修改存储过程,例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 100,Pr_searchScore,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.5 编译存储过程,在使用了一次存储过程后，可能会因为某些原因，必须向表中新增加数据列或者为表新添加索引，从而改变了数据库的逻辑结构。这时，需要对存储过程进行重新编译。,8.1.5 编译存储过程,SQL Server提供三种重新编译存储过程的方法：1)在建立存储过程时设定重新编译语法格式：CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement2)在执行存储过程时设定重编译 语法格式：EXECUTE procedure_name WITH RECOMPILE3)通过使用系统存储过程设定重编译 语法格式为：EXEC sp_recompile procedure_name,8.1 创建管理简单的存储过程,8.1.1 无参数存储过程的创建8.1.2 无参数存储过程的执行8.1.3 查看存储过程8.1.4 修改存储过程 8.1.5 编译存储过程8.1.6 删除存储过程,8.1.6 删除存储过程,1).使用SSMS对象资源管理器删除存储过程 2).使用DROP PROCEDURE语句删除存储过程 DROP PROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除：DROP PROCEDURE 存储过程名称,n例：删除存储过程pr_searchScore DROP PROCEDURE pr_searchScore,第8章 存储过程,8.1 创建管理简单存储过程 8.2 创建管理带参数的存储过程,8.2 创建管理带参数的存储过程,8.2.1 创建带参数存储过程的语法 8.2.2 带输入参数的存储过程 8.2.3 带输出参数的存储过程 8.2.4 存储过程的返回值,创建带参数存储过程的语法,1）语法：CREATE PROCDURE procedure_nameparameter data_type=defaultOUTPUT,nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION AS Sql_statement,创建带参数存储过程的语法,2）语法注释：parameter：存储过程中的输入和输出参数。data_type：参数的数据类型。OUTPUT：表明参数是输出参数。该选项的值可以返回给EXECUTE。,8.2 创建管理带参数的存储过程,8.2.1 创建带参数存储过程的语法 8.2.2 带输入参数的存储过程 8.2.3 带输出参数的存储过程 8.2.4 存储过程的返回值,8.2.2 带输入参数的存储过程,例3：在student数据库中创建一个存储过程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,8.2.2 带输入参数的存储过程,执行带输入参数的存储过程：EXEC UTE return_status=procedure_name parameter=value|variable OUTPUT|DEFAULT,.n WITH RECOMPILE 执行存储过程pr_searchStuScore：EXECUTE pr_searchStuScore 黄蓉 EXEC pr_searchStuScore sname=黄蓉,8.2.2 带输入参数的存储过程,执行带输入参数的存储过程：EXEC UTE return_status=procedure_name parameter=value|variable OUTPUT|DEFAULT,.n WITH RECOMPILE 执行存储过程pr_searchStuScore：declare Sname nchar(10)set Sname=黄蓉 EXEC pr_searchStuScore Sname,8.2.2 带输入参数的存储过程,例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.Lid,sname nchar(10),and b.Sname=,sname,and c.Lname=,lname,lname nchar(20),8.2.2 带输入参数的存储过程,1)按位置传递参数值 在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的书写顺序一致。2)通过参数名传递参数值 在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。,8.2.2 带输入参数的存储过程,3)在输入参数中使用默认值 在执行有参数的存储过程时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时给输入参数指定默认值。,8.2.2 带输入参数的存储过程,按位置传递参数值exec pr_searchStuLesson 黄蓉,打狗棒法通过参数名传递参数值exec pr_searchStuLesson sname=黄蓉,lname=打狗棒法或exec pr_searchClaLesson lname=打狗棒法,sname=黄蓉,8.2.2 带输入参数的存储过程,例5：对存储过程pr_searchStuLesson进行修改，指定课程默认值为打狗棒法，指定学生默认值为黄蓉。ALTER PROC pr_searchStuLesson sname nchar(10)=黄蓉,lname nchar(20)=打狗棒法AS SELECT Lname,Sname,score FROM T_opt 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 创建管理带参的存储过程,8.2.1 创建带参存储过程的语法 8.2.2 带输入参数的存储过程 8.2.3 带输出参数的存储过程 8.2.4 存储过程的返回值,8.2.3 带输出参数的存储过程,例6：在student数据库中创建一个存储过程pr_avgScore,输出某门课程的平均成绩。CREATE PROC pr_avgScorelname nchar(20)AsSelect avg(Score)from T_opt a,T_lesson bWhere a.Lid=b.Lid and lname=lname执行:declare result decimal(3,1)exec pr_avgScore 九阴真经,select result as 平均成绩,ageScore decimal(3,1)output,ageScore=,result output,8.2.3 带输出参数的存储过程,例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)DESCGO,name=,8.2.3 带输出参数的存储过程,执行:DECLARE result nchar(10)EXEC pr_getTopStudent result OUTPUTSELECT 平均成绩最高的学生是：+result,8.2 创建管理带参的存储过程,8.2.1 创建带参存储过程的语法 8.2.2 带输入参数的存储过程 8.2.3 带输出参数的存储过程 8.2.4 存储过程的返回值,8.2.4 存储过程的返回值,存储过程在执行后都会返回一个整型值。如果执行成功，则返回0；否则返回-1到-99之间的随机数，也可以使用RETURN语句来指定一个存储过程的返回值。,8.2.4 存储过程的返回值,例8：在student数据库中创建一个存储过程，返回学生人数。,create proc pr_numStudentas declare fanhuizhi int select fanhuizhi=count(sid)from T_student return fanhuizhi,create proc pr_numStudentfanhuizhi int outputas select fanhuizhi=count(sid)from T_student,8.2.4 存储过程的返回值,接收返回值必须要用变量来接收 declare jieshouzhi int exec jieshouzhi=pr_ numStudent print jieshouzhi,8.2.4 存储过程的返回值,接收output的返回值也必须要用变量，如：declare jieshouzhi int exec pr_ numStudent_2 jieshouzhi output print jieshouzhi注意：1)return返回的是整数，output可以返回任何数据。2)如果让return返回非整数值，在创建存储过程时不会出错，但是运行存储过程时将会出错。,练习1,在student数据库中创建一个带参数的存储过程pr_SelectStu，查询出学生表中是否存在XXX(姓名)同学，如果不存在，则返回值为1，否则查询该学生的班级是否XX班（班级编号），如是，则返回值为2，否则返回值为0。,练习1,create 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),练习1,执行存储过程语句如下:Declare fanhui intExec fanhui=pr_SelectStu 黄蓉,10010911If fanhui=0Print 黄蓉是10010911班的学生If fanhui=1Print 没有黄蓉这个学生If fanhui=2Print 黄蓉不是10010911班的学生,练习2,例：建立一个带参的存储过程，用于向student数据库中的T_student表中添加一条记录，在添加记录前先查看是否存在此学生的学号，如不存在则插入该学生信息，存储过程返回值为0，如已存在，则不进行插入记录操作，存储过程返回值为-1。,练习2,实现的T-SQL语句:CREATE PROC pr_InsertStu Id char(10),Name nchar(10),class nchar(10)，group nchar(20),练习2,AS IF EXISTS(SELECT*FROM T_student WHERE Sid=Id)Return-1 ELSE BEGIN INSERT T_student(Sid,Sname,Cid,Gname)VALUES(Id,name,class,group)RETURN 0 END,练习2,存储过程的执行如下：DECLARE result int Exec result=pr_InsertStu 1001091204,裘千仞,软件1101 IF result=0 PRINT 记录插入成功 IF result=-1 PRINT 学号已存在，记录重复！GO,练习3,例12：建立一个带参的存储过程pr_updateScore，用于修改student数据库T_opt表中某位同学的某门课程成绩。,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,课后练习1,在student数据库中创建一个带参的存储过程pr_updateStudent，其功能是修改某学生的姓名。接受用户输入的学生学号、原姓名和新姓名，首先查询出T_student表中是否存在该学生学号和姓名，如没有，则返回（-1），否则修改其姓名。,课后练习2,建立一个带参的存储过程pr_getScore，根据输入的学号查询某学生的最高成绩和平均成绩,并将这两个成绩值返回。,