第10章 可编程对象(5)

--10.7 例程
--10.7.1 用户定义函数
--用户定义函数(UDF,user-defined function)的目的是要封装计算的逻辑处理，有可能需要基于输入的参数，并返回结果。
--SQL Server支持两种用户定义函数：标量UDF和表值UDF。标量UDF只返回单个数据值，而表值UDF则返回一个表。
--UDF不允许有任何副作用.这一规定明显的含义是UDF不能对数据库中的任何架构或数据进行修改.
use TSQLFundamentals2008;
if OBJECT_ID('dbo.fn_age') is not null drop function dbo.fn_age;
go

create function dbo.fn_age
(@birthday as datetime,
@eventdate as datetime)
returns int
as 
begin
return
	datediff(year,@birthday, @eventdate)-
	case 
		when 100*month(@eventdate)+day(@birthday)<100*month(@birthday)+day(@birthday) then 1
		else 0
	end 
end;
go

select empid, firstname, birthdate, dbo.fn_age(birthdate, CURRENT_TIMESTAMP) as age
from HR.Employees;

--10.7.2 存储过程
--存储过程是封装了T-SQL代码的服务器端例程.存储过程可以有输入和输出参数,可以返回多个查询的结果集,也允
--许调用具有副作用的代码.通过存储过程不但可以对数据进行修改,也可以对数据库架构进行修改.
--存储过程好处:
--1. 存储过程可以封装逻辑处理.如果需要修改存储过程的实现,则只要在数据库的一个地方进行修改,存储过程的所有
--用户就能够使用修改过的版本.
--2. 通过存储过程可以更好地控制安全性.可以授予用户执行某个存储过程的权限,而不是授予用于直接执行底层操作的
--权限.此外,存储过程也有助于避免SQL注入,尤其是从客户端通过参数来替换特殊的SQL的注入形式.
--3. 在存储过程中可以整合所有的错误处理,当有错误发生时,默默地进行纠正错误的操作.
--4. 存储过程可以提高执行性能.存储过程在默认情况下是重用执行计划的,而SQL Server对其他特殊计划的重用有更多
--的限制.使用存储过程的另一个好处是可以减少网络通信流量.
use TSQLFundamentals2008;
if OBJECT_ID('sales.usp_getcustomerorders', 'P') is not null drop proc sales.usp_getcustomerorders;
go
create proc sales.usp_getcustomerorders
(@custid as int,
@fromdate as datetime = '19000101',
@todate as datetime = '99991231',
@numrows as int output)
as
set nocount on;
select orderid, custid, empid, orderdate
from Sales.Orders
where custid=@custid
and orderdate>=@fromdate
and orderdate<@todate;
set @numrows=@@rowcount;
go

--命令 set nocount on用于禁止显示DML语句影响了多少行的消息。
declare @rc as int;
exec Sales.usp_getcustomerorders @custid=1,@fromdate=N'20070101',@todate=N'20080101',@numrows=@rc output;
select @rc as numrows;