Các hàm UDFs trong SQL Server


Diễn đàn chia sẻ kiến thức, kinh nghiệm về IT và cuộc sống!
 
Trang ChínhGalleryTrợ giúpTìm kiếmThành viênNhómĐăng kýĐăng Nhập
Top posters
Sakura (1124)
 
hotboy (705)
 
Già Làng (373)
 
con_ca_nho90 (289)
 
that_true (154)
 
theanhkkt (143)
 
phamay (137)
 
lovelonelyman (134)
 
o0ovioletstaro0o (128)
 
stevenhung (122)
 
Âm - Dương lịch
Clock
Logo
11TH02 Pro!
Liên kết
Tin tức 60s
Tin công nghệ
Thời sự 24h
Game Moblie

Share | 
 

 Các hàm UDFs trong SQL Server

Xem chủ đề cũ hơn Xem chủ đề mới hơn Go down 
Tác giảThông điệp
Sakura


avatar

Thú CƯng :
Nam Scorpio

Số bài viết : 1124
Điểm : 1688
Được cảm ơn : 35
Ngày sinh : 03/11/1990
Tham gia ngày : 16/03/2010
Tuổi : 27
Đến từ : Bình Dương
Ngề nghiệp : IT Student

Bài gửiTiêu đề: Các hàm UDFs trong SQL Server   18/6/2011, 18:29

Hàm do người dùng định nghĩa (User Defined Function)

Đối với người lập trình (lập trình cơ sở dữ liệu cũng không phải là ngoại lệ) thì công việc viết các thủ tục. hàm là rất cần thiết.
Các thủ tục nội tại (stored procedure) và các hàm của người dùng (UDFs) được lưu trong các database trên server. Chúng được gọi thực hiện bằng T-SQL hoặc gọi từ ứng dụng
Bài viết này nhằm giới thiệu một kiến thức căn bản về các UDFs trong SQL Server
Phiên bản cài đặt MS SQL Server 2005
Cơ sở dữ liệu AdventureWorks

Kể từ phiên bản SQL Server 2000 trở đi, người lập trình với ngôn ngữ T-SQL được phép tạo ra các Hàm của chính mình để sử dụng.
Có ba loại UDF:

Hàm đơn trị (scalar Function)
Hàm đọc bảng (inline Table)
Hàm tạo bảng (multi statement Table)



Hàm đơn trị:

Hàm trả về đúng một giá trị, giá trị trả về có thể là kiểu chuỗi, số, ngày giờ, …
Sử dụng cú pháp sau để tạo hàm:
Code:
CREATE FUNCTION Tên Hàm (Danh sách tham số)
RETURNS Kiểu trả về
BEGIN
--Khai báo các biến cục bộ

--Các lệnh xử lý, tính toán

--Trả về giá trị
RETURN Giá trị trả về
END
Ví dụ: tạo hàm đơn trị fnTong2So nhận vào hai số nguyên và trả ra số tổng
Code:
CREATE FUNCTION fnTong2So(@a INT,@b INT)
RETURNS INT
BEGIN
--Khai báo các biến cục bộ
DECLARE @s INT
--Các lệnh xử lý, tính toán
SET @s = @a+@b
--Trả về giá trị
RETURN @s
END
--Gọi thực hiện
Code:
PRINT dbo.fnTong2So(25,18)
Lưu ý:
Hàm tạo mặc định trong schema dbo nên khi gọi phải có dbo.fnTong2So(25,18)
--Xóa hàm fnTong2So
Code:
DROP FUNCTION fnTong2So
Ví dụ: tạo hàm đơn trị fnThamNien nhận vào một ngày vào làm và trả ra số năm làm việc
Code:
CREATE FUNCTION fnThamNien(@ngay_vao_lam DATETIME)
RETURNS INT
BEGIN
--Khai báo các biến cục bộ
DECLARE @so_nam INT
--Các lệnh xử lý, tính toán
SET @so_nam = YEAR(GETDATE())-YEAR(@ngay_vao_lam)
--Trả về giá trị
RETURN @so_nam
END
--Gọi thực hiện
Code:
PRINT dbo.fnThamNien('1/27/1989')
--Gọi thực hiện
Code:
SELECT EmployeeID, LoginID, dbo.fnThamNien(HireDate)
FROM HumanResources.Employee
Ví dụ: tạo hàm đơn trị để lấy dữ liệu
Trong hai ví dụ đầu chỉ mang tính cách minh họa cho việc tạo hàm đơn trị.
Tiếp theo giả sử chúng ta cần liệt kê các nhân viên trong bảng HumanResources.Employee, thông tin liệt kê bao gồm EmployeeID, FirstName, LastName. Nhưng các cột FirstName và LastName chỉ có trong bảng Person.Contact, có nhiều cách để giải quyết nhưng sau đây là cách giải quyết bằng hàm đơn trị:
Code:
SELECT EmployeeID, dbo.fnGetName(ContactID) Name
FROM HumanResources.Employee
Hàm fnGetName nhận vào một Contact ID và trả ra chuỗi FirstName, LastName
--Hàm đơn trị
Code:
CREATE FUNCTION fnGetName(@ContactID INT)
RETURNS VARCHAR(100)
BEGIN
--Khai báo các biến cục bộ
DECLARE @name VARCHAR(100)
--Các lệnh xử lý, tính toán
SELECT @name=FirstName+', '+LastName
FROM Person.Contact
WHERE ContactID=@ContactID
--Trả về giá trị
RETURN @name
END
--Gọi thực hiện
Code:
SELECT EmployeeID,dbo.fnGetName(ContactID) Name
FROM HumanResources.Employee

