实现存储过程详解

• 存储过程介绍
• 创建和管理存储过程

• 在存储过程中使用参数

• 处理错误信息
 
存储过程介绍
　　• 定义存储过程

　　• 存储过程的优点

　　• 存储过程分类
 
定义存储过程
　　• 存储过程
　　　　– 是存储在服务器上的 Transact-SQL 语句的命名集合
　　　　– 是封装重复性任务的方法
　　　　– 支持用户声明变量、条件执行以及其他强有力的编程特性
　　• SQL Server 中的存储过程与其他编程语言中的过程类似，它可以
　　　　– 包含执行数据库操作（包括调用其他过程）的编程语句
　　　　– 接受输入参数
　　　　– 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）
　　　　– 以输出参数的形式将多个值返回至调用过程或批处理
存储过程的优点
　　• 实现了模块化的程序设计。
　　　　存储过程一旦完成,即可在应用程序中反复调用
　　• 简化复杂的语句。存储过程内可以调用其它存储过程
　　• 提供了安全性机制。用户可以被赋予执行存储过程的权限
　　• 更快的执行速度。存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。
　　• 减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包
 
存储过程的分类
　　• 在SQL Server中的存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。
　　• 系统存储过程：由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。例如:sp_help(帮助) , sp_adduser(添加用户).
　　• 用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。
 
创建存储过程（续）
　　• 查看存储过程的信息
　　　　– 查看所有类型存储过程的额外信息
　　• 系统存储过程 sp_help、sp_helptext、 sp_depends
　　　　– 显示数据库中的存储过程以及拥有者名字的列表
　　• 系统存储过程 sp_stored_procedures
　　　　– 得到存储过程的信息
　　• 查询系统表 sysobjects、syscomments、 sysdepends
 
实现存储过程
　　• 存储过程介绍
　　• 创建和管理存储过程
　　• 在存储过程中使用参数 • 处理错误信息
 
创建和管理存储过程
　　• 创建存储过程
　　• 执行存储过程
　　• 修改和删除存储过程
 
创建存储过程
　　• 在SQL Server中，可以使用两种方法创建存储过程：
　　• 当创建存储过程时，需要确定存储过程的三个组成部分：
　　• 所有的输入参数以及传给调用者的输出参数。
　　• 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。
　　• 返回给调用者的状态值，以指明调用是成功还是失败。
 
使用SQL Server管理控制台创建存储过程
　　• 在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程…”选项。
　　• 例1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询”计算机” 学生的学号、姓名、性别、出生日期四个字段的内容。
 
 
建存储过程
　　• 可以使用CREATE PROCEDURE命令创建存储过程，考虑下列几个事项：
　　• CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。
　　• 必须具有数据库的CREATE PROCEDURE权限。
　　• 只能在当前数据库中创建存储过程。
　　• 不要创建任何使用sp_作为前缀的存储过程。
 
建存储过程
　　• CREATE PROCEDURE的语法形式如下：
　　• CREATE { PROC | PROCEDURE }
　　[schema_name.] procedure_name
　　• [ { @parameter [ type_schema_name. ] data_type }
　　• [ VARYING ] [ = default ] [ OUT |
　　OUTPUT ] ] [ ,...n ] [ WITH ENCRYPTION ]
　　• AS { <sql_statement> [;][ ...n ] }[;]
　　• <sql_statement> ::= { [ BEGIN ] statements [ END ] }
建存储过程
　　• 其中，各参数的意义如下：
　　• schema_name：过程所属架构的名称。
　　• procedure_name：新存储过程的名称。
　　• @ parameter：过程中的参数。
　　• [ type_schema_name. ] data_type：参数以及所属架构的数据类型。
　　• VARYING：指定作为输出参数支持的结果集。仅适用于cursor参数。
 
建存储过程
　　• 例2：创建一个存储过程StuScoreInfo，完成的功能是在表 STUDENT、表COURSE和表SC中查询以下字段：学号、姓名、性别、课程名称、考试分数。
　　• create proc StuScoreInfo
　　• as
　　• SELECT student.sno,sname,ssex,cname,grade from student,course,SC
　　• WHERE student.SNO=SC.SNO AND SC.CNO=course.CNO
　　• Exec StuScoreInfo
 
建存储过程
　　例3：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生学号，在STUDENT中查询此学生的信息。
　　• CREATE PROCEDURE Stu_Info
　　• @S_sno char(7)
　　• AS
　　• Select sno,sname,ssex,sdept,sage FROM STUDENT
　　• WHERE sno=@S_sno
　　• GO
　　• exec Stu_Info '93210'
 
