HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU

1. HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER

2. NỘI DUNG • KHUNG NHÌN (VIEW) • THỦ TỤC LƯU TRÚ (STORED PROCEDURE - SP) • TRIGGER • HÀM CỦA NGƯỜI DÙNG (USER DEFINED FUNCTION - UDF)

3. VIEW KHUNG NHÌN Bảng ảo

4. NỘI DUNG • Định nghĩa • Câu lệnh • Mục đích • Cập nhật dữ liệu thông qua View • Mã hóa View

5. Định nghĩa VIEW • View là bảng không chứa dữ liệu, nó chỉ là truy vấn kết hợp dữ liệu từ 1 hay nhiều bảng có quan hệ với nhau và đượ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.

6. USER_VIEWS nv_kinhdoanh SELECT manv, hoten,luong FROM NHANVIEN WHERE ma_phong = ‘KDA’; NHANVIEN Định nghĩa VIEW SQL Server SELECT * FROM nv_kinhdoanh;

7. Câu lệnh tạo VIEW CREATE VIEW <Tên_View> [(<danhsáchtêncột>) ] [WITH ENCRYPTION] AS <CâulệnhSELECT>[with check option]

8. Câu lệnh tạo VIEW Vídụ 1: tạo View nv_kinhdoanh CREAT VIEWnv_kinhdoanh AS SELECTManv, Hoten, Luong FROM NHANVIEN * Sửdụng View: SELECT * FROM NV_KINHDOANH

9. Câu lệnh tạo VIEW 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 đó.

10. Mục đích dùng VIEW • Hạn chế tính phức tạp của dữ liệu đối với NSD đơn giản. • Tạo ra bảng ảo có dữ liệu theo yêu cầu cho NSD và sử dụng trong thiết kế báo cáo. • Hạn chế quyền truy cập dữ liệu của NSD. • View dùng để trình bày các thông tin dẫn xuất.

11. Cập nhật dữ liệu thông qua View • View định nghĩa dữ liệu trên một bảng thì có thể dùng Insert • Nếu trong định nghĩa View có chứa mệnh đề Inner join thì không thể dùng các thao tác Insert hay Delete để thay đổi dữ liệu • Nếu trong định nghĩa View có chứa mệnh đề With check option thì chỉ những bản ghi thỏa mãn điều kiện của View mới được Insert, Update

12. Xây dựng View dựa trên View khác • Khixóa 1 view, mọi view đượcxâydựngdựatrên view đócũngbịxóa. • Cóthểdùngthủtụcsp_helptextđểxemđịnhnghĩaView • Tạo View bằng EM • Mãhoá View: dùngWITH ENCRYPTION • Khôngthểxemđượcnội dung View • Khôngthểthayđổilạiđược

13. STORED PROCEDURE THỦ TỤC LƯU TRÚ

14. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • SP được xây dựng từ các câu lệnh T-SQL và được lưu trú trên SQL server. • Muốn thực hiện một SP, NSD chỉ cần thực hiện một lời gọi hàm. • Khi SP được chạy lần đầu tiên nó sẽ được biên dịch qua 5 bước và sinh ra một mô hình truy vấn. Mô hình này sẽ được đặt trong một CSDL của SQL server, lần sau chạy lại thủ tục sẽ không phải dịch lại nữa.

15. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Năm bước biên dịch thủ tục: • Thủ tục được phân tích ra thành nhiều phần • Kiểm tra sự tồn tại của các đối tượng (view, table, …) mà thủ tục tham chiếu tới. • Lưu trữ tên thủ tục vào bảng sysobject, lưu trữ các mã lệnh của thủ tục vào bảng syscomments. • Sinh ra mô hình truy vấn của thủ tục và lưu vào bảng sysprocedure • Khi SP được chạy lần đầu tiên, cây truy vấn sẽ được đọc và được tối ưu thành một kế hoạch thủ tục và chạy  tiết kiệm thời gian tái phân tích, biên dịch cây truy vấn mỗi khi chạy thủ tục.

16. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Trong một phiên làm việc, nếu SP được thực hiện, nó sẽ được lưu trữ vào vùng nhớ đệm. Những lần sau nếu SP được gọi thực hiện lại thì nó sẽ được đọc trực tiếp ra từ vùng nhớ đệm  nâng cao hiệu suất chạy truy vấn.

17. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Cú pháp lệnh tạo SP: CREATE PROC[EDURE] <tên SP> [@ <tên tham số 1> <kiểu DL> [= <giá trị>] [OUTPUT], . . .] [WITH RECCOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION] AS <các câu lệnh SQL, câu lệnh điều khiển, . . .> Giải thích: • =<giá trị>: gán giá trị ban đầu cho tham số • OUTPUT: giá trị thay đổi của tham số ở trong thủ tục được đưa ra bên ngoài thủ tục. Ví dụ

18. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Thực hiện thủ tục: EXEC <tên SP> • Thay đổi thủ tục ALTER PRO[CEDURE] . . . • Xóa thủ tục DROP PRO[CEDURE] <tên SP> • Bó thủ tục

19. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Sử dụng tùy chọn RECOMPILE trong câu lệnh CREATE PRO: • Thủ tục sẽ được biên dịch lại từ đầu mỗi khi thực hiện • Khi biên dịch lại các thông tin mới của SP được cập nhật và tối ưu lại. • Sử dụng tùy chọn RECOMPILE trong câu lệnh EXEC • Thủ tục sẽ được biên dịch lại trong lần thực thi đó và lưu lại kế hoạch thực thi mới của nó vào vùng nhớ đệm. • Biên dịch lại tất cả các thủ tục của bảng: EXEC sp_recompile <tên bảng>

20. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Vídụ: USE qlnv; GO CREATE PROC DS_Phong @BienMaPhong CHAR (3) AS SELECT Manv, HoTen, NgaySinh FROM NhanVien WHERE MaPhong=@BienMaPhong GO Exec DS_Phong 'KDA' SP này như một view có tham số!

21. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Vídụ: USE qlnv; GO CREATE PROC Soluong_Phong @BienMaPhong CHAR (3), @SL int OUTPUT AS SELECT @SL = COUNT (*) FROM NhanVien WHERE MaPhong=@BienMaPhong GO -- Thuc hien thu tuc luu tru DECLARE @dem INT; DECLARE @phong CHAR(3); SET @phong='KDA'; EXEC Soluong_Phong @phong, @dem OUTPUT --Hoac EXEC Soluong_Phong @BienMaPhong=@phong, @SL=@dem OUTPUT PRINT 'So luongnhanvienphong ' + @phong + ' la: ' + CAST (@dem AS CHAR(20)) --Xoa thu tuc luu tru DROP PROC Soluong_Phong

22. THỦ TỤC LƯU TRÚ (STORED PROCEDURE) • Vídụ: (Thu tucluutruvoitham so macdinh) CREATE PROC Soluong_Phong_Default @BienMaPhong CHAR (3)='KDA', @SL int OUTPUT AS SELECT @SL = COUNT (*) FROM NhanVien WHERE MaPhong=@BienMaPhong GO DECLARE @dem INT; EXEC Soluong_Phong_Default @SL=@dem EXEC Soluong_Phong_Default 'QTA', @dem

23. Create proc XepLoaiSV @MaSV varchar(10), @DTB float output, @XL nvarchar(20) output As Set @DTB = (Select avg(Diem) from KetQua Kq Where MaSV = @MaSV and not exists ( select * from KetQua Kq1 where Kq1.MaSV = @MaSV and Kq1.MaMH=Kq.MaMH and Kq1.LanThi > Kq.LanThi ) ) If @DTB >= 9 Set @XL = N’Xuât sac’ Else if @DTB >= 8 Set @XL = N’Gi~i’ Else if @DTB >= 7 Set @XL = N’Khá’ Else if @DTB >= 5 Set @XL = N’Trung bình’ Else Set @XL = N’Yêu’ Go THỦ TỤC LƯU TRÚ (STORE PROCEDURE)

24. USER DEFINED FUNCTION Hàm của người dùng

25. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs) • Cúphápcâulệnh: CREATE FUNCTION <tênhàm> ([@<tênthamsố> <kiểu DL vôhướng> [= <giátrị>] [, . . .]]) RETURNS <kiểuvôhướng>|<table> [WITH ENCRYPTION] AS BEGIN [<cáccâulệnh>] RETURN <giátrịvôhướng>| (<câulệnh SELECT>)

26. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs) --Ham vohuong CREATE FUNCTION Ham_Soluong_Phong ( @BienMaPhong CHAR (3) ) RETURNSint AS BEGIN DECLARE @SL int; SELECT @SL = COUNT (*) FROM NhanVien WHERE MaPhong=@BienMaPhong ; RETURN (@SL); END --Su dung ham (Ham xuat hien trong bieu thuc) SELECT dbo.Ham_Soluong_Phong('KDA'); SELECT MaPhong, count (*) FROM NhanVien GROUP BY MaPhong HAVING count(*) > dbo.Ham_Soluong_Phong('QTA')

27. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs) --Ham cho ket qua la mot bang CREATE FUNCTION Ham_DS_Phong (@BienMaPhong CHAR (3)) RETURNS @kq TABLE (Manv CHAR(5), HoTen NCHAR(40), NgaySinh DATETIME) BEGIN INSERT INTO @kq SELECT Manv, HoTen, NgaySinh FROM NhanVien WHERE MaPhong=@BienMaPhong RETURN END -- Su dunghamtra ket qua bang nhu la TABLE SELECT * FROM Ham_DS_Phong ('QTA')

28. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs) • UDFs giống như SP nhưng khác ở các điểm sau:

29. TRIGGER BẪY LỖI

30. Nội dung • Kháiniệmvề trigger • Câulệnhtạo trigger • Cácloại trigger • Ứngdụngcủa trigger • Sửa, xóa trigger

31. Khái niệm về trigger • Thủtụclưutrú (Stored Procedure) đặcbiệt • Trigertựđộngđượcchạymỗikhicómộthànhđộngliênquanđếnnóxảyra • Trigerkhôngcóthamsố, khôngtrảragiátrịnhư SP bìnhthường. • Cácloại trigger: Insert trigger, Delete trigger, Update trigger, trigger tổnghợp.

32. Cú pháp câu lệnh tạo trigger CREATE TRIGGER <tên triger> ON <tên bảng>|<tên view> [WITH ENCRYPTION] { {FOR | AFTER| INSTEAD OF} <INSERT [, UPDATE] [, DELETE]> } AS <câu lệnh SQL>

33. Trigger chèn (Insert trigger): định nghĩa • Tự động được thực hiện mỗi khi bản ghi mới được chèn vào bảng gắn với nó • Một bảng tạm Inserted sẽ được sinh ra • Bản ghi cần chèn sẽ được ghi vào bảng cơ sở và bảng Inserted

34. Trigger chèn: ví dụ CREATE TRIGGER trInsNV ON NHANVIEN FOR INSERT AS RAISERROR('%d hang daduoc them vao bang NHANVIEN', 0, 1,@@rowcount) Trigger nàytựđộngđượcthựchiệnmỗikhicóbảnghimớiđượcchènvàobảngNHANVIEN

35. Trigger xóa: ví dụ CREATE TRIGGER trDelNV ON NHANVIEN FOR DELETE AS RAISERROR('%d hàngbịxóatrongbảng NHANVIEN', 0, 1,@@rowcount) Trigger nàytựđộngđượcthựchiệnmỗikhicómộthoặcnhiềubảnghibịxóa ở bảng NHANVIEN

36. Trigger cập nhật (Update trigger): ví dụ CREATE TRIGGER trUpNV ON NHANVIEN FOR UPDATE AS RAISERROR('%d %d hàngđãđượcsửa ở bảngbảng NHANVIEN', 0, 1,@@rowcount) Trigger nàytựđộngđượcthựchiệnmỗikhicómộtcâulệnh Update đượcthựchiệntrênbảng NHANVIEN.

37. 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)

38. Trigger Instead of • chophépcậpnhật DL cácbảngthông qua view cóliênkếtnhiềubảng

39. Ứ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 xoatrong bang NHANVIEN', 0, 1,@@rowcount) ------------ CREATE TRIGGER trDelPhong ON PHONG FOR DELETE AS DELETE NHANVIEN FROM DELETED WHERE DELETED.MAPHONG =NHANVIEN.MAPHONG

40. Ứng dụng: Đóng gói quy tắc nghiệp vụ CREATE TRIGGER trInsNV_PHONG ON NHANVIEN FOR INSERT AS UPDATE PHONG SET SONV = SONV+1 WHERE PHONG.MAPHONG = (SELECT MAPHONG FROM INSERTED)

41. 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

42. Xóa, sửa trigger • Xoá trigger DROP TRIGGER <tên trigger> • Sửa trigger ALTER TRIGGER <tên trigger> . . . • Xem lại định nghĩa của trigger: sp_helptext • Tạo trigger bằng EM