[Database-Tự Học SQL] COM2034_SQL Server - Lab 7: SQL Function & View

NỘI DUNG:

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

Xong!