hệ quản trị csdl sql server - chương 9 function
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (127.29 KB, 14 trang )
Chương 9:
FUNCTION
Giáo viên: Tạ Thúc Nhu
Khoa CNTT trường ĐH Lạc Hồng
HỆ QUẢN TRỊ CSDL
SQL SERVER
Mã hó
a
2
I-Khái niệm:
I-Khái niệm:
Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục. Điểm khác
biệt giữa hàm vàthủ tục là:
• Hàm trả về một giátrị vô hướng (scalar value) hoặc một bảng
dữ liệu. Điều này cho phép ta sử dụng hàm trong biểu thức hoặc
trong mệnh đề FROM.
• Hàm không cótham số Output.
Mã hó
a
3
II-Tạo hàm trả về giátrị vô hướng (Scalar):
II-Tạo hàm trả về giátrị vô hướng (Scalar):
CREATEFUNCTION tên_hàm ([danh_sách_tham_số])
RETURNS <kiểu dữ liệu trả về của hàm>
AS
BEGIN
Các_câu_lệnh_của_hàm
RETURN [<giá_trị>]
END
• Hàm không trả về giátrị kiểu timestamp.
Mã hó
a
4
Vídụ: hàm trả về thứ trong tuần
Vídụ: hàm trả về thứ trong tuần
CREATEFUNCTION func_Thu(@ngay DATETIME)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @S NVARCHAR(10)
SELECT @S= 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 @S /* Trị trả về của hàm */
END
Mã hó
a
5
Gọi thực hiện hàm Scalar
Gọi thực hiện hàm Scalar
• Trước tên hàm ta phải chỉ định thêm dbo (database owner).
Vídụ:
-Gọi hàm trong phát biểu Select
SELECT SoHD, dbo.func_Thu(NGAYHD)+ ', ' + Convert(VarChar,NgayHD,103)
FROM HoaDon
-Gọi hàm trong phát biểu EXEC
DECLARE @Thu int
Exec @Thu = dbo.func_Thu ‘5/15/2009’
Print @Thu
Mã hó
a
6
III-Tạo hàm trả về một bảng dữ liệu
III-Tạo hàm trả về một bảng dữ liệu
• Có2 loại hàm trả về một bảng dữ liệu:
–Hàm nội tuyến (Inline table-valued functions)
–Hàm nhiều phát biểu (Multistatement table-valued
functions)
• Hàm trả về bảng dữ liệu cóthể được sử dụng trong mệnh
đề FROM.
• Khi gọi hàm trả về bảng dữ liệu không cần chỉ định tên
người dùng như hàm Scalar
Mã hó
a
7
1-Tạo hàm nội tuyến:
(Inline table-valued functions)
1-Tạo hàm nội tuyến:
(Inline table-valued functions)
CREATEFUNCTION tên_hàm ([danh_sách_tham_số])
RETURNS TABLE
AS
RETURN (câu_lệnh_select)
Mã hó
a
8
Vídụ: Hàm trả về bảng chứa MaMH, số
lượng mỗi mặt hàng đã bán từ một ngày
Vídụ: Hàm trả về bảng chứa MaMH, số
lượng mỗi mặt hàng đã bán từ một ngày
CREATEFUNCTION func_banhang(@Ngay DateTime)
RETURNS TABLE
AS
RETURN(SELECT MaMH,
SUM(ISNULL(SL,0)) AS TongSL
FROM CTHD Inner Join HoaDon hd
On CTHD.SoHD=hd.SoHD
Where NgayHD >= @Ngay
GROUP BY MaMH )
Mã hó
a
9
Sử dụng hàm đã định nghĩa để tính số tồn ở
đầu một tháng nào đó:
Sử dụng hàm đã định nghĩa để tính số tồn ở
đầu một tháng nào đó:
SELECT a.MaMH, a.TenMH, SoTon -TongSL
FROM MatHang a INNER JOIN func_banhang('2007/5/1') b
ON a.MaMH=b.MaMH
Mã hó
a
10
2-Hàm nhiều phát biểu:
(Multistatement table-valued functions)
2-Hàm nhiều phát biểu:
(Multistatement table-valued functions)
CREATEFUNCTION tên_hàm([danh_sách_tham_số])
RETURNS @biến_bảng TABLE (cấu_trúc_ bảng)
AS
BEGIN
các_câu_lệnh_trong_thân_hàm
RETURN
END
Vídụ: Xây dựng hàm tạo bảng chứa số mặt hàng trong từng
loại hàng nếu MaLH=0, ngược lại chỉ hiển thị số mặt hàng
của loại hàng đã chỉ định.
Mã hó
a
11
Vídụ:
Vídụ:
CREATEFUNCTION Func_TongMH(@MaLH SMALLINT = 0)
RETURNS @bangthongke TABLE(MaLH NVARCHAR(5),
TenLH NVARCHAR(50), TongSoMH INT )
AS
BEGIN
IF @MaLH=0
INSERT @bangthongke SELECT lh.MaLH,TenLH,COUNT(*)
FROM LoaiHang lh INNER JOIN MatHang mh ON lh.MaLH=mh.MaLH
GROUP BY lh.MaLH,TenLH
ELSE
INSERT @bangthongke SELECT lh.MaLH,TenLH,COUNT(*)
FROM LoaiHang lh INNER JOIN MatHang mh ON lh.MaLH=mh.MaLH
WHERE lh.MaLH = @MaLH GROUP BY lh.MaLH,TenLH
RETURN /*Trả kết quả về cho hàm*/
END
Mã hó
a
12
Thực hiện hàm
Thực hiện hàm
• Thống kê tổng số mặt hàng của loại hàng mã số 25:
SELECT * FROM func_TongMH(25)
• Sử dụng giátrị mặc định của tham số:
SELECT * FROM func_TongSV(DEFAULT)
Cho ta biết tổng số mặt hàng hiện cócủa mỗi loại hàng
Mã hó
a
13
V-Sửa XóaHàm:
V-Sửa XóaHàm:
1. Đổi Tên hàm:
sp_Rename <OldName>, <NewName>
2. Xóa hàm:
DROP FUNCTION <spName>
3. Thay đổi nội dung hàm:
ALTERFUNCTION func_Name ([danh_sách_tham_số])
RETURNS <kiểu dữ liệu trả về của hàm>
AS
BEGIN
Các_câu_lệnh_của_hàm
RETURN [<giá_trị>]
END
Mã hó
a
14
V-Xem Thông Tin Hàm:
V-Xem Thông Tin Hàm:
1. Liệt kê danh sách hàm trong CSDL hiện hành:
Select * From Sys.Objects
Where Type In (‘FN’, 'IF', 'TF')
2. Liệt kê danh sách tham số của thủ tục:
Select* From sys.Parameters
Where Object_ID= Object_ID(' func_Thu‘)
3. Xem nội dung thủ tục: SP_HELPTEXT ‘func_Name’