_________________________________________________
Khách viếng thăm muốn liên hệ với mình thì xem thông tin phía dưới nha:
Email: [You must be registered and logged in to see this link.]
Nick Yahoo: Edward_Thien
Về Đầu Trang Go down
Xem lý lịch thành viên
Sakura


avatar

Thú CƯng :
Nam Scorpio

Số bài viết : 1124
Điểm : 1688
Được cảm ơn : 35
Ngày sinh : 03/11/1990
Tham gia ngày : 16/03/2010
Tuổi : 27
Đến từ : Bình Dương
Ngề nghiệp : IT Student

Bài gửiTiêu đề: Re: Các hàm UDFs trong SQL Server   18/6/2011, 18:31

Hàm đọc bảng:

Hàm trả về một bảng (table) là kết quả của câu lệnh SELECT.
Do trong hàm chỉ có đúng một câu SELECT nên được gọi là inline table Function.
Hàm đọc bảng được xem như View có tham số.
Sử dụng cú pháp sau để tạo hàm:
Code:
CREATE FUNCTION Tên Hàm (Danh sách tham số)
RETURNS TABLE
AS
RETURN (Câu lệnh SELECT)

Ví dụ: tạo hàm đọc bảng fnLietKeNV nhận vào mã người quản lý và trả về bảng chứa các nhân viên của người quản lý đó
--Hàm đọc bảng
Code:
CREATE FUNCTION fnLietKeNV(@ManagerID INT)
RETURNS TABLE
AS
RETURN (SELECT EmployeeID,LoginID
FROM HumanResources.Employee
WHERE ManagerID=@ManagerID)
GO
--Gọi thực hiện để xem các nhân viên của người quản lý có mã 21
Code:
SELECT * FROM dbo.fnLietKeNV(21)
--Gọi thực hiện để xem các nhân viên của người quản lý có mã 185
SELECT * FROM dbo.fnLietKeNV(185)

Ví dụ: sửa lại hàm đọc bảng fnLietKeNV
Giả sử thông tin đọc được muốn có thêm cột FirstName và LastName thì chúng ta phải sửa lại câu lệnh SELECT trong hàm.
Có thể làm như sau:
Sử dụng lại hàm đơn trị fnGetName trong ví dụ trước. Hàm fnGetName nhận vào một Contact ID và trả ra chuỗi FirstName, LastName.
Hàm fnLietKeNV được sửa lại như sau:
--Sủa lại
Code:
ALTER FUNCTION fnLietKeNV(@ManagerID INT)
RETURNS TABLE
AS
RETURN (SELECT EmployeeID,LoginID,dbo.fnGetName(ContactID) Name
FROM HumanResources.Employee
WHERE ManagerID=@ManagerID)
GO
Code:
--Gọi thực hiện để xem các nhân viên của người quản lý có mã 185
SELECT * FROM dbo.fnLietKeNV(185)

