数据库原理实验报告S10-数据库存储过程.doc

数据库管理系统SQL Server实验报告实验10 数据库存储过程的建立实验日期和时间：实验室：班级：学号：姓名：实验环境：1. 硬件：2. 软件：实验原理：1. 理解存储过程的概念、优点和使用原则2. 掌握存储过程的创建、执行、查看、修改和删除方法一、存储过程的概念 SQL SERVER中的存储过程类似于编程语言中的过程或函数。-以下为创建一个简单的用户存储过程create proc p_test(a int,b int output)asset b=a+1-以下为调用declare a int,b intselect a=1,b=0exec p_test a,b outputselect a,b在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。存储过程有以下特点： 存储过程中可以包含一条或多条Transact-SQL语句。 存储过程可以接受输入参数并可以返回输出值。 在一个存储过程中可以调用另一个存储过程。 存储过程可以返回执行情况的状态代码给调用它的程序。存储过程的优点：见实验教材：P211 实现了模块化编程，一个存储过程可以被多个用户共享和重用。 通过通用编程结构和过程重用实现编程框架。 存储过程具有对数据库立即访问的功能。 使用存储过程可以通过本地存储、代码预编译和缓存技术实现高性能的数据操作，加快程序的运行速度。 使用存储过程可以减少网络流量。 使用存储过程通过隔离和加密的方法提高数据库的安全性。存储过程的分类：在SQL Server中的存储过程分为两类：即系统提供的存储过程、扩展存储过程和用户自定义的存储过程。 系统存储过程：由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。并且都带有sp_前缀。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。 用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。在SQL Server 2005中，用户自定义存储过程有两种类型：Transact-SQL存储过程和CLR存储过程。 Transact-SQL存储过程：是指保存的Transact-SQL语句集合。 CLR存储过程：是指对Microsoft .NET Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。二、存储过程的创建方法在SQL Server中，可以使用两种方法创建存储过程：1. 使用SQL Server管理控制平台创建存储过程。在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程”选项，此时出现创建存储过程窗口。在文本框中可以看到系统自动给出了创建存储过程的格式模板语句，可以根据模板格式进行修改来创建新的存储过程。或者在创建存储过程的窗口中单击“查询”菜单，选择“指定模板参数的值”，会弹出“指定模板参数的值”对话框，做后续的操作。2. 使用Transact-SQL 中的CREATE PROCEDURE创建存储过程。CREATE PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_type VARYING = default OUT | OUTPUT ,.n WITH ENCRYPTION AS ; .n ; := BEGIN statements END 其中，各参数的意义如下： schema_name：过程所属架构的名称。 procedure_name：新存储过程的名称。 parameter：过程中的参数。 type_schema_name. data_type：参数以及所属架构的数据类型。 VARYING：指定作为输出参数支持的结果集。仅适用于cursor参数。 default：参数的默认值。 OUTPUT：指示参数是输出参数。 ENCRYPTION：将CREATE PROCEDURE语句的原始文本加密。 ：要包含在过程中的一个或多个Transact-SQL语句。使用CREATE PROCEDURE命令创建存储过程，考虑下列几个事项： CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。 必须具有数据库的CREATE PROCEDURE权限。默认情况下，创建存储过程的许可权归数据库的所有者，数据库的所有者可以把许可权给其他用户。 只能在当前数据库中创建存储过程。 不要创建任何使用sp_作为前缀的存储过程。SQL_Server使用sp_前缀指定系统存储过程。当创建存储过程时，需要确定存储过程的三个组成部分：1. 所有的输入参数以及传给调用者的输出参数。2. 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。3. 返回给调用者的状态值，以指明调用是成功还是失败。实验主要任务：附加或导入学生成绩数据库，对数据库实施如下操作：一、创建存储过程（一）无参存储过程的创建： 形如：CREATE PROCEDURE 存储过程名AS SELECT 子句1. 利用SQL-SERVER管理控制台：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：查询06级学生的学号、姓名、班级名称，班级号和学院编号五个字段的内容2. 利用Transact-创建一个存储过程StuScoreInfo，完成的功能是查询艾宏迪同学的班级、学号、姓名、课程名称、考试分数。（二）有参存储过程的创建： 形如：CREATE PROCEDURE 存储过程名 变量名1 变量类型，变量名2 变量类型，变量名3 变量类型 OUTPUT-声明变量，一个是局部变量，两个是全局变量，是系统预定义的。如ERROR返回最后执行的SQL语句的错误代码，OUTPUT作为输出变量关键字。AS SELECT 子句3. 一个有参变量（输入）的存储过程的建立：利用Transact-SQL创建一个存储过程StuScoreInfo2，完成的功能是传入学生的姓名，如分别传入艾宏迪，安然，白冰等同学的姓名，查看该同学的班级、学号、姓名、课程名称、考试分数。4. 多个有参变量（输入）的存储过程的建立：利用Transact-SQL创建一个存储过程Stu_Class_Lastname，完成的功能是传入某班班级号，某种姓氏，查相应同学的班级名称、学号、姓名、课程名称、考试分数。(多个变量之间用逗号分隔)5. 带有返回（输出）参数的存储过程的建立。/*当需要从存储过程中返回一个或多个值时，可以在存储过程的语句中定义这些输出参数，此时需要在CREATE PROCEDRUE 语句中使用OUTPUT 关键字说明是输出参数*/ 5.1 创建一个带有输出参数的存储过Stu_Classnum该题目可参考实验教材P215中的问题11.17的存储过程，在2003学生名单中使得它能够根据用户给定的班级名称统计该班的学生人数，并将学生人数返回给用户5.2 创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的姓名，在2003学生名单表中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。(多个变量之间用逗号分隔)二、执行存储过程存储过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程，语法形式如下：EXECUTE return_status= procedure_name|procedure_name_var parameter=value|variableOUTPUT|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得到。三、修改存储过程（一）使用SQL Server管理控制台修改存储过程使用SQL Server管理控制台可以很方便地修改存储过程的定义。在SQL Server管理控制台中，展开存储过程，右击要修改的存储过程，从弹出的快捷菜单中选择“修改”选项，则会出现与创建存储过程时类似的窗口。在该窗口中，可以直接修改定义该存储过程的Transact-SQL语句。（二）使用Transact-SQL语句修改存储过程使用ALTER PROCEDURE语句可以更改存储过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTER PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_type VARYING = default OUT PUT ,.n WITH ENCRYPTIONAS sql_statement .n 修改存储过程时，应该注意以下几点： 如果原来的过程定义是使用WITH ENCRYPTION创建的，那么只有在ALTER PROCEDURE中也包含这个选项时，这个选项才有效。 每次只能修改一个存储过程。 用ALTER PROCEDURE更改的存储过程的权限保持不变。11. 修改前面创建的Stu_Info存储过程，使之完成以下功能：根据班级名称，查询该班的班级、学号、姓名、性别、考试课程名称和考试分数，并执行该存储过程。（原StuInfo：利用SQL-SERVER管理控制台：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：查询06级学生的学号、姓名、班级名称，班级号和学院编号五个字段的内容）四、查看存储过程（一）使用SQL Server管理控制台查看用户创建的存储过程在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，单击其中的“存储过程”，在右边的窗口中就会显示出当前数据库中的所有存储过程。（二）使用系统存储过程查看用户创建的存储过程sp_help：用于显示存储过程的参数及其数据类型sp_help objname= name 参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptext objname= namesp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object 参数object为要查看依赖关系的存储过程的名称。12. 使用SQL Server管理控制台查看用户创建的所有存储过程。13. 使用系统存储过程sp_help查看Stu_Age存储过程的参数及其数据类型。14. 使用系统存储过程sp_depends查看StuScoreInfo存储过程的相关的数据库对象。五、重命名存储过程（一）使用SQL Server管理控制台修改存储过程名称 通过SQL Server管理控制台可以修改存储过程的名称。方法是：在SQL Server管理控制台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。（二）使用系统存储过程修改存储过程名称 修改存储过程的名称也可以使用系统存储过程sp_rename，其语法形式如下：sp_rename 原存储过程名称，新存储过程名称15. 使用系统存储过程sp_rename将StuInfo存储过程的名称修改为Stu_Info_SCORE。六、删除存储过程（一）使用SQL Server管理控制台删除存储过程在SQL Server管理控制台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，会弹出“删除对象”对话框。在该对话框中，单击“确定”按钮，即可完成删除操作。（二）使用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，完成的功能是查询艾宏迪同学的班级、学号、-姓名、课程名称、考试分数。create procedure StuScoreInfoasselect 班级名称,学生.学号,姓名,课程名,成绩from 班级,学生,课程,成绩where 姓名=艾宏迪 and 学生.班级号=班级.班级号and 课程.课程号=成绩.课程号and 学生.学号=成绩.学号 总结：(实验结果及原理的分析)任务3： 完成情况(代码及结果)： -3.一个有参变量（输入）的存储过程的建立：利用Transact-SQL创建一个存储过程-StuScoreInfo2，完成的功能是传入学生的姓名，如分别传入艾宏迪，安然，白冰等-同学的姓名，查看该同学的班级、学号、姓名、课程名称、考试分数。create procedure StuScoreInfo2SNAME NVARCHAR(5)asselect 班级名称,学生.学号,姓名,课程名,成绩from 班级,学生,课程,成绩where 姓名=SNAME and 学生.班级号=班级.班级号and 课程.课程号=成绩.课程号and 学生.学号=成绩.学号 declare SNAME NVARCHAR(5)set SNAME=安然exec StuScoreInfo2 SNAME总结：(实验结果及原理的分析)任务4： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务5： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务6： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务7： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务8： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务9： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务10： 完成情况(代码及结果)： 总结：(实验结果及原理的分析)任务11