数据库基础--自定义函数

--标量自定义函数
USE Northwind
CREATE FUNCTION fn_NewRegion
  (@myinput nvarchar(30))
  RETURNS nvarchar(30)
BEGIN
  IF @myinput IS NULL
  SET @myinput = 'Not Applicable'
  RETURN @myinput
END


--自定义函数的调用
SELECT LastName, City,Region, dbo.fn_NewRegion(Region)
AS Region1,
Country
FROM Employees

------------------------------
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
SELECT dbo.fn_DateFormat(GETDATE(),':')
--------------------------------
USE Northwind
GO
CREATE FUNCTION fn_CustomerNamesInRegion
  ( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
SELECT * FROM fn_CustomerNamesInRegion(N'WA')
-------------------------------
USE Northwind
GO
CREATE FUNCTION fn_Employees (@length nvarchar(9))
RETURNS @fn_Employees table
  (EmployeeID int PRIMARY KEY NOT NULL,
  [Employee Name] nvarchar(61) NOT NULL)
AS
BEGIN
  IF @length = 'ShortName'
    INSERT @fn_Employees SELECT EmployeeID, LastName FROM Employees
  ELSE IF @length = 'LongName'
    INSERT @fn_Employees SELECT EmployeeID,(FirstName + ' ' + LastName) FROM Employees
RETURN
END
--调用函数
SELECT * FROM dbo.fn_Employees('LongName')
Or
SELECT * FROM dbo.fn_Employees('ShortName')
--TABLE变量
USE pubs
DECLARE @pricelist
TABLE(tid varchar(6), price money)
INSERT @pricelist SELECT title_id, price FROM titles
SELECT * FROM @pricelist
“内嵌表值函数”与视图的区别(均是单行 SELECT)
内嵌表值函数可以带参数
“多语句表值函数”与存储过程的区别(均可以有多行 T-SQL 语句)
多语句表值函数可以象“表/视图”一样使用
--修改和删除自定义函数
--修改
ALTER FUNCTION dbo.fn_NewRegion
  <New function content>
--删除
DROP FUNCTION dbo.fn_NewRegion