数据库系统概论实验报告八

山西大学计算机与信息技术学院实验报告姓名张浩田学号2014241027专业班级计算机科学与技术课程名称数据库系统概论实验日期2016/12/26成绩指导教师王俊红批改日期实验名称一、实验目的：•了解存储过程的概念和作用•掌握存储过程的创建、执行以及查看、修改和删除二、实验原理：Transact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。三．实验设备安装SQLSERVER2005的PC一台。四、实验步骤：•存储过程的创建与执行•存储过程的查看、修改与删除1．使用创建存储过程模板创建存储过程（1）打开SQLServerManagementStudio,展开节点“对象资源管理器”|“数据定义”|“可编程性”|“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令（2）在打开的SQL命令窗口中，系统给出了创建存储过程命令的模板在模板中可以输入创建存储过程的T-SQL语句，按照下面的代码建立存储过程的命令模板后，单击“执行”按钮,即可创建存储过程，（3）在新建的getstudent存储过程可以通过以下代码来执行，2.使用SQL命令窗口创建存储过程（1）打开SQLServerManagementStudio，选择“对象资源管理器”中的“学生数据库”，然后用鼠标单击“新建查询”按钮，打开SQL命令窗口，在可编辑区输入如下代码，单击“执行”按钮即可创建存储过程，（2）新建立的getstudent_all存储过程可以通过以下代码执行，执行结果同使用创建存储过程模板创建的存储过程。创建一个不带参数的存储过程，从Course中选择所有“Grade90”的记录，输入如下语句，单击“执行”，创建一个带参数的存储过程，从Course中选择Grade介于65-95之间的记录，输入如下语句，单击“执行”创建与执行带输入、输出参数的存储过程，实现显示“Course”中给定学号的信息，并输出“Course”中该学号的学生所选课程中的最高分数和最低分数，具体步骤如下：1）单击MicrosoftSQLServerManagementStudio，选择“对象资源管理器”中的“数据定义”。2）单击常用工具栏中的“新建查询”按钮，新建一个数据库引擎查询文档。3）在数据库引擎文档中输入如下代码，单击“执行”按钮，即可创建名称为getstudent_3的存储过程（3）存储过程创建成功后，输入如下T-SQL语句，单击“执行”按钮，执行已经创建的存储过程getstudent_3，3.查看存储过程（1）使用MircrosoftSQLServer2005ManagementStudio查看用户创建存储过程。在使用SQLServerManagementStudio中，展开指定的服务器和数据库，选择展开“可编程性”│“存储过程”节点，然后在要查看的存储过程名称上单击鼠标右键。从弹出的快捷菜单中选择“编写存储过程脚本为”│“CREATE到”│“新查询编辑器”窗口命令，即可看到存储过程的源代码。（2）使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及语法格式如下：·sp_help，用于显示存储过程的参数机器数据类型，其语法为：sp_help[[@objname=]name],参数name为要查看的存储过程的名称。·sp_helptext，用于显示存储过程的代码，其语法为：sp_helptext[[@objname=]name],参数name为要查看的存储过程的名称。·sp_depends，用于显示和存储过程相关的数据库对象，其语法为：sp_depends[[@objname=]’object’,参数object为要查看依赖关系的存储过程的名称。·sp_stored_procedurse,用于返回当前数据库中的存储过程列表，其语法为：sp_stored_procedurse[[@sp_name=]’name’][,[@sp_owner=]’owner’,][,[@sp_qualifier=]’qualifier’]其中[@sp_name=]’name’用于制定返回目录信息的过程名；[@sp_owner=]’owner’用于指定存储过程的所有者的名称；[@sp_qualifier=]’qualifier’用于指定过程限定符的名称。4.修改存储过程存储过程可以根据用户的要求或基表定义的改变而改变。可以使用ALTERPROCEDURE语句修改已经存在的存储过程。修改存储过程与删除和重建存储过程不同，其特点是保持存储过程的权限不发生变化，其主要语法格式如下：ALTERPROCEDUREprocedure_name[;number][{parameterdata_type}[VARYING][=defualt][OUTPUT][,…n][WITH{PECOMPILE│ENCRYPTION│RECOMPILE,ENCRYPTION}][FORREPLICATIONASSql_statement[…n]当使用ALTERPROCEDURE语句时，如果在ALTERPROCEDURE语句中使用或参数，那么在ALTERPROCEDURE语句中也应该使用这些参数。每次只能修改一个存储过程。存储过程的创建者、db_owner和db_ddladmin的成员拥有执行ALTERPROCEDURE语句的许可，其他用户不能呢个使用。需要注意的是，用ALTERPROCEDURE更改权限和启动属性保持不变。另外使用SQLServerManagementStudio也可以很方便地修改存储过程的定义，早SQLServerManagementStudio中，展开指定的服务器和数据库，选择并展开“可编程性”│“存储过程”节点，选择要修改的存储过程，单击鼠标右键，在弹出的快捷菜单中选择“修改”命令，打开修改存储过程的窗口，在该窗口中可以直接修改定义该存储过程的T-SQL语句，然后单击“执行”按钮，执行该存储过程的修改。5.重命名和删除存储过程（1）重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法格式如下：sp_rename[@objname=]’object_name’,[‘object’_type]另外，通过SQLServerManagementStudio也可以修改存储过程的名称，在SQLServerManagementStudio中，在要操作的存储过程名称上单击鼠标右键，从弹出的快捷菜单中选择“重命名”命令，当存储过程名称变成可输入状态时，直接修改该存储过程的名称即可。（2）删除存储过程删除存储过程可以使用T-SQL语句中的DROP命令，DROP命令可以将一个或多个存储过程从当前数据库中删除，其语法过程如下：DROPPROCEDUREprocedure_name[…n]另外，通过SQLServerManagementStudio也可以很方便地删除存储过程。在SQLServerManagementStudio中，在要删除的存储过程上单击鼠标右键，从弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框，选中该存储过程，然后删除即可。使用SQL命令窗口创建存储过程:为sc表创建查询学号，课程号，成绩的存储过程，不带参数：为course表创建查询课程号，课程名的存储过程，其中课程号作为参数：执行存储过程（1）使用对象资源管理器：在建立的存储过程名上点击右键选择执行，执行后会自动生成代码，代码和结果如下：（2）使用T-SQL语句执行存储过程：带参数和不带参数的修改存储过程（1）使用对象资源管理器：在建立的存储过程名上点击右键选择修改，修改代码。（2）使用T-SQL语句：将pro_sc存储过程增加sno升序排列删除存储过程（1）使用对象资源管理器：在建立的存储过程名上点击右键选择删除，在出现的窗口点击确定完成删除操作。（2）使用T-SQL语句六．思考题在数据库Company_Data中创建存储过程Procedure_SalaryByDept，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。(1)打开查询编辑器。(2)在查询窗口书写CREATEPROCEDURETransact-SQL语句创建存储过程Procedure_SalaryByDept，并带有一个输入参数@Department用于传递部门名称，一个输出参数@TotalSalary用于传递输出结果。CREATEPROCEDUREProcedure_SalaryByDept@Departmentvarchar(40),@TotalSalarymoneyOUTPUTAS(3)选择所属部门为该参数传递的部门名称的所有记录，并对其工资字段求和。SELECT@Tota1Sa1ary=sum(工资)FROM员工数据表WHERE所属部门=@DepartmentGO(4)报告该部门的工资总额。PRINT'该部门的工资总额’+CONVERT(varchar,@TotalSalary)GO(5)执行这个创建存储过程的语句。(6)执行系统存储过程sp_help查看该存储过程的一般信息。(7)执行该存储过程，分别计算项目部、录入部和检验部的工资总额。(8)用ALTTERPROCEDURE语句将存储过程加密。教师评语