3. Các store function – Các hàm

1. Các khái niệm

Tất cả các ngôn ngữ lập trình, bao gồm cả T-SQL, việc có các hàm tạo cho các ứng dụng trở lên mạnh mẽ. Ngoài ra, người lập trình có thể tự tạo một hàm riêng cho mình làm cho hệ thống dễ được mở rộng.

Một hàm - function - trong SQL Server được định nghĩa là một thủ tục

đơn giản bao gồm một nhóm các câu lệnh SQL

2. Tạo các hàm

Một hàm do người dùng định nghĩa được tạo bằng cách sử dụng câu lệnh CREATE FUNCTION theo cú pháp sau:

CREATE FUNCTION [owner_name.]function_name (

[ {@parameter_name scalar_data_type [= default]} [,...n] ]

)

RETURNS scalar_data_type | TABLE(column_definition | table_constraint [,...n])

[WITH ENCRYPTION | SCHEMABINDING [,...n] ] [AS]

[BEGIN function_body END] | RETURN [(]

select_statement [)]

Trong đó:

1. [owner_name.]: Chỉ định tên đối tượng sẽ sở hữu. Ta không phải bắt buộc chỉ định tên người sẽ tạo đối tượng sở hữu nó.
2. function_name: tên của hàm ta sẽ tạo.
3. parameter_name: Là các tham số Input cho hàm. Các tham số này xây dựng cũng tương tự như trong stored procedure.
4. scalar_data_type: Là kiểu dữ liệu vô hướng của tham số. Một hàm có thể nhận bất kỳ kiểu dữ liệu nào như là tham số trừ các kiểu timestamp, cursor, text, ntext, image.
5. default: Chỉ định giá trị mặc định cho tham số, tương tự như trong stored procedure.
6. [,...n]: Chỉ định một hàm có thể tạo nhiều tham số. Một hàm trong SQL Server có thể chứa tới 1024 tham số.
7. RETURNS: từ khóa này chỉ định kiểu dữ liệu hàm sẽ trả về. Kiểu dữ liệu của hàm có thể là một kiểu dữ liệu vô hướng hoặc một bảng.
8. scalar_data_type: Ta sẽ chỉ định kiểu dữ liệu nếu như hàm trả về một giá trị vô hướng. Ở đây ta phải chỉ định kiểu độ dài dữ liệu.
9. TABLE: Đây là kiểu dữ liệu cho phép hàm có thể trả về nhiều dòng dữ liệu.
10. column_definition: Định nghĩa các cột cho kiểu dữ liệu TABLE. Các cột này được định nghĩa tương tự như định nghĩa các cột trong bảng.
11. table_constraint: Định nghĩa các ràng buộc trong kiểu dữ liệu TABLE này.
12. [,...n]: Chỉ định có thể có nhiều cột và nhiều ràng buộc trong bảng.
13. WITH ENCRYPTION: Từ khóa chỉ định code của hàm sẽ được mã hóa trong bảng syscomments.
14. SCHEMABINDING:Từ khóa này chỉ định hàm được tạo để buộc vào tất cả các đối tượng mà nó tham chiếu.
15. [,...n]: Chỉ dịnh có thể có nhiều từ khóa khác ngoài hai từ khóa trên.
16. AS: Từ khóa cho biết code của hàm bắt đầu.
17. BEGIN: Đi cùng với END để tạo thành bao khối bao các câu lệnh trong thân hàm.
18. function_body: thân của hàm.
19. END: Đi cùng với BEGIN để tạo thành bao khối bao các câu lệnh trong thân hàm.
20. RETURN: Từ khóa này sẽ gửi giá trị tới thủ tục gọi hàm.
21. select_statement: đi kèm với RETURN để gửi giá trị tới thủ tục gọi hàm.

3. Các ví dụ tạo các hàm

Ví dụ 4.38. Xây Dựng một hàm fncGetThreeBusinessDays trả về ngày làm việc thứ 3 tính từ ngày bắt đầu @dtmDateStart.  

CREATE FUNCTION fncGetThreeBusinessDays (@dtmDateStart DATETIME)

RETURNS DATETIME

AS

BEGIN

IF DATEPART(dw, @dtmDateStart) = 4 BEGIN

RETURN(DATEADD(dw, 5, @dtmDateStart))

END

ELSE IF DATEPART(dw, @dtmDateStart) = 5 BEGIN

RETURN(DATEADD(dw, 5, @dtmDateStart))

END

ELSE IF DATEPART(dw, @dtmDateStart) = 6 BEGIN

RETURN(DATEADD(dw, 5, @dtmDateStart))

END

ELSE IF DATEPART(dw, @dtmDateStart) = 7 BEGIN

RETURN(DATEADD(dw, 4, @dtmDateStart))

END

RETURN(DATEADD(dw, 3, @dtmDateStart))

END

Thực hiện thử nghiệm hàm trên, ta xây dựng scipt sau:

DECLARE @dtmDate DATETIME SELECT @dtmDate = '1/10/2008' SELECT DATENAME(dw, @dtmDate) SELECT DATENAME(dw, dbo.fncGetThreeBusinessDays(@dtmDate))

Ví dụ 4.39. Sử dụng hàm fncGetThreeBusinessDays trên để tính toán trên một cột trong một bảng.  

CREATE TABLE OrderInfo (

OrderID INT NOT NULL,

ShippingMethod VARCHAR(16) NOT NULL,

OrderDate DATETIME NOT NULL DEFAULT GETDATE(),

ExpectedDate AS(

dbo.fncGetThreeBusinessDays(OrderDate)

)

) GO

INSERT OrderInfo VALUES (1, 'UPS GROUND', GETDATE()) INSERT OrderInfo VALUES (2, 'FEDEX STANDARD',

DATEADD(dd, 2, GETDATE())) INSERT OrderInfo VALUES (3, 'PRIORITY MAIL',

DATEADD(dd, 4, GETDATE()))

GO

SELECT OrderID,ShippingMethod,CONVERT(VARCHAR(12), OrderDate,1) + '(' + DATENAME(dw, OrderDate) + ')'

AS 'OrderDate', CONVERT(VARCHAR(12),ExpectedDate,1)+ '('+ DATENAME(dw, ExpectedDate) + ')' AS 'ExpectedDate'

FROM OrderInfo

Ví dụ 3.40. Xây dựng hàm trả về các dòng dữ liệu gồm thông tin về điểm của các môn học theo Mã lớp.

CREATE FUNCTION fncBangDiem(@MaLop CHAR(10))

RETURNS @TableName TABLE ( MaSV CHAR(10),

Hoten nvarchar(100), TenMH NVARCHAR(50), DiemL1 INT,

DiemL2 INT )

AS BEGIN

INSERT INTO @TableName

SELECT Di.MaSV, Ho.HoDem + ' '+ Ho.TenSV, Mo.TenMH,

Di.DiemL1,Di.DiemL2

FROM DIEM Di

JOIN HOSOSV Ho ON (Di.MaSV = Ho.MaSV) JOIN MONHOC Mo ON (Di.MaMH = Mo.MaMH)

WHERE Ho.MaLop = @MaLop RETURN

END GO

Thử nghiệm gọi hàm này trong đoạn script chương trình sau và kết quả cho trong hình 4.10: Select * from fncBangDiem('TH03A')

CREATE FUNCTION fncDSSV(@MaLop CHAR(10)) RETURNS TABLE

AS RETURN

SELECT MaSV, HoDem + ' '+ TenSV As Hoten, NgaySinh FROM HOSOSV

WHERE MaLop=@MaLop

GO

Gọi hàm này trong đoạn script chương trình sau: Select * from fncDSSV ('TH03A')

4. Thay đổi, xóa, xem nội dung store function

a) Thay đổi các hàm

Để thay đổi các hàm ta dùng câu lệnh ALTER FUNCTION.

ALTER FUNCTION [owner_name.]function_name (

[ {@parameter_name scalar_data_type [= default]} [,...n] ]

)

RETURNS scalar_data_type | TABLE(column_definition | table_constraint [,...n])



[WITH ENCRYPTION | SCHEMABINDING [,...n] ] [AS]

[BEGIN function_body END] | RETURN [(]

select_statement [)]

Ví dụ 4.42. Thay đổi hàm fncDSSV

ALTER FUNCTION fncDSSV(@MaLop CHAR(10)) RETURNS TABLE

AS RETURN

SELECT MaSV, HoDem + ' '+ TenSV As Hoten FROM HOSOSV

WHERE MaLop=@MaLop

GO

b) Xóa hàm

Để xóa hàm ta dùng câu lệnh DROP FUNCTION.

DROP FUNCTION { [ schema_name. ] function_name }

Ví dụ 4.43. Xóa hàm fncDSSV DROP FUNCTION fncDSSV

Được cập nhật: 14 tháng 4 lúc 10:08:38 | Lượt xem: 469