实验八 数据库的创建和使用过程文档格式.docx

1、了解存储过程的概念和作用掌握存储过程的创建、执行以及查看、修改和删除 实验内容存储过程的创建与执行存储过程的查看、修改与删除实现步骤存储过程是独立与数据表之外的数据库对象，是SQL SERVER服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它们可以接受参数、输出参数、返回单个或多个结果、返回状态值和参数值。存储过程独立于源代码，可单独修改，可以被调用任意次，可以引用其他存储过程。（T-SQL是Microsoft公司在关系型数据库管理系统SQL Server中的SQL-3标准的实现,是微软对SQL的扩展,具有SQL的主要特点,同时增加了变量,运算符,函数,流程控制和注释等语

2、言元素,使得其功能更加强大.） 存储过程使数据管理变得更容易。通常在设计数据库的同时，设计其相应的功能。比如，在学生选课数据库中，需要完成如下工作：1、根据学号，检索某位学生的各科成绩和平均成绩。2、根据课程名，检索选修某门课程的学生姓名和学生总人数。3、但在学生表中修改某个同学的学号时，要检索这个同学的选课表信息，并在选课表中调整相应的学号。4、判断在选课表中加入的新记录是否在学生表中存在。我们可以将上述功能以存储过程的形式存储在数据库中，以供日后调用。SQL Server中存储过程分为两类：系统存储过程和用户自定义的存储过程。系统存储过程主要存储在master数据库中，并一般以sp_为前缀

3、，它们从系统表中获取信息，以完成许多管理性或信息性的活动。系统存储过程可以在其他数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。在创建新数据库时，一些系统存储过程会在新数据库中被自动创建。用户定义的存储过程是由用户创建的，用来完成特定设计功能。存储过程的主要优点：1、模块化编程。创建一次存储过程，它就能永久存储在数据库中，可以在程序中重复调用任意多次。所有的客户端程序可以使用同一个存储过程实现对数据库的操作，从而确保数据访问和操作的一致性，也提高了应用程序的可维护性。2、快速执行。当某操作要求大量的Transact-SQL代码或者要重复执行时，存储过程要比Transact-SQL

4、批处理代码快得多。当创建存储过程时，SQL Server对它进行分析和优化，在第一次执行后，它就驻留在内存中，省去了重新分析、重新优化和重新编译的工作，提高了执行效率。3、减少网络通信量。存储过程可以由几百条Transact-SQL语句组成，但存储过程存放在服务器端，因此客户端要执行存储过程，只需要传送一条执行存储过程的命令，从而减少了网络流量和网络传输时间。4、提供安全机制。可以授予用户执行存储过程的权限，那么即使该用户没有访问在存储过程中引用的表或视图的权限，该用户也完全可以执行存储过程。1 使用创建存储过程模板创建存储过程（1）打开SQL Server Management Studio

5、,展开节点“对象资源管理器”|“数据定义”|“可编程性”|“存储过程”，在窗口的右侧显示出当前数据库的所有存储过程。单击鼠标右键，在弹出的快捷菜单中选择“新建存储过程”命令，如图8-1所示。图8-1 （2）在打开的SQL命令窗口中，系统给出了创建存储过程命令的模板，如图8-2所示。图8-2在模板中可以输入创建存储过程的T-SQL语句，按照下面的代码建立存储过程的命令模板后，单击“执行”按钮,即可创建存储过程，如图8-3所示。 图8-3（3）在新建的getstudent存储过程可以通过以下代码来执行，执行结果如图8-4所示。图8-42. 使用SQL命令窗口创建存储过程 （1）打开SQL Serv

6、er Management Studio，选择“对象资源管理器”中的“学生数据库”，然后用鼠标单击“新建查询”按钮，打开SQL命令窗口，在可编辑区输入如下代码，单击“执行”按钮即可创建存储过程，如图8-5所示。图8-5 （2）新建立的getstudent_all存储过程可以通过以下代码执行，执行结果同使用创建存储过程模板创建的存储过程。创建一个不带参数的存储过程，从Course中选择所有“Grade90”的记录，输入如下语句，单击“执行”，结果如图8-6所示。图8-6创建一个带参数的存储过程，从Course中选择Grade介于65-95之间的记录，输入如下语句，单击“执行”，结果如图8-7所示

7、。图8-7创建与执行带输入、输出参数的存储过程，实现显示“Course”中给定学号的信息，并输出“Course”中该学号的学生所选课程中的最高分数和最低分数，具体步骤如下：1）单击Microsoft SQL Server Management Studio，选择“对象资源管理器”中的“数据定义”。 2）单击常用工具栏中的“新建查询”按钮，新建一个数据库引擎查询文档。 3）在数据库引擎文档中输入如下代码，单击“执行”按钮，即可创建名称为getstudent_3的存储过程，如图8-8所示。图8-8 （3）存储过程创建成功后，输入如下T-SQL语句，单击“执行”按钮，执行已经创建的存储过程getst

8、udent_3，执行结果如图8-9所示。图8-93. 查看存储过程（1）使用Mircrosoft SQL Server 2005 Management Studio查看用户创建存储过程。 在使用SQL Server Management Studio中，展开指定的服务器和数据库，选择展开“可编程性”“存储过程”节点，然后在要查看的存储过程名称上单击鼠标右键。从弹出的快捷菜单中选择“编写存储过程脚本为”“CREATE到”“新查询编辑器”窗口命令，即可看到存储过程的源代码。（2）使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及语法格式如下：sp_help，用于显示存储过程的参数机

