数据库原理实验报告S10数据库存储过程.docx
《数据库原理实验报告S10数据库存储过程.docx》由会员分享，可在线阅读，更多相关《数据库原理实验报告S10数据库存储过程.docx（14页珍藏版）》请在冰豆网上搜索。
数据库原理实验报告S10数据库存储过程
实验10数据库存储过程的建立
实验日期和时间：
实验室：
班级：
学号：
姓名：
实验环境：
1.硬件：
2.软件：
实验原理：
1.理解存储过程的概念、优点和使用原则
2.掌握存储过程的创建、执行、查看、修改和删除方法
一、存储过程的概念
▪SQLSERVER中的存储过程类似于编程语言中的过程或函数。
--以下为创建一个简单的用户存储过程
createprocp_test（@aint,@bintoutput）
as
set@b=@a+1
--以下为调用
declare@aint,@bint
select@a=1,@b=0
execp_test@a,@boutput
select@a,@b
在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名来调用它们，这些过程就叫做存储过程。
▪存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
存储过程有以下特点：
▪存储过程中可以包含一条或多条Transact-SQL语句。
▪存储过程可以接受输入参数并可以返回输出值。
▪在一个存储过程中可以调用另一个存储过程。
▪存储过程可以返回执行情况的状态代码给调用它的程序。
存储过程的优点：
▪实现了模块化编程，一个存储过程可以被多个用户共享和重用。
▪通过通用编程结构和过程重用实现编程框架。
▪存储过程具有对数据库立即访问的功能。
▪使用存储过程可以通过本地存储、代码预编译和缓存技术实现高性能的数据操作，加快程序的运行速度。
▪使用存储过程可以减少网络流量。
▪使用存储过程通过隔离和加密的方法提高数据库的安全性。
存储过程的分类：
在SQLServer中的存储过程分为两类：
即系统提供的存储过程、扩展存储过程和用户自定义的存储过程。
▪系统存储过程：
由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。
并且都带有sp_前缀。
在SQLServer2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。
▪用户自定义存储过程：
是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。
可以接受输入参数，返回输出参数。
在SQLServer2005中，用户自定义存储过程有两种类型：
Transact-SQL存储过程和CLR存储过程。
✧Transact-SQL存储过程：
是指保存的Transact-SQL语句集合。
✧CLR存储过程：
是指对Microsoft.NETFramework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。
二、存储过程的创建方法
在SQLServer中，可以使用两种方法创建存储过程：
1.使用SQLServer管理控制平台创建存储过程。
在SQLServer管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程…”选项，
此时出现创建存储过程窗口。
在文本框中可以看到系统自动给出了创建存储过程的格式模板语句，可以根据模板格式进行修改来创建新的存储过程。
或者在创建存储过程的窗口中单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框，做后续的操作。
2.使用Transact-SQL中的CREATEPROCEDURE创建存储过程。
CREATE{PROC|PROCEDURE}[schema_name.]procedure_name
[{@parameter[type_schema_name.]data_type}
[VARYING][=default][OUT|OUTPUT]][,...n][WITHENCRYPTION]
AS{[;][...n]}[;]
:
:
={[BEGIN]statements[END]}
其中，各参数的意义如下：
▪schema_name：
过程所属架构的名称。
▪procedure_name：
新存储过程的名称。
▪@parameter：
过程中的参数。
▪[type_schema_name.]data_type：
参数以及所属架构的数据类型。
▪VARYING：
指定作为输出参数支持的结果集。
仅适用于cursor参数。
▪default：
参数的默认值。
▪OUTPUT：
指示参数是输出参数。
▪ENCRYPTION：
将CREATEPROCEDURE语句的原始文本加密。
▪：
要包含在过程中的一个或多个Transact-SQL语句。
使用CREATEPROCEDURE命令创建存储过程，考虑下列几个事项：
▪CREATEPROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。
▪必须具有数据库的CREATEPROCEDURE权限。
默认情况下，创建存储过程的许可权归数据库的所有者，数据库的所有者可以把许可权给其他用户。
▪只能在当前数据库中创建存储过程。
▪不要创建任何使用sp_作为前缀的存储过程。
SQL_Server使用sp_前缀指定系统存储过程。
当创建存储过程时，需要确定存储过程的三个组成部分：
1.所有的输入参数以及传给调用者的输出参数。
2.被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
3.返回给调用者的状态值，以指明调用是成功还是失败。
实验主要任务：
附加或导入学生成绩数据库，对数据库实施如下操作：
一、创建存储过程
（一）无参存储过程的创建：
形如：
CREATEPROCEDURE存储过程名
AS
SELECT子句
1.利用SQL-SERVER管理控制台：
创建一个名称为“StuInfo”的存储过程，要求完成以下功能：
查询06级学生的学号、姓名、班级名称，班级号和学院编号五个字段的内容
2.利用Transact-创建一个存储过程StuScoreInfo，完成的功能是查询艾宏迪同学的班级、学号、姓名、课程名称、考试分数。
（二）有参存储过程的创建：
形如：
CREATEPROCEDURE存储过程名
@变量名1变量类型，
@变量名2变量类型，
@变量名3变量类型OUTPUT
--声明变量，一个@是局部变量，两个@@是全局变量，是系统预定义的。
如@@ERROR返回最后执行的SQL语句的错误代码，OUTPUT作为输出变量关键字。
AS
SELECT子句
3.一个有参变量（输入）的存储过程的建立：
利用Transact-SQL创建一个存储过程StuScoreInfo2，完成的功能是传入学生的姓名，如分别传入艾宏迪，安然，白冰等同学的姓名，查看该同学的班级、学号、姓名、课程名称、考试分数。
4.多个有参变量（输入）的存储过程的建立：
利用Transact-SQL创建一个存储过程Stu_Class_Lastname，完成的功能是传入某班班级号，某种姓氏，查相应同学的班级名称、学号、姓名、课程名称、考试分数。
（多个变量之间用逗号分隔）
5.带有返回（输出）参数的存储过程的建立。
/*当需要从存储过程中返回一个或多个值时，可以在存储过程的语句中定义这些输出参数，此时需要在CREATEPROCEDRUE语句中使用OUTPUT关键字说明是输出参数*/
5.1创建一个带有输出参数的存储过Stu_Classnum的存储过程，在2003学生名单中使得它能够根据用户给定的班级名称统计该班的学生人数，并将学生人数返回给用户
5.2创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的姓名，在2003学生名单表中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。
（多个变量之间用逗号分隔）
二、执行存储过程
存储过程创建成功后，保存在数据库中。
在SQLServer中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：
[[EXEC[UTE]]{[@return_status=]{procedure_name|@procedure_name_var}
[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n]]}]
其中，各选项的含义如下：
▪EXECUTE：
执行存储过程的命令关键字。
▪@return_status：
是一个可选的整型变量，保存存储过程的返回状态。
▪procedure_name：
指定执行的存储过程的名称。
▪@procedure_name_var：
是局部定义变量名，代表存储过程名称。
▪@parameter：
是在创建存储过程时定义的过程参数。
（一）、无参存储过程的执行：
6.利用EXECUTE执行名称为“StuInfo”的存储过程
7.利用EXECUTE执行名称为StuScoreInfo的存储过程
（二）、有参存储过程的执行
8.一个有参变量（输入）的存储过程的执行：
利用EXECUTE执行名称为StuScoreInfo2”的存储过程，如分别传入艾宏迪，安然，白冰等同学的姓名，查看该同学的班级、学号、姓名、课程名称、考试分数。
注意与无参数的存储过程“StuScoreInfo”进行比较。
9.多个有参变量（输入）的存储过程的执行：
利用EXECUTE执行名称为Stu_Class_Lastname的存储过程
10.利用EXECUTE执行名称执行存储过程Stu_Age，该存储过程有一个输入参数“姓名”，另外，还有一个输出参数@Age。
存储过程执行完后，有一个返回的状态值，这个值可以从变量@ErrorValue得到。
三、修改存储过程
（一）使用SQLServer管理控制台修改存储过程
使用SQLServer管理控制台可以很方便地修改存储过程的定义。
在SQLServer管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“修改”选项，则会出现与创建存储过程时类似的窗口。
在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。
（二）．使用Transact-SQL语句修改存储过程
使用ALTERPROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。
其语法形式如下：
ALTER{PROC|PROCEDURE}[schema_name.]procedure_name[{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITHENCRYPTION]
ASsql_statement[...n]
修改存储过程时，应该注意以下几点：
Ø如果原来的过程定义是使用WITHENCRYPTION创建的，那么只有在ALTERPROCEDURE中也包含这个选项时，这个选项才有效。
Ø每次只能修改一个存储过程。
Ø用ALTERPROCEDURE更改的存储过程的权限保持不变。
11.修改前面创建的Stu_Info存储过程，使之完成以下功能：
根据班级名称，查询该班的班级、学号、姓名、性别、考试课程名称和考试分数，并执行该存储过程。
（原StuInfo：
利用SQL-SERVER管理控制台：
创建一个名称为“StuInfo”的存储过程，要求完成以下功能：
查询06级学生的学号、姓名、班级名称，班级号和学院编号五个字段的内容）
四、查看存储过程
（一）使用SQLServer管理控制台查看用户创建的存储过程
在SQLServer管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。
（二）使用系统存储过程查看用户创建的存储过程
sp_help：
用于显示存储过程的参数及其数据类型
sp_help[[@objname=]name]
▪参数name为要查看的存储过程的名称。
sp_helptext：
用于显示存储过程的源代码
sp_helptext[[@objname=]name]
sp_depends：
用于显示和存储过程相关的数据库对象
sp_depends[@objname=]’object’
▪参数object为要查看依赖关系的存储过程的名称。
12.使用SQLServer管理控制台查看用户创建的所有存储过程。
13.使用系统存储过程sp_help查看Stu_Age存储过程的参数及其数据类型。
14.使用系统存储过程sp_depends查看StuScoreInfo存储过程的相关的数据库对象。
五、重命名存储过程
（一）．使用SQLServer管理控制台修改存储过程名称
▪通过SQLServer管理控制台可以修改存储过程的名称。
方法是：
在SQLServer管理控制台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。
（二）．使用系统存储过程修改存储过程名称
▪修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：
sp_rename原存储过程名称，新存储过程名称
15.使用系统存储过程sp_rename将StuInfo存储过程的名称修改为Stu_Info_SCORE。
六、删除存储过程
（一）．使用SQLServer管理控制台删除存储过程
在SQLServer管理控制台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，会弹出“删除对象”对话框。
在该对话框中，单击“确定”按钮，即可完成删除操作。
（二）．使用Transact-SQL语句删除存储过程
删除存储过程也可以使用Transact-SQL语言中的DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：
DROP{PROC|PROCEDURE}{[schema_name.]procedure}[,...n]
16.使用DROP命令删除Stu_class_lastname和StuScoreInfo两个存储过程。
将以上任务的实验完成情况、实验结果、实验原理、总结分栏一一填写到下表中，格式参考任务1或者自定。
任务1：
完成情况（代码及运行结果评析）：
代码：
运行结果评析：
小结：
任务2：
完成情况（代码及结果）：
--2.利用Transact-创建一个存储过程StuScoreInfo，完成的功能是查询艾宏迪同学的班级、学号、
--姓名、课程名称、考试分数。
createprocedureStuScoreInfo
as
select班级名称,学生.学号,姓名,课程名,成绩
from班级,学生,课程,成绩
where姓名='艾宏迪'and学生.班级号=班级.班级号and课程.课程号=成绩.课程号and
学生.学号=成绩.学号
总结：
（实验结果及原理的分析）
任务3：
完成情况（代码及结果）：
--3.一个有参变量（输入）的存储过程的建立：
利用Transact-SQL创建一个存储过程
--StuScoreInfo2，完成的功能是传入学生的姓名，如分别传入艾宏迪，安然，白冰等
--同学的姓名，查看该同学的班级、学号、姓名、课程名称、考试分数。
createprocedureStuScoreInfo2
@SNAMENVARCHAR（5）
as
select班级名称,学生.学号,姓名,课程名,成绩
from班级,学生,课程,成绩
where姓名=@SNAMEand学生.班级号=班级.班级号and课程.课程号=成绩.课程号and学生.学号=成绩.学号
declare@SNAMENVARCHAR（5）
set@SNAME='安然'
execStuScoreInfo2@SNAME
总结：
（实验结果及原理的分析）
任务4：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务5：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务6：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务7：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务8：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务9：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务10：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务11：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务12：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务13：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务14：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务15：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务16：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
任务17：
完成情况（代码及结果）：
总结：
（实验结果及原理的分析）
回答问题：
▪使用存储过程的主要优点有哪些？
▪存储过程分哪些类，各有什么特点？
▪存储过程和视图的区别有哪些？
小结：
（不少于100字）
你在完成查询任务的过程中遇到的问题及解决的方法有：
要求：
1.报告格式和内容要求：
a.内容和格式整齐。
大标题采用黑体四号字加粗，小标题采用小四号字加粗。
正文采用五号宋体，单倍行距。
b.贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。
c.不要在报告中写与实验无关的话，内容要有条理、完整、并能突出重点，要将遇到的主要问题说明。
2.文件格式要求：
a.将实验成果放入一个文件夹中，文件夹的内容包括：
本实验报告、分离后的数据库、如有查询代码（.sql文件）和其它文件也一并放入。
b.文件夹以“学号姓名班级_S10”为文件名。
注意：
你的学号放在姓名前。
c.注意：
所有文件保存后关闭，然后打包成RAR文件，以免提交的内容丢失或打不开。
3.提交方式和时间：
一周内完成。
可以将文件包发到我邮箱sunliyan_99@。
4.主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。
升级会员 