SQL Sever 2005第13章 存储过程及自定义函数

第5章 存储过程及自定义函数
回顾
视图类型
标准视图 索引视图 分区视图
事务的四个特征（ACID特性）：
原子性 一致性 隔离性 持久性
事务类型
显示事务 隐性事务 自动提交事务
2
本章目标
创建存储过程 管理存储过程 创建标量函数 创建内联表值函数 创建多语句表值函
输出参数的特性
调用语句必须包含一个变量名，以接受返回值。不能传 递常数 可以在随后的 Transact-SQL 语句中使用返回变量 输出参数可以是任何类型，除了 text 或 image 输出参数可以是游标占位符
13
显式地重新编译存储过程
三种显式重新编译存储过程的方法
CREATE PROCEDURE [WITH RECOMPILE]
10
依赖于可用内存，存储过程的最大大小为128 MB
使用输入参数
输入参数允许传递信息到存储过程内
在 CREATE PROCEDURE 中指定 @参数名 数据类型 [=默认值]
指定参数的依据和指导原则
所有的输入参数值都应该在存储过程开始的时候进行检查， 以尽早捕获缺失值和非法值的情况 应该为参数提供合适的默认值。若定义了默认值，用户可以 在未指定参数值的基础上执行存储过程 一个存储过程最多可以有1024个参数 存储过程内局部变量的数目没有限制，只和可用内存有关 参数对存储过程而言是局部的。在不同存储过程中可以使用 相同名字的参数


24
使用标量用户自定义函数
标量函数返回 RETURNS 子句中定义 的数据类型的单个数据值
在 BEGIN … END 块之间定义了函数体，包含 返回值的一系列 Transact-SQL 语句 返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型
通过位置传递参数
只传递值（而没有对被传值参数的引用）称为通过位置传 递 参数值必须以参数在 CREATE PROCEDURE 语句中的定 义顺序列出 可以忽略有默认值的参数，但不能中断次序
12
使用输出参数返回值
输出参数：以 OUTPUT 关键字指定 的变量
存储过程通过输出参数向调用它的存储过程或客户端返 回信息 通过输出参数，存储过程的运行结果可以得到保留，即 使存储过程运行结束
用户自定义函数的使用

使用标量用户定义函数

标量函数以和内置函数相同的形式调用： 拥有者名.函数名([参数列表]) 不可省略拥有者名，也不能用命名参数的形式（例如 @参 数名=值），且参数也不可以省略（但可以使用 DEFAULT 关 键字指明使用默认值） SQL Server 提供少量内置用户定义函数。它们的名字以“ fn_”开头
15
错误信息处理
sp_addmessage 系统存储过程
允许开发者创建用户定义的错误信息，指定消息 号、严重级别和消息文本，可设定为把错误信息 自动记录到 Windows 2005 应用程序日志中
@@error 函数
@@error 系统函数包含了最近执行的 TransactSQL 语句的错误号，随着每一条语句的执行而更 新 使用 @@error 系统函数检测特定的错误号或有条 件地退出存储过程
额外的限制
标量函数内的 SQL 语句不能包括任何非确定性 系统函数
25
标量用户自定义函数示例

创建函数
USE Northwind CREATE FUNCTION fn_DateFormat (@indate datetime, @separator char(1)) RETURNS Nchar(20) AS BEGIN RETURN CONVERT(Nvarchar(20), datepart(mm,@indate)) + @separator + CONVERT(Nvarchar(20), datepart(dd, @indate)) + @separator + CONVERT(Nvarchar(20), datepart(yy, @indate)) END
22
23
--计算工龄工资函数 use pubs go create function WorkYearWage(@hiredate datetime, @today datetime,@per_wage money) --today表示当前的日期 per_wage表示每一年工龄应得的工资 额 --hiredate表示雇佣日期 returns money as begin declare @WorkYearWage money set @WorkYearWage=(year(@today)year(@hiredate))*@per_wage return(@WorkYearWage) end --结束函数存储过程具有许多优点（续）
改善性能。预编译的 Transact-SQL 语句， 可以根据条件决定执行哪一部分 减少网络通信量。客户端用一条语句调 用存储过程，就可以完成可能需要大量 语句才能完成的任务，这样减少了客户 端和服务器之间的请求/回答包
能够实现较快的执行速度
可以在错误处理逻辑中检查下列错误：返回码、 SQL Server 错误、用户定义的错误信息
RETURN 语句
从查询或存储过程无条件返回，同时可以返回一 个整数状态值（返回码） 返回码为0表示成功。0至-14的返回码已被系统使 用，-15至-99的返回码被系统保留作将来扩展。 若用户不提供返回码，则返回 SQL Server 的返 回码。用户定义的返回码优先级高于系统提供的 返回码
17
用户自定义函数
SQL Server 2005 允许用户设计 自己的函数，以补充和扩展系统 提供（内置）函数的功能
用户定义函数采用零或多个输入参数 并返回标量值或表 SQL Server 2005 支持三种用户定义 函数：
标量函数 多语句表值函数 内联表值函数
18
用户自定义函数


在 Transact-SQL 语句中使用用户定义函数

标量用户定义函数可以在任何它们返回值的数据类型可以 用的地方使用 表值用户定义函数只能在 SELECT 语句的 FROM 子句中使 用

21
创建标量函数
create function [拥有者名.] 函数名称 ([@参数名称 参数数据类型[=默认值]]) returns 返回值类型 as begin <函数体T-SQL语句> return 表达式 end
RAISERROR 语句
16
返回用户定义的错误信息并设系统标志，记录发 生的错误
修改和删除存储过程
修改存储过程
alter procedure
删除存储过程
语法：DROP PROCEDURE {存储过程名} [,...n]
用 DROP PROCEDURE 语句从当前数据 库中移除用户定义存储过程
3
存储过程
定义存储过程
是存储在服务器上的 Transact-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性
SQL Server 中的存储过程与其他编程 语言中的过程类似，它可以
包含执行数据库操作（包括调用其他过程）的编程语句 接受输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以 及失败原因） 以输出参数的形式将多个值返回至调用过程或批处理
7
创建存储过程
USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null GO
8
创建存储过程
语法格式： CREATE PROC [ EDURE ] procedure_name [ { @parameter data_type } [ = default ] [ OUTPUT ] ] AS sql_statement [ ...n ]
9
创建存储过程
创建存储过程
CREATE PROCEDURE 定义可以包括任何数 目和类型的Transact-SQL语句，但不包括下列 对象创建语句：CREATE PROCEDURE、 CREATE TRIGGER 和 CREATE VIEW 执行 CREATE PROCEDURE 语句的用户必须 是 sysadmin、db_owner 或 db_ddladmin角色 的成员，或必须拥有 CREATE PROCEDURE 权限
11
通过参数名传递值
使用输入参数执行存储过程
在 EXECUTE 语句中以“@参数名=值”的格式指定参数 称为通过参数名传递 当通过参数名传递值时，可以以任何顺序指定参数值，并 且可以省略允许空值或具有默认值的参数 若在存储过程中定义了参数的默认值，则在下列情况使 用：当调用存储过程的时候，参数未指定值或者参数的值 指定为 DEFAULT 关键字
4
定义存储过程
SQL Server 支持五种类型的存储过程
系统存储过程（sp_）：存储在 master 数据库 内，以“sp_”前缀标识 本地存储过程：本地存储过程在单独的用户数据 库内创建 临时存储过程：临时存储过程可能是局部的，名 称以“#”开 头；也可能是全局的，名称以“##”开头 远程存储过程：远程存储过程是 SQL Server 早 期版本的特性，分布式查询支持这项功能 扩展存储过程（xp_）：扩展存储过程以动态链 接库（DLL）的形式实现，在 SQL Server 环境 外执行