Hàm Do Người Dùng định Nghĩa (User Defined Function-UDF) Trong ...

Bài viết sau đây sẽ giới thiệu tới các bạn hàm do người dùng định nghĩa (User Defined Function-UDF) trong SQL Server

Hàm do người dùng định nghĩa được chia làm 3 loại: (1) scalar (hàm vô hướng), (2) inline table-valued (hàm nội tuyến, giá trị trả về dạng bảng), (3) multi -statement table-valued (hàm bao gồm nhiều câu lệnh SQL bên trong, trả về giá trị dạng bảng)

Scalar UDF: được sử dụng để trả về một duy nhất một giá trị dựa tr ên một các tham số truyền vào. Ví dụ: ta có thể tạo ra một UDF vô hướng nhận Customerid là tham số và trả về

CustomerName.

Inline table-valued: trả về một bảng dựa trên một câu lệnh SQL duy nhất định nghĩa các dòng và các cột trả về.

Multi-statement table-value: cũng trả về kết quả là một tập hợp nhưng có thể dựa trên nhiều câu lệnh SQL.

1 Hàm vô hướng - Scalar UDF

Scarlar UDF được tạo ra bằng câu lệnh CREATE FUNCTION có cấu trúc nh ư sau;

CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS (kiểu_trả_về_của_hàm) AS BEGIN các_câu_lệnh_của_hàm END

Ví dụ:

Câu lệnh dưới đây định nghĩa hàm tính ngày trong tuần (thứ trong tuần) của một giá trị kiểu ngày

create function f_ thu(@ngay datetime) returns nvarchar(10) as begin declare @st nvarchar(10) select @st=case datepart(dw,@ngay) when 1 then N'chủ nhật' when 2 then N'thứ hai' when 3 then N 'thứ ba' when 4 then N 'thứ tư' when 5 then N 'thứ năm' when 6 then N 'thứ sáu' else N 'thứ bảy' end return (@st) /* trị trả về của hàm */ end

Sau khi chạy thành công, hàm trở thành một đối tượng trong CSDL và có thể được truy xuất như các hàm được xây dựng sẵn trong SQL Server 2005 Express Edition.

Ví dụ:

select CUSTOMERNAME, dbo.f_thu(BIRTHDAY) from customers

2 Hàm nội tuyến - Inline UDF

Hàm nội tuyến được định nghĩa bằng lệnh CREATE FUNCTION.

CREATE FUNCTION tên_hàm ([danh_sách_tham_s ố]) RETURNS TABLE AS RETURN (câu_lệnh_select)

Cú pháp của hàm nội tuyến phải tuân theo các qui tắc sau:

Kiểu trả về của hàm phải được chỉ định bởi mệnh đề RETURNS TABLE.

Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT. Ngoài ra, không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm.

Ví dụ: Ví dụ dưới đây lấy ra các khách hàng tùy thuộc vào giá trị mã khách hàng truyền vào cho tham số.

create function f_SelectCustomer ( @customerid int ) returns table as return (select * from customers where customerid > @customerid)

Việc gọi các hàm nội tuyến cũng tương tự như việc gọi các hàm vô hướng.

Ví dụ:

select tmp.CUSTOMERNAME, o.ORDERDATE from orders o inner join dbo.f_SelectCustomer(3) as tmp on o.customerid = tmp.customerid

3 Hàm bao gồm nhiều câu lệnh bên trong – Multi statement UDF

Hàm này cũng được định nghĩa bằng lệnh CREATE FUNCTION

CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS @biến_bảng TABLE định_nghĩa_bảng AS BEGIN các_câu_lệnh_trong_thân_hàm RETURN END

Lưu ý: sau từ khóa RETURNS là một biến bảng được định nghĩa. Và sau từ khóa

RETURN ở cuối hàm không có tham số nào đi kèm.

Ví dụ:

create function f_SelectCustomer (@customerid int) returns @myCustomers table ( customerid int, customername nvarchar(50), orderdate datetime ) as begin if @customerid = 0 insert into @myCustomers select c.customerid, c.customername, o.orderdate from customers c inner join orders o on o.customerid = c.customerid else insert into @myCustomers select c.customerid, c.customername, o.orderdate from customers c inner join orders o on c.customerid = o.customerid where c.customerid = @customerid return end

Việc gọi hàm multi statement UDF cũng tương tự các loại hàm khác

4 Thay đổi hàm

Dùng lệnh ALTER FUNCTION để thay đổi định nghĩa h àm. Cấu trúc của câu lệnh ALTER FUNCTION tương tự như CREATE FUNCTION

Ví dụ:

alter function f_SelectCustomer ( @customerid int ) returns table as return (select * from customers where customerid > @customerid)

5 Xóa hàm

Dùng lệnh DROP FUNCTION để xóa hàm. Cấu trúc lệnh DROP FUNCTION như sau

DROP FUNCTION tên_hàm

Ví dụ:

drop function f_thu

Tương tự như thủ tục lưu trữ, khi hàm bị xóa các quyền cấp cho người dùng trên hàm đó cũng bị xóa. Do đó khi định nghĩa lại hàm này, ta phải cấp lại quyền cho các người dùng.

Tags: Sql 2005sql server 2005giáo trình sql servergiáo trình sql servertự học sql 2005tự học sql 2005tìm hiểu sql serversql serverHàm do người dùng định nghĩa (User Defined Function-UDF) trong SQL Server