第十一讲存储过程.pptVIP

课前回顾 1、索引分哪两种两种索引各有什么特点？ 2、查询选修了课程的学生姓名和课程名 3、说说自己对函数的理解。 第十一章 11.1 存储过程的定义 定义：存储过程是一系列预先编辑好的、能实现特定数据操作功能的SQL代码集，它与特定的数据库相关联，存储在SQL Server服务器上。 用户可以像使用函数一样重复调用这些存储过程，实现它所定义的操作。 存储过程分类 系统存储过程 系统存储过程是指安装SQL Server时由系统创建的存储过程。存储在master数据库中，其前缀为sp_。 扩展存储过程 扩展存储过程是对动态链接库（DLL）函数的调用。其前缀为xp_。 用户定义的存储过程 由用户为完成某一特定功能而编写的存储过程。 11.2 存储过程的优点 存储过程具有以下优点： 存储过程提供了处理复杂任务的能力 增强代码的重用性和共享性 减少网络数据流量 加快系统运行速度 加强系统安全性 11.3 存储过程的创建 1 使用T-SQL语句创建 2 创建带输入参数的存储过程 创建带输出参数的存储过程 使用SSMS创建 11.3.1 使用T-SQL语句创建存储过程 1.创建存储过程的SQL语句语法 CREATE PROC [EDURE] [OWNER.] procedure_name [({@parameter data_type} [VARYING] [=default] [OUTPUT])][ , . . . n ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION} ] AS sql_statement [...n] ● @parameter：存储过程的输入或输出参数。 ● default： 参数缺省值。 ● WITH RECOMPILE：重编译选项。 ● sql_statements：存储过程中实现功能的SQL语句。 一般来说，创建一个存储过程应按 照以下步骤进行： ① 编写SQL语句。 ② 测试SQL语句是否正确，并能实现功能要求。 ③ 若得到的结果数据符合预期要求，则按照存储过程的语法，创建该建存储过程。 ④ 执行该存储过程，验证其正确性。 【例11-1】创建一存储过程，名称为proc1要求该存储过程返回学生姓名及选修的课程名 CREATE PROCEDURE proc1 AS SELECT 姓名，课程名 FROM student ，xskc，course WHERE student.学号= xskc.学号 andCourse.课程号= xskc.课程号 创建存储过程时需要注意如下几点： ① 每个存储过程应该完成一项单独的工作。 ② 为防止别的用户看到自己所编写的存储过程的脚本，创建存储过程时可以使用参数WITH ENCRYPTION。 ③ 一般存储过程都是在服务器上创建和测试，在客户机上使用时，还应该进行测试。 11.3.2 创建带输入参数的存储过程 输入参数在创建存储过程语句中被定义，其参数值在执行该存储过程时由调用该存储过程的语句给出。 具体语法如下: @parameter_name dataype[=default] 其中： ● @parameter_name：存储过程的输入参数名，必须以@符号为前缀。 ● default：如果执行存储过程时未提供该参数值，则使用DEFAULT值。 CREATE PROCEDURE proc2 @name nvarchar(4) AS SELECT 姓名，课程名 FROM student ，xskc，course WHERE student.学号= xskc.学号 andCourse.课程号= xskc.课程号 and 姓名=@name 查询学生林虹所学的课程： EXEC proc2 ‘林虹 注意：如果用户不给出传递给该存储过程所需参数中的任何一个，将会产生错误。解决这种问题的一种方法是建立使用默认值的参数。 方法：在参数的定义之后加上等号，并在等号后面写出默认值。 将: @studentname varchar(8) 替换为： @studentname varchar(8)=‘林国海 create proc d @id char(6),@name nvarchar(4), @sex char(1),@age int,@sdept nvarchar(8) as if @id is null or @name is null or @sdept is null begi