Giáo trình Hệ quản trị cơ sở dữ liệu - Chương 8: Hàm (Function) - Lê Thị Minh Nguyên

8/25/2017 1 Chương 8. Hàm (Function) GV: Lê Thị Minh Nguyện Email: nguyenltm@huflit.edu.vn Nội dung 1. Khái niệm 2. Các loại Function 3. Hàm Scalar 4. Hàm Inline table valued 5. Hàm Multi statement table valued 6. Nhận xét Hệ quản trị Cơ sở dữ liệu 2 1. Khái niệm • Cũng giống như Stored Procedure Hàm là một đối tượng trong cơ sở dữ liệu bao gồm một tập nhiều câu lệnh SQL được nhóm lại với nhau thành một nhóm. • Điểm khác biệt giữa hàm và thủ tục là hàm trả về một giá trị thông qua tên hàm. Điều này cho phép ta sử dụng hàm như là một thành phần của một biểu thức chẳng hạn như trong các câu lệnh truy vấn hay các câu lệnh thực hiện cập nhật dữ liệu Hệ quản trị Cơ sở dữ liệu 3 2. Các loại Function • Loại 1: Hàm được định nghĩa sẵn trong DBMS • Loại 2: Hàm do người dùng định nghĩa • Giá trị trả về là “dữ liệu kiểu bảng” • Giá trị trả về là một giá trị – Scalar-valued Hệ quản trị Cơ sở dữ liệu 4 8/25/2017 2 3. Hàm Scalar Hệ quản trị Cơ sở dữ liệu 5 Hàm vô hướng trả về duy nhất một giá trị dựa trên tham số truyền vào. Cú pháp CREATE FUNCTION func_name ([Danh_Sach_Cac_Tham_So]) RETURNS Kieu_Du_Lieu_Tra_Ve_Cua_Ham AS BEGIN các_câu_lenh_của_hàm END 3. Hàm Scalar (tt) Hệ quản trị Cơ sở dữ liệu 6 CREATE FUNCTION Test_function(@b int, @c int) RETURNS int AS BEGIN declare @kq int if @b>2 set @kq=@b+@c else set @kq=@b+@c+1 return @kq END Gọi hàm SELECT dbo. Test_function(3,4) 3. Hàm Scalar (tt) Hệ quản trị Cơ sở dữ liệu 7 CREATE FUNCTION GiaiThua(@x int) RETURNS int AS Begin Declare @i int If @x>20 OR @x IS NULL Set @i = NULL Else If @x < 2 Set @i = @x Else Set @i = @x * dbo.GiaiThua(@x-1) Return @i End SELECT dbo.GiaiThua(3) 3. Hàm Scalar(tt) Hệ quản trị Cơ sở dữ liệu 8 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 End 8/25/2017 3 3. Hàm Scalar (tt) Hệ quản trị Cơ sở dữ liệu 9 Thực thi hàm: Select MaNV, TenNV, dbo.f_thu (NgayVaoLam) From NhanVien Kết qủa: 4. Hàm Inline table valued Hàm nội tuyến, trả về dạng bảng. Cú pháp: CREATE FUNCTION func_name ([Danh_Sach_Cac_Tham_So]) RETURNS TABLE AS RETURN (câu_lenh_select) Hệ quản trị Cơ sở dữ liệu 10 • 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 4. Hàm Inline table valued (tt) Hàm nội tuyến, trả về dạng bảng. Cú pháp: CREATE FUNCTION func_name ([Danh_Sach_Cac_Tham_So]) RETURNS TABLE AS RETURN (câu_lenh_select) Hệ quản trị Cơ sở dữ liệu 11 • 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 4. Hàm Inline table valued (tt) Hệ quản trị Cơ sở dữ liệu 12 Tạo hàm trả về 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_KhachHang (@MaKH int) RETURNS TABLE AS RETURN (Select * From KhachHang Where MaKH > @MaKH) 8/25/2017 4 4. Hàm Inline table valued (tt) Hệ quản trị Cơ sở dữ liệu 13 Thực thi hàm: SELECT tmp.TenKH, dh.NgayDatHang FROM DonHang dh, dbo.f_KhachHang(3) as tmp WHERE dh.MaKH = tmp.MaKH 5. Hàm Multi statement table valued 14 Hàm gồm nhiều câu lệnh SQL bên trong, trả vê dạng bảng. Cú pháp: CREATE FUNCTION func_name ([Danh_Sach_Cac_Tham_So]) RETURNS @biến_bảng TABLE định_nghia_bảng AS BEGIN các_câu_lenh_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 15 CREATE FUNCTION f_DSKhachHang (@MaKH int) RETURNS @myKhachHang table (MaKH int, TenKH nvarchar(50), NgayDatHang datetime) AS Begin If @MaKH = 0 Insert into @myKhachHang Select kh.MaKH, kh.TenKH, dh.NgayDatHang From KhachHang kh, DonHang dh Where dh.MaKH = kh.MaKH --- 5. Hàm Multi statement table valued (tt) 16 CREATE FUNCTION f_DSKhachHang (@MaKH int) RETURNS @myKhachHang table (MaKH int, TenKH nvarchar(50), NgayDatHang datetime) AS Begin --- Else Insert into @myKhachHang Select kh.MaKH, kh.TenKH, dh.NgayDatHang From KhachHang kh, DonHang dh Where kh.MaKH=dh.MaKH and kh.MaKH = @MaKH Return End 5. Hàm Multi statement table valued (tt) 8/25/2017 5 17 Thực thi hàm: Select * From f_DSKhachHang (0) 5. Hàm Multi statement table valued (tt) Hệ quản trị Cơ sở dữ liệu 18