Các hàm UDFs trong SQL Server

Hàm do người dùng định nghĩUser Defined Function)Đối với người lập trình (lập trình a ( 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ụngBà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 ServerPhiên bản cài đặt MS SQL Server 2005Cơ sở dữ liệu AdventureWorks...
Nội dung trích xuất từ tài liệu:
Các hàm UDFs trong SQL ServerCác hàm UDFs trong SQL ServerNguồn : forum.t3h.vnHà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ệcviế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 trongcá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ụngBà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 ServerPhiên bản cài đặt MS SQL Server 2005Cơ sở dữ liệu AdventureWorksKể 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éptạ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: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ềENDVí dụ: tạo hàm đơn trị fnTong2So nhận vào hai số nguyên và trả ra số tổngCREATE FUNCTION fnTong2So(@a INT,@b INT)RETURNS INTBEGIN--Khai báo các biến cục bộDECLARE @s INT--Các lệnh xử lý, tính toánSET @s = @a+@b--Trả về giá trịRETURN @sEND--Gọi thực hiệnPRINT 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 fnTong2SoDROP FUNCTION fnTong2SoVí 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àmviệcCREATE FUNCTION fnThamNien(@ngay_vao_lam DATETIME)RETURNS INTBEGIN--Khai báo các biến cục bộDECLARE @so_nam INT--Các lệnh xử lý, tính toánSET @so_nam = YEAR(GETDATE())-YEAR(@ngay_vao_lam)--Trả về giá trịRETURN @so_namEND--Gọi thực hiệnPRINT dbo.fnThamNien(1/27/1989)--Gọi thực hiệnSELECT EmployeeID, LoginID, dbo.fnThamNien(HireDate)FROM HumanResources.EmployeeVí dụ: tạo hàm đơn trị để lấy dữ liệuTrong 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ảngHumanResources.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ị:SELECT EmployeeID, dbo.fnGetName(ContactID) NameFROM HumanResources.EmployeeHàm fnGetName nhận vào một Contact ID và trả ra chuỗi FirstName, LastName--Hàm đơn trị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ánSELECT @name=FirstName+, +LastNameFROM Person.ContactWHERE ContactID=@ContactID--Trả về giá trịRETURN @nameEND--Gọi thực hiệnSELECT EmployeeID,dbo.fnGetName(ContactID) NameFROM HumanResources.EmployeeHà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:CREATE FUNCTION Tên Hàm (Danh sách tham số)RETURNS TABLEASRETURN (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ứacác nhân viên của người quản lý đó--Hàm đọc bảngCREATE FUNCTION fnLietKeNV(@ManagerID INT)RETURNS TABLEASRETURN (SELECT EmployeeID,LoginIDFROM HumanResources.EmployeeWHERE 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ã 21SELECT * 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ã 185SELECT * FROM dbo.fnLietKeNV(185)Ví dụ: sửa lại hàm đọc bảng fnLietKeNVGiả sử thông tin đọc được muốn có thêm cột FirstName và LastName thì chúng ta phảisử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ộtContact ID và trả ra chuỗi FirstName, LastName.Hàm fnLietKeNV được sửa lại như sau:--Sủa lạiALTER FUNCTION fnLietKeNV(@ManagerID INT)RETURNS TABLEASRETURN (SELECT EmployeeID,LoginID,dbo.fnGetName(ContactID) NameFROM HumanResources.EmployeeWHERE 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ã 185SELECT * 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ạoSử dụng cú pháp sau để tạo hàm: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, …)ASBEGIN--Tạo dữ liệu ban đầu cho bảng` --Cập nhật dữ liệu (nếu muốn)--Kết thúcRETURNENDVí dụ: tạo hàm tạo bảng fnLietKeTenNV nhận vào 1 hoặc 0 và trả về các nh ...