《SQL 第11章 存储过程》由会员分享，可在线阅读，更多相关《SQL 第11章 存储过程（37页珍藏版）》请在金锄头文库上搜索。
1、第11章 存储过程的创建和使用,11.1 存储过程的定义11.2 存储过程的优点11.3 存储过程的创建11.4 重新编译存储过程11.5 自动执行存储过程11.6 查看、修改和删除存储过程11.7 扩展存储过程,返回目录,2,11.1 存储过程的定义,存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。存储过程分为三类：系统提供的存储过程、用户定义的存储过程和扩展存储过程。系统存储过程 系统存储过程是指安装SQL Server时由系统创建的存储过程。存储
2、在master数据库中，其前缀为sp_。系统存储过程主要用于从系统表中获取信息，也为系统管理员和有权限的用户提供更新系统表的途径。它们中的大部分可以在用户数据库中使用。扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。它允许用户使用DLL访问SQL Server，用户可以使用编程语言（诸如C或C+等）创建自己的扩展过程。用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。,本章首页,3,11.2 存储过程的优点,存储过程是一种把重复的任务操作封装起来的一种方法，支持用户提供参数，可以返回、修改值，允许多个用户使用相同的代码，完成相同的数据操作。它提供了一种
3、集中且一致的实现数据完整性逻辑的方法。存储过程用于实现频繁使用的查询、业务规则、被其它过程使用的公共例行程序。存储过程具有以下优点：存储过程提供了处理复杂任务的能力 存储过程提供了许多标准SQL语言所没有的高级特性，它通过传递参数和执行逻辑表达式，能够使用十分复杂的SQL语句处理复杂任务。增强代码的重用性和共享性 每一个存储过程都是为了实现一个特定的功能而编写的模块，模块可以在系统中重复地调用，也可以被多个有访问权限的用户访问。所以，存储过程可以增强代码的重用性和共享性，加快应用系统的开发速度，减少工作量，提高开发的质量和效率。,4,减少网络数据流量 存储过程是与数据库一起存放在服务器中并在服
4、务器上运行的。应用系统调用存储过程时只有触发执行存储过程的命令和执行结束返回的结果在网络中传输。用户端不需要将数据库中的数据通过网络传输到本地进行计算，再将计算结果通过网络传送到服务器。所以，使用存储过程可以减少网络中数据流量。加快系统运行速度 第一次执行后的存储过程会在缓冲区中创建查询树，第二次执行时就不用进行预编译，从而加快了系统运行速度。另外，由于存储过程是在服务器上运行，分担了用户端的数据处理工作，也加快了应用系统的处理速度。加强系统安全性 SQL Server可以不授予用户某些表、视图的访问权限，但授予用户执行存储过程的权限，通过存储过程来对这些表或视图进行访问操作。这样，既可以保证
5、用户能够通过存储过程操作数据库中的数据，又可以保证用户不能直接访问与存储过程相关的表，从而保证表中数据的安全性。,本章首页,5,11.3 存储过程的创建,11.3.1 使用T-SQL语句创建存储过程11.3.2 创建带输入参数的存储过程11.3.3 创建带输出参数的存储过程,本章首页,6,11.3.1 使用T-SQL语句创建存储过程,1.创建存储过程的SQL语句语法创建一个存储过程的语法如下：CREATE PROC EDURE OWNER. procedure_name(parameter data_type VARYING =default OUTPUT) , . . . n WITH RE
6、COMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASsql_statement .n只有两个必需的参数必须传递给CREATE PROCEDURE语句：创建存储过程所需的procedure_name和sql_statements。,7,在以上语句的语法中： procedure_name：为新创建的存储过程所指定的名字，它必须遵循标准SQL Server命名约定，且必须在同一个数据库中是唯一的。 parameter：存储过程的输入或输出参数。 default：参数缺省值。 WITH RECOMPILE：重编译选项。 sql_statements：存储过程
7、中实现功能的SQL语句。2.创建步骤一般来说，创建一个存储过程应按照以下步骤进行： 编写SQL语句。 测试SQL语句是否正确，并能实现功能要求。 若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。 执行该存储过程，验证其正确性。,8,3. 存储过程创建示例【例11-1】创建一存储过程，要求该存储过程返回学生姓名、所学课程名称和任课教师。CREATE PROCEDURE spStuCouTea_nameAS SELECT a.student_name,b.course_name,c.teacher_nameFROM student a INNER JOIN student_c
8、ourse d ON a.student_id = d.student_id INNER JOIN course b ON d.course_id = b.course_id INNER JOIN teacher_course_class e ON (e.class_id = a.class_id) and (e.course_id = d.course_id) INNER JOIN teacher cON c.teacher_id = e.teacher_id执行以上脚本，便可创建存储过程spStuCouTea_name。如要执行该存储过程，可在查询分析器中执行如下语句：EXEC spStu
9、CouTea_name,9,4.创建存储过程的注意事项在创建存储过程的时候，需要注意如下几点： 每个存储过程应该完成一项单独的工作。 为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITH ENCRYPTION。 一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。,本节首页,10,11.3.2 创建带输入参数的存储过程,输入参数是指由调用程序向存储过程传递的参数。它们在创建存储过程语句中被定义，其参数值在执行该存储过程时由调用该存储过程的语句给出。具体语法如下: parameter_name dataype=default其中： paramete
10、r_name：存储过程的输入参数名，必须以符号为前缀。执行该存储过程时，应该向输入参数提供相应的值。 datatype：该参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。 default：如果执行存储过程时未提供该参数值，则使用DEFAULT值。,11,【例11-2】继续上一节的例子。建立一个存储过程，选择某一个指定学生的学生姓名、所学课程及任课教师姓名。CREATE PROCEDURE spStuCouTea_withParamstudentname varchar(8)AS SELECT a.student_name,b.course_name,c.teache
11、r_nameFROM student a INNER JOIN student_course d ON a.student_id = d.student_id INNER JOIN course b ON d.course_id = b.course_id INNER JOIN teacher_course_class e ON (e.class_id = a.class_id) and (e.course_id = d.course_id) INNER JOIN teacher c ON c.teacher_id = e.teacher_idWHERE student_name = stud
12、entname,12,用户可以运行下面的语句来执行它，以查询学生张虹所学的课程及这些课程的任课教师姓名： EXEC spStuCouTea_withParam 张虹这种类型的存储过程存在的一个问题，如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。要做到这一点，用户必须在参数的定义之后加上等号，并在等号后面写出默认值。如将上例中第二行： studentname varchar(8)替换为： studentname varchar(8)=%，重新创建存储过程spStuCouTea_withParam，如果执行该存储过程时不提供任何参数
13、，则执行返回的结果集将是空集，而不会产生错误。,13,【例11-3】为JWGL数据库建立一个存储过程，通过执行存储过程将学生信息添加到student表。CREATE PROCEDURE spAddStudent id char(8)= NULL, name nvarchar(8)= NULL, sex char(2)= NULL, birthday smalldatetime= NULL, class char(6)= NULL, InDate smalldatetime= NULL, home nvarchar(40)= NULL AS IF id IS NULL OR name IS NU
14、LL OR sex IS NULL OR birthday IS NULL OR class IS NULL OR InDate IS NULL,14,BEGIN PRINT 请重新输入该学生信息！ PRINT 你必须提供学生的学号、姓名、性别、出生日期、班级号及入学日期。 PRINT （家庭地址可以为空） RETURN ENDDECLARE bitSex bitIF sex=男 SET bitSex = 1ELSE SET bitSex = 0,15,BEGIN TRANSACTION INSERT student (student_id,student_name,sex,birth,cla
15、ss_id,entrance_date,home_addr) VALUES (id, name, bitSex, birthday, class, InDate, home) IF error 0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSACTIONPRINT 学生+name+的信息成功添加到表student中。,本节首页,16,11.3.3 创建带输出参数的存储过程,具体语法如下： parameter_name dataype=default OUTPUT其中： parameter_name：存储过程的输出参数名，必须以符号为前缀。 datatype：输出参数的数据类型说明，它可以是系统提供的数据类型，也可以是用户定义的数据类型。 OUTPUT：指明该参数是一个输出参数。这是一个保留字，输出参数必须位于所有输入参数之后。返回值是当存储过程执行完成时参数的当前值。为了保存这个返回值，在调用该过程时SQL调用脚本必须使用OUTPUT关键字。,