建存储过程
　　• 例4：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生学号，在STUDENT中计算此学生的出生年份.
　　• CREATE PROCEDURE Stu_Age
　　• @S_sno char(7)
　　• AS
　　• --定义并初始化局部变量，用于保存返回值
　　• DECLARE @ErrorValue int
　　• SET @ErrorValue=0
　　• SELECT sname,YEAR(GETDATE())-sage as 出生年份 FROM STUDENT
　　• WHERE sno=@S_sno
 
 
创建存储过程的指导原则
　　• 避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象
　　• 每个存储过程完成单个任务
　　• 命名本地存储过程的时候，避免使用“sp_”前缀
　　• 尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表
　　• 不要直接从 syscomments 系统表里删除项
 
执行存储过程
　　• 单独执行存储过程
　　　　不带参数的情况：[[EXEC[UTE]   存储过程名
　　　　[ WITH RECOMPILE]
 
　　• 在 INSERT 语句内执行存储过程
　　　　语法：INSERT INTO 表名EXEC[UTE] ……
　　　　– 将本地或远程存储过程返回的结果集插入本地表中
　　　　– 在 INSERT 语句内执行的存储过程必须返回关系结果集
　　　　修改和删除存储过程
• 修改存储过程
 
　　– 用 ALTER PROCEDURE 中的定义取代现有存储 过程原先的定义，但保留权限分配修改和删除存储过程（续）
• 删除存储过程
　　语法：DROP PROCEDURE {存储过程名} [,...n]
　　– 用 DROP PROCEDURE 语句从当前数据库中移除用户定义存储过程
　　• 删除存储过程的注意事项
　　– 在删除存储过程之前，执行系统存储过程
　　sp_depends 检查是否有对象依赖于此存储过程
实现存储过程
　　• 存储过程介绍 • 创建和管理存储过程
　　• 在存储过程中使用参数
　　• 处理错误信息
在存储过程中使用参数
　　• 使用输入参数 • 使用输入参数执行存储过程 • 使用输出参数返回值
 
使用输入参数
　　• 输入参数允许传递信息到存储过程内
　　– 在 CREATE PROCEDURE 中指定
@参数名数据类型[=默认值]
 
　　使用输入参数执行存储过程
　　EXEC • 通过参数名传递值OverdueOrders2  @Employee_ID = 1 , (顺序无所谓)
　　@Order_date = '1996-7-17'
　　• 通过位置传递参数（顺序保持一致）
 使用输出参数返回值
CREATE PROC proc1
@A int • 输, @出参数：以B int , @RESULT int OUTPUTOUTPUT 关键字指定的
ASSET  @RESULT = @A * @B变量
GO
• 执行有输出参数的存储过程 必须定义一个变量，
以接受返回值
DECLARE  @answer int
EXEC proc1 4, 7, @answer OUTPUT
SELECT @answer as ANSWER 写上OUTPUT,才可以接收到返回值
 
实现存储过程
　　• 存储过程介绍

 　　•创建和管理存储过程

　　• 在存储过程中使用参数
　　• 处理错误信息
 
错误信息处理
　　• 为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）
　　　　– 可以在错误处理逻辑中检查下列错误：返回码、
　　　　SQL Server 错误、用户定义的错误信息
　　• RETURN 语句
　　　　– 从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）
　　　　– 返回码为0表示成功。返回非零表示失败。用 户定义的返回值总是优先于系统的返回值。
错误信息处理（续）
　　• @@error 全局变量
　　　　– @@error 包含了最近执行的 Transact-SQL 语句的错误号，随着每一条语句的执行而更新
　　　　– 如果语句成功执行，返回0
 
 
演示错误信息处理例：创建一个存储过程，插入学生信息。
 
演示1  错误信息处理（续）
　　用下面的语句验证：
　　EXEC upStudInsert  ‘Tom’, ‘2007-1-1’, ‘1988-11-1’
　　结果： INSERT 语句与 COLUMN CHECK 约束
　　‘CK__tblstuden__birth__6383C8BA’ 冲突。该冲突发生于数据库‘student1’，表‘tblstudent’, column ‘birthdate’。语句已终止。
　　fail
 
　　ENDING
　　• 描述存储过程是如何处理的 • 创建、执行、修改和删除存储过程 • 创建接受参数的存储过程 • 创建用户定义错误信息