9、器数据类型，其语法为： sp_helpobjname=name,参数name为要查看的存储过程的名称。sp_helptext，用于显示存储过程的代码，其语法为： sp_helptextobjname=name,参数name为要查看的存储过程的名称。sp_depends，用于显示和存储过程相关的数据库对象，其语法为： sp_dependsobjname=object,参数object为要查看依赖关系的存储过程的名称。sp_stored_procedurse,用于返回当前数据库中的存储过程列表，其语法为： sp_stored_procedursesp_name=name ,sp_owner=own

10、er, ,sp_qualifier=qualifier其中sp_name=name用于制定返回目录信息的过程名；sp_owner=owner用于指定存储过程的所有者的名称；sp_qualifier=qualifier用于指定过程限定符的名称。4. 修改存储过程 存储过程可以根据用户的要求或基表定义的改变而改变。可以使用ALTER PROCEDURE语句修改已经存在的存储过程。修改存储过程与删除和重建存储过程不同，其特点是保持存储过程的权限不发生变化，其主要语法格式如下： ALTER PROCEDURE procedure_name;number parameter data_type VARY

11、ING=defualt OUTPUT,n WITH PECOMPILEENCRYPTIONRECOMPILE,ENCRYPTION FOR REPLICATION AS Sql_statementn 当使用ALTER PROCEDURE语句时，如果在ALTER PROCEDURE语句中使用或参数，那么在ALTER PROCEDURE语句中也应该使用这些参数。每次只能修改一个存储过程。存储过程的创建者、db_owner和db_ddladmin的成员拥有执行ALTER PROCEDURE语句的许可，其他用户不能呢个使用。需要注意的是，用ALTER PROCEDURE更改权限和启动属性保持不变。 另

12、外使用SQL Server Management Studio也可以很方便地修改存储过程的定义，早SQL Server Management Studio中，展开指定的服务器和数据库，选择并展开“可编程性”“存储过程”节点，选择要修改的存储过程，单击鼠标右键，在弹出的快捷菜单中选择“修改”命令，打开修改存储过程的窗口，在该窗口中可以直接修改定义该存储过程的T-SQL语句，然后单击“执行”按钮，执行该存储过程的修改。5. 重命名和删除存储过程（1）重命名存储过程 修改存储过程的名称可以使用系统存储过程sp_rename，其语法格式如下： sp_renameobjname=object_name,

13、object_type 另外，通过SQL Server Management Studio也可以修改存储过程的名称，在SQL Server Management Studio中，在要操作的存储过程名称上单击鼠标右键，从弹出的快捷菜单中选择“重命名”命令，当存储过程名称变成可输入状态时，直接修改该存储过程的名称即可。（2）删除存储过程 删除存储过程可以使用T-SQL语句中的DROP命令，DROP命令可以将一个或多个存储过程从当前数据库中删除，其语法过程如下： DROP PROCEDURE procedure_namen 另外，通过SQL Server Management Studio也可以很方

14、便地删除存储过程。在SQL Server Management Studio中，在要删除的存储过程上单击鼠标右键，从弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框，选中该存储过程，然后删除即可。例子:使用SQL命令窗口创建存储过程:为sc表创建查询学号，课程号，成绩的存储过程，不带参数：为course表创建查询课程号，课程名的存储过程，其中课程号作为参数：执行存储过程（1）使用对象资源管理器：在建立的存储过程名上点击右键选择执行，执行后会自动生成代码，代码和结果如下：（2）使用T-SQL语句执行存储过程：带参数和不带参数的修改存储过程在建立的存储过程名上点击右键选择修改，修改代码。（

15、2）使用T-SQL语句：将pro_sc存储过程增加sno升序排列删除存储过程在建立的存储过程名上点击右键选择删除，在出现的窗口点击确定完成删除操作。（2）使用T-SQL语句习题在数据库Company_Data中创建存储过程Procedure_SalaryByDept，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。（1）打开查询编辑器。（2）在查询窗口书写 CREATE PROCEDURE Transact-SQL语句创建存储过程Procedure_SalaryByDept，并带有一个输入参数Department用于传递部门名称，一个输出参数TotalS

16、alary用于传递输出结果。CREATE PROCEDURE Procedure_SalaryByDept Department varchar（40）,TotalSalary money OUTPUTAS（3）选择所属部门为该参数传递的部门名称的所有记录，并对其工资字段求和。 SELECT Tota1Sa1ary=sum（工资）FROM员工数据表WHERE所属部门=DepartmentGO（4）报告该部门的工资总额。PRINT该部门的工资总额+CONVERT（varchar,TotalSalary）（5）执行这个创建存储过程的语句。（6）执行系统存储过程sp_help查看该存储过程的一般信息。（7）执行该存储过程，分别计算项目部、录入部和检验部的工资总额。（8）用ALTTER PROCEDURE语句将存储过程加密。体会： 这次实验主要是存储过程的创建与执行和存储过程的查看、修改与删除，这对以后数据库的应用有很大的帮助，并且通过实验我还深刻理解了存储过程是独立与数据表之外的数据库对象，是SQL SERVER服务器上一组预编译的Transact-SQL语句，用于完成某项任务，它们可以接受参数、输出参数、返回单个或多个结果、返回状态值和参数值。教 师评 语