[工学]实验九存储过程

《[工学]实验九存储过程》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中。是SQL语句和可选控制流程语句的预编译集合，作为一个单元被DBMS处理。存储过程以一个名称存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量（指定参数)）有条件地执行。也可被应用程序嵌套调用。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。优点：1、增强了SQL语言的功能和灵活性。可以在单个存储过程中执行一系列SQL语句；可以从自己的存储过程内引用其它存储过程。故即可以简化一系列复杂语句，同时完成复杂的判断和较复杂的

2、运算。2、保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全；可以使相关的动作在一起发生，从而可以维护数据库的完整性。3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。SQLServer2005提供了三种存储过程1、系统存储过程：由系统提供，以‘sp_’为前缀。可以作为命令执行各种操作。用来进行系统的各项设定，取得系统及用户信息，执行系统管理工作。系统存储过程定义在系统数据库master中。　　如sp_help就是取得指定对象的相关信息。　2、扩展存储过程：在SQLServer环境之外执行的动态链接库称为扩展存储过程

3、，其前缀是‘sp_’（或’xp_‘,用来调用操作系统提供的功能）。使用时需要先加载到SQLServer系统中，并且按照使用存储过程的方法调用执行。3、用户自定义的存储过程：也称本地存储过程，是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以‘sp_’为前缀。如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。用户存储过程的创建与执行语法格式：CREATEPROC[EDURE]procedure_name[;number]/*定义存储过程名*/[{@parameterdata_type}/*定义参数的类

4、型*/[VARYING][=default][OUTPUT] ]/*定义参数的属性*/[,...n][WITH  {RECOMPILE

5、ENCRYPTION

6、RECOMPILE,ENCRYPTION}]/*定义存储过程的处理方式*/[FORREPLICATION]ASsql_statements/*执行的SQL操作*/v参数：每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQLServer所支持的数据类型都可使用。vOUTPUT：用来指定该参数是既有输入又有输出值的。在调用这个创建的存储过程时，如果所指定的参数值是用户需要输入的参数，同

7、时也需要在结果中输出的，则该项必须为OUTPUT；而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。例如：declare@varint　　executeproc_kk;1,@varoutput　　select@tot_amtvRECOMPILE：表示每次执行此存储过程时都重新编译一次vENCRYPTION：所创建的存储过程的内容会被加密vFORREPLICATION：为复制创建，不可在代理服务器上执行【例1】利用school数据库中的school表、score表和course表，编写一个无参数存储过程，用于查询所有学生的选修课程成绩

8、记录。USEschoolGOCREATEPROCEDUREaASSELECTstudent.sno,sname,course.cno,cname,degreeFROMstudent,score,courseWHEREstudent.sno=score.snoANDscore.cno=course.cno执行：EXECaGO存储过程的参数传递v调用（执行）存储过程时，可由用户指定存储过程中定义的参数，用来传递输入和输出的参数值。v执行存储过程时，参数传递通常有两种方式：传递参数依次为存储过程中定义的参数列表的值。调用形式：EXECprocedure_name<实参列表>执行存储过程时，为已定义参

9、数指定值调用形式：EXECprocedure_name参数1=值1,参数2=值2,…【例1】设计一个存储过程maxno，以学号为参数，输出指定学号学生的所有课程中最高分和对应的课程名。USEschoolGOCREATEPROCEDUREmaxno(@noint)ASSELECTs.sno,s.sname,c.cname,sc,degreeFROMstudents,coursec,scorescWH