Bài giảng Hệ quản trị cơ sở dữ liệu - Bài: Hàm do người dùng định nghĩa - Nguyễn Ngọc Quỳnh Châu

HÀM DO NGƯỜI DÙNG ĐỊNH NGHĨA (USER-DEFINED FUNCTION) KHÁI NIỆM • Là một đối tượng của CSDL • Có hai loại hàm: – Hàm do hệ quản trị CSDL cung cấp sẵn – Hàm do người dùng tự định nghĩa để phục vụ cho mục đích của riêng mình • Có thể sử dụng hàm trong các biểu thức tính toán. CÁC LOẠI HÀM • Hàm vô hướng: trả về một giá trị • Hàm thao tác trên bản ghi: trả về một bảng ĐỊNH NGHĨA HÀM VÔ HƯỚNG • Cú pháp: VÍ DỤ VỀ HÀM • Ví dụ về hàm vô hướng: viết một hàm để tính số lượng nhân viên trong một phòng CREATE FUNCTION Ham_Soluong_Phong ( @BienMaPhong CHAR (3) ) RETURNS int AS BEGIN DECLARE @SL int; SELECT @SL = COUNT (*) FROM NhanVien WHERE MaPhong=@BienMaPhong ; RETURN (@SL); END VÍ DỤ VỀ HÀM Sử dụng hàm: SELECT Ham_Soluong_Phong('KD'); SELECT MaPhong, count (*) FROM NhanVien GROUP BY MaPhong HAVING count(*) > Ham_Soluong_Phong('QT') ĐỊNH NGHĨA HÀM TRẢ VỀ BẢNG • Cú pháp 1: CREATE FUNCTION Tên_hàm ([tham số]) RETURNS TABLE BEGIN Các câu lệnh END ĐỊNH NGHĨA HÀM TRẢ VỀ BẢNG • Ví dụ 1: viết hàm để trả về danh sách nhân viên trong phòng. CREATE FUNCTION HAM_DSPHONG (@bienmaphong char(5)) RETURNS TABLE AS BEGIN RETURN (SELECT MANV, HOTEN, DIACHI FROM NHANVIEN WHERE MAPHONG=@bienmaphong) END • Ví dụ 2: Viết một hàm trả về ngày sinh của một nhân viên. Tham số của hàm là mã nhân viên. • Viết một thủ tục sử dụng hàm đó so sánh ngày sinh của hai nhân viên bất kỳ. Nếu trùng nhau thì in ra “Hai nhân viên cùng ngày sinh”. Ngược lại thì in ra “Hai nhân vien không cùng ngày sinh” VÍ DỤ VỀ HÀM • Sử dụng hàm HAM_DSPHONG: • SELECT * FROM HAM_DSPHONG(‘QT’) ĐỊNH NGHĨA HÀM TRẢ VỀ BẢNG • Cú pháp 2 ĐỊNH NGHĨA HÀM TRẢ VỀ BẢNG Ví dụ 2: sử dụng cú pháp 2 để viết lại hàm trong ví dụ 1: CREATE FUNCTION HAM_DSPHONG (@bienmaphong char(5)) RETURNS @kq TABLE (MaNV char(10), hoten nchar(40), diachi nchar(50)) AS BEGIN INSERT INTO @kq SELECT MANV, HOTEN, DIACHI FROM NHANVIEN WHERE MAPHONG=@bienmaphong RETURN END BÀI TẬP • Cho CSDL như sau: – Sinhvien (MãSV, Họ tên, Ngày sinh, Mã lớp ) – Lớp (Mã lớp, Tên lớp, Mã khóa) Câu 1: Viết hàm để tính xem một ngày nào đó là rơi vào thứ mấy trong tuần. (Gợi ý, sử dụng cấu trúc IFELSE hoặc cấu trúc CASE). Câu 2: Viết hàm cho biết (mã khóa, tổng số sinh viên) của một khóa nào đó. (Mã khóa >=1) Câu 3: Viết hàm cho biết (mã khóa, tổng số sinh viên) của một khóa nào đó.. Trong trường hợp khóa có giá trị=0 thì cho biết (mã khóa, tổng số sinh viên) của tất cả các khóa KHÁC NHAU GIỮA HÀM VÀ THỦ TỤC • Sinh viên tự tìm hiểu VIEW KHÁI NIỆM – View là bảng không chứa dữ liệu, nó chỉ là truy vấn dữ liệu từ 1 hay nhiều bảng. View được lưu thành một đối tượng của SQL SV – NSD có thể áp dụng ngôn ngữ thao tác dữ liệu trên các View giống như Table. ĐỊNH NGHĨA VIEW Cú pháp: CREATE VIEW [() ] AS Ví dụ 1: tạo View nv_kinh doanh CREAT VIEW nv_kinhdoanh AS SELECT Manv, Hoten, Luong FROM NHANVIEN * Sử dụng View: SELECT * FROM NV_KINHDOANH Ví dụ 2: tạo View nv_tre (nhân viên dưới 35 tuổi) CREAT VIEW nv_tre (Manv, Hoten, Tuoi) AS SELECT Manv, Hoten, Year(Getdate()) – Year(Ngaysinh) FROM NHANVIEN WHERE Year(Getdate()) – Year(Ngaysinh) <= 35 * Sử dụng View: SELECT * FROM NV_TRE  Nếu một thuộc tính trong View được xây dựng từ một biểu thức thì bắt buộc phải đặt tên cho thuộc tính đó. HAI LOẠI VIEW • VIEW chỉ đọc (read-only view): View này chỉ dùng để xem dữ liệu • VIEW có thể cập nhật (updatable view): – Xem dữ liệu – Có thể sử dụng câu lệnh INSERT, UPDATE, DELETE để cập nhật dữ liệu trong các bảng cơ sở qua View • Các yêu cầu để tạo view có thể cập nhật:Câu lệnh SELECT trong định nghĩa VIEW không được chứa: – Mệnh đề DISTINCT hoặc TOP. – Một hàm kết tập (Aggregate function). – Một giá trị được tính toán. – Mệnh đề GROUP BY và HAVING. – Toán tử UNION. – Mệnh đề JOIN • Nếu câu lệnh tạo View vi phạm một trong số điều kiện trên =>VIEW được tạo ra là VIEW chỉ đọc. MỤC ĐÍCH DÙNG VIEW • Hạn chế quyền truy cập dữ liệu của người dùng • Hạn chế tính phức tạp của dữ liệu đối với người sử dụng đơn giản • Tóm lược nhiều cơ sở dữ liệu vật lý vào một cơ sở dữ liệu logic TRIGGER KHÁI NIỆM • Là một stored procedure đặc biệt • Trigger không có tham số đầu vào, đầu ra • Trigger tự động chạy khi có một hành động liên quan đến nó xảy ra. • Sử dụng trong việc: – Kiểm tra dữ liệu nhập – Tính toán, cập nhật giá trị tự động • Các loại trigger: INSERT TRIGGER, DELETE TRIGGER, UPDATE TRIGGER, TRIGGER tổng hợp Cú pháp CREATE TRIGGER ON | FOR | AFTER| INSTEAD OF AS Trigger chèn: ví dụ CREATE TRIGGER trInsNV ON NHANVIEN FOR INSERT AS PRINT ‘Ban da chen thanh cong’ Trigger này tự động được thực hiện mỗi khi có bản ghi mới được chèn vào bảng NHANVIEN Trigger xóa: ví dụ CREATE TRIGGER trDelNV ON NHANVIEN FOR DELETE AS PRINT ‘Ban da xoa thanh cong’ Trigger này tự động được thực hiện mỗi khi có một hoặc nhiều bản ghi bị xóa ở bảng NHANVIEN Trigger cập nhật (Update trigger): ví dụ CREATE TRIGGER trUpNV ON NHANVIEN FOR UPDATE AS PRINT ‘Ban da cap nhat thanh cong’ Trigger này tự động được thực hiện mỗi khi có một câu lệnh Update được thực hiện trên bảng NHANVIEN. Trigger tổng hợp: ví dụ CREATE TRIGGER trIns_UpNV ON NHANVIEN FOR INSERT, UPDATE AS RAISERROR('%d hàng đã được thêm hoặc sửa ở bảng NHANVIEN', 0, 1,@@rowcount) Trigger Instead of – cho phép cập nhật DL các bảng thông qua view có liên kết nhiều bảng Xóa, sửa trigger – Xoá trigger DROP TRIGGER – Sửa trigger ALTER TRIGGER . . . – Xem lại định nghĩa của trigger: sp_helptext – Tạo trigger bằng EM Ứng dụng: Đảm bảo ràng buộc toàn vẹn dữ liệu CREATE TRIGGER trDelNV ON NHANVIEN FOR DELETE AS RAISERROR('%d hang bi xoa trong bang NHANVIEN', 0, 1,@@rowcount) ------------ CREATE TRIGGER trDelPhong ON PHONG FOR DELETE AS DELETE NHANVIEN FROM DELETED WHERE DELETED.MAPHONG =NHANVIEN.MAPHONG Một số chú ý khi dùng trigger – Một bảng có nhiều trigger – Mỗi một trigger có tên duy nhất – Trong trigger thường dùng mệnh đề IF EXISTS 1. Viết một trigger không cho phép thêm một nhân viên nếu như tuổi của anh ta >60 2. Viết một trigger không cho phép cập nhật trên bảng NHANVIEN nếu như số lượng bản ghi được cập nhật >3 3. Viết một trigger để đảm bảo rằng lương của nhân viên không được phép thay đổi giá trị >1000 4. Viết một trigger để đảm bảo rằng không được phép xóa những nhân viên là trưởng phòng 1. Trong CSDL QLNV, tạo một view chứa những thông tin sau: MaNv, TenNV, MaP TenP 2. Tạo một view cho biết mức lương trung bình của mỗi phòng 3. Sử dụng view ở câu 1để tìm xem phòngnào có số nhân viên đông nhất. • CREATE sp_configure 'nested trigger', 1 Create trigger casc_del on Phong for delete as delete Nhanvien from deleted, nhanvien where deleted.MaPhong=nhanvien.map create trigger del_NV on Nhanvien for delete as select * from Phong select * from Nhanvien • CSDL NHANVIEN gồm bảng: NHANVIEN (MaNV, TenNV, Ngaysinh, Luong, MATP) PHONG (MaP, TenP, SoLuong)