›
›

Bài giảng Hệ quản trị dữ liệu - Chương III: Procedure, function, view, trigger & index

pdf 124 trang hapham 2720
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị dữ liệu - Chương III: Procedure, function, view, trigger & index", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_he_quan_tri_du_lieu_chuong_iii_procedure_function.pdf

Nội dung text: Bài giảng Hệ quản trị dữ liệu - Chương III: Procedure, function, view, trigger & index

  1. Chương III. PROCEDURE, FUNCTION, VIEW, TRIGGER & INDEX 1
  2. Nội dung  1. Thủ tục (stored procedure)  2. Hàm (Function)  3. Khung nhìn (view)  4. Ràng buoc toàn vẹn (trigger)  5. Chỉ mục (index) 2
  3. 1. Stored procedure  Stored procedure trong CSDL tương tự như các thủ tục trong ngôn ngữ lập trình. Mọi stored procedure có thể: Nhận tham số đầu vào, thực thi các câu lệnh bên trong và trả vê các giá trị. Bên trong mỗi thủ tục có thể có nhiều câu lệnh và có thể gọi các thủ tục khác Trả về các thông báo cho biết thủ tục thực hiện thành công hay thất bại Các câu trúc điều khiển (IF, WHILE, FOR) có thể được sử dụng trong thủ tục 3
  4. 1. Stored procedure  Các lợi ích của stored procedure: Cho phép module hóa các công việc và thực thi các câu lệnh dễ dàng hơn Tối ưu hóa việc phân tích, biên dịch và thực thi câu lệnh Thực hiện một yêu cầu bằng một câu lệnh đơn giản hơn thay vì phải sử dụng nhiều dòng lệnh SQL khi thực thi làm giảm thiểu sự lưu thông trên mạng Tăng khả năng bảo mật khi cấp phát quyền thông qua thủ tục 4
  5. 1. Stored procedure  Tạo thủ tục tên danh sách có tham số truyền vào là tên nhân viên. Liệt kê mã nhân viên, họ lót, tên nhân viên, ngày vào làm, lương: CREATE PROC sp_danhsach @ten nvarchar(20) AS SELECT MaNV, HoLot, TenNV, NgayVaoLam, Luong FROM NhanVien WHERE TenNV= @ten 5
  6. 1. Stored procedure  Thực thi thủ tục và truyền tham số cho thủ tục như sau: @tham_số=giá_trị  Thực thi: sp_danhsach 'Linh‘ hoặc exec sp_danhsach ‘Linh’ 6
  7. 1. Stored procedure  Người dùng có thể gán giá trị mặc định cho tham số trong câu lệnh create procedure CREATE PROC sp_danhsach1 @ten nvarchar(20)='Bình' AS SELECT * FROM NhanVien WHERE TenNV = @ten Thực thi: sp_danhsach1 ‘Duy’ 7
  8. 1. Stored procedure  Tạo thủ tục tăng lương nhân viên với tham số đầu vào là mức lương tăng và mã nhân viên  CREATE PROC asc_salary(@ascsalary int, @idemp int) AS BEGIN UPDATE NhanVien SET Luong=Luong+ @ascsalary WHERE MaNV=@idemp END Thực thi: asc_salary 100, 1 (chú ý thứ tự giá trị truyền vào theo đúng tham số) Hoặc thực thi: asc_salary (@ascsalary=100, @idemp=1) (Nếu thực thi câu lệnh tường minh có thể hoán đổi vị trí 8 các tham sô)
  9. 1. Stored procedure Tạo thủ tục tăng lương nhân viên với tham số đầu vào là mã nhân viên. Nếu lương nhân viên lớn hơn 1000$ thì tăng 150$, ngược lại tăng 100$ CREATE PROC asc_salary(@idemp int) AS DECLARE @salary INT BEGIN SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp If @salary>1000 BEGIN UPDATE NhanVien SET Luong=Luong+150 WHERE MaNV=@idemp END Else BEGIN UPDATE NhanVien SET Luong=Luong+100 WHERE MaNV=@idemp 9 END
  10. 1. Stored procedure Tạo thủ tục tăng lương nhân viên lên 5 lần, mỗi lần tăng 50$ dùng câu lệnh While CREATE PROCEDURE asc_salary(@idemp int) AS DECLARE @count int BEGIN SET @count=1 WHILE @count<=5 BEGIN UPDATE NhanVien SET Luong=Luong+50 WHERE MaNV=@idemp SET @count=@count+1 END END 10
  11. 1. Stored procedure  Thông tin trả về các thủ tục:  Các thủ tục có thể trả về giá trị số nguyên để thông báo thủ tục thực hiện thành công hay thất bại. SQL Server định nghĩa sẵn tập các giá trị trả về nằm trong khoảng [-99;0]. Giá trị 0 cho biết thủ tục thực hiện thành công, các giá trị còn lại cho biết nguyên nhân lỗi xảy ra. 11
  12. 1. Stored procedure  Ngoài ra, người dùng có thể định nghĩa giá trị trả về bằng cách bổ sung một tham số vào câu lệnh RETURN. Tất cả các số nguyên đều được chấp nhận ngoại trừ các số do hệ thống định nghĩa và sử dụng CREATE PROC sp_vidu @ten nvarchar(20) AS IF EXISTS (SELECT * FROM NhanVien WHERE TenNV = @ten) RETURN 1 ELSE RETURN 2 12
  13. 1. Stored procedure Thực thi thủ tục có câu lệnh RETURN: Declare @a int Exec @a = sp_vidu N‘Hà’ Select @a 13
  14. 1. Stored procedure Tạo thủ tục với tham số mặc định CREATE PROC sp_testdefault @MaNV int =3 AS SELECT * FROM NhanVien WHERE MaNV=@MaNV Thực thi thủ tục: Exec sp_testdefault Hoặc Exec sp_testdefault 4 14
  15. 1. Stored procedure  Khi cả 2 câu lệnh Create Procedure và Execute chứa mục chọn OUTPUT cho tên một tham số, thủ tục có thể sử dụng một biến để trả về trị của tham số đó đến người gọi CREATE PROC Chia @sobichia real,@sochia real, @ketqua real OUTPUT AS IF (@sochia =0) Print ‘Loi chia cho 0’ ELSE SELECT @ketqua = @sobichia / @sochia 15
  16. 1. Stored procedure Thực thi thủ tục: Thực thi thủ tục DECLARE @ketqua real DECLARE @ketqua real EXEC Chia 100, 2, @ketqua OUTPUT EXEC Chia 100, 0, @ketqua OUTPUT SELECT @ketqua SELECT @ketqua 16
  17. 1. Stored procedure  Tạo thủ tục với tham số đầu ra là lương nhân viên với tham số đầu vào là mã nhân viên CREATE PROCEDURE salary (@idemp int, @salary int OUTPUT) AS SELECT @salary=Luong FROM NhanVien WHERE MaNV=@idemp Thực thi: DECLARE @salary int EXEC salary 5, @salary=@salary OUTPUT PRINT ‘Lương nhân viên:’+CAST(@salary AS VARCHAR(10))+’$’ 17
  18. 1. Stored procedure Cho biết tổng lương của nhân viên với tham số đầu vào là mã phòng ban CREATE PROCEDURE sum_salary (@deptid int, @sumsalary int OUTPUT) AS BEGIN If NOT EXISTS (SELECT * FROM NhanVien WHERE MaPB=@deptid) RETURN 1 SELECT @sumsalary=SUM(Luong) FROM NhanVien WHERE MaPB=@deptid RETURN If @sumsalary IS NULL SET @sumsalary=0 18 END
  19. 1. Stored procedure Thự c thi thủ tục: DECLARE @sumsalary, @result EXEC @result=sum_salary 44,@sumsalary OUTPUT If @result=1 PRINT ‘Không tồn tại phòng ban’ Else PRINT ‘Tổng lương cả phòng là:’ + CAST(@sumsalary AS VARCHAR(15))+ ‘$’ 19
  20. 1. Stored procedure Tạo thủ tục gọi trực tiêp: CREATE PROC count_rows (@name Nvarchar(50)) WITH EXECUTE AS CALLER AS EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name) Tạo thủ tục gọi bởi người tạo ra thủ tục: CREATE PROC count_rows_as_me (@name Nvarchar(50)) WITH EXECUTE AS SELF AS EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name) 20
  21. 1. Stored procedure Tạo thủ tục thực thi bởi người dùng: CREATE PROC count_rows_as_user1 (@name Nvarchar(50)) WITH EXECUTE AS ‘User1’ AS EXECUTE(‘SELECT COUNT(*) FROM ‘ + @name) 21
  22. 1. Stored procedure Thực thi thủ tục: DECLARE @sname VARCHAR(50) SET @sname = ‘authors;drop table customers’ count_rows_as_me @sname 22
  23. 1. Stored procedure Một thủ tục có thể sử dụng bất kỳ hoặc tất cả khả năng sau để trả về giá trị:  Một hoặc nhiều giá trị  Một giá trị trả vê rõ ràng (lệnh RETURN)  Một tham sô OUTPUT Bên trong câu lệnh Create Procedure có thể bao gồm bất kỳ câu lệnh nào trừ các câu lệnh sau: Create Procedure, Create View, Create Rule, Create Default, Create Trigger 23
  24. 1. Stored procedure Cần chỉ rõ tên chủ sở hữu đối tượng khi gọi đối tượng bên trong thủ tục CREATE PROC sp_index AS CREATE INDEX PhongBan_ind ON user1.PhongBan (TenPB) 24
  25. 1. Stored procedure Cú pháp sửa thủ tục: ALTER PROC proc_name Cú pháp đổi tên thủ tục: SP_RENAME old_name, new_name SP_RENAME sp_index, sp_index_user1 Cú pháp xóa thủ tục: DROP PROCEDURE proc_name DROP PROCEDURE sp_index_user1 25
  26. 2. Function  2.1 Hàm Scalar  2.2 Hàm Inline table valued  2.3 Hàm Multi statement table valued 26
  27. 2.1 Hàm Scalar Hàm vô hướng trả về duy nhất một giá trị dựa trên tham số truyền vào. Cú pháp như sau: CREATE FUNCTION func_name ([parametername datatype, parameter2, ]) RETURNS (function datatype) AS BEGIN các_câu_lenh_của_hàm END 27
  28. 2.1 Hàm Scalar Câu lệnh dưới đây định nghĩa hàm tính ngày trong tuần(thứ trong tuần) của một giá trị kiểu ngày CREATE FUNCTION f_thu (@ngay datetime) returns nvarchar(10) As Begin declare @st nvarchar(10) select @st=case datepart(dw,@ngay) when 1 then N'chủ nhật' when 2 then N'thứ hai’ when 3 then N 'thứ ba' 28
  29. 2.1 Hàm Scalar CREATE FUNCTION f_thu(@ngay datetime) Returns nvarchar(10) AS Begin 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 (@st) /* trị trả về của hàm */ End 29
  30. 2.1 Hàm Scalar Thực thi hàm: Select MaNV, TenNV, dbo.f_thu (NgayVaoLam) From NhanVien Kết qủa: 30
  31. 2.2 Hàm Inline table valued Hàm nội tuyến, trả về dạng bảng. Cú pháp như sau: CREATE FUNCTION func_name ([parameter datatype]) RETURNS TABLE AS RETURN (câu_lenh_select) 31
  32. 2.2 Hàm Inline table valued Cú pháp của hàm nội tuyến phải tuân theo các qui tắc sau: - Kiểu trả về của hàm phải được chỉ định bởi mệnh đề RETURNS TABLE - Trong phần thân của hàm chỉ có duy nhất một câu lệnh RETURN xác định giá trị trả về của hàm thông qua duy nhất một câu lệnh SELECT. Ngoài ra, không sử dụng bất kỳ câu lệnh nào khác trong phần thân của hàm 32
  33. 2.2 Hàm Inline table valued Tạo hàm trả về các khách hàng tùy thuộc vào giá trị mã khách hàng truyền vào cho tham số CREATE FUNCTION f_KhachHang (@MaKH int) RETURNS TABLE AS RETURN (Select * From KhachHang Where MaKH > @MaKH) 33
  34. 2.2 Hàm Inline table valued Thực thi hàm: SELECT tmp.TenKH, dh.NgayDatHang FROM DonHang dh, dbo.f_KhachHang(3) as tmp WHERE dh.MaKH = tmp.MaKH 34
  35. 2.3 Hàm Multi statement table valued Hàm gồm nhiều câu lệnh SQL bên trong, trả vê dạng bảng. Cú pháp như sau: CREATE FUNCTION func_name (parameter datatype) RETURNS @biên_bng TABLE dnh_nghia_bng AS BEGIN các_câu_lenh_trong_thân_hàm RETURN END Lưu ý: sau từ khóa RETURNS là một biến bảng được định nghĩa và sau từ khóa RETURN cuối hàm không có tham sô nào đi kèm 35
  36. 2.3 Hàm Multi statement table valued CREATE FUNCTION f_DSKhachHang (@MaKH int) returns @myKhachHang table (MaKH int, TenKH nvarchar(50), NgayDatHang datetime) AS Begin If @MaKH = 0 Insert into @myKhachHang Select kh.MaKH, kh.TenKH, dh.NgayDatHang From KhachHang kh, DonHang dh 36 Where dh.MaKH = kh.MaKH
  37. 2.3 Hàm Multi statement table valued CREATE FUNCTION f_DSKhachHang (@MaKH int) returns @myKhachHang table (MaKH int, TenKH nvarchar(50), NgayDatHang datetime) AS Begin Else Insert into @myKhachHang Select kh.MaKH, kh.TenKH, dh.NgayDatHang From KhachHang kh, DonHang dh Where kh.MaKH=dh.MaKH and kh.MaKH = @MaKH Return End 37
  38. 2.3 Hàm Multi statement table valued Thực thi hàm: Select * From f_DSKhachHang (0) 38
  39. 2.3 Hàm Multi statement table valued Lệnh ALTER FUNCTION dùng để thay đổi hàm ALTER FUNCTION f_KhachHang (@MaKH int) Returns table AS Return (Select * From KhachHang Where MaKH > @MaKH) 39
  40. 2.3 Hàm Multi statement table valued Lệnh DROP FUNCTION dùng để xóa hàm DROP FUNCTION func_name DROP FUNCTION f_KhachHang 40
  41. 3. View  View là một bảng logic hay ‘bảng ảo’ truy cập đến một hoặc nhiều bảng dữ liệu hoặc view khác. View truy xuât dên các cột và dòng dữ liệu bên trong bảng và hiển thị ra đúng các thông tin tối thiểu mà người sử dụng cần dùng. 41
  42. 3. View Đối với một số view, người dùng có thể thực hiện các thao tác thêm, xóa, sửa dữ lieu. Việc thực hiện các thao tác này Phải thỏa các điều kiện sau: - Trong câu lệnh SELECT định nghĩa view không được sử Dụng các từ khóa DISTINCT, TOP, GROUP BY, UNION - Các thành phần xuất hiện trong danh sách chọn của câu Lệnh SELECT không xuất hiển các biểu thức tính toán, các hàm gộp - Các ràng buộc toàn vẹn trên các bảng cơ sở phải được đảm bảo 42
  43. 3. View Cú pháp tạo view: CREATE VIEW [schema_name.] tên_view [(column[, n])][WITH ENCRYPTION] AS câu_lenh_select [ ; ] [ WITH CHECK OPTION ] 43
  44. 3. View Tạo view cho biết mã nhân viên, tên nhân viên và tên chức vụ của nhân viên ( phòng ban có mã phòng ban là 45) CREATE VIEW empvu45 AS SELECT MaNV, TenNV, TenCV FROM NhanVien nv, ChucVu cv WHERE nv.MaCV=cv.MaCV and MaPB=45 Truy vấn đến view empvu45: Select * From empvu45 44
  45. 3. View Tạo view cho biết mã nhân viên, họ lót có bí danh là ‘FIRST NAME’, tên nhân viên có bí danh là ‘LAST NAME’ và lương của nhân viên có bí danh là ‘MONTHLY_SALARY’ ( phòng ban có mã phòng ban là 41) CREATE VIEW salvu41 AS SELECT MaNV, HoLot AS ‘FIRST NAME’, TenNV AS ‘LAST NAME’, Luong AS ‘MONTHLY_SALARY’ FROM NhanVien WHERE MaPB=41 45
  46. 3. View Cú pháp xóa view: DROP VIEW [tên_schema.] tên_view Xóa view salvu41: DROP VIEW salvu41 46
  47. 4. Trigger  Trigger là một dạng đặc biệt của thủ tục lưu trữ và thực thi tự động khi người dùng áp dụng câu lệnh cập nhật dữ liệu lên một table chỉ định nhằm mục đích đảm bảo tính toàn vẹn dữ liệu. Nếu trigger bị vi phạm, câu lệnh sẽ không được thực thi 47
  48. 4. Trigger  Trigger được sử dụng trong các cách sau:  Có thể thay đổi đồng loạt các table có liên quan với nhau trong CSDL  Có thể không cho phép hoặc hủy bỏ những thay đổi vi phạm ràng buoc toàn vẹn tham chiêu và các giao dịch sửa đổi dữ liệu 48
  49. 4. Trigger  Có thể áp đặt các giới hạn phức tạp hơn những giới hạn được định nghĩa bằng ràng buộc CHECK và có thể tham chiếu đến các cột trong các bảng khác  Có thể tìm sự khác biệt giữa các trạng thái của một table trước và sau khi sửa đổi dữ liệu và lấy ra những tác động dựa trên sự thay đổi đó 49
  50. 4. Trigger  Cơ chế hoạt động của trigger  3 biến cố kích hoạt 1 trigger  INSERT  UPDATE  DELETE  Trigger lưu trữ dữ liệu của mẩu tin vừa thêm vào một table mới có tên là INSERTED.  Trigger lưu trữ dữ liệu của mẩu tin vừa xoá vào một table có tên là DELETED.  Trigger lưu trữ dữ liệu của mẩu tin vừa cập nhật là sự phối hợp của 2 table DELELTED và INSERTED 50
  51. 4. Trigger  Cú pháp: CREATE TRIGGER Tên_trigger ON tên_table| tên_view AFTER | INSTEAD OF biến_cố_kích_hoạt_trigger AS Các câu lệnh T-SQL Có thể thay bằng FOR. After là mặc định, chỉ định nghĩa duy nhất được trên view 51
  52. 4. Trigger CREATE TRIGGER Them_HH ON HANG_HOA AFTER INSERT AS Select * From Inserted  Thêm dữ liệu INSERT HANG_HOA(MaHH, TenHH) VALUES(‘TV01’, ‘Tivi Sony’) 52
  53. 4. Trigger CREATE TRIGGER SUA_HH ON HANG_HOA AFTER UPDATE AS Select * From Inserted Select * From Deleted  Cập nhật dữ liệu UPDATE HANG_HOA SET Ten_HH = ‘Man Hinh Sony’ WHERE MaHH = ‘TV01’ 53
  54. 4. Trigger CREATE TRIGGER Xoa_HH ON HANG_HOA AFTER DELETE AS Select * From Inserted Select * From Deleted  Xóa dữ liệu DELETE HANG_HOA WHERE MaHH = ‘TV01’ 54
  55. 4. Trigger  Các thao tác trigger phổ biến  Thêm mới mẩu tin  Xóa mẩu tin  Sửa mẩu tin 55
  56. 4. Trigger - Thêm mới mẩu tin  Kiểm tra ràng buộc dữ liệu  Khoá ngoại  Miền giá trị  Liên bộ trên một quan hệ  Liên thuộc tính trong cùng một bảng  Liên thuộc tính của nhiều bảng khác nhau 56
  57. 4. Trigger  HOADON_DH(MaHD, NgayDH, MaKH)  PHIEU_XUAT(MaPX, NgayXuat, #MaHD )  CHITIET_DH(MAHD, MaHH, SoLuong, DonGia)  Xây dựng trigger trong bảng PHIEU_XUAT để kiểm tra các ràng buộc toàn vẹn dữ liệu khi người dùng thêm mới thông tin của một phiếu xuất hàng cho một bảng hoá đơn đặt hàng trước đó. Các ràng buộc toàn vẹn dữ liệu bao gồm.  Khoá ngoại: cần kiểm tra số đặt hàng phải tồn tại trong bảng đơn đặt hàng.  Miền giá trị: cần kiểm tra ngày giao hàng phải ở sau ngày đặt hàng. 57
  58. 4. Trigger CREATE TRIGGER tg_PhieuXuat_Insert ON PHIEU_XUAT FOR INSERT AS DECLARE @NgayHD datetime, @ErrMsg varchar(200) Kiểm tra số hoá đơn đã có trong bảng DONDH không? IF NOT EXISTS(Select * From Inserted I, HOADON_DH D Where I.MaHD= D.MaHD) Begin Rollback Tran Raiserror(‘Số đơn đặt hàng không tồn tại’, 16,1) 58 Return End
  59. 4. Trigger Tính ra ngày đặt hàng Select @NgayDH=NgayDH From HoaDon_DH D, Inserted I Where D.MaHD = I.MaHD Kiểm tra ngày giao hàng phải sau ngày đặt hàng IF @NgayDH < (Select ngayxuat From Inserted) Begin Set @ErrMsg = ‘ngày giao hàng phải ở sau ngày:’ + Convert(char(10), ngayDH, 103 ) Raierror(@ErrMsg,16,1) Rollback tran End 59
  60. 4. Trigger  Kiểm tra ràng buộc  Kiểm tra ràng buộc khóa ngoại  Ví dụ: khi xoá một số hoá đơn đặt hàng trong bảng HOADON_DH cần phải kiểm tra các RBTV dữ liệu sau:  Kiểm tra xem đơn đặt hàng bị xoá đã được xuất hàng chưa? Nếu đã được xuất rồi thì thông báo không thể xoá đơn đặt hàng được.  Ngược lại thì xoá dữ liệu liên quan bên bảng chi tiết đơn đặt hàng (CHITIET_HD) 60
  61. 4. Trigger CREATE TRIGGER tg_HOADON_Delete ON HOADON_DH FOR DELETE AS DECLARE @SoPX char(5), @ErrMsg char(200), @Delete_Err int Kiểm tra xem đơn hàng đã được xuất chưa IF EXISTS(Select MaPX From PHIEU_XUAT Where MaHD IN(Select MaHD From Deleted)) Begin Select @MaPX = MaPX From PHIEU_XUAT Where MaHD In(Select MaHD From Deleted) Set @ErrMsg = ‘Đơn đặt hàng đã được nhập theo ’+ ‘số xuất hàng ’+ @SoPX + char(13) + ‘.Không thể huỷ được’ RaiseError(@ErrMsg,16,1) 61 Rollback tran End
  62. 4. Trigger Else Begin Xoá tự động chi tiết các đơn đặt hàng liên quan Delete FROM CHITIET_DH Where MaHD In(Select MaHD From DELETED) Set @Delete_Err = @@ERROR IF @Delete_Err <> 0 Begin Set @ErrMsg = ‘Lỗi vi phạm xóa trên bảng chi tiết đặt hàng’ RaisError(@ErrMsg, 16, 1) Rollback Tran 62 End End
  63. 4. Trigger - Sửa đổi mẩu tin  Kiểm tra ràng buộc dữ liệu  Khoá ngoại  Miền giá trị  Liên bộ trên một quan hệ  Liên thuộc tính trong cùng một bảng  Liên thuộc tính của nhiều bảng khác nhau 63
  64. 4. Trigger  Hàm Update  Ý nghĩa  kiểm tra dữ liệu của cột bên trong bảng có bị thay đổi trong các trigger sửa đổi dữ liệu  Cú pháp  UPDATE (tên_cột) (biểu thức luận lý)  Tên_cột: tên cột mà chúng ta muốn kiểm tra xem dữ liệu tại đó có bị sửa đổi trong trigger không.  Biểu thức luận lý: trả về True khi giá trị dữ liệu của cột đã bị sửa đổi, ngược lại trả về False khi giá trị dữ liệu của cột không bị sửa đổi 64
  65. 4. Trigger  Sửa đổi thông tin của một số đặt hàng bên trong bảng HOADON_DH cần phải kiểm tra các ràng buộc toàn vẹn dữ liệu sau:  Không cho phép sửa đổi dữ liệu tại cột MaDH hoặc MaKH vì khi đó dữ liệu sẽ ảnh hưởng đến nhiều bảng.  Sửa đổi giá trị cột ngày đặt hàng thì phải đảm bảo luôn luôn trước ngày giao hàng đầu tiên của số đặt hàng đó (nếu đơn đặt hàng đã có giao hàng). 65
  66. 4. Trigger CREATE TRIGGER tg_HOADON_DH_Update ON HOADON_DH FOR UPDATE AS Declare @MinNgayXH date, @ErrMsg varchar(200) Khi sửa đổi các cột MaDH hoặc MaKH IF Update(MaDH) OR Update(MaKH) Begin Rollback Tran Set @ErrMsg = ‘Không thể thay đổi số đặt hàng hoặc mã khách hàng’ RaisError(@ErrMsg, 16, 1) Return End 66
  67. 4. Trigger Khi sửa đổi ngày đặt hàng IF Update(NgayDH) Begin Kiểm tra đơn đặt hàng đã được xuất chưa IF EXISTS (Select MaPX From PHIEU_XUAT PX, Deleted d where px.madh=d.madh Begin Tính ra ngày nhập hàng đầu tiên Select @MinNgayXH = Min(NgayXuat) From PHIEU_XUAT PX, DELETED D Where PX.MaDH = D.MaDH 67
  68. 4. Trigger kiểm tra giá trị ngày đăt hàng sau khi sửa đổi phải luôn trước ngày giao hàng đầu tiên IF @MinNgayXH < (Select NgayDH From Inserted) Begin Rollback tran Set @ErrMsg = ‘Ngày đặt hàng phải ở trước ngày:’ + Convert(char(10), @MinNgayXH, 103) RaisError(@ErrMsg, 16, 1) End End End 68
  69. 4. Trigger Cài đặt trigger ngăn chặn tạo login ở cấp độ server IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = ‘rtg_KhongTaoLoginMoi') DROP TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER GO CREATE TRIGGER rtg_KhongTaoLoginMoi ON ALL SERVER FOR CREATE_LOGIN AS PRINT N'Phi DROP trigger rtg_KhongTaoLoginMoi trưc khi to account' rollback 69
  70. 4. Trigger Cho phép (enable) hoặc vô hiệu hóa (disable) trigger: ENABLE | DISABLE TRIGGER trigger_name ON {OBJECT | DATABASE | SERVER} Vô hieu hóa trigger rtg_KhongTaoLoginMoi: disable trigger rtg_KhongTaoLoginMoi Cho phép trigger rtg_KhongTaoLoginMoi hoạt động trở lại: enable trigger rtg_KhongTaoLoginMoi 70
  71. 4. Trigger Hiển thị thông tin vê các triggger: Tất cả các đối tượng trong CSDL được liệt kê trong bảng hệ thống sysobjects. Cột type trong sysobjects xác định các trigger với chữ viết tắt là TR SELECT * FROM sysobjects WHERE type=‘TR’ 71
  72. 4. Trigger Cú pháp hiển thị thông tin về triggger: sp_help tên_trigger Hiển thị thông tin trigger tgr_test: Sp_help tgr_test 72
  73. 4. Trigger  Câu lệnh Create triggger của mỗi trigger được lưu trữ trong bảng hệ thống syscomments. Người dùng có thể hiển thị nội dung câu lệnh trigger bằng cách sử dụng thủ tục sp_helptext  Hiển thị nội dung trigger tgr_test: Sp_helptext tgr_test 73
  74. Biến kiểu dữ liệu cursor . CSDL quan hệ thường làm việc trên dữ liệu có nhiều dòng mẫu tin – còn gọi là các bộ mẫu tin. Ví dụ lệnh SELECT kết quả luôn trả về nhiều dòng dữ liệu hơn là một dòng dữ liệu. Tuy nhiên có một số ngôn ngữ lập trình việc xử lý và tính toán dữ liệu trên từng dòng riêng lẻ. Để đáp ứng được yêu cầu này SQL Server tạo ra một kiểu dữ liệu đó chính là kiểu cursor.
  75. Biến kiểu dữ liệu cursor
  76. Biến kiểu dữ liệu cursor Các bước sử dụng kiểu dữ liệu cursor . Định nghĩa biến kiểu cursor bằng lệnh DECLARE. . Sử dụng lệnh OPEN để mở ra cursor đã định nghĩa trước đó. . Đọc và xử lý trên từng dòng dữ liệu bên trong cursor. . Đóng cursor bằng lệnh CLOSE và DEALLOCATE.
  77. Biến kiểu dữ liệu cursor . Cú pháp định nghĩa biến có kiểu cursor DECLARE Tên_cursor CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | DYNAMIC | KEYSET] [READ_ONLY | SCROLL_LOCK] FOR Câu_lệnh SELECT [FOR UPDATE [OF danh_sách_cột_n]]
  78. Biến kiểu dữ liệu cursor Trong đó: . Tên cursor: tên của biến kiểu cursor . Từ khoá LOCAL | GLOBAL: dùng chỉ phạm vi hoạt động của biến cursor hoặc là cục bộ (local) bên trong một thủ tục. . FORWARD_ONLY: đọc dữ liệu trong cursor theo chiều đi tới duyệt từ mẫu tin đầu tiên đến mẫu tin cuối cùng.
  79. Biến kiểu dữ liệu cursor . SCROLL: Đọc dữ liệu trong cursor được phép di chuyển tới lui, qua lại các dòng mẫu tin bên trong cursor tùy thích.
  80. Biến kiểu dữ liệu cursor . STATIC: Đọc dữ liệu bên trong cursor tĩnh. Khi đó nếu những người dùng khác có thay đổi bên dưới dữ liệu gốc thì các thay đổi đó sẽ không được cập nhật tự động trong dữ liệu của cursor. Bởi vì khi đó dữ liệu trong cursor chính là dữ liệu của bảng tạm đã được hệ thống sao chép và lưu trữ trong CSDL tempdb của hệ thống khi định nghĩa cursor.
  81. Biến kiểu dữ liệu cursor . DYNAMIC: dùng chỉ định dữ liệu trong cursor là động. Khi đó việc cập nhật dữ liệu trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật tự động trong dữ liệu cursor có kiểu là DYNAMIC.
  82. Biến kiểu dữ liệu cursor . KEYSET: hoạt động giống với kiểu DYNAMIC, các thay đổi dữ liệu trên các cột không là khóa chính trong bảng cơ sở bởi những người dùng khác sẽ được cập nhật trong dữ liệu cursor. Tuy nhiên đối với mẫu tin vừa thêm mới hoặc các mẫu tin đã bị hủy bỏ bởi những người dùng khác sẽ không được hiển thị trong dữ liệu cursor có kiểu là KEYSET.
  83. Biến kiểu dữ liệu cursor . READ_ONLY: chỉ định dữ liệu trong cursor chỉ đọc nhằm hạn chế việc sửa đổi dữ liệu bên trong cursor. Khi khai báo cursor với kiểu dữ liệu tĩnh (STATIC) thì dữ liệu trong cursor xem như chỉ đọc.
  84. Biến kiểu dữ liệu cursor . SCROLL_LOCK: chỉ định hệ thống SQL Server tự động khóa các dòng mẫu tin cần phải thay đổi giá trị hoặc hủy bỏ bên trong bảng nhằm bảo đảm các hành động cập nhật luôn thành công.
  85. Biến kiểu dữ liệu cursor . SELECT: dùng để chỉ đến các cột bên trong bảng mà chúng ta cần đọc dữ liệu. . Danh sách các cột cập nhật: chỉ định danh sách tên các cột sẽ được phép thay đổi giá trị trong cursor.
  86. Biến kiểu dữ liệu cursor . Ví dụ 1: để định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng SV, các dòng dữ liệu trong cursor cho phép được cập nhật. Declare C_SV cursor dynamic For select * From SV
  87. Biến kiểu dữ liệu cursor . Ví dụ 2: Định nghĩa một biến cursor chứa toàn bộ các dòng dữ liệu bên trong bảng MH, các dữ liệu trong cursor chỉ được phép đọc và việc đọc dữ liệu trong cursor chỉ theo một chiều đi tới. Declare C_MH cursor forward_only static Read_only For select * From MH
  88. Biến kiểu dữ liệu cursor . Mở Cursor . Cú pháp: OPEN Tên_cursor . Trong đó: Tên cursor: tên của biến cursor đã được định nghĩa trước đó bằng lệnh DECLARE
  89. Biến kiểu dữ liệu cursor . Ví dụ: Mở các cursor đã định nghĩa ở ví dụ 1 trên. Chúng ta sử dụng lệnh OPEN như sau: OPEN C_SV
  90. Biến kiểu dữ liệu cursor . Đọc và xử lý dữ liệu trong cursor FETCH [Next | Prior | First | Last | Absolute n | Relative n ] FROM Tên_cursor [INTO danh_sách_biến]
  91. Biến kiểu dữ liệu cursor Trong đó: . Next, Prior, First, Last: dùng để đọc dữ liệu kế tiếp, trước,đầu,sau cùng. . Absolute: dữ liệu chính xác thứ n trong cursor. N>0 chỉ định việc đọc dữ liệu tại dòng thứ n đếm từ dòng đầu tiên, n<0 dùng chỉ định việc đọc dữ liệu tại dòng thứ n được đếm ngược từ dòng cuối trở lên.
  92. Biến kiểu dữ liệu cursor Trong đó: . Relative: dùng chỉ định việc đọc dữ liệu tại một dòng tương đối so với dòng dữ liệu hiện hành. N là một số nguyên có thể dương có thể âm để chỉ định theo chiều tới hoặc lui so với dòng dữ liệu hiện hành.
  93. Biến kiểu dữ liệu cursor . Tên_cursor: tên của biến cursor đã định nghĩa trước đó bằng lệnh DECLARE. . Danh sách biến: danh sách tên các biến cục bộ đã được định nghĩa trước đó. Các biến này sẽ lưu trữ các giá trị dữ liệu được đọc từ lệnh FETCH.
  94. Biến kiểu dữ liệu cursor
  95. Biến kiểu dữ liệu cursor Đóng cursor . Cú pháp: CLOSE Tên_cursor DEALLOCATE Tên_cursor
  96. Biến kiểu dữ liệu cursor Trong đó . CLOSE giải phóng các dòng dữ liệu tham chiếu bên trong cursor. . Lệnh DEALLOCATE giải phóng thật sự biến cursor ra khỏi bộ nhớ
  97. Biến kiểu dữ liệu cursor . SQL Server cung cấp một biến hệ thống @@FETCH_STATUS dùng để kiểm tra trình trạng đọc dữ liệu thành công hay thất bại. Giá trị trả về 0 khi việc đọc dữ liệu là thành công.
  98. Biến kiểu dữ liệu cursor Cho lược đồ quan hệ như sau: . MAT_HG (MaMH,TenMH, DVT, MaNCC) . Pnhap (MaPN, NgayNhap,ThanhTien) . CTPNhap (MaMH, MaPN, SLNhap, DonGia)
  99. Biến kiểu dữ liệu cursor . Ví dụ 1: Đọc dữ liệu cursor của bảng MAT_HANG chỉ đọc các vật tư làTivi
  100. Biến kiểu dữ liệu cursor Khai báo biến cursor declare cr_MatHang cursor keyset FOR SELECT * FROM MAT_HANG WHERE MaMH like ‘TV%’ ORDER BY MaMH Mở cursor OPEN cr_MatHang
  101. Biến kiểu dữ liệu cursor Đọc dữ liệu FETCH NEXT FROM cr_MatHang WHILE @@FETCH_STATUS = 0 BEGIN Đọc tiếp dòng kế FETCH NEXT FROM cur_MatHang END
  102. Biến kiểu dữ liệu cursor Đóng cursor CLOSE cr_MatHang DEALLOCATE cr_MatHang
  103. Biến kiểu dữ liệu cursor . Ví dụ 2: Đọc dữ liệu cursor của bảng SV chỉ đọc các sinh viên có họ bắt đầu là L.
  104. Biến kiểu dữ liệu cursor Khai báo biến cursor Declare cr_sv cursor keyset For select * from SV Where tensv like‘L%' Order by masv Mở cursor OPEN cr_SV
  105. Biến kiểu dữ liệu cursor Đọc dữ liệu Fetch next from cr_sv While @@fetch_status =0 Begin Đọc tiếp dòng kế FETCH NEXT FROM cr_SV END
  106. Biến kiểu dữ liệu cursor Đóng cursor CLOSE cr_SV DEALLOCATE cr_SV
  107. Biến kiểu dữ liệu cursor . Ví dụ 3: Cập nhật dữ liệu cho cột ThanhTien trong bảng PNHAP bằng cách duyệt qua từng phiếu nhập, tính ra trị giá nhập của từng phiếu căn cứ vào số lượng nhập và đơn giá nhập của từng vật tư trong bảng CTPNHAP, sau cùng cập nhật vào cộtThanhTien.
  108. Biến kiểu dữ liệu cursor Khai báo biến cursor,các biến cục bộ declare @Sopn char(4), @TongTT Money DECLARE cr_Pnhap CURSOR FORWARD_ONLY FOR SELECT MAPN FROM PNHAP
  109. Biến kiểu dữ liệu cursor Mở cursor OPEN cr_Pnhap
  110. Biến kiểu dữ liệu cursor Dịch chuyển con trỏ Đọc dữ liệu và cập nhật giá trị cr_Pnhap vào @SoPN fetch next from cr_Pnhap into @Sopn while @@fetch_status = 0 begin select @Tongtt = sum(SLNhap*dongia) from ctpnhap where mapn = @sopn Print‘dang cap nhat phieu nhap: ’ + @SoPN update pnhap set Thanhtien = @TongTT Where Current OF cr_Pnhap// mapn=@SOPN dịch con trỏ đến dòng kế tiếp fetch next from cr_Pnhap into @Sopn end
  111. Biến kiểu dữ liệu cursor Đóng cursor CLOSE cr_Pnhap DEALLOCATE cr_Pnhap
  112. Biến kiểu dữ liệu cursor Khai báo biến cursor,các biến cục bộ declare @sopn char(4), @tongtt money declare cr_pnhap cursor forward_only for select mapn from pnhap Mở cursor Open cr_Pnhap
  113. Biến kiểu dữ liệu cursor Đọc dữ liệu và cập nhật giá trị while (0 = 0) begin fetch next from cur_pnhap into @sopn if @@fetch_status<>0 Break Select @Tongtg = Sum(SLNhap*dongia) From Ctpnhap where mapn = @sopn Print‘dang cap nhat phieu nhap: ’ + @sopn Update Pnhap Set Thanhtien = @TongTT where current of cr_pnhap end
  114. Biến kiểu dữ liệu cursor Đóng cursor CLOSE cr_Pnhap DEALLOCATE cr_Pnhap
  115. Biến kiểu dữ liệu cursor DECLARE Tên_cursor CURSOR {kiểu đọc | cập nhật dữ liệu} FOR Câu lệnh SELECT Mở cursor OPEN Tên_cursor
  116. Biến kiểu dữ liệu cursor Đọc dữ liệu và cập nhật giá trị WHILE (0=0) Begin FETCH NEXT FROM [INTO danh_sách_biến] IF @@FETCH_STATUS <> 0 Break End
  117. Biến kiểu dữ liệu cursor Đóng cursor CLOSETên_cursor DEALLOCATETên_cursor
  118. Biến kiểu dữ liệu cursor Khi nào chúng ta cần sử dụng kiểu dữ liệu cursor trongTransaction-SQL để giải quyết các vấn đề: . SQL Server là một hệ quản trị CSDL quan hệ (Relational Database Management System) do đó chúng ta nên chọn giải pháp làm việc trên các bộ mẫu tin.
  119. Biến kiểu dữ liệu cursor . Khi cần giải quyết vấn đề cập nhật dữ liệu thì luôn ưu tiên chọn các hướng giải quyết trên bộ mẫu tin bởi vì khi đó làm cho các bộ xử lý được nhanh hơn. . Sau cùng là hướng giải quyết theo kiểu cursor là giải pháp sau cùng nhất để chọn lựa khi không còn giải pháp nào tốt hơn
  120. Vô hiệu hóa ràng buộc  Vô hiệu hóa ràng buộc Alter table nocheck constraint all  Cho kích hoạt việc kiểm tra các ràng buộc Alter table check constraint all 120
  121. 5. Index Tạo Index nhằm tăng tốc độ truy xuất dữ liệu. Thông thường, việc tạo index thường trên các thuộc tính PRIMARY KEY, UNIQUE CREATE INDEX tên_index ON tên_bng (tên_cot1(,tên_cot2), ) Tạo index tên cột TenNV trong bảng NhanVien: CREATE INDEX NV_idx ON NhanVien (TenNV) 121
  122. 5. Index  Nên tạo Index trong các trường hợp sau:  Những cột thường dùng trong mệnh đề WHERE để liên kết cột này với các bảng khác  Những cột có miền trị lớn và nhiều  Những cột có giá trị NULL lớn  Đối với các bảng lớn và truy vấn dữ liệu trên bảng trả về kết quả <4% sô dòng trong bảng 122
  123. 5. Index  Không nên tạo Index trong các trường hợp sau:  Những bảng nhỏ  Những cột thường không dùng trong mệnh đề WHERE  Những bảng thường xuyên cập nhật dữ liệu 123
  124. 5. Index Cú pháp câu lệnh xóa index DROP INDEX tên_bng.tên_index Xóa index NV_idx: DROP INDEX NhanVien.NV_idx 124