[工学]实验九 存储过程.doc

存储过程（Stored Procedure） 是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中。是 SQL 语句和可选控制流程语句的预编译集合，作为一个单元被DBMS处理。 存储过程以一个名称存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量（指定参数)）有条件地执行。也可被应用程序嵌套调用。 存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 优点： 1、增强了SQL语言的功能和灵活性。可以在单个存储过程中执行一系列 SQL 语句； 可以从自己的存储过程内引用其它存储过程。故即可以简化一系列复杂语句，同时完成复杂的判断和较复杂的运算。 2、保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接 地存取数据库，从而保证数据的安全；可以使相关的动作在一起发生，从而可以维护数据库的完整性。 3、存储过程在创建时即在服务器上进行编译，所以执行起来比单个 SQL 语句快。 SQL Server 2005提供了三种存储过程 1、系统存储过程： 由系统提供，以‘sp_’为前缀。 可以作为命令执行各种操作。用来进行系统的各项设定，取得系统及用户信息，执行系统管理工作。 系统存储过程定义在系统数据库master中。 　　 如 sp_help就是取得指定对象的相关信息 。 　 2、扩展存储过程： 在SQL Server环境之外执行的动态链接库称为扩展存储过程，其前缀是‘sp_’（或’xp_ ‘,用来调用操作系统提供的功能 ）。 使用时需要先加载到SQL Server系统中，并且按照使用存储过程的方法调用执行。 3、用户自定义的存储过程： 也称本地存储过程，是指在用户数据库中创建的存储过程，这种存储过程完成用户指定的数据库操作，其名称不能以‘sp_’为前缀。 如果本地存储过程的名称前面有一个“#”，该存储过程就称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。 用户存储过程的创建与执行 语法格式： CREATE PROC [ EDURE ] procedure_name [ ; number ] /* 定义存储过程名*/ [ { @parameter data_type } /*定义参数的类型*/ [ VARYING ] [ = default ] [ OUTPUT ]?] /*定义参数的属性*/ [ ,...n ] [ WITH??{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }] /*定义存储过程的处理方式*/ [ FOR REPLICATION ] AS sql_statements /*执行的SQL操作*/ 参数： 每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。 OUTPUT：用来指定该参数是既有输入又有输出值的。在调用这个创建的存储过程时，如果所指定的参数值是用户需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT；而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。 例如：declare @var int 　　 execute proc_kk ;1, @var output 　　 select @tot_amt RECOMPILE：表示每次执行此存储过程时都重新编译一次 ENCRYPTION：所创建的存储过程的内容会被加密 FOR REPLICATION：为复制创建，不可在代理服务器上执行 【例1】利用school数据库中的school表、score表和course表，编写一个无参数存储过程，用于查询所有学生的选修课程成绩记录。 USE school GO CREATE PROCEDURE a AS SELECT student.sno,sname,course.cno,cname,degree FROM student,score,course WHERE student.sno=score.sno AND score.cno=course.cno 执行：EXEC a