数据库原理实验4存储过程

多验报书课程名称 数据库原理 实验序号 4 实验项目 存储过程 实验地点 实验学时实验类型 验证性指导教师实验员专业—班级学号—姓名年月日成绩：A该学生这一次的实验报告进步很大，实验步骤清晰，过程符合要求，实验结果正确，教分析和心得一目了然，完整地表达了通过实验所获取的知识。质量较高。师评语一、实验目的及要求1、 了解存储过程的概念；2、 掌握系统存储过程和用户自定义的存储过程。3、 掌握存储过程的创建、执行方法，并学会对存储过程进行查看、修改和删除。二、实验原理与内容1、 存储过程的概念存储过程是存储于数据库中的一组T-SQL语句。可将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute，即可自动完成命令。存储过程是一种数据库对象，可以包含程序流、逻辑控制以及对数据库的查询，可以接受参数、输出参数、返回单个或多个结果集以及状态集，并可重用或嵌套。2、 系统存储过程系统存储过程是由SQLServe系统创建的存储过程，用户可直接使用。系统存储过程存储在master数据库中，以“sp_”开头命名。系统存储过程主要用于系统管理、用户登录管理、权限设置、数据库对象管理、数据复制等操作3、 常用的系统存储过程p_help:报告有关数据库对象（sysobjects表中列出的任何对象）、用户定义数据类型或系统所提供的数据类型的信息。sp_addlogin：创建新的SQLServer登录，该登录允许用户使用SQLServer身份验证连接到SQLServer实例。Sp_password：为MicrosoftSQLServer登录名添加或更改密码。sp_cursor_list：报告当前为连接打开的服务器游标的属性。Sp_adduser：向当前数据库中添加新的用户。sp_addrole:在当前数据库中创建新的数据库角色。sp_addrolemember：为当前数据库中的数据库角色添加数据库用户、数据库角色、Windows登录或Windows组。sp_droplogin：删除SQLServer登录名。这样将阻止使用该登录名对SQLServer实例进行访问。sp_dropuser：从当前数据库中删除数据库用户。sp_droprole:从当前数据库中删除数据库角色。spdroprolemember：从当前数据库的SQLServer角色中删除安全帐户。sp_addtype:创建别名数据类型。4、 用户自定义存储过程数据库用户可根据某一特定功能的需要，在用户数据库中由用户创建的存储过程存储过程命名时不能以“sp_”开头。5、 存储过程的创建CREATEPROCEDUREprocedure_name[;number][(^parameterdata_type}[VARYING][=default][OUTPUT]][,•••][WITH(RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement语法说明procedure_name：新创建的存储过程名称，过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。;number：是可选的整数，用来对同名的过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。^parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值，或者该值设置为等于另一个参数)。使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。存储过程最多可有2100个参数。data_type:参数的数据类型。除table之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor数据类型只能用于OUTPUT参数。如果指定cursor数据类型，则还必须指定VARYING和OUTPUT关键字。对于可以是cursor数据类型的输出参数，没有最大数目的限制。VARYING:指定作为输出参数支持的结果集，仅适用于游标参数。default:参数的默认值。如果定义了默认值，则不必指定该参数的值即可执行过程。默认值必须是常量或NULL。OUTPUT:表明参数是返回参数，可将信息返回给调用过程。{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}：RECOMPILE表明SQLServer不会缓存该过程的被引用的对象，该过程将在运行时重新编译。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。AS：指定过程要执行的操作。sql_statement:过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。6、 存储过程的执行存储过程一般不会自动执行，用户可使用EXECUTE命令来直接执行存储过程。执行存储过程必须具有执行该存储过程的权限。如果存储过程是批处理中的第一个语句，EXECUTE可以省略。EXEC[UTE]][@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][，…][WITHRECOMPILE]语法说明只能在当前数据库中创建存储过程。@return_status=：是一个可选的整形变量，用来保存存储过程的返回状态。该变量在用于EXECUTE语句之前必须在批处理、存储过程或函数中声明。procedure_name：要调用的存储过程名称。;number：可选的整数，具体含义同CREATEPROCEDURE中的;number。@procedure_name_var：是局部定义变量名，代表存储过程名称。@parameter，value:是过程参数及其值。@variable:用来保存参数或返回参数的变量。OUTPUT:指定存储过程必须返回一个参数。DEFAULT：根据过程的定义，提供参数的默认值。WITHRECOMPILE:可强制重新编译存储过程代码，但消耗较多的系统资源。7、存储过程的查看和修改存储过程的有关信息以及创建存储的文本均被存储在SQLServer数据库中的系统表sysobjects和syscomments中，通过SELECT语句可直接查看存储过程的定义（1） 存储过程的查看electsysobjects.id,syscomments.textfromsysobjects,syscommentswheresysobjects.id=syscomments.idandsysobjects.type='P'andsysobjects,name='procedure_name'其中，procedure_name为要查看的存储过程的名称。（2） 存储过程的修改ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,•••][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement其中的参数和保留字的含义说明与CREATEPROCEDURE语句一致。（3）存储过程的删除DROPPROCEDURE{procedure_name}[…]语法说明：procedure_name：为要删除的存储过程或存储过程组的名称。存储过程分组后，将无法删除组内的单个存储过程。删除一个存储过程将会把同组的所有存储过程都删除。三、实验软硬件环境装有MicrosoftSQLServer2008ManagementStudio的电脑四、实验过程（实验步骤、记录、数据、分析）1、 使用系统存储过程查询某一内容；2、 创建自定义的存储过程，并执行存储过程，然后进行查看、修改和删除。五、测试/调试及实验结果分析1、系统存储过程（1）使用存储过程sp_help查看bank数据库的信息。其命令为usestudent_misgoexecsp_helpgo执行结果如下：

snosnamesnosnamessexsagsdna1sDI王建平男21dD12s(M李伟里19d033sffi黄河男1S4s(K长江里20dgBEGINselect*fromstudentswheresnoNOTIN(selectsnofromreportswherecnoIN(selectcnofromcourseswherecname=@kcms))END执行上述命令后，执行executep2'数据结构'命令，其结果如右图，显示没有参加数据结构课程的学生的信息。（3）创建带有一个输入参数和一个输出参数的存储过程P3，输入学生的学号，返回姓名。其命令CREATEPROCEDUREP5@xhchar(10),@xmchar(10)OUTPUTASBEGINselect@xm=snamefromstudentswheresno=@xhEND执行上述命令后，执行如下命令declare@xmvarchar(10)executep1'101@xmOUTPUTprint@xm'显示结果如右图（4）创建带有多个输入参数和多个输出参数的存储过程P6，输入学生的学号和课程号，返回姓名和成绩。其命令为CREATEPROCEDUREP6@xhchar(10),@kchchar(10),@xmchar(10)OUTPUT,@cjintOUTPUTASBEGINselect@xm=snamefromstudentswheresno=@xhselect@cj=gradefromreportswheresno=@xhandcno=@kchENDEND执行上述命令后，执行如下命令declare @xmvarchar(10),@cjintexecutep1'101201@xmOUTPUT,@cjOUTPUTprint@xmPrint@cj'由CREATEPROCEDUREP6@shGhan(10>2kuhch3ir(10>f@xnchartIDj0UTPmf3cjintOUTPinAS£jBKGIPJselect®xir—anarneframtuid=ntswhe-x-«=sna—@xhFlselect—aradefromxepoztswriezesno=@xiiandaiia=3JcaH(ETTDdeclaregimvarcftar(ID)r8clintexeeure■1D1201@xrnOLTT