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

24
使用标量用户自定义函数
标量函数返回 RETURNS 子句中定义 的数据类型的单个数据值
在 BEGIN … END 块之间定义了函数体，包含 返回值的一系列 Transact-SQL 语句 返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型
6
存储过程的优点
存储过程具有许多优点（续）
改善性能。预编译的 Transact-SQL 语句， 可以根据条件决定执行哪一部分 减少网络通信量。客户端用一条语句调 用存储过程，就可以完成可能需要大量 语句才能完成的任务，这样减少了客户 端和服务器之间的请求/回答包
能够实现较快的执行速度
可以在错误处理逻辑中检查下列错误：返回码、 SQL Server 错误、用户定义的错误信息
RETURN 语句
从查询或存储过程无条件返回，同时可以返回一 个整数状态值（返回码） 返回码为0表示成功。0至-14的返回码已被系统使 用，-15至-99的返回码被系统保留作将来扩展。 若用户不提供返回码，则返回 SQL Server 的返 回码。用户定义的返回码优先级高于系统提供的 返回码
3
存储过程
定义存储过程
是存储在服务器上的 Transact-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性
SQL Server 中的存储过程与其他编程 语言中的过程类似，它可以
包含执行数据库操作（包括调用其他过程）的编程语句 接受输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以 及失败原因） 以输出参数的形式将多个值返回至调用过程或批处理
10
依赖于可用内存，存储过程的最大大小为128 MB
使用输入参数
输入参数允许传递信息到存储过程内
在 CREATE PROCEDURE 中指定 @参数名 数据类型 [=默认值]
指定参数的依据和指导原则
所有的输入参数值都应该在存储过程开始的时候进行检查， 以尽早捕获缺失值和非法值的情况 应该为参数提供合适的默认值。若定义了默认值，用户可以 在未指定参数值的基础上执行存储过程 一个存储过程最多可以有1024个参数 存储过程内局部变量的数目没有限制，只和可用内存有关 参数对存储过程而言是局部的。在不同存储过程中可以使用 相同名字的参数
用户自定义函数的使用

使用标量用户定义函数

标量函数以和内置函数相同的形式调用： 拥有者名.函数名([参数列表]) 不可省略拥有者名，也不能用命名参数的形式（例如 @参 数名=值），且参数也不可以省略（但可以使用 DEFAULT 关 键字指明使用默认值） SQL Server 提供少量内置用户定义函数。它们的名字以“ fn_”开头
9
创建存储过程
创建存储过程
CREATE PROCEDURE 定义可以包括任何数 目和类型的Transact-SQL语句，但不包括下列 对象创建语句：CREATE PROCEDURE、 CREATE TRIGGER 和 CREATE VIEW 执行 CREATE PROCEDURE 语句的用户必须 是 sysadmin、db_owner 或 db_ddladmin角色 的成员，或必须拥有 CREATE PROCEDURE 权限


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

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

21
创建标量函数
create function [拥有者名.] 函数名称 ([@参数名称 参数数据类型[=默认值]]) returns 返回值类型 as begin <函数体T-SQL语句> return 表达式 end
11
通过参数名传递值
使用输入参数执行存储过程
在 EXECUTE 语句中以“@参数名=值”的格式指定参数 称为通过参数名传递 当通过参数名传递值时，可以以任何顺序指定参数值，并 且可以省略允许空值或具有默认值的参数 若在存储过程中定义了参数的默认值，则在下列情况使 用：当调用存储过程的时候，参数未指定值或者参数的值 指定为 DEFAULT 关键字
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 --结束函数定义 go
额外的限制
标量函数内的 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
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 ]
sp_recompile

sp_recompile 系统存储过程强制在下次运行存储过程或触 发器时进行重新编译。若 @objname 参数指定的是表或视 图，那么所有使用指定对象的存储过程在下次执行时都会重 新编译
14
错误信息处理
为了增强存储过程的效率，应使用错误 信息向用户传达事务状态（成功或失败）
4
定义存储过程
SQL Server 支持五种类型的存储过程
系统存储过程（sp_）：存储在 master 数据库 内，以“sp_”前缀标识 本地存储过程：本地存储过程在单独的用户数据 库内创建 临时存储过程：临时存储过程可能是局部的，名 称以“#”开 头；也可能是全局的，名称以“##”开头 远程存储过程：远程存储过程是 SQL Server 早 期版本的特性，分布式查询支持这项功能 扩展存储过程（xp_）：扩展存储过程以动态链 接库（DLL）的形式实现，在 SQL Server 环境 外执行
RAISERROR 语句
16
返回用户定义的错误信息并设系统标志，记录发 生的错误
修改和删除存储过程
修改存储过程
alter procedure
删除存储过程
语法：DROP PROCEDURE {存储过程名} [,...n]
用 DROP PROCEDURE 语句从当前数据 库中移除用户定义存储过程
输出参数的特性
调用语句必须包含一个变量名，以接受返回值。不能传 递常数 可以在随后的 Transact-SQL 语句中使用返回变量 输出参数可以是任何类型，除了 text 或 image 输出参数可以是游标占位符
13
显式地重新编译存储过程
三种显式重新编译存储过程的方法
CREATE PROCEDURE [WITH RECOMPILE]
通过位置传递参数
只传递值（而没有对被传值参数的引用）称为通过位置传 递 参数值必须以参数在 CREATE PROCEDURE 语句中的定 义顺序列出 可以忽略有默认值的参数，但不能中断次序
12
使用输出参数返回值
输出参数：以 OUTPUT 关键字指定 的变量
存储过程通过输出参数向调用它的存储过程或客户端返 回信息 通过输出参数，存储过程的运行结果可以得到保留，即 使存储过程运行结束

创建存储过程时在其定义中指定 WITH RECOMPILE 选项， 表明 SQL Server 将不对该存储过程计划进行高速缓存，该 存储过程将在每次执行时都重新编译
EXECUTE [WITH RECOMPILE]

在执行存储过程时指定 WITH RECOMPILE 选项，可强制 对存储过程进行重新编译
标量函数
标量函数返回一个标量（单值）结果 可在与标量函数返回的数据类型相同的值所能 使用的任何位置使用该标量函数，包括 SELECT 语句中列的列表和 WHERE 子句、表 达式、表定义中的约束表达式，甚至作为表中 列的数据类型
19
用户自定义函数
多语句表值函数
多语句表值函数返回一个由一条或多条 TransactSQL 语句建立的表，类似于存储过程 与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图 一样
5
存储过程的优点
存储过程具有许多优点
语句块。与其他应用程序共享应用逻辑，确保一致的数 据访问和修改。存储过程封装了商务逻辑。若规则或策 略有变化，则只需要修改服务器上的存储过程，所有的 客户端就可以直接使用 封装。屏蔽数据库模式的详细资料。用户不需要访问底 层的数据库和数据库内的对象 提供了安全性机制。用户可以被赋予执行存储过程的权 限，而不必在存储过程引用的所有对象上都有权限
第5章 存储过程及自定义函数
回顾
视图类型
标准视图 索引视图 分区视图
事务的四个特征（ACID特性）：
原子性 一致性 隔离性 持久性
事务类型
显示事务 隐性事务 自动提交事务
2
本章目标
创建存储过程 管理存储过程 创建标量函数 创建内联表值函数 创源自文库多语句表值函
15
错误信息处理
sp_addmessage 系统存储过程
允许开发者创建用户定义的错误信息，指定消息 号、严重级别和消息文本，可设定为把错误信息 自动记录到 Windows 2005 应用程序日志中
@@error 函数
@@error 系统函数包含了最近执行的 TransactSQL 语句的错误号，随着每一条语句的执行而更 新 使用 @@error 系统函数检测特定的错误号或有条 件地退出存储过程
17
用户自定义函数
SQL Server 2005 允许用户设计 自己的函数，以补充和扩展系统 提供（内置）函数的功能
用户定义函数采用零或多个输入参数 并返回标量值或表 SQL Server 2005 支持三种用户定义 函数：
标量函数 多语句表值函数 内联表值函数
18
用户自定义函数
内联表值函数
内嵌表值函数返回一个单条 SELECT 语句产生的 结果的表，类似于视图 相对于视图，内嵌表值函数可使用参数，提供了 更强的适应性，扩展了索引视图的功能
20

创建用户定义函数

创建用户自定义函数
每个完全合法的用户定义函数名 （数据库名.拥有者名.函数 名）必须惟一 语句指定了输入参数及它们的数据类型、处理指令，以及 返回的值及其数据类型