HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 9:FUNCTION

Chia sẻ: Nguyễn Mạnh Cường | Ngày: | Loại File: PDF | Số trang:14

Thêm vào BST

Báo xấu

538
lượt xem 92
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: HỆ QUẢN TRỊ CSDL SQL SERVER - Chương 9:FUNCTION

HỆ QUẢN TRỊ CSDL SQL SERVER Chương 9: FUNCTION Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng
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 2
II- Tạo hàm trả về giá trị vô hướng (Scalar): CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS AS BEGIN Các_câu_lệnh_của_hàm RETURN [] END • Hàm không trả về giá trị kiểu timestamp. Mã hóa 3
Ví dụ: hàm trả về thứ trong tuần CREATE FUNCTION 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 4
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 5
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 6
1- Tạo hàm nội tuyến: (Inline table-valued functions) CREATE FUNCTION tên_hàm ([danh_sách_tham_số]) RETURNS TABLE AS RETURN (câu_lệnh_select) Mã hóa 7
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 CREATE FUNCTION 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 8
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 9
2- Hàm nhiều phát biểu: (Multistatement table-valued functions) CREATE FUNCTION 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 10
Ví dụ: CREATE FUNCTION 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 11
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 12
V- Sửa Xóa Hàm: 1. Đổi Tên hàm: sp_Rename , 2. Xóa hàm: DROP FUNCTION 3. Thay đổi nội dung hàm: ALTER FUNCTION func_Name ([danh_sách_tham_số]) RETURNS AS BEGIN Các_câu_lệnh_của_hàm RETURN [] END Mã hóa 13
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’ Mã hóa 14