Hàm tạo bảng:

Hàm trả về một bảng (table), bảng phải được định nghĩa cấu trúc trong Hàm.
Do trong hàm có nhiều câu lệnh nên được gọi là multi statement Function.
Sử dụng hàm tạo bảng để tự tạo ra một cấu trúc cho bảng, tạo dữ liệu cho bảng và trả về bảng đã tạo
Sử dụng cú pháp sau để tạo hàm:
Code:
CREATE FUNCTION Tên Hàm (Danh sách tham số)
RETURNS @Tên bảng TABLE(Tên cột Kiểu dữ liệu, …)
AS
BEGIN
--Tạo dữ liệu ban đầu cho bảng

` --Cập nhật dữ liệu (nếu muốn)

--Kết thúc
RETURN
END

Ví dụ: tạo hàm tạo bảng fnLietKeTenNV nhận vào 1 hoặc 0 và trả về các nhân viên theo các yêu cầu sau:
Nếu nhận vào 0 thì các nhân viên trả về gồm có EmployeeID, FirstName
Nếu nhận vào 1 thì các nhân viên trả về gồm có EmployeeID, FirstName và LastName
--Hàm tạo bảng
Code:
CREATE FUNCTION fnLietKeTenNV(@ten_day_du INT)
RETURNS @bang TABLE(manv INT, ten NVARCHAR(100))
AS
BEGIN
--Tao du lieu ban dau cho bang
IF @ten_day_du=1
INSERT INTO @bang(manv,ten)
SELECT EmployeeID,FirstName+', '+LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON e.ContactID=c.ContactID
ELSE
INSERT INTO @bang(manv,ten)
SELECT EmployeeID,FirstName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON e.ContactID=c.ContactID
--Ket thuc
RETURN
END
Code:
--Gọi thực hiện để liệt kê FisrtName và LastName
SELECT * FROM dbo.fnLietKeTenNV(1)
--Gọi thực hiện để liệt kê chỉ FisrtName
SELECT * FROM dbo.fnLietKeTenNV(0)

Ví dụ: sửa lại hàm tạo bảng fnLietKeTenNV để hiển thị tên chữ HOA
Code:
--Sửa hàm
ALTER FUNCTION fnLietKeTenNV(@ten_day_du INT)
RETURNS @bang TABLE(manv INT, ten NVARCHAR(100))
AS
BEGIN
--Tao du lieu ban dau cho bang
IF @ten_day_du=1
INSERT INTO @bang(manv,ten)
SELECT EmployeeID,FirstName+', '+LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON e.ContactID=c.ContactID
ELSE
INSERT INTO @bang(manv,ten)
SELECT EmployeeID,FirstName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON e.ContactID=c.ContactID
--Cap nhat du lieu sang chu HOA
UPDATE @bang
SET ten=UPPER(ten)
--Ket thuc
RETURN
END
Code:
--Gọi thực hiện để liệt kê FisrtName và LastName
SELECT * FROM dbo.fnLietKeTenNV(1)

_________________________________________________
Khách viếng thăm muốn liên hệ với mình thì xem thông tin phía dưới nha:
Email: [You must be registered and logged in to see this link.]
Nick Yahoo: Edward_Thien
Về Đầu Trang Go down
Xem lý lịch thành viên
 

Các hàm UDFs trong SQL Server

Xem chủ đề cũ hơn Xem chủ đề mới hơn Về Đầu Trang 
Trang 1 trong tổng số 1 trang

 Similar topics

-
» Quy Định Về Cấp Bậc Trong Diễn Đàn
» Bán nhà trong HXH Nguyễn Minh Hoàng, nhà bán trong khu K300 giá 5.5 tỷ
» VÀNH NGOÀI BẢY CHỮ VÀNH TRONG TÁM NGHỀ
» Kho vũ khí khổng lồ trong Truy Kích
» Bán xe trượt scooter 3 trong 1 dành cho bé

Permissions in this forum:Bạn không có quyền trả lời bài viết
IT World! :: HỌC TẬP :: HỌC KỲ VI :: Hệ quản trị cơ sở dữ liệu-