Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Chương 4: Lập trình với Transact-SQL - Nguyễn Thị Mỹ Dung
1. Thiết kế View
- Là một bảng ảo được tạo ra từ tập con của các
bảng (Table) thật khác. Đối với người dùng View là
bảng thật.
- Cũng tương tự như truy vấn dữ liệu là dùng để
xem dữ liệu từ nhiều bảng khác nhau trong CSDL.
- Làm giảm sự phức tạp của CSDL bảo vệ dữ
liệu đối với người dùng không được phép truy cập.
- Các lệnh sử dụng trên View tương tự như trên
Table trong CSDL.
- Nhược điểm của View là mất thời gian truy cập
dữ liệu từ bảng (table) gốc.
Trang 1
Trang 2
Trang 3
Trang 4
Trang 5
Trang 6
Trang 7
Trang 8
Trang 9
Trang 10
Tải về để xem bản đầy đủ
Bạn đang xem 10 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Chương 4: Lập trình với Transact-SQL - Nguyễn Thị Mỹ Dung", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
Tóm tắt nội dung tài liệu: Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Chương 4: Lập trình với Transact-SQL - Nguyễn Thị Mỹ Dung
GIN PRINT N'CÁC ĐỀ TÀI CÓ KINH PHÍ CAO:' + CONVERT(CHAR, @TBKP) SELECT MADT, TENDT, CHUNHIEM, KINHPHI FROM DETAI WHERE KINHPHI > @TBKP END Biên soạn: Nguyễn Thị Mỹ Dung 14 CH4: LẬP TRÌNH TRANSACT - SQL VD2: Tìm thông tin những đề tài có nhiều hơn 3 sinh viên thực hiện, nếu không có in ra thông báo để biết. IF EXISTS (SELECT MASV, COUNT (MADT) FROM SV_DT GROUP BY MASV HAVING COUNT (MADT) > 3) BEGIN PRINT N'CÁC ĐỀ TÀI CÓ SỐ LƯỢNG TRÊN 2 SINH VIÊN THỰC HIỆN!' SELECT DISTINCT D.MADT, TENDT, CHUNHIEM, KINHPHI, COUNT(SD.MASV)AS SL_SV FROM DETAI D INNER JOIN SV_DT SD ON D.MADT = SD.MADT GROUP BY D.MADT, TENDT, CHUNHIEM, KINHPHI HAVING COUNT(SD.MASV) > 3 END ELSE PRINT N'KHÔNG CÓ ĐỀ TÀI NÀO TRÊN 3 SINH VIÊN THỰC HIỆN!' Biên soạn: Nguyễn Thị Mỹ Dung 4. Câu lệnh Case end Dùng để lựa chọn nhiều giá trị, nếu sau Case xuất hiện khi biểu thức có kiểu dữ liệu số. Case [] when then when then .. [Else ] End Biên soạn: Nguyễn Thị Mỹ Dung 15 CH4: LẬP TRÌNH TRANSACT - SQL Ví dụ1: DECLARE @TEN CHAR(3), @XUAT NVARCHAR(100) SET @TEN = 'PHI' SET @XUAT = (CASE @TEN WHEN 'MR' THEN N'Xin chào quý ông!!!' WHEN 'Mrs' THEN N'Xin chào quý bà!!!' WHEN 'Ms' THEN N'Xin chào quý cô!!!' ELSE N'XIN CHÀO MỌI NGƯỜI!!!' END) PRINT @XUAT Biên soạn: Nguyễn Thị Mỹ Dung Ví dụ 2: Xếp loại học lực cho sinh viên SELECT SINHVIEN.MASV,HOTENSV, HOCLUC, XEPLOAI= (CASE WHEN HOCLUC <5 THEN 'YEU' WHEN HOCLUC>=5 AND HOCLUC<7 THEN 'TB' WHEN HOCLUC>=7 AND HOCLUC<8 THEN 'KHA' ELSE 'GIOI' END) FROM SINHVIEN Biên soạn: Nguyễn Thị Mỹ Dung 16 CH4: LẬP TRÌNH TRANSACT - SQL 5. Lệnh vòng lặp Các câu lệnh được thực thi nhiều lần (lặp) khi nào điều kiện vẫn còn đúng. WHILE BEGIN --Các câu lệnh END - Break: lệnh break nằm bên trong vòng lặp while dùng để kết vòng lặp. - Continue: lệnh continue nằm bên trong vòng lặp while để bỏ qua các lệnh phía sau nó và bắt đầu vòng lặp mới. Biên soạn: Nguyễn Thị Mỹ Dung VD1: In tổng từ 1 đến 10 DECLARE @TONG INT, @I INT SET @TONG = 0 SET @I = 1 WHILE (@I <= 10) BEGIN SET @TONG = @TONG + @I SET @I = @I + 1 END PRINT @TONG Biên soạn: Nguyễn Thị Mỹ Dung 17 CH4: LẬP TRÌNH TRANSACT - SQL VD2: Tính tổng các số chẵn từ 1 đến 20 DECLA RE @TONGCHA N INT, @I INT SET @TONGCHA N = 0 SET @I = 1 WHILE (@I <= 20) BEGIN IF @I % 2 = 0 BEGIN SET @TONGCHA N = @TONGCHA N + @I PRINT N'TỔNG ['+ CONV ERT(CHA R(2), @I)+'] = '+CONV ERT(CHAR(5), @TONGCHA N) END ELSE BEGIN SET @I = @I + 1 CONTINUE END SET @I = @I + 1 END PRINT @TONGCHA N Biên soạn: Nguyễn Thị Mỹ Dung 1. Khái niệm Thủ tục là một đối tượng trong CSDL bao gồm một tập câu lệnh SQL được kết hợp với các câu lệnh khác như: - Cấu trúc điều khiển (IF, CASE, WHILE,...), các biến,... - Các câu lệnh SQL. Sử dụng các thủ tục lưu trữ trong CSDL sẽ giúp tăng hiệu năng của CSDL như: - Đơn giản hoá các thao tác trên CSDL nhờ vào khả năng module hoá các thao tác. - Thủ tục lưu trữ cho phép chúng ta thực hiện cùng một yêu cầu bằng một câu lệnh đơn giản thay vì phải sử dụng nhiều dòng lệnh SQL (tiết kiệm thời gian thực thi). - Ngoài ra, thủ tục có thể cấp phát quyền cho người dùng, nhờ đó tăng khả năng bảo mật đối với hệ thống. Biên soạn: Nguyễn Thị Mỹ Dung 18 CH4: LẬP TRÌNH TRANSACT - SQL 2. Tạo procedure Procedure có thể sử dụng để kiểm tra tham số đầu vào, đầu ra cho các thao tác dữ liệu. - Cú pháp: CREATE PROC | PROCEDURE [()] AS --Tập lệnh SQL -- GO - Thực thi lời gọi thủ tục: EXEC | EXECUTE Biên soạn: Nguyễn Thị Mỹ Dung VD1: Viết thủ tục nhập vào một tên. Xác định: - Nếu nhập ‘Mr’ thì print ‘Xin chào quý ông!’ - Nếu nhập ‘Mrs’ thì print ‘Xin chào quý bà!’ - Nếu nhập ‘Ms’ thì print ‘Xin chào quý cô!’ --Tham khảo CREATE PROC Kt (@tensv nvarchar(3)) AS IF (@tensv='Mr') PRINT (N'Xin chào quý ông!!!') IF (@tensv='Mrs') PRINT (N'Xin chào quý bà!!!') IF (@tensv = 'Ms') PRINT (N'Xin chào quý cô!!!') GO EXECUTE kt 'Ms KIM' Biên soạn: Nguyễn Thị Mỹ Dung 19 CH4: LẬP TRÌNH TRANSACT - SQL VD2 : Kiểm tra MASV, MADT khi nhập dữ liệu vào bảng SV_DT CREATE PROC PRO_SV_DT (@MASV NVARCHAR(10), @MADT NVARCHAR(10), @NOIA_D NVARCHAR(40), @KETQUA FLOAT) AS IF EXISTS (SELECT MASV FROM SINHVIEN WHERE MASV = @MASV) BEGIN IF EXISTS (SELECT MADT FROM DETAI WHERE MADT = @MADT) BEGIN INSERT INTO SV_DT (MASV, MADT, NOIA_D, KETQUA) VALUES (@MASV, @MADT, @NOIA_D, @KETQUA) PRINT N'ĐÃ THÊM THÀNH CÔNG' END END ELSE PRINT 'KHÔNG THE INSERT DO MASV HAY MADT KHÔNG TON TAI' RETURN 0 GO --Thực hiện lệnh để kiểm tra EXEC PRO_SV_DT 'SV12','DT04','AN GIANG',8 Biên soạn: Nguyễn Thị Mỹ Dung VD3: Giả sử ta cần thực hiện một chuỗi các thao tác như sau trên cơ sở dữ liệu 1. Thêm vào danh sách đề tài (MADT, TênDT, Chủ nhiệm, Kinh phí): DT1001, Xây dựng Website hỗ trợ việc làm, Trần Kiến Quốc, 20 2. Lập danh sách sinh viên thực hiện đề tài DT1001 cho tất cả sinh viên có học lực trên 8. Cách 1: Theo cách thông thường INSERT INTO DETAI VALUES (‘DT1001', N‘Xây dựng Website hỗ trợ việc làm’, N‘Trần Kiến Quốc’, 20) INSERT INTO SV_DT (MASV,MADT) SELECT MASV, ‘DT1001' FROM SINHVIEN WHERE HOCLUC>8 Biên soạn: Nguyễn Thị Mỹ Dung 20 CH4: LẬP TRÌNH TRANSACT - SQL VD3 - Cách 2: Sử dụng Procedure CREATE PROC THEM(@MA DT CHA R(10), @TENDT CHA R(50), @CHUNHIEM CHAR(40), @KINHPHI INT) AS BEGIN INSERT INTO DETAI (MADT, TENDT, CHUNHIEM, KINHPHI) VALUES (@MADT, @TENDT, @CHUNHIEM, @KINHPHI) INSERT INTO SV_DT (MASV, MADT) SELECT MASV, @MADT FROM SINHV IEN WHERE HOCLUC > 8 END --Thực hiện lệnh để kiểm tra EXEC THEM 'DT1001','Xây dựng Website hỗ trợ v iệc làm', 'Trần Kiến Quốc', 20 Biên soạn: Nguyễn Thị Mỹ Dung 3. Sửa Procedure Khi một thủ tục đã được tạo ra, ta có thể tiến hành định nghĩa lại thủ tục đó bằng câu lệnh ALTER PROCEDURE. Câu lệnh này sử dụng tương tự như câu lệnh CREATE PROCEDURE. Cú pháp: ALTER PROC | PROCEDURE [()] AS [WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION] --Tập lệnh SQL -- GO Biên soạn: Nguyễn Thị Mỹ Dung 21 CH4: LẬP TRÌNH TRANSACT - SQL VD: Sửa Procedure từ VD3 ALTER PROC THEM(@MAD T CHAR(10),@TEND T CHAR(50),@CHUNHIEM CHAR(40),@KINHPHI INT) AS BEGIN IF NOT EXISTS (SELECT MADT FROM DETAI WHERE MADT = @MADT) BEGIN INSERT INTO DETAI (MADT, TENDT, CHUNHIEM, KINHPHI) VALUES (@MADT, @TENDT, @CHUNHIEM, @KINHPHI) PRINT N'THÊM THÀNH CÔNG' END ELSE PRINT N'MADT ĐÃ CÓ!!!' BEGIN INSERT INTO SV_DT (MASV, MADT) SELECT MASV, @MADT FROM SINHVIEN WHERE HOCLUC > 8 PRINT N'ĐÃ THÊM HOÀN TẤT!!!' END END Biên soạn: Nguyễn Thị Mỹ Dung 4. Xóa Procedure Cú pháp: DROP PROCEDURE Ví dụ: DROP PROCEDURE PRO_SV_DT Bài tập: 1. Viết procedure kiểm tra một số là chẵn hay lẻ 2. Viết proc in ra thứ tương ứng với số nhập vào 3. Viết thủ tục kiểm tra khóa chính khi thêm dữ liệu vào bảng Sinhvien, Monhoc, Khoa. 4. Viết thủ tục kiểm tra khóa chính Masv, Mamh đã có trong bảng Sinhvien và bảng Monhoc khi thêm vào bảng kết quả. Biên soạn: Nguyễn Thị Mỹ Dung 22 CH4: LẬP TRÌNH TRANSACT - SQL 1. Tạo Proc xóa một khoa nhập vào nếu không vi phạm RBTV về khóa. 2. Tạo Pro xóa môn học nhập vào nếu không vi phạm RBTV về khóa 3. Tạo Pro xóa Sinh viên nhập vào nếu không vi phạm RBTV về khóa. 4. Tạo Proc nâng điểm của sinhvien có MASV Mã môn học, số điểm nâng được nhập vào (sửa lại bài này: mã môn học bằng tên môn học) 5. Biên soạn: Nguyễn Thị Mỹ Dung 1. Khái niệm Trigger là một kiểu thủ tục được lưu trữ đặc biệt, chúng được tự động gọi khi có sự sửa đổi dữ liệu mà trigger được thiết kế để bảo vệ. Trigger giúp đảm bảo tính toàn vẹn dữ liệu trong CSDL bằng cách ngăn không cho những sự thay đổi không nhất quán được thực hiện. Trigger chỉ được kích hoạt khi các câu lệnh được thực thi trên bảng là: INSERT, UPDATE, DELETE. Biên soạn: Nguyễn Thị Mỹ Dung 23 CH4: LẬP TRÌNH TRANSACT - SQL 2. Giao tác - transaction Dùng để chỉ công việc gồm nhiều bước, các bước được thi hành lần lượt. - Mặc định SQL Server thực hiện các chế độ giao tác không tường minh. Mỗi một câu lệnh INSERT, UPDATE, DELETE là một Transaction. Sau khi thực hiện lệnh, các thay đổi sẽ cập nhật vào CSDL. - Giao tác tường minh + Khai báo trước với từ khóa: BEGIN TRAN + Kết thúc giao tác với từ khóa ROLLBACK TRAN nếu có lỗi xảy ra hoặc không, mọi thay đổi đều bị hủy. + Kết thúc giao tác với từ khóa COMMIT TRAN khi các lệnh hoàn tất, dữ liệu thay đổi sẽ giữ lại. Biên soạn: Nguyễn Thị Mỹ Dung VD: Đếm số lượng đề tài trước và sau khi thêm SELECT COUNT(*) AS [SL_OLD] FROM DETAI BEGIN TRAN INSERT INTO DETAI VALUES ('DT1003', N'QUẢN LÝ ĐIỂM RÈN LUYỆN SINH VIÊN', N'MAI THANH TÂM', 15) SELECT COUNT(*) AS [SL MOI] FROM DETAI ROLLBACK TRAN | COMMIT TRAN SELECT COUNT(*) AS [SL HIENTAI] FROM DETAI Biên soạn: Nguyễn Thị Mỹ Dung 24 CH4: LẬP TRÌNH TRANSACT - SQL 3. Trigger Kiểm tra RBTV khi thao tác dữ liệu đối với các chức năng: thêm (insert), sửa (update), xóa (delete), a/ Khai báo: CREATE TRIGGER ON [INSTEAD OF] | [FOR | AFTER] AS BEGIN --TẬP LỆNH SQL --. END Biên soạn: Nguyễn Thị Mỹ Dung b/ Các thành phần - Tên bảng: Trigger có ảnh hưởng trên bảng này - INSTEAD OF: loại Trigger này sẽ kiểm tra dữ liệu trước, dữ liệu không bị thay đổi, thay thế cập nhật dữ liệu bằng hành động khác. - FOR | AFTER: loại Trigger thông thường sẽ kiểm tra các Rule và ràng buộc, dữ liệu tạm thời sẽ thay đổi - INSERT | UPDATE | DELETE: các hành động này sẽ kích hoạt trigger hoạt động. Biên soạn: Nguyễn Thị Mỹ Dung 25 CH4: LẬP TRÌNH TRANSACT - SQL c/ Các bảng trung gian và bảng tạm - Inserted: Chứa dữ liệu được thêm mới trong INSERT | UPDATE, có cấu trúc bảng giống bảng thực. - Deleted: Chứa dữ liệu bị xóa trong DELETE | UPDATE, cấu trúc bảng giống bảng thực được cập nhật. - Chức năng UPDATE trong SQL sẽ xóa dòng dữ liệu cũ, thêm dòng dữ liệu mới với thông tin được cập nhật. Biên soạn: Nguyễn Thị Mỹ Dung VD1: Tạo trigger cập nhật, chèn dữ liệu năm sinh cho sinh viên phải trên 18 CREATE TRIGGER TRIG_SV ON SINHVIEN FOR INSERT, UPDATE AS BEGIN --CAP NHAT DU LIEU IF UPDATE(NGAYSINH) BEGIN IF EXISTS (SELECT * FROM DELETED A, INSERTED B WHERE (A.NAMSINH- B.NAMSINH)<18 BEGIN PRINT 'SINH VIEN PHAI TREN 18 TUOI' ROLLBACK TRAN END END --CHEN DU LIEU IF EXISTS (SELECT * FROM INSERTED WHERE (YEAR(GETDATE()) - NAMSINH))<18) BEGIN PRINT 'SINH VIEN PHAI TREN 18 TUOI' ROLLBACK TRAN END END--KETTHUCTRIGGER Biên soạn: Nguyễn Thị Mỹ Dung 26 CH4: LẬP TRÌNH TRANSACT - SQL - Nhập dòng lệnh INSERT INTO để kiểm tra INSERT INTO SINHVIEN VALUES ('SV100', N'NGUYỄN THỊ MỸ DUNG', 1999, 7.0, N'ĐỒNG THÁP') - Thông báo lỗi như sau: Biên soạn: Nguyễn Thị Mỹ Dung VD2: Cài đặt Trigger khi xóa dữ liệu ở bảng Sinh viên sẽ xóa luôn dữ liệu ở bảng tham chiếu. B1: Tạo Trigger CREATE TRIGGER DEL_SV ON SINHVIEN FOR DELETE AS BEGIN IF (@@ROWCOUNT = 0) BEGIN PRINT N'BẢNG SINH VIÊN KHÔNG CÓ DỮ LIỆU!' RETURN -- NẾU KHÔNG CÓ THÌ KHỎI XÓA END DELETE SV_DT FROM SV_DT S, DELETED T WHERE S.MASV = T.MASV PRINT N'ĐÃ XÓA DỮ LIỆU CÁC BẢNG SINHVIEN, DETAI!' END Biên soạn: Nguyễn Thị Mỹ Dung 27 CH4: LẬP TRÌNH TRANSACT - SQL Bước 2: TẠO THỦ TỤC KIỂM TRA TRIGGER XÓA CREATE PROC XOASV @MASV CHAR(10) AS IF EXISTS (SELECT * FROM SINHVIEN WHERE MASV = @MASV) BEGIN --VÔ HIỆU HÓA TẠM THỜI CÁC RÀNG BUỘC ALTER TABLE SV_DT NOCHECK CONSTRAINT ALL --CHẠY TRIGGER DELETE FROM SINHVIEN WHERE MASV = @MASV --KÍCH HOẠT TRỞ LẠI CÁC RÀNG BUỘC ALTER TABLE SV_DT CHECK CONSTRAINT ALL END ELSE PRINT N'KHÔNG CÓ SINH VIÊN CÓ MÃ SỐ: '+ @MASV GO EXEC XOASV 'SV100' Biên soạn: Nguyễn Thị Mỹ Dung VD3: Tạo trigger thỏa mãn ràng buộc khi thay đổi mã số đề tài sẽ thay đổi các thông tin liên quan B1: Tạo Trigger cập nhật CREATE TRIGGER UPDATE_DT ON DETAI FOR UPDATE AS BEGIN IF (@@ROWCOUNT = 0) BEGIN PRINT N'BẢNG ĐỀ TÀI KHÔNG CÓ DỮ LIỆU!' RETURN -- NẾU KHÔNG CÓ THÌ KHỎI XÓA END IF UPDATE (MADT) BEGIN UPDATE T1 SET T1.MADT = T3.MADT FROM SV_DT T1, DELETED T2, INSERTED T3 WHERE T1.MADT = T2.MADT --Tương tự cho các bảng khác (nếu có) PRINT N'ĐÃ CẬP NHẬT XONG DỮ LIỆU!' END END Biên soạn: Nguyễn Thị Mỹ Dung 28 CH4: LẬP TRÌNH TRANSACT - SQL B2: Tạo thủ tục kiểm tra Trigger cập nhật CREATE PROC CAPNHATDT @MADT_OLD CHAR(10), @MADT_NEW CHAR(10) AS IF EXISTS (SELECT * FROM DETAI WHERE MADT = @MADT_OLD) BEGIN --VÔ HIỆU HÓA TẠM THỜI CÁC RÀNG BUỘC ALTER TABLE SV_DT NOCHECK CONSTRA INT ALL --CHẠY TRIGGER UPDATE DETAI SET MADT = @MADT_NEW WHERE MADT = @MADT_OLD --KÍCH HOẠT TRỞ LẠI CÁC RÀNG BUỘC ALTER TABLE SV_DT CHECK CONSTRA INT ALL END ELSE PRINT N'KHÔNG CÓ ĐỀ TÀI CÓ MÃ SỐ: '+ @MADT_OLD GO EXEC CAPNHATDT 'DT3003', 'DT2002' Biên soạn: Nguyễn Thị Mỹ Dung 4. Sửa Trigger Khi một trigger đã được tạo ra, ta có thể tiến hành thay đổi trigger đó bằng câu lệnh ALTER TRIGGER. Câu lệnh này sử dụng tương tự như câu lệnh CREATE TRIGGER. ALTER TRIGGER ON [INSTEAD OF] | [FOR | AFTER] AS BEGIN --TẬP LỆNH SQL --. END Ví dụ: SV tự thực hiện Biên soạn: Nguyễn Thị Mỹ Dung 29 CH4: LẬP TRÌNH TRANSACT - SQL 5. Xóa trigger Cú pháp: DROP TRIGGER Ví dụ: DROP TRIGGER TRIG_SV Lưu ý: - Không được tạo và tham chiếu bảng tạm - Không tạo hay thay đổi, xóa cấu trúc các đối tượng sẵn có trong CSDL (CREATE, ALTER, DROP) - Không gán hay cấp quyền cho người dùng - Khi cài đặt nhiều Trigger sẽ làm chậm hệ thống. Biên soạn: Nguyễn Thị Mỹ Dung Tạo các trigger sau: 1. Khi sửa table khoa, sửa đổi makhoa trên table sinhvien. 2. Khi sửa table môn, sửa đổi mamh trên table ketqua. 3. Khi sửa table sinhvien, sửa đổi masv trên table ketqua. 4. Khi xóa table sinhvien. Xóa những sinh viên tương ứng trong table ketqua. 5. Khi xoa table khoa: để trống mã khoa cho cho những sinh viên có mã khoa trùng với mã mã khoa vừa bị xóa bên table khoa. Biên soạn: Nguyễn Thị Mỹ Dung 30 CH4: LẬP TRÌNH TRANSACT - SQL 6. Khi thêm vào table monhoc: số tiết phải >=15 và <=90 7. Khi thêm vào table ketqua: Kiểm tra sự tồn tại của masv, mamh. 8. Khi thêm table sinhvien: Nếu mã khoa không tồn tại bên table khoa, nếu mã sv để trống hoặc bị trùng thì không cho thêm vào. Biên soạn: Nguyễn Thị Mỹ Dung - Cách sử dụng, tạo View và Index - Khai báo và lập trình trong T - SQL + Declare: khai báo + Set, Select: gán giá trị + Print, Select: hiển thị giá trị + Câu lệnh if [else] + Câu lệnh Case end + Câu lệnh While - Xây dựng Procedure - Xây dựng Trigger Biên soạn: Nguyễn Thị Mỹ Dung 31
File đính kèm:
- bai_giang_he_quan_tri_co_so_du_lieu_sql_server_chuong_4_lap.pdf