Sử dụng hàm do người dùng định nghĩa và tạo khung nhìn view.
➢ Khai báo,
tạo function
➢ Thực thi các function
➢ Tạo và sử dụng khung nhìn
view
YÊU CẦU:
Bài 1:
Viết các hàm:
➢ Nhập vào MaNV cho biết tuổi của nhân viên này.
➢ Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
➢ Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo phái
➢ Truyền tham số đầu vào là tên phòng, tính mức lương trung bình của phòng
đó, Cho biết
họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên
mức lương trung bình
của phòng đó.
➢ Tryền tham số đầu vào là Mã Phòng, cho biết tên phòng ban, họ tên người
trưởng phòng
và số lượng đề án mà phòng ban đó chủ trì.
Bài 2:
Tạo các view:
➢ Hiển thị thông tin HoNV,TenNV,TenPHG, DiaDiemPhg.
➢ Hiển thị thông tin TenNv, Lương, Tuổi.
➢ Hiển thị tên phòng ban và họ tên trưởng phòng của phòng ban có đông nhân
viên nhất
THỰC HIỆN:
Bài 1:
Viết các hàm:
➢ Nhập vào MaNV cho biết tuổi của nhân viên này.
-- 1.1. Nhập vào MaNV cho biết tuổi của nhân viên này
CREATE OR ALTER FUNCTION Tuoi_NV (@maNV NVARCHAR(9))
RETURNS INT
AS
BEGIN
RETURN (SELECT YEAR(GETDATE()) - YEAR(NgSinh)
FROM NhanVien
WHERE MaNV = @maNV);
END
GO
-- cách gọi hàm kiểm tra tuổi
SELECT dbo.Tuoi_NV('005') AS Tuoi;
GO
-- hoặc
PRINT 'Tuoi cua nhan vien: ' + CAST(DBO.Tuoi_NV('005') AS NVARCHAR(20))
GO
➢ Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
-- 1.2. Nhập vào Manv cho biết số lượng đề án nhân viên này đã tham gia
CREATE OR ALTER FUNCTION SoLuong_DeAnDaThamGia (@maNV NVARCHAR(9))
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM PhanCong
WHERE Ma_NVien = @maNV);
END
GO
-- gọi hàm
SELECT dbo.SoLuong_DeAnDaThamGia('003') AS SoLuong_DeAnDaThamGia;
GO
➢ Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo
phái
-- 1.3. Truyền tham số vào phái nam hoặc nữ, xuất số lượng nhân viên theo phái
CREATE OR ALTER FUNCTION SoLuong_NhanVien_TheoPhai (@phai NVARCHAR(4))
RETURNS INT
AS
BEGIN
RETURN (SELECT COUNT(*)
FROM NhanVien
WHERE Phai = @phai);
END
GO
-- Gọi hàm
SELECT dbo.SoLuong_NhanVien_TheoPhai(N'Nữ') AS SoLuong_NhanVien;
GO
SELECT dbo.SoLuong_NhanVien_TheoPhai(N'Nam') AS SoLuong_NhanVien;
GO
➢ Truyền tham số đầu vào là tên phòng, tính mức lương trung bình của phòng
đó, Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên
mức lương trung bình của phòng đó.
-- 1.4. Truyền tham số đầu vào là tên phòng, tính mức lương trung bình của phòng đó, Cho biết họ tên nhân viên (HONV, TENLOT, TENNV) có mức lương trên mức lương trung bình của phòng đó
CREATE OR ALTER FUNCTION LuongNV_TrenLuongTB (@tenPhG NVARCHAR(15))
RETURNS @nV TABLE (HoTen NVARCHAR(45))
AS
BEGIN
DECLARE @luongTB FLOAT = (SELECT AVG(Luong)
FROM NhanVien
WHERE PhG = (SELECT MaPhG FROM PhongBan WHERE TenPhG = @tenPhG));
INSERT INTO @nV
SELECT CONCAT(HoNV, ' ', TenLot, ' ', TenNV) -- hoặc dùng concat để nối chuỗi
FROM NhanVien
WHERE Luong > @luongTB;
RETURN;
END
GO
-- Gọi hàm
SELECT *
FROM dbo.LuongNV_TrenLuongTB(N'CNTT');
GO
Hoặc
-- hoặc
CREATE OR ALTER FUNCTION fn_Cau_14 (@tenPhG NVARCHAR(15))
RETURNS TABLE
AS
RETURN
(
SELECT honv, tenlot, tennv
FROM nhanvien
WHERE luong > (
SELECT AVG(luong)
FROM nhanvien
WHERE phg = (SELECT maphg FROM PhongBan WHERE tenPhG = @tenPhG)
)
)
GO
--Gọi Hàm
SELECT *
FROM fn_Cau_14('CNTT')
GO
➢ Tryền tham số đầu vào là Mã Phòng, cho biết tên phòng ban, họ tên người
trưởng phòng
và số lượng đề án mà phòng ban đó chủ trì.
-- 1.5. Tryền tham số đầu vào là Mã Phòng, cho biết tên phòng ban, họ tên người trưởng phòng và số lượng đề án mà phòng ban đó chủ trì
CREATE OR ALTER FUNCTION Tim_PhongBan (@maPhg INT)
RETURNS TABLE
AS
RETURN (SELECT TenPhG, CONCAT(HoNV, ' ', TenLot, ' ', TenNV) AS HoTenNV, COUNT(MaDA) AS SoLuong_DeAn
FROM PhongBan
JOIN NhanVien ON PhongBan.TrPhG = NhanVien.MaNV
JOIN DeAn ON PhongBan.MaPhG = DeAn.Phong
WHERE MaPhG = @maPhg
GROUP BY TenPhG, HoNV, TenLot, TenNV);
GO
-- Gọi hàm
SELECT *
FROM dbo.Tim_PhongBan(5);
GO
Bài 2:
Tạo các view:
➢ Hiển thị thông tin HoNV,TenNV,TenPHG, DiaDiemPhg.
-- 2.1. Hiển thị thông tin HoNV, TenNV, TenPHG, DiaDiemPhg
CREATE OR ALTER VIEW View_ThongTinNhanVien
AS
SELECT HoNV, TenNV, TenPhG, DiaDiem
FROM NhanVien
JOIN PhongBan ON NhanVien.PhG = PhongBan.MaPhG
JOIN DiaDiem_PhG ON PhongBan.MaPhG = DiaDiem_PhG.MaPhG;
GO
-- Thực hiện select dữ liệu trong view vừa tạo
SELECT *
FROM View_ThongTinNhanVien;
GO
➢ Hiển thị thông tin TenNv, Lương, Tuổi.
-- 2.2. Hiển thị thông tin TenNv, Lương, Tuổi
CREATE OR ALTER VIEW View_ThongTinNhanVien_Tuoi
AS
SELECT TenNV, Luong, YEAR(GETDATE()) - YEAR(NgSinh) AS Tuoi
FROM NhanVien
GO
SELECT *
FROM View_ThongTinNhanVien_Tuoi;
GO
➢ Hiển thị tên phòng ban và họ tên trưởng phòng của phòng ban có đông nhân
viên nhất
-- 2.3. Hiển thị tên phòng ban và họ tên trưởng phòng của phòng ban có đông nhân viên nhất
CREATE OR ALTER VIEW View_PhongBan_MaxNV
AS
SELECT TOP(1) WITH TIES COUNT(NhanVien.PhG) AS SLNV
, TenPhG
, CONCAT(NV.HoNV, ' ', NV.TenLot, ' ', NV.TenNV) AS HoTenTP
FROM NhanVien
JOIN PhongBan ON NhanVien.PhG = PhongBan.MaPhG
JOIN NhanVien NV ON PhongBan.TrPhG = NV.MaNV
GROUP BY NhanVien.PhG, TenPhG, NV.HoNV, NV.TenLot, NV.TenNV
ORDER BY SLNV DESC
GO
SELECT *
FROM View_PhongBan_MaxNV;
GO
