Bài giảng Hệ quản trị Cơ sở dữ liệu SQL Server - Bài 10: Hàm do người dùng định nghĩa (UDF – User Defined Function)

12/8/2018 Microsoft SQL Server 2005 1 Bài 10: Hàm do người dùng định nghĩa (UDF – User Defined Function) 2/8/2018 Microsoft SQL Server 2005 2 Khái niệm n Là một chương trình con: - Nhận các giá trị đầu vào - Trả về một giá trị n 2 nhóm hàm: - Hàm xác định (deterministic) - Hàm không xác định (non - deterministic) 2/8/2018 Microsoft SQL Server 2005 3 Tạo mới hàm n Cú pháp: Create Function Tên_Function (các_tham_số) Returns Kiểu_dữ_liệu_trả_về As Begin Các_lệnh Return End Lưu ý: - Các tham số phải bắt đầu bằng ký hiệu @ - Một hàm có thể không có tham số - Kiểu dữ liệu trả về có thể là một giá trị vô hướng hoặc một bảng dữ liệu 2/8/2018 Microsoft SQL Server 2005 4 Tạo mới hàm n Ví dụ về tạo hàm đơn trị: Tạo hàm chuyển chuỗi ngày tháng năm sang xâu ký tự theo mã 112 Create Function Namthang(@d datetime) Returns char(6) As Begin Declare @st char(6) Set @st=convert(char(6),@d,112) Return @st End 22/8/2018 Microsoft SQL Server 2005 5 Tạo mới hàm n Tạo hàm trả về một bảng: - Dạng Inline Table (hàm đọc bảng) Ví dụ: Xây dựng hàm đọc bảng hiển thị danh sách vật tư thuộc loại tivi Create Function DSachTivi() Returns Table As Return (Select * From VATTU where left(MaVTu,2) = 'TV') 2/8/2018 Microsoft SQL Server 2005 6 Tạo mới hàm - Dạng MultiStatement Table (hàm tạo bảng): Ví dụ: Tạo hàm tạo bảng lưu danh sách hàng xuất với giá khuyến mại, tham số truyền vào là mã vật tư và % khuyến mại Create Function DSHangxuat(@MaVTu char(4), @Phantram numeric) Returns @DSHangxuat Table (SoPX char(4), MaVT char(4), SlXuat int, Giakhuyenmai money) 2/8/2018 Microsoft SQL Server 2005 7 Tạo mới hàm - Dạng MultiStatement Table (hàm tạo bảng) (tiếp): As Begin Insert Into @DSHangxuat(SoPX,MaVT,SlXuat,Giakhuyenmai) Select * From CTPXUAT where MaVTu = @MaVTu Update @DSHangxuat Set Giakhuyenmai= Giakhuyenmai*(100-@Phantram)/100 Return End 2/8/2018 Microsoft SQL Server 2005 8 Tạo mới hàm n Các lưu ý khi tạo hàm: Với hàm đọc bảng: - Phần thân hàm không đặt trong Begin End - Câu lệnh Select có các hạn chế như khi tạo bảng ảo Với hàm tạo bảng: - Không thể gọi một thủ tục nội tại từ bên trong hàm - Không thể sử dụng các hàm thuộc loại không xác định như getdate(), rand(), - Không thể sử dụng hàm để sửa đổi thông tin trong các bảng cơ sở 32/8/2018 Microsoft SQL Server 2005 9 Quản lý hàm n Sửa cấu trúc hàm: Cú pháp tương tự cú pháp tạo hàm, chỉ thay Create bằng Alter n Đổi tên hàm: sử dụng cú pháp sp_rename Tên_cũ, Tên_mới Ví dụ: sp_rename DSHangxuat, DSVattuxuat 2/8/2018 Microsoft SQL Server 2005 10 Quản lý hàm n Xem nội dung hàm: Cú pháp: sp_helptext Tên_hàm Ví dụ: sp_helptext DSVattuxuat n Xóa hàm: Cú pháp: Drop Function Tên_hàm Ví dụ: Drop Function DSVattuxuat Lưu ý: các thao tác trên có thể thực hiện trực tiếp từ cửa sổ Object Explorer 2/8/2018 Microsoft SQL Server 2005 11 Gọi thực hiện hàm n Cú pháp gọi hàm: dbo.Tên_hàm(danh_sách_các_tham_số_thực_sự) - Gọi hàm đơn trị: Có thể thực hiện lời gọi tại bất kỳ vị trí nào mà biểu thức đơn trị có kiểu dữ liệu tương đương được chấp nhận Ví dụ: Print dbo.Namthang('02/12/2010') - Gọi hàm đa trị: Ví dụ: Select * from dbo.DSachTivi() Select * from dbo.DSHangxuat('DD01',10) 2/8/2018 Microsoft SQL Server 2005 12 Hàm đệ quy n Xây dựng hàm tính giai thừa: Create Function Giaithua(@n bigint) Returns Bigint As Begin Declare @i bigint IF @n>20 or @n is null set @n=null else if @n<2 set @i=@n else set @i=@n*dbo.Giaithua(@n-1) Return @i End