实验8存储过程市公开课一等奖省赛课获奖课件

试验8存放过程

存放过程是使用SQLServer所提供Transact-SQL语言所编写程序。SQLServer不但提供了用户自定义存放过程功效，而且也提供了许多可作为工具使用系统存放过程。经过本试验来学习、掌握用户自定义存放过程创建、修改、删除、执行方法。【知识关键点】

1．存放过程类型SQLServer中存放过程划分为四类：系统存放过程扩展存放过程用户自定义存放过程暂时性存放过程惯用存放过程为两类：系统提供存放过程和用户自定义存放过程。系统提供存放过程是系统自动创建，主要存放在master数据库中并以sp_为前缀。功效主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。用户自定义存放过程是由用户创建并完成某一特定功效存放过程，存放在所属数据库中。实验8存储过程第1页试验8存放过程

2．存放过程特点使用SQLServer中存放过程而不使用存放在客户计算机当地T-SQL程序原因主要是存放过程含有以下特点：（1）允许模块化程序设计存放过程只需创建一次便可作为数据库中对象之一存放在数据库中，以后各用户即可在程序中调用该过程任意次。（2）执行速度更加快存放过程只在第一次执行时需要编译且被存放在存放器内，其它次执行就能够无须由数据引擎逐一再翻译，从而提升了执行速度。（3）降低网络流量一个需要数百行Transact-SQL代码操作由一条执行过程代码单独语句就可实现，而不需要在网络中发送数百行代码。（4）可作为安全机制使用对于没有直接执行存放过程中某个（些）语句权限用户，也可授予他们执行该存放过程权限。（5）减轻操作人员和程序设计者劳动强度用户能够经过执行现有存放过程，并提供存放过程所需参数就能够得到他要结果而不用接触SQL命令。实验8存储过程第2页试验8存放过程

3．SQLServer应用程序在使用SQLServer创建应用程序时，Transact-SQL编程语言是应用程序和SQLServer数据库之间主要编程接口。使用Transact-SQL程序时，可用两种方法存放和执行程序：（1）在当地（客户端）创建并存放程序，把此程序发送给SQLServer执行；（2）在SQLServer中创建存放过程，并将其存放在SQLServer中；然后SQLSERVER或客户端调用执行此存放过程。4．存放过程功效SQLServer中存放过程与其它编程语言中过程类似：（1）能够以输入参数形式引用存放过程以外参数（2）能够以输出参数形式将多个值返回给调用它过程或批处理。（3）存放过程中包含有执行数据库操作编程语句，也可调用其它存放过程。（4）用RETURN向调用过程或批处理返回状态值，以表明成功或失败，以及失败原因。实验8存储过程第3页试验8存放过程

5．创建存放过程SQL语句CREATEPROCEDURE[拥有者.][存放过程名][；程序编号][{@参数名数据类型}

[VARYING][=默认值][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}]AS程序行6．执行存放过程SQL语句[[EXEC[UTE]][@返回值=]{程序名[；程序编号]|@存放程序名变量}[[@参数名=]{参数值}|@变量[OUTPUT]|[DEFAULT]][,…n][WITHRECOMPILE]7．存放过程权限（1）执行CreateProcedure语句，必须是系统管理员、数据库拥有者或数据定义语言管理员角色中一个组员，或被授予CreateProcedure权限。（2）创建过程者一定拥有过程中所引用全部对象权限：UPDATE、INSERT、DELETE、SELECT（3）若过程拥有者把执行此过程权限授予某用户，那么此用户就拥有此过程执行权限。实验8存储过程第4页试验8存放过程

【试验目标】经过试验掌握存放过程概念、功效；掌握用户自定义存放过程创建、修改、删除和执行。实验8存储过程第5页试验8.1创建并执行存放过程

【试验目标】掌握用企业管理器创建存放过程；掌握用SQL创建存放过程；掌握执行存放过程方法。【试验内容】用企业管理器创建并执行一存放过程Prc_Qsinf：经过学生学号来查询学生姓名、年纪、系名。默认学号是“S2”；用SQL创建并执行一存放过程Pro_Qscore：经过学生姓名和课程名查询该生该课程成绩。实验8存储过程第6页

【试验步骤】1．用企业管理器创建并执行一存放过程（1）创建存放过程Pro_Qsinf：经过学生学号来查询学生姓名、年纪、系名

用企业管理器创建存放过程数据库jxsk中存放过程对象试验8.1创建并执行存放过程实验8存储过程第7页（2）执行存放过程Pro_Qsinf。查询并显示出默认学号（即S2）和学号为S4学生姓名和年纪。

执行存放过程查询S2和S4学生信息试验8.1创建并执行存放过程实验8存储过程第8页

2．用SQL创建一存放过程（1）创建存放过程Pro_Qscore：经过学生姓名和课程名查询该生该课程成绩。（2）执行存放过程Pro_Qscore。查询并显示学生“李思”“程序设计”课程成绩。

（1）用SQL创建存放过程（2）调用存放过程实现查询试验8.1创建并执行存放过程实验8存储过程第9页试验8.2修改存放过程

【试验目标】

掌握修改用CREATEPROCEDURE命令创建存放过程方法，而且不改变权限。【试验内容】

1.用企业管理器修改存放过程Prc_Qsinf。存放过程定义以下：CreateProcedurePro_Qsinf@sno_inchar(8)='S2',@sname_outchar(8)output,@sage_outintoutput，@dept_outchar(10)outputAsselect@sname_out=sn,@sage_out=age,@dept_out=deptfromswheresno=@sno_in

修改要求：把定义中变量sno_in长度修改为2字节；sage_out变量类型改为tinyint。2.用SQL修改存放过程Prc_Qsinf。存放过程定义改为：依据学号查询姓名、性别、系名。设默认学号为“S1”。实验8存储过程第10页试验8.2修改存放过程

【试验步骤】1．用企业管理器修改存放过程Prc_Qsinf存放过程属性窗口修改后存放过程Pro_Qsinf实验8存储过程第11页试验8.2修改存放过程

2．用SQL修改存放过程Prc_Qsinf修改存放过程Pro_Qsinf存放过程Pro_Qsinf中对象实验8存储过程第12页试验8.3删除存放过程

【试验目标】掌握使用企业管理器删除存放过程操作；掌握使用SQL删除存放过程方法。【试验内容】用企业管理器删除存放过程Pro_Qsinf；用SQL删除存放过程Pro_Qscore。实验8存储过程第13页试验8.3删除存放过程

【试验步骤】1．用企业管理器删除存放过程Pro_Qsinf2．用SQL删除存放过程Prc_Qscore删除存放过程命令删除存放过程SQL语句实验8存储过程第14页试验8习题

【试验题】内容与要求

基于试验6中“教学数据库jiaoxuedb”，创建下面存放过程：1.利用学生名查询该生选修课程名、成绩，任课教师名。2.查询某系学生最大年纪和最小年纪。3.利用学生姓名和课程名检索该生该课程成绩。4.依据职称查询人数。并给出“副教授”人数。5.统计某系某职称人数、平均年纪、平均工资、最高工资。6.查询某系教师人数、平均年纪和学生人数。7.利用课程名查询选修该课程学生姓名、系别、成绩。并给出“程序设计”课程该查询信息。8.利用教师姓名和课程名检索该教师该任课课程名、课时数、选课人数、平均成绩、最高成绩。并查询教师“张雪”“微机原理”课程情况统计。9.删除上面“1”创建存放过程。10.删除上面“3”和“4”创建存放过程。实验8存储过程第15页试验8习题【思索题】1．存放过程存放在什么地方？答：

。2．存放过程在什么时候被编译？在每次执行时编译能够吗？答：

。3．下面是一个存放过程定义：CREATEPROCEDUREInsertRecord（@snochar(6),@snchar(8),@agetinyint,@sexchar(2),@deptchar(10))ASINSERTINTOSVALUES(@sno,@sn,@age,@sex,@dept)GO

实验8存储过程第16页试验8习题回答以下问题：（1）@sno是何种参数？其它参数呢？答：