Sharing & Linking: [Database-Tự Học SQL] COM2034_SQL Server - Lab 4: Điều Kiện & Vòng Lặp

LÝ THUYẾT:

TRANSACTION: là việc thực hiện một nhóm các câu lệnh, các câu lệnh này được thực thi một cách tuần tự và độc lập nhau. Nếu tất cả câc lệnh trong nhóm thực hiện thành công khi đó tất cả các sự thay đổi dữ liệu được thực hiện trong Transaction được lưu vào cơ sở dữ liệu (COMMIT). Tuy nhiên, nếu có một trong các lệnh của nhóm lệnh thực thi thất bại thì toàn bộ tiến trình sẽ thất bại, đồng nghĩa với việc dữ liệu phải ROLLBACK về trạng thái ban đầu.

Các tiêu chuẩn Transaction:

Bảo toàn (Atomicity) - đảm bảo rằng tất cả các câu lệnh trong nhóm lệnh được thực thi thành công. Nếu không, transaction bị hủy bỏ tại thời điểm thất bại và tất cả các thao tác trước đó được khôi phục về trạng thái cũ.
Nhất quán (Consistency) - đảm bảo rằng cơ sở dữ liệu thay đổi chính xác các trạng thái khi một transaction được thực thi thành công.
Độc lập (Isolation) - cho phép các transaction hoạt động độc lập và minh bạch với nhau.
Bền bỉ (Durability) - đảm bảo rằng kết quả của một transaction được commit vẫn tồn tại trong trường hợp lỗi hệ thống.

Transaction T-SQL tường minh có dạng (code mẫu đơn giản)

SET XACT_ABORT ON

BEGIN TRAN

BEGIN TRY
	--  Lệnh 1
	--  Lệnh 2
	--  Lệnh ...
 
COMMIT
END TRY
 
BEGIN CATCH
 
  ROLLBACK
 
END CATCH

Ý nghĩa các lệnh dùng để xử lý transaction.

COMMIT - để lưu các thay đổi.
ROLLBACK - để khôi phục lại các thay đổi.
SAVEPOINT - tạo ra các điểm trong transaction để ROLLBACK.
SET TRANSACTION - thiết lập các thuộc tính cho transaction.

Các lệnh điều khiển transaction chỉ được sử dụng với các lệnh thao tác dữ liệu DML như - INSERT, UPDATE và DELETE.

Noted: Chúng không thể được sử dụng trong lệnh CREATE TABLE hoặc DROP TABLE vì các hoạt động này được tự động được commit trong cơ sở dữ liệu.

Giao dịch lồng (nested transaction) là giao dịch được viết bên trong một giao dịch khác.

Mỗi khi câu lệnh BEGIN TRAN được thực thi, hàm hệ thống @@TRANCOUNT được tăng thêm 1.
Khi thực thi câu lệnh COMMIT TRAN.
Nếu @@TRANCOUNT > 1, các thay đổi sẽ không được commit. Thay vào đó @@TRANCOUNT giảm đi 1.
Nếu @@TRANCOUNT = 1, mọi thay đổi đã được thực hiện trên CSDL trong suốt giao dịch sẽ được commit và @@TRANCOUNT được gán bằng 0.
Câu lệnh ROLLBACK TRAN roll-back toàn bộ các giao dịch đang hoạt động và thiết lập giá trị cho @@TRANCOUNT về 0

YÊU CẦU:

Bài 1: (Mục đích là làm quen với Transaction)

Hiển thị mã phòng ban bằng 2, hiển thị Transaction count
Thực hiện đổi tên (TENPHG) thành 'Phong KT 004'
Thực hiện Rollback lại tên ban đầu (lúc chưa update ...set....)

Bài 2:

Yêu cầu sử dụng giao dịch kết hợp với try catch để chặn lỗi thực hiện 2 yêu cầu sau:

Viết chương trình thêm một nhân viên mới. Nếu thêm nhân viên thành công thì thêm 1 thân nhân cho nhân viên vừa tạo.
Viết chương trình thêm một công việc mới cho đề án số 1, đồng thời phân công nhân viên mói thêm ở câu trên đảm nhận công việc mới tạo.

THỰC HIỆN:

Bài 1:

Hiển thị mã phòng ban bằng 2, hiển thị Transaction count
Thực hiện đổi tên (TENPHG) thành 'Phong KT 004'
Thực hiện Rollback lại tên ban đầu (lúc chưa update ...set....)

Code:

-- ban đầu chưa thay đổi (1)
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2
SELECT @@TRANCOUNT AS dem_Tran

-- thực hiện sửa TENPHG thành 'Phong KT 004' (2)
BEGIN TRAN
	  UPDATE PHONGBAN
	  SET TENPHG = 'Phong KT 004'
	  WHERE MAPHG = 2

--COMMIT TRAN -- Sau khi commit, TRANCOUNT sẽ  = 0
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2 -- kiểm sau khi thực hiện lệnh update... set..
SELECT @@TRANCOUNT AS dem_Tran -- kiểm tra biến đếm 

-- thực hiện Rollback và kiểm tra kết quả (3)
ROLLBACK TRAN -- RollBack thành không khi TRANCOUNT > 0 
SELECT * FROM PHONGBAN WHERE PHONGBAN.MAPHG = 2
SELECT @@TRANCOUNT AS dem_Tran

Kết quả:

(1) Ban đầu TRANCOUNT = 0
(2) Khi thực hiện thay đổi tên TRANCOUNT tăng lên 1 đơn vị
(3) Thực hiện Rollback thì TRANCOUNT sẽ thiết lập giá trị là 0

Bài 2:

Yêu cầu sử dụng giao dịch kết hợp với try catch để chặn lỗi thực hiện 2 yêu cầu sau:

Viết chương trình thêm một nhân viên mới. Nếu thêm nhân viên thành công thì thêm 1 thân nhân cho nhân viên vừa tạo.

Code mẫu đơn giản:

DECLARE @ma_nv NVARCHAR(9) = '2222'
SET XACT_ABORT ON

BEGIN TRAN

BEGIN TRY
	INSERT INTO NHANVIEN ( [HONV] ,[TENLOT] ,[TENNV] ,[MANV] ,[NGSINH] ,[DCHI] ,[PHAI] ,[LUONG] ,[MA_NQL] ,[PHG] )
					VALUES ('Van', 'Cong',  'Khanh',  @ma_nv, '2000/10/10','Binh Duong','Nam',30000,'005', 5)
	INSERT INTO THANNHAN ([MA_NVIEN] ,[TENTN],[PHAI],[NGSINH] ,[QUANHE]	)
					VALUES (@ma_nv, 'Nhan Nhan', 'Nam','2000/10/10','ban be') -- sửa @ma_nv thành 99999
	PRINT 'Them THANH CONG'

COMMIT -- thực hiện lưu nếu cả 2 dòng lệnh thêm thành công

END TRY
 
BEGIN CATCH
 
  ROLLBACK -- Nếu 1 trong 2 lệnh INSERT không thành công sẽ thông báo lỗi
  PRINT 'Them THAT BAI'
 
END CATCH

Noted: Với code trên khi thêm nhân viên mới chúng ta cần thay dòng thứ 1 là được, tuy nhiên để kiểm tra ROLLBACK thi chúng ta cố tình sửa dòng INTSERT trong bảng thân sai đi để kiểm tra. Ví dụ: thay đổi @ma_nv thành '99999' (dòng số 10)

Với cách trên nếu xảy lỗi chúng ta sẽ khó biết được lỗi xảy ra ở câu lệnh nào, nên chúng ta có thể thiết lập 1 cờ để kết hợp với IF... ESLE/CASE để việc xử lý được tường minh hơn.

Code tham khảo

DECLARE @is_error BIT = 0;
DECLARE @ma_nv NVARCHAR(9) = '77777';
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
	INSERT INTO NHANVIEN ( [HONV] ,[TENLOT] ,[TENNV] ,[MANV] ,[NGSINH] ,[DCHI] ,[PHAI] ,[LUONG] ,[MA_NQL] ,[PHG] )
					VALUES ('Van', 'Cong',  'Khanh',  @ma_nv, '2000/10/10','Binh Duong','Nam',30000,'005', 5)
	-- SELECT * FROM NHANVIEN WHERE MANV = @ma_nv
END TRY

BEGIN CATCH
	SET @is_error = 1;
END CATCH

IF @is_error = 0
	BEGIN
		BEGIN TRY
			INSERT INTO THANNHAN ([MA_NVIEN] ,[TENTN],[PHAI],[NGSINH] ,[QUANHE]	)
					VALUES (@ma_nv, 'Nhan Nhan', 'Nam','2000/10/10','ban be')
			PRINT 'Them THANH CONG'
		END TRY
		BEGIN CATCH
			ROLLBACK TRAN
			PRINT 'Them vao bang (THANNHAN) THAT BAI'
		END CATCH
	END
ELSE
BEGIN
	PRINT 'Them vao bang (NHANVIEN) THAT BAI'
END;

Viết chương trình thêm một công việc mới cho đề án số 1, đồng thời phân công nhân viên mới thêm ở câu trên đảm nhận công việc mới tạo.

Chúng tôi thực với cú pháp đơn để các bạn dễ hiểu, các bạn có thể kết với RAISEERROR kết hợp với các mã lỗi (tham khảo ở phần 2 )

DECLARE 
	@ma_dean INT = 1
	,@so_tt INT = 12,
	@ma_nv NVARCHAR(9) = '111';
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
	
	INSERT INTO CONGVIEC (MADA, STT, TEN_CONG_VIEC) VALUES (@ma_dean, @so_tt, 'Tin Hoc Van Phong')
	INSERT INTO PHANCONG (MA_NVIEN, MADA, STT, THOIGIAN) VALUES (@ma_nv, @ma_dean, @so_tt,20)

	COMMIT TRAN
	PRINT 'Them THANH CONG'
END TRY
BEGIN CATCH
	ROLLBACK TRAN
	PRINT 'Them THAT BAI'
END CATCH

Tham khảo thêm tại đây

Xong!

Sharing & Linking

Pages

[Database-Tự Học SQL] COM2034_SQL Server - Lab 4: Điều Kiện & Vòng Lặp - P3 TRANSACTION (Transact-SQL)

Để có những bài viết tốt hơn, hãy cho chúng tôi bình luận của bạn về bài viết này. Cám ơn các bạn!

No comments:

Post a Comment