Bài giảng SQL - Chương 5: Function

FUNCTIONNội dung chi tiếtGiới thiệuTạo FunctionHiệu chỉnh FunctionXóa FunctionVí dụ2Giới thiệuKhái niệm hàm người dùng (user define function):Hầu hết các ngôn ngữ lập trình, hay cơ sở dữ liệu lớn, luôn luôn có một phần mở rộng cho phép người dùng tự định nghĩa một số quy tắc, hàm hoặc thủ tục.User define function giống như stored procedure của SQL server. Hàm người dùng cũng có thể truyền tham số nhưng không được mang thuộc tính OUTPUT. Thay vào đó chúng ta dùng câu lệnh RETURN.3Giới thiệu (tt)Có hai loại hàm do người dùng định nghĩa:Hàm người dùng trả về giá trịHàm người dùng trả về một bảng dữ liệu4Giới thiệuFunction cho phép:Nhận tham số vàoGọi hàm bằng lệnh EXECUTE giống như gọi PROCEDURE (đối với hàm trả về kiểu vô hướng)5Giới thiệuChú ý:Hàm do người dùng định nghĩa không dùng giá trị với kiểu dữ liệu ntext, text, image, cursor, timestamp làm giá trị trả về.Có thể cung cấp thông tin về lỗi nếu phát sinh.Có thể sử dụng các hàm do người dùng định nghĩa trong các câu lệnh SQL như SELECT.6Nội dung chi tiếtGiới thiệuTạo FunctionHiệu chỉnh FunctionXóa FunctionVí dụ7Tạo Function (trả về giá trị)Cú phápCREATE FUNCTION ([ @ [ = ]][ ,...n ] )RETURNS [ AS ]     BEGIN                          RETURN     END 8Tạo Function (tt)Ví dụ: tính tổng 2 số nguyênGọi FunctionCách 1:PRINT 'TONG: ' + STR(DBO.TONG(6,3))Cách 2:DECLARE @TONG INT--EXEC DBO.TONG 6,3SET @TONG = DBO.TONG(6,3)SELECT @TONG CREATE FUNCTION TONG(@a INT, @b INT) RETURNS INTBEGIN RETURN @a+@bEND 9Tạo Function (tt)Hàm tự viết:Chuyển ngày về đạng DDMMYYYY:create Function dbo.dngayDDMMYYYY(@date smalldatetime)returns varchar(10)ASBegin return convert(varchar(10),@Date,103)End10Ứng dụng hàm người dùng:select MaNV, Hoten, dbo.dngayDDMMYYYY(Ngaysinh), Phaifrom NHANVIEN11Hàm người dùng trả về một bảng dữ liệu:Ngoài giá trị trả về là kiểu số nguyên, số thực, kiểu ký tự Hàm người dùng còn cho phép giá trị trả về là một bảng dữ liệu. Khi đó, công việc này rất giống với VIEW.Tuy nhiên VIEW không thể sử dụng giá trị từ bên ngoài truyền vào.12Tạo Function (trả về bảng dữ liệu)Cú phápCREATE FUNCTION [owner_name.] ten_ham (@ten_bien [AS] kieu_gia_tri_bien [ = default ] ,...) RETURNS TABLEASRETURN (cau_lenh_select)Chú ý: phần thân của hàm chỉ cho phép sự xuất hiện duy nhất của câu lệnh RETURN. 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.13Ví dụ:create function dbo.HienthiNV()returns TableAS Return (Select * from NHANVIEN)Ứng dụng:Sau khi tạo thành công. Chúng ta có thể sử dụng hàm như sau:select * from dbo.HienthiNV()14Tạo Function (trả về bảng dữ liệu)Ứng dụng hàm người dùng trả về bảng dữ liệu (trường hợp có tham số truyền vào):Create function dbo.HienthiNV(@MaNV varchar(4))returns TableASReturn (select MaNV, Hoten, Ngaysinh, Phai from NHANVIEN where MaNV like @MaNV)15Tạo Function (trả về bảng dữ liệu)Trong trường hợp cần phải sử dụng đến nhiều câu lệnh trong phần thân của hàm, ta sử dụng cú pháp như sau để định nghĩa hàm:CREATE FUNCTION tên_hàm([danh_sách_tham_số])RETURNS @biến_bảng TABLE định_nghĩa_bảngASBEGIN các_câu_lệnh_trong_thân_hàm RETURNEND16Khi định nghĩa hàm dạng này cần lưu ý một số điểm sau:Cấu trúc của bảng trả về bởi hàm được xác định dựa vào định nghĩa của bảng trong mệnh đề RETURNS.Biến @biến_bảng trong mệnh đề RETURNS có phạm vi sử dụng trong hàm và được sử dụng như là một tên bảng.Câu lệnh RETURN trong thân hàm không chỉ định giá trị trả về.Giá trị trả về của hàm chính là các dòng dữ liệu trong bảng có tên là @biến_bảng được định nghĩa trong mệnh đề RETURNS.17Ví dụ:create function F4(@MaNV varchar(5))returns @Tonghop table( MaPX int, MaNV varchar(5))as begin if (@MaNV='') INSERT INTO @Tonghop select MaPX, MaNV from PHIEUXUAT else INSERT INTO @Tonghop select MaPX, N.MaNV from NHANVIEN N, PHIEUXUAT P where N.MaNV=P.MaNV and N.MaNV=@MaNV returnend18Thực thi:select * from F4('NV01')select * from F4('')19Hiệu chỉnh FunctionCú pháp (chung cho cả 3 dạng):ALTER FUNCTION ([ @ [ = ]][ ,...n ] )RETURNS [ AS ]     BEGIN                          RETURN     END 20Hiệu chỉnh Function (tt)Ví dụ: tính tổng 3 số nguyênALTER FUNCTION TONG(@a INT, @b INT, @c INT) RETURNS INTBEGIN RETURN @a+@b+@cEND 21Nội dung chi tiếtGiới thiệuTạo FunctionHiệu chỉnh FunctionXóa FunctionVí dụ22Xóa FunctionCú phápVí dụDROP FUNCTION DROP FUNCTION TONG23Nội dung chi tiếtGiới thiệuTạo FunctionHiệu chỉnh FunctionXóa FunctionVí dụ24Ví dụ 1Tính tổng lương theo mã phòngCREATE FUNCTION TONG_LUONG_PHONG(@PHG INT) RETURNS INTBEGIN DECLARE @TONGLUONG INT SET @TONGLUONG = (SELECT SUM(LUONG) FROM NHANVIEN WHERE PHG = @PHG) RETURN @TONGLUONGEND 25Ví dụ 2Tính tổng thời gian theo tên đề ánCREATE FUNCTION TONG_TG_DA(@TENDA NVARCHAR(40)) RETURNS FLOATBEGIN DECLARE @TONG_TG FLOAT SET @TONG_TG = (SELECT SUM(THOIGIAN) FROM PHANCONG, DEAN WHERE TENDA = @TENDA AND SODA = MADA) RETURN @TONG_TG END 26