计算机操作系统复习资料-第8章

第8章存储过程.存储过程存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象。存储过程类型

系统存储过程扩展存储过程用户自定义存储过程系统存储过程存储以SP_为前缀,是由SQLServer2005自己创建、管理和使用的一种特殊的存储过程，不能对其进行修改或删除。如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）语法格式：CREATEPROC[EDURE

]procedure_nameAS

sql_statement[...n]

2）语法注释：

procedure_name：新建存储过程的名称，其名称必须符合标识符命名规则，且对于数据库及其所有者必须唯一。

sql_statement：指存储过程中的任意数目和类型的Transact-SQL语句。.8.1.1无参存储过程的创建例1：在student数据库中，创建一个名称为pr_searchScore的存储过程，该存储过程将查询出选修表中“降龙十八掌”课程的成绩信息。代码如下：

CREATEPROC

ASSELECTa.Lid,Sid,scoreFROMT_opta,T_lessonbWHEREa.Lid=b.Lidandlname=‘降龙十八掌'

pr_searchScore.创建存储过程的注意事项1)存储过程是数据库对象，其名称必须遵守标识符命名规则。2)不能将CREATEPROCEDURE语句与其它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的执行语句如下：

EXECpr_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修改存储过程

修改存储过程语法格式为：ALTERPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement.8.1.4修改存储过程

例2：修改存储过程pr_searchScore,查询出成绩表中’降龙十八掌’课程所有低于100分的成绩。

ALTERPROCAsSELECTa.Lid,Sid,scoreFROMT_opta,T_lessonbWHEREa.Lid=b.Lidandlname=‘降龙十八掌‘

andscore<100Pr_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编译存储过程.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).使用DROPPROCEDURE语句删除存储过程

DROPPROCEDURE语句可以一次从当前数据库中将一个或多个存储过程或过程组删除：

DROPPROCEDURE

存储过程名称[,n]例：删除存储过程pr_searchScoreDROPPROCEDUREpr_searchScore.第8章存储过程8.1创建管理简单存储过程

8.2创建管理带参数的存储过程

.8.2创建管理带参数的存储过程8.2.1创建带参数存储过程的语法8.2.2带输入参数的存储过程

8.2.3带输出参数的存储过程

8.2.4存储过程的返回值.8.2.1创建带参数存储过程的语法1）语法：CREATEPROC[DURE]procedure_name[{@parameterdata_type}[=default][OUTPUT][,…n]][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSql_statement.8.1.1创建带参数存储过程的语法2）语法注释：parameter：存储过程中的输入和输出参数。

data_type：参数的数据类型。

OUTPUT：表明参数是输出参数。该选项的值可以返回给EXEC[UTE]。.8.2创建管理带参数的存储过程8.2.1创建带参数存储过程的语法

8.2.2带输入参数的存储过程

8.2.3带输出参数的存储过程

8.2.4存储过程的返回值.8.2.2带输入参数的存储过程

例3：在student数据库中创建一个存储过程pr_searchStuScore，查询出指定的某位学生的所有成绩。CREATEPROCpr_searchStuScoreAsSelectLid,a.Sid,ScoreFromT_opta,T_studentbWherea.Sid=b.Sidandsname=@snamenchar(10)@sname.8.2.2带输入参数的存储过程执行带输入参数的存储过程：

[[EXEC[UTE]]{[@return_status=]{procedure_name}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]

执行存储过程pr_searchStuScore：

EXECUTEpr_searchStuScore‘黄蓉’

EXECpr_searchStuScore@sname=‘黄蓉’.8.2.2带输入参数的存储过程执行带输入参数的存储过程：

[[EXEC[UTE]]{[@return_status=]{procedure_name}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]

执行存储过程pr_searchStuScore：

declare@Snamenchar(10) set@Sname=‘黄蓉’

EXECpr_searchStuScore@Sname

.8.2.2带输入参数的存储过程例4：创建存储过程pr_searchStuLesson，根据指定的学生姓名和课程名查询成绩。CREATEPROCpr_searchStuLessonASSELECTLname,Sname,scoreFROMT_opta,T_studentb,T_lessoncWHEREa.Sid=b.Sidandb.Lid=c.Lid@snamenchar(10)andb.Sname=@snameandc.Lname

=@lname,@lnamenchar(20).8.2.2带输入参数的存储过程1)按位置传递参数值在执行存储过程的语句中，直接给出参数的值。当有多个参数时，给出的参数的顺序与创建执行存储过程的语句中的参数的书写顺序一致。2)通过参数名传递参数值在执行存储过程的语句中，使用【参数名=参数值】的形式给出参数值。其优点是参数可以以任意顺序给出。.8.2.2带输入参数的存储过程3)在输入参数中使用默认值

在执行有参数的存储过程时，如果没有指定参数，则系统运行就会出错；此时如果希望在执行时不给出参数也能正确运行，则在创建存储过程时给输入参数指定默认值。.8.2.2带输入参数的存储过程按位置传递参数值execpr_searchStuLesson‘黄蓉’,‘打狗棒法'通过参数名传递参数值execpr_searchStuLesson@sname=’黄蓉’,@lname=’打狗棒法‘或execpr_searchClaLesson@lname=’打狗棒法’,@sname=’黄蓉’.8.2.2带输入参数的存储过程例5：对存储过程pr_searchStuLesson进行修改，指定课程默认值为‘打狗棒法’，指定学生默认值为‘黄蓉’。ALTERPROCpr_searchStuLesson@snamenchar(10)=’黄蓉’,@lnamenchar(20)=‘打狗棒法’ASSELECTLname,Sname,scoreFROMT_opta,T_studentb,T_lessoncWHEREa.Sid=b.Sidandb.Lid=c.Lidandb.Sname=@snameandc.Lname=@lnameGOEXECpr_searchStuLesson.8.2创建管理带参的存储过程8.2.1创建带参存储过程的语法

8.2.2带输入参数的存储过程

8.2.3带输出参数的存储过程

8.2.4存储过程的返回值.8.2.3带输出参数的存储过程例6：在student数据库中创建一个存储过程pr_avgScore,输出某门课程的平均成绩。CREATEPROCpr_avgScore@lnamenchar(20)AsSelectavg(Score)fromT_opta,T_lessonbWherea.Lid=b.Lidandlname=@lname执行:

declare@resultdecimal(3,1)execpr_avgScore‘九阴真经',select@resultas平均成绩,@ageScoredecimal(3,1)output@ageScore=@resultoutput.8.2.3带输出参数的存储过程例7：创建一个存储过程pr_getTopStudent，查询平均成绩最高的学生姓名。CREATEPROCpr_getTopStudent@namenchar(10)OUTPUTAS SELECTtop1sname FROMT_studentajoinT_optbona.Sid=b.Sid groupbySname orderbyavg(Score)DESCGO@name=.8.2.3带输出参数的存储过程执行:DECLARE@resultnchar(10)EXECpr_getTopStudent@resultOUTPUTSELECT‘平均成绩最高的学生是：'+@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数据库中创建一个存储过程，返回学生人数。createprocpr_numStudentasdeclare@fanhuizhiintselect@fanhuizhi=count(sid)fromT_studentreturn@fanhuizhicreateprocpr_numStudent@fanhuizhiintoutputasselect@fanhuizhi=count(sid)fromT_student.8.2.4存储过程的返回值接收返回值必须要用变量来接收

declare@jieshouzhiintexec@jieshouzhi=pr_numStudentprint@jieshouzhi.8.2.4存储过程的返回值接收output的返回值也必须要用变量，如：

declare@jieshouzhiint

execpr_numStudent_2@jieshouzhioutputprint@jieshouzhi注意：1)return返回的是整数，output可以返回任何数据。2)如果让return返回非整数值，在创建存储过程时不会出错，但是运行存储过程时将会出错。.练习1.练习1createprocpr_SelectStu@snamenchar(10),@sclassnchar(10)asif@snamenotin(selectSnamefromT_student)return(1)elseifexists(select*fromT_studentwheresname=@snameandCid=@sclass)return(2)elsereturn(0).练习1执行存储过程语句如下:Declare@fanhuiintExec@fanhui=pr_SelectStu‘黄蓉’,’10010911’If@fanhui=0Print‘黄蓉是10010911班的学生’If@fanhui=1Print‘没有黄蓉这个学生’If@fanhui=2Print‘黄蓉不是10010911班的学生’.练习2例：建立一个带参的存储过程，用于向student数据库中的T_student表中添加一条记录，在添加记录前先查看是否存在此学生的学号，如不存在则插入该学生信息，存储过程返回值为0，如已存在，则不进行插入记录操作，存储过程返回值为-1。.练习2实现的T-SQL语句:CREATEPROCpr_InsertStu@Idchar(10), @Namenchar(10),@classnchar(10)，