存储过程.docx
《存储过程.docx》由会员分享，可在线阅读，更多相关《存储过程.docx（11页珍藏版）》请在冰豆网上搜索。
存储过程
实训八存储过程
一、实训目的
1．通过对常用系统存储过程的使用，了解存储过程的类型；
2．通过创建和执行存储过程，了解存储过程的基本概念，掌握使用存储过程的操作技巧和方法；
3．通过对已创建的存储过程的改变，掌握修改存储过程的操作技巧和方法；
二、实训要求
1．实训前做好上机实训的准备，针对实训内容，认真复习与本次实训有关的知识，完成实训内容的预习准备工作；
2．能认真独立完成实训内容；
3．实训后做好实训总结，根据实训情况完成总结报告。
三、实训学时
2学时
四、实训操作及流程分析
1、创建与执行存储过程
（1）用T-SQL语句创建存储过程
可以使用CREATEPROCEDURE语句来创建存储过程。
例8.1在学生成绩库中创建一个名为proc_8_2的存储过程，产生一个学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、学分等。
分析：
要产生一个学生选课情况列表，包含学号、姓名、性别、课程号、课程名、成绩、学分等字段信息，其Select语句如下：
SELECTxscj.学号,xsqk.姓名,xsqk.性别,xscj.课程号,xskc.课程名,xscj.成绩,xscj.学分
FROMxscj,xsqk,xskc
WHERExscj.学号=xsqk.学号ANDxscj.课程号=xskc.课程号
那么，完整的创建存储过程proc_8_2的T-SQL语句如下：
CREATEPROCEDUREproc_8_2
AS
SELECTxscj.学号,xsqk.姓名,xsqk.性别,xscj.课程号,xskc.课程名,xscj.成绩,xscj.学分
FROMxscj,xsqk,xskc
WHERExscj.学号=xsqk.学号ANDxscj.课程号=xskc.课程号
GO
上述T-SQL语句执行后，在学生成绩库中就存在了名为proc_8_2的存储过程。
（2）用企业管理器创建存储过程
例8.2用企业管理器实现例8.2所述存储过程的创建。
具体操作如下：
●打开企业管理器，展开服务器组，展开服务器，展开“数据库”文件夹。
再展开“学生成绩库”，右击“存储过程”，如图1用企业管理器创建存储过程。
●单击“新建存储过程”命令，见图2新建存储过程过程窗口。
●在该窗口的文本框中，编写存储过程的语句。
如图3编写存储过程语句。
●单击“确定”命令。
可以看到在企业管理器中，已存在名为proc_8_3的存储过程。
图1用企业管理器创建存储过程
图2新建存储过程窗口
图3编写存储过程语句
存储过程创建后，可以通过在查询分析器的查询窗口中键入存储过程名来执行，也可以使用EXECUTE语句来执行存储过程。
例8.3调用例8.2或例8.3创建的存储过程proc_8_2或proc_8_3，查看执行结果。
在查询分析器的查询窗口中，输入如下语句：
EXECproc_8_2
或
proc_8_2
执行后，可以在结果窗格中看到相应信息。
（3）指定参数
存储过程通过其参数与调用程序通讯。
当程序执行存储过程时，可通过存储过程的参数向该存储过程传递值。
这些值可作为Transact-SQL编程语言中的标准变量使用。
存储过程也可通过OUTPUT参数将值返回至调用程序。
一个存储过程可有多达2100个参数，每个参数都有名称、数据类型、方向和默认值。
例8.4在学生成绩库中创建一个名为proc_8_5的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、系别、班级、专业、性别等。
要求输入某门课程的课程号，得到上述信息。
分析：
根据题意，需要在存储过程中使用参数，然后通过参数实现向存储过程传递值，得到所需的信息。
完整的T-SQL语句如下：
CREATEPROCEDUREproc_8_5
@cnochar（3）--@cno是参数，字符型，表示某门课的课程号
AS
SELECTxscj.课程号,xskc.课程名,xscj.学号,xsqk.姓名,xsqk.系别,xsqk.班级,xsqk.专业,xsqk.性别
FROMxscj,xsqk,xskc
WHERExscj.学号=xsqk.学号ANDxscj.课程号=xskc.课程号
ANDxscj.课程号=@cno
GO
在查询分析器的查询窗口中输入上述T-SQL语句，执行后，即在“学生成绩库”中创建了存储过程proc_8_5。
如果要产生课程号为“101”的课程的选课学生情况列表，可以调用已创建的存储过程proc_8_5实现。
具体操作如下：
在查询分析器的查询窗口中，输入语句：
proc_8_5‘101’
或
EXECproc_8_5‘101’
或
EXECproc_8_5@cno=’101’
或
proc_8_5@cno=’101’
执行后，在结果窗格中可以看到相应信息。
例8.5在学生成绩库中，定义存储过程proc_8_6，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！
”，否则显示“此学生综合成绩不合格！
”。
在查询分析器的查询窗口中，输入语句：
CREATEPROCEDUREproc_8_6
@snochar（8）--@sno是参数，字符型，表示学生的学号
AS
DECLARE@savgtinyint--声明变量@savg，用于存放学生平均成绩
SELECT@savg=avg（成绩）FROMxscj
WHERExscj.学号=@sno--变量@savg存放学号为@sno的学生平均成绩
IF@savg>=60--进行平均成绩的判断
PRINT‘此学生综合成绩合格！
’
ELSE
PRINT‘此学生综合成绩不合格！
’
GO
执行后，在学生成绩库中创建了名为proc_8_6的存储过程。
如果要查询“02020101”号学生的平均成绩是否合格，可以调用上述存储过程，具体操作如下：
在查询分析器的查询窗口中，输入语句：
proc_8_6‘02020101’
或
EXECproc_8_6‘02020101’
或
EXECproc_8_6@sno=’02020101’
或
proc_8_6@sno=’02020101’
执行后，在结果窗格中可以看到如下的提示信息：
此学生综合成绩合格！
同样道理，可以查询到“02020202”号学生的平均成绩不合格，调用上述存储过程，执行结果如下：
此学生综合成绩不合格！
2、修改存储过程
（1）用T-SQL修改存储过程
根据需要的变化或基表定义的改变，可以使用ALTERPROCEDURE修改存储过程，该语句修改了存储过程并保留以前赋予的许可。
例8.6如果已完成例8.6的存储过程proc_8_6的创建，现要求对它进行修改，实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格，成绩为XX分。
”，否则显示“此学生综合成绩不合格，成绩为XX分”。
分析：
存储过程proc_8_6已创建，根据要求对它进行修改，只要使用ALTERPROCEDURE语句即可实现。
在查询分析器的查询窗口中，输入如下语句：
USE学生成绩库
GO
ALTERPROCEDUREproc_8_6
@snochar（8）--@sno是参数，字符型，表示学生的学号
AS
DECLARE@savgtinyint--声明变量@savg，用于存放学生平均成绩
SELECT@savg=avg（成绩）FROMxscj
WHERExscj.学号=@sno--变量@savg存放学号为@sno的学生平均成绩
IF@savg>=60--进行平均成绩的判断
PRINT'此学生综合成绩合格，成绩为'+convert（char
（2）,@savg）+'分。
'
ELSE--convert为数据类型转换函数
PRINT'此学生综合成绩不合格，成绩为'+convert（char
（2）,@savg）+'分。
'
GO
执行后，存储过程proc_8_6已被修改。
（2）用企业管理器修改存储过程
例8.7用企业管理器实现例8.7所要求的功能。
具体操作如下：
◆打开企业管理器，展开服务器组，展开服务器，展开“数据库”文件夹。
再展开“学生成绩库”，单击“存储过程”，如图4用企业管理器修改存储过程。
◆右击“proc_8_7”存储过程，然后单击“属性”命令。
见图5修改存储过程。
◆在该窗口的文本框中，按需要更改存储过程的文本。
如图6更改存储过程。
◆单击“确定”命令。
存储过程proc_8_7被修改成功。
图4用企业管理器修改存储过程
图5修改存储过程
图6更改存储过程文本
我们可以调用此存储过程，查看修改后的存储过程执行情况。
假设要查“02020101”号学生成绩是否合格，可以调用此过程实现，具体语句如下：
在查询分析器的查询窗口中，输入：
proc_8_7'02020101'
或
EXECproc_8_7‘02020101’
或
EXECproc_8_7@sno=’02020101’
或
proc_8_7@sno=’02020101’
执行后，在结果窗格中可以看到如下的提示信息：
此学生综合成绩合格，成绩为77分。
同样道理，可以查询到“02020202”号学生的平均成绩不合格，调用上述存储过程，执行结果如下：
此学生综合成绩不合格，成绩为55分。
3、删除存储过程
不再需要存储过程时可将其删除。
如果另一个存储过程调用某个已删除的存储过程，则SQLServer2000会在执行该调用过程时显示一条错误信息。
但如果定义了同名和参数相同的新存储过程来替换已删除存储过程，那么引用该过程的其它过程仍能顺利执行。
例如，如果存储过程proc1引用存储过程proc2，而proc2被删除，但创建了另一个名为proc2的存储过程，现在proc1将引用这一新存储过程，proc1也不必重新编译。
删除存储过程可以使用DROPPROCEDURE命令，其语法如下：
DROPPROCEDURE
这里是需要从系统中删除的存储过程名。
例8.8删除学生成绩库中的存储过程proc_8_1。
存储过程proc_8_1已经创建，存在于学生成绩库中，可以使用DROPPROCEDURE删除它。
具体操作如下：
在查询分析器的查询窗口中，输入语句：
DROPPROCDEUREproc_8_1
执行后，可以看到在学生成绩库中，已没有proc_8_1存储过程。
也可以在企业管理器中删除存储过程proc_8_1，具体操作如下：
◆展开服务器组，然后展开服务器。
展开“数据库”文件夹，展开存储过程所属的数据库，然后单击“存储过程”文件夹。
◆在详细信息窗格中右击要删除的存储过程，然后单击“删除”命令。
如图7用企业管理器删除存储过程。
若要查看删除此存储过程对数据库的影响，单击"显示相关性"命令。
◆单击“全部除去”按钮。
如图8删除存储过程。
存储过程即被删除成功。
图7用企业管理器删除存储过程
图8删除存储过程
五、实训项目
1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。
2、在学生成绩库中创建存储过程proc_8_t1，要求实现如下功能：
产生学分为’4’的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、系别、专业、性别等。
并调用此存储过程，显示执行结果。
3、在学生成绩库中创建存储过程proc_8_t2，要求实现如下功能：
输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。
并调用此存储过程，显示“信息管理”专业学生的选课情况列表。
4、在学生成绩库中创建存储过程proc_8_t3，要求实现如下功能：
输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9，则显示“此学生学分不足！
”，否则显示“此学生学分已足！
”。
并调用此存储过程，显示“02020101”学生的总学分情况。
对学生成绩库中已创建的存储过程proc_8_t1进行修改，要求在显示列表中增加班级字段，即产生学分为’4’的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、系别、专业、班级、性别等。
5、对学生成绩库中已创建的存储过程proc_8_t2进行修改，要求实现如下功能：
输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。
并调用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。
6、对学生成绩库中已创建的存储过程proc_8_t3进行修改，要求实现如下功能：
输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9，则显示“此学生所选总学分为XXX，学分不足！
”，否则显示“此学生所选总学分为XXX，学分已足！
”。
并调用修改后的存储过程，显示“02020101”学生的总学分情况。
7、删除学生成绩库中的存储过程proc_8_t1。
六、实训思考题
1、存储过程的类型有哪些？
分别有什么特征？
。
2、如何创建一个存储过程？
试述存储过程在程序设计的作用。
1、3、假设必须修改数据库中的一个存储过程，同时有几个用户被授予执行这个存储过程的权限，请问执行哪个语句可以实现修改，但又不影响现有的权限。
对格式的要求
知网学位论文检测为整篇上传，上传论文后，系统会自动检测该论文的章节信息，如果有自动生成的目录信息，那么系统会将论文按章节分段检测，否则会自动按每一万字左右分段检测。
格式对检测结果可能会造成影响，需要将最终交稿格式提交检测，将影响降到最小，此影响为几十字的小段可能检测不出。
都不会影响通过。
系统的算法比较复杂，每次修改论文后再测可能会有第一次没测出的小段抄袭（经2年实践经验证明，该小段不会超过200字，并且二次修
改后论文一般会大大降低抄袭率）
升级会员 