Dunk

-- 資料來源:德瑞克老師的上課筆記+自行修改
USE ProductDB
GO

-- Scalar Function(純量值函數)
-- 取得員工所屬單位名稱
-- SELECT BRNAME
-- FROM
--	dbo.EMPLOYEE a
--	INNER JOIN dbo.BRANCH b
--		ON a.BRNO = b.BRNO
-- WHERE
--	a.STAFF = '123456'
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EmpUnitName]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[EmpUnitName]
GO

CREATE FUNCTION dbo.EmpUnitName
(	
	@STAFF nvarchar(6)
)
RETURNS  varchar(30)		
as
BEGIN
declare @unitname nvarchar(30)
SELECT @unitname = BRNAME
FROM
	dbo.EMPLOYEE a
	INNER JOIN dbo.BRANCH b
		ON a.BRNO = b.BRNO
WHERE
	a.STAFF = @STAFF;
return @unitname;
END
GO
-- 測試函式
DECLARE @STAFF nvarchar(6)
SET @STAFF = '123456';
SELECT dbo.EmpUnitName(@STAFF) AS [單位名稱]

-- Inline table-valued functions(內嵌資料表值函數)
-- 建立員工資料的分頁函式
-- 根據員工編號排序
-- SELECT rank() OVER (ORDER BY STAFF) rno
--	 , STAFF
--	 , BRNO
-- FROM
--	dbo.EMPLOYEE
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EmpDataPager]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[EmpDataPager]
GO

CREATE FUNCTION dbo.EmpDataPager
(	
	@lower int,
	@upper int
)
RETURNS  TABLE		
as
RETURN
(
SELECT *
FROM
	(SELECT rank() OVER (ORDER BY STAFF) rno
		  , STAFF
		  , BRNO
	 FROM
		 dbo.EMPLOYEE) a
WHERE
	rno BETWEEN @lower AND @upper
)
GO
-- 測試函式
SELECT *
FROM
	dbo.EmpDataPager(1, 10)
ORDER BY
	BRNO

-- Multi-statement table-valued functions(多重陳述式資料表值函數)
-- 查循現行員工的單位代號與前一日不同的部份
-- SELECT a.STAFF
--	 , a.BRNO
--	 , b.BRNO
-- FROM
--	dbo.EMPLOYEE a
--	INNER JOIN dbo.EMPLOYEE_SYNC b
--		ON a.STAFF = b.STAFF AND a.BRNO <> b.BRNO
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EmpDataDiffer]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[EmpDataDiffer]
GO

CREATE FUNCTION dbo.EmpDataDiffer()
RETURNS  @tbl_EmpDiffer TABLE		
(
	STAFF nvarchar(6),
	BEFORE_BRNO char(4),
	AFTER_BRNO char(4)
)
as
BEGIN
INSERT @tbl_EmpDiffer
SELECT a.STAFF
	 , a.BRNO
	 , b.BRNO
FROM
	dbo.EMPLOYEE a
	INNER JOIN dbo.EMPLOYEE_SYNC b
		ON a.STAFF = b.STAFF AND a.BRNO <> b.BRNO;
RETURN;				
END
GO
-- 測試函式
SELECT *
FROM
	dbo.EmpDataDiffer()
GO

-- 時間函數
-- 將UTC時間轉換為本地時間
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[fn_myTimeZone]')
	 AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[fn_myTimeZone]
GO

CREATE FUNCTION dbo.fn_myTimeZone
	(@UTCDate datetime)
	RETURNS datetime
AS
BEGIN
	declare @LocalDate datetime
SET @LocalDate = dateadd(HOUR, datediff(HOUR, getutcdate(), getdate()), @UTCDate);
	return @LocalDate;
END
GO
-- 測試函式
SELECT getutcdate() AS [UTC時間]
	 , dbo.fn_myTimeZone(getutcdate()) AS [本地時間]