Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 2: Quản lý truy xuất đồng thời - Lương Trần Hy Hiến

Khái niệm giao tác (transaction).
Các vấn ñề xảy ra khi nhiều người cùng khai thác Cơ Sở Dữ Liệu.
Các giải pháp cho các vấn ñề trên. DBMS02 – Slides 2
Sử dụng giao tác trong SQL Server.
Giao tác là một dãy các thao tác cần thực hiện trên cơ sở dữ liệu dưới một ñơn vị duy nhất, nghĩa là hoặc thực hiện tất cả các thao tác hoặc không thực hiện thao DBMS02 – Slides 3 tác nào cả.
Ví dụ
Hệ thống giao dịch ngân hàng
Hệ thống ñặt vé bay
DBMS là môi trường ña người dùng Nhiều thao tác truy xuất lên cùng một ñơn vị dữ Giao tác DBMS02 – Slides 4
liệu
Nhiều thao tác thi hành ñồng thời Thời gian Khách hàng 1 Khách hàng 2 Tìm thấy 1 chỗ trống Tìm thấy 1 chỗ trống ðặt vé bay ðặt vé bay 2 khách hàng ñặt cùng 1 chỗ trống ??? Cơ chế tuần tự
Ví dụ: giao tác chuyển khoản từ A  B gồm 2 thao tác sau:
Trừ tiền A
Cộng tiền B DBMS02 – Slides 5
Chuyển khoản ñược thực hiện dưới dạng giao tác (transaction) nghĩa là hoặc thực hiện cả 2 việc trừ tiền A và cộng tiền B hoặc nếu có sự cố thì không làm gì cả và thông báo giao tác thất bại.
Khi DBMS gặp sự cố
Các thao tác có thể làm cho trạng thái CSDL không chính xác Giao tác Tài khoản A Tài khoản B DBMS02 – Slides 6 ðọc số dư của tài khoản A Kiểm tra (số dư > số tiền cần rút) Tăng số dư của tài khoản B Giảm số dư của tài khoản A Sự cố Ngân hàng chịu lỗ 1 khoảng tiền ???
Vấn ñề mất dữ liệu ñã cập nhật
Vấn ñề không thể ñọc lại
Vấn ñề dữ liệu không nhất quán DBMS02 – Slides 7
Vấn ñề mất dữ liệu ñã cập nhật
Ví dụ: Nhà sách còn 500 quyển sách.
Vào lúc T1 nhân viên A nhân yêu cầu mua 400 quyển từ khách hàng X. DBMS02 – Slides 8
Cũng vào T1 nhân viên B nhân yêu cầu mua 300 quyển từ khách hàng Y.
A và B ñọc dữ liệu thấy còn 500 quyển nên ñều ñồng ý bán
Vấn ñề mất dữ liệu ñã cập nhật (tt)
Ví dụ: Nhà sách còn 500 quyển sách.
Vào lúc T2 nhân viên A sẽ thực hiện cập nhật số sách từ 500 thành 100. DBMS02 – Slides 9
Vào lúc T3 nhân viên B sẽ thực hiện cập nhật số sách từ 500 thành 200.
Như vậy thao tác cập nhật của A không có tác dụng hay dữ liệu mà A cập nhật sẽ bị mất vì B cập nhật sau??? (last in wind)
Vấn ñề không thể ñọc lại
Ví dụ: Giả sử nhà sách còn 200 quyển sách.
Vào lúc T1 nhân viên A bán cho khách 150 quyển, sẽ thực hiện cập nhật số sách từ 200 thành 50. (giao dịch chưa hoàn thành chẳng hạn vì việc giao nhận DBMS02 – Slides 10 tiền chưa xong)
Sau ñó lúc T2, B nhận ñươc yêu cầu mua 100 quyển sách, nếu B ñược ñọc dữ liệu chưa hoàn tất thì B sẽ từ chối bán 100 quyển sách này.
Vấn ñề không thể ñọc lại (tt)
Ví dụ: Giả sử nhà sách còn 200 quyển sách.
Nếu vào lúc T3 vì lý do nào ñó chẳng hạn không ñủ tiền khách hàng của A không mua 150 quyển sách nửa. Giao tác bán hàng của A sẽ không thể thực hiện DBMS02 – Slides 11 nên quay về trạng thái số sách còn là 200.
Nhưng B ñã từ chối khách hàng.
Nếu B không ñọc ñược dữ liệu từ lúc T1 ñến T3 thì sẽ như thế nào?
Vấn ñề dữ liệu không nhất quán
Ví dụ: Giả sử nhân viên C cần tổng hợp 5 dòng dữ liệu 1 2 3 4 5 ñể làm một bản báo cáo. DBMS02 – Slides 12
T1:C ñọc và ñưa các dòng 1 2 3 4 vào báo cáo
T2:D lại xóa dòng 1 thay bằng dòng 6.
T3:C ñọc tiếp các dòng 5 6 ñưa vào báo cáo
Vậy báo cáo này xử lý cả dữ liệu cũ và mới  SAI
Nguyên tố (Atomicity)
Hoặc là toàn bộ hoạt ñộng của giao dịch ñược phản ánh ñúng ñắn trong CSDL hoặc không có hoạt ñộng nào cả
Nhất quán (Consistency)
Một giao tác ñược thực hiện ñộc lập với các giao tác khác Tính chất ACID của giao tác DBMS02 – Slides 13 xử lý ñồng thời với nó ñể bảo ñảm tính nhất quán cho CSDL
Cô lập (Isolation)
Một giao tác không quan tâm ñến các giao tác khác xử lý ñồng thời với nó
Bền vững (Durability)
Mọi thay ñổi mà giao tác thực hiện trên CSDL phải ñược ghi nhận bền vững Consistency Ví dụ T: Read(A,t); t:=t-50; Write(A,t); Read(B,t); t:=t+50; Write(B,t); DBMS02 – Slides 14

Tổng A+B là không ñổi
Nếu CSDL nhất quán trước khi T ñược thực hiện thì sau khi T hoàn tất CSDL vẫn còn nhất quán
Atomicity Ví dụ (tt) T: Read(A,t); t:=t-50; Write(A,t); Read(B,t); t:=t+50; Write(B,t); DBMS02 – Slides 15
A=100, B=200 (A+B=300)
Tại thời ñiểm sau khi write(A,t)
A=50, B=200 (A+B=250) - CSDL không nhất quán
Tại thời ñiểm sau khi write(B,t)
A=50, B=250 (A+B=300) - CSDL nhất quán
Nếu T không bao giờ bắt ñầu thực hiện hoặc T ñược ñảm bảo phải hoàn tất thì trạng thái không nhất quán sẽ không xuất hiện Durability Ví dụ (tt) T: Read(A,t); t:=t-50; Write(A,t); Read(B,t); t:=t+50; Write(B,t); DBMS02 – Slides 16

Khi T kết thúc thành công
Dữ liệu sẽ không thể nào bị mất bất chấp có sự cố hệ thống xãy ra
Isolation Ví dụ (tt) T’ T: Read(A,t); t:=t-50; Write(A,t); Read(B,t); t:=t+50; Write(B,t); DBMS02 – Slides 17
Giả sử có 1 giao tác T’ thực hiện phép toán A+B và chen vào giữa thời gian thực hiện của T
T’ kết thúc: A+B=50+200=250
T kết thúc: A+B=50+250=300
Hệ thống của các giao tác thực hiện ñồng thời có trạng thái tương ñương với trạng thái hệ thống của các giao tác thực hiện tuần tự theo 1 thứ tự nào ñó
Active
Ngay khi bắt ñầu thực hiện thao tác ñọc/ghi
Partially committed
Sau khi lệnh thi hành cuối cùng thực hiện
Failed Trạng thái của giao tác DBMS02 – Slides 18
Sau khi nhận ra không thể thực hiện các hành ñộng ñược nữa
Aborted
Sau khi giao tác ñược quay lui và CSDL ñược phục hồi về trạng thái trước trạng thái bắt ñầu giao dịch
Bắt ñầu lại giao tác (nếu có thể)
Hủy giao tác
Committed
Sau khi mọi hành ñộng hoàn tất thành công Sơ ñồ trạng thái của giao tác DBMS02 – Slides 19 Phân loại Transaction
Giao dịch tường minh (Explicit transaction)
Giao dịch ngầm ñịnh (Implicit transaction) DBMS02 – Slides 20
Giao dịch xác nhận (Commit transaction) Locks
Khóa (Lock) ñược sinh ra ñể giới hạn quyền truy nhập trên môi trường ña người dùng.
Microsoft SQL Server 200X sử dụng lock ñể ñảm bảo tính toàn vẹn của transaction và tính thống nhất của database. DBMS02 – Slides 21
Nếu lock không ñược sử dụng, dữ liệu bên trong CSDL có thể bị sai về logíc, và các query chạy trên ñó sẽ ñưa ra các kết quả không mong ñợi.
Bản chất của lock là việc một người muốn truy nhập riêng vào một bảng, vì vậy server sẽ lock bảng ñó lại cho riêng người ñó. Phân loại các Locks trong SQL Server
Pessimistic Lock
Optimistic Lock
Shared Locks DBMS02 – Slides 22
Exclusive Locks
Update Locks
Share Lock: khi một giao tác ñang ñọc dữ liệu X thì X sẽ bị share lock. Nghĩa là giao tác khác trong cùng thời ñiểm chỉ có quyền ñọc X và không có quyền sửa X. DBMS02 – Slides 23
Exclusive Lock: khi một giao tác ñang cập nhật dữ liệu X thì X sẽ bị exclusive lock. Nghĩa là giao tác khác trong cùng thời ñiểm không thể ñọc hay sửa X.
Kỹ thuật khóa 2 giai ñoạn
Kỹ thuật khóa trên dữ liệu phân cấp
Khóa chết (dead lock): là tình trạng 2 hay nhiều giao tác cùng trong trạng thái chờ DBMS02 – Slides 24 giao tác giải phóng tài nguyên cần thiết ñể hoàn thành giao tác.
Khóa chết (dead lock) TRANSACTION A TRANSACTION B DBMS02 – Slides 25 DISTRIBUTOR PRODUCTS Deadlock
Một deadlock xảy ra khi có 2 người dùng (hoặc 2 phiên làm việc) ñã ñặt khóa trên 2 ñối tượng riêng, và mỗi user muốn ñặt khóa trên ñối tượng của user kia. Mỗi user ñều phải ñợi người kia giải phóng khóa của họ ra ñể mình có thể ñặt khóa. DBMS02 – Slides 26
SQL Server tự ñộng nhận ra deadlock và giải quyết bằng cách chọn một ứng dụng và bắt nó phải giải phóng khóa, trong khi ñó vẫn cho ứng dụng còn lại chạy tiếp.
Cách tốt nhất ñể tránh deadlock là tránh nó. Một cách ñể tránh nó là không chạy các transaction ñồng thời. Giải quyết Deadlock
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT DBMS02 – Slides 27 Giải quyết Dead Lock
Phát hiện
Cho phép trạng thái deadlock xãy ra và sau ñó cố gắng khôi phục lại hệ thống
Chọn 1 giao tác ñể rollback
Phương pháp ðồ thị chờ (wait-for graph) DBMS02 – Slides 28

Ngăn ngừa
Quản lý các giao tác sao cho không bao giờ có deadlock
Phương pháp
Sắp thứ tự tài nguyên (resource ordering)
Timeout
Wait-die
Wound-wait
ðồ thị gồm
ðỉnh là các giao tác ñang giữ khóa hoặc ñang chờ khóa
Cung ñi từ ñỉnh T sang U khi
U ñang giữ khóa trên ñơn vị dữ liệu A
T ñang chờ khóa trên A T không thể khóa ñơn vị dữ liệu A nếu U không giải phóng khóa ðồ thị chờ DBMS02 – Slides 29

Nếu ñồ thị chờ không có chu trình
Các giao tác có thể hoàn tất
Ngược lại
Không một giao tác nào trong chu trình có thể tiếp tục thực hiện → deadlock Ví dụ T1 T2 L(A); R(A) L(C); R(C) 1 T3 T4 L(B); R(B) L(D); R(D) 2 3 4 5 6 L(A) L(C)↓ DBMS02 – Slides 30 7 8 L(A) L(B) ↓ Chờ ↓ Chờ Chờ ↓ Chờ T2T1 T3 T4 Ví dụ (tt) T1 T2 L(A); R(A) L(C); R(C) 1 T3 T4 L(B); R(B) L(D); R(D) 2 3 4 5 6 L(A) L(C) DBMS02 – Slides 31 T2T1 T3 T4 7 8 L(A) L(B)
Áp ñặt một thứ tự nào ñó lên các ñơn vị dữ liệu
Nếu các giao tác thực hiện khóa những ñơn vị dữ liệu theo thứ tự này Sắp thứ tự tài nguyên DBMS02 – Slides 32
Thì không có deadlock xãy ra trong khi chờ ñợi
Giới hạn các giao tác chỉ ñược thực hiện trong 1 khoảng thời gian nào ñó
Nếu giao tác vượt quá thời gian này Thì giao tác phải bị rollback Timeout DBMS02 – Slides 33

Mỗi giao tác sẽ ñược gán một nhãn ghi nhận thứ tự xuất hiện, kí hiệu: ts(T)
Xét 2 giao tác T và U
U ñang giữ khóa trên ñơn vị dữ liệu A Wait-die DBMS02 – Slides 34
T muốn khóa ñơn vị dữ liệu A
T sẽ chờ-wait U khi ts(T) < ts(U)
Ngược lại T sẽ bị hủy-die và bắt ñầu làm lại ở 1 thời ñiểm khác UT UT
Mỗi giao tác sẽ ñược gán một nhãn ghi nhận thứ tự xuất hiện, kí hiệu: ts(T)
Xét 2 giao tác T và U
U ñang giữ khóa trên ñơn vị dữ liệu A Wound-wait DBMS02 – Slides 35
T muốn khóa ñơn vị dữ liệu A
T buộc U rollback và trao khóa lại cho T-wound khi ts(T) < ts(U)
Ngoại lệ: nếu U ñã kết thúc và giải phóng khóa, U sẽ không rollback
Ngược lại T sẽ chờ-wait U UT UT
Timeout
ðơn giản
Khó chọn ñược khoảng thời gian timeout thích hợp
Có hiện tượng starvation Nhận xét DBMS02 – Slides 36
Giao tác lập ñi lập lại quá trình: bắt ñầu, deadlock, rollback
Resource ordering
Không thực tế
Chờ ñợi nhiều → tiềm ẩn của deadlock
Wait-die và Wound-wait
Không có starvation
Wound-wait ít rollback các giao tác hơn wait-die
Dễ cài ñặt hơn wait-for graph Có thể rollback những giao tác không gây ra deadlock Nhận xét (tt) DBMS02 – Slides 37

Wait-for graph
Nếu ñồ thị quá lớn sẽ tốn nhiều thời gian phân tích
Rất phức tạp khi CSDL phân tán
Giảm tối thiểu rollback các giao tác
Chỉ rollback 1 trong những giao tác gây ra deadlock
Lịch thao tác (schedule)
Giới thiệu
ðịnh nghĩa
Lịch tuần tự (serial schedule) Lịch thao tác (schedule) DBMS02 – Slides 38
Lịch khả tuần tự (serilizable schedule)
Conflict-Serializable
View-Serializable Nhãn thời gian (timestamps)
Giới thiệu
Nhãn thời gian toàn phần
Nhãn thời gian riêng phần Nhãn thời gian nhiều phiên bản DBMS02 – Slides 39
(multiversion) Transaction với T-SQL
BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION DBMS02 – Slides 40
SAVE TRANSACTION 2 loại giao tác
Giao tác không tường minh: Mặc ñịnh các lệnh bên trong 1 lô (batch), chỉ cần 1 lệnh thực hiện không thành công thì tất cả các lệnh còn lại trong lô ñó sẽ không thực DBMS02 – Slides 41 hiện  không khuyến khích.
Giao tác tường minh: Có chỉ ñịnh BEGIN ñánh dấu bắt ñầu giao tác và COMMIT / ROLLBACK ñể kết thúc giao tác. Transaction in SQL Server
Transaction Recovery: 2 thao tác
COMMIT: hoàn tất giao tác thành công
ROLLBACK: giao tác thất bại quay về trạng thái trước khi thực hiện giao tác DBMS02 – Slides 42
Transaction Log: undo the changes
Khi user muốn một chỉnh sửa dữ liệu, trong transaction log lưu 2 phiên bản của dòng dữ liệu ñó: trước và sau khi chỉnh sửa.
Nếu user thực hiện câu lệnh Commit thì end-of-transaction ñược ghi xuống transaction log.
Nếu user thực hiện câu lệnh Rollback thì hệ thống sẽ tìm phiên bản trước khi chỉnh DBMS02 – Slides 43 sửa và cập nhật lại vào CSDL.
ðiều gì xảy ra khi SQL Server khởi ñộng và trong transaction log có một giao tác chưa Commit và cũng không Rollback? Chuyển giao tự ñộng các transaction – Autocommit Transactions
Mode chuyển giao tự ñộng (Autocommit mode) là mode quản lý transaction mặc ñịnh của SQL Server.
Một lệnh (statement) ñược chuyển giao (committed) nếu nó thực hiện thành công hay sẽ trả ngược về lại ban ñầu (roll back) nếu nó gặp lỗi. DBMS02 – Slides 44
Lệnh BEGIN TRANSACTION vượt quyền mode tự ñộng chuyển giao (autocommit) mặc ñịnh.
SQL Server trở về lại mode autocommit khi transaction tường minh ñã ñược chuyển giao (commit) hay trả ngược về ñầu (roll back), hay khi mode transaction ngầm ñịnh bị tắt. ðảm bảo tính nhất quán của dữ liệu Vấn ñề 1: Một nhân viên có mã ‘000002’ ñược chọn làm ‘Sales Manager’ (position code=‘0001’). Ta sẽ cần cập nhật trong bảng Employee và cả trong bảng Position số lượng người hiện tạ nắm giữ vị trí có Position DBMS02 – Slides 45 Code=‘0001’. • Lệnh SQL tương ứng: UPDATE Employee SET cCurrentPosition = '0001' WHERE cEmployeeCode= '000002‘ UPDATE Position SET iCurrentStrength=iCurrentStrength + 1 WHERE cPositionCode='0001’ Giải quyết vấn ñề 1: BEGIN TRANSACTION trnUpdatePosition UPDATE Employee SET cCurrentPosition = '0001' WHERE cEmployeeCode= '000002' DBMS02 – Slides 46 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = '0001' COMMIT TRANSACTION trnUpdatePosition V
n ñ 2: Ten candidates have been recruited for the position 0015. To reflect this change, the siNoOfVacancy attribute of the Requisition table is to be decreased by 10 for cRequisitionCode 000004. Also the iCurrentStrength attribute of the Position table is to be increased by 10 for cPositionCode 0015 using the following commands: DBMS02 – Slides 47 UPDATE Requisition set siNoOfVacancy=siNoOfVacancy - 10 WHERE cRequisitionCode='000004‘ UPDATE Position set iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode='0015’ Both these statements should be atomic and if the iCurrentStrength attribute becomes more than the iBudgetedStrength attribute, then the changes made by the UPDATE statements must be reverted. Gi0i quy2t v
n ñ 2: In the Query Analyzer window, type: BEGIN TRANSACTION UPDATE Requisition DBMS02 – Slides 48 SET siNoOfVacancy=siNoOfVacancy - 10 WHERE cRequisitionCode='000004' UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode='0015' IF (SELECT iBudgetedStrength-iCurrentStrength FROM Position WHERE cPositionCode = '0015') <0 BEGIN PRINT 'Current strength cannot be more than budgeted strength. Transaction has not been committed.' ROLLBACK TRANSACTION DBMS02 – Slides 49 END ELSE BEGIN PRINT 'The transaction has been committed.' COMMIT TRANSACTION END Press F5 to execute the transaction Tạo ñiểm dừng cho 1 TRANSACTION
Lệnh SAVE TRANSACTION dùng ñể ñặt 1 ñiểm dừng (save point) bên trong 1 transaction. ðiểm dừng chia transaction thành các phần khác nhau sao cho transaction có thể quay về lại ñiểm dừng này nếu 1 phần của transaction bị DBMS02 – Slides 50 loại bỏ có ñiều kiện.
Cú pháp SAVE TRAN[SACTION] {savepoint_name ! @savepoint_variable} CREATE TABLE #TestTran(CotA INT PRIMARY KEY, CotB char(3)) GO BEGIN TRAN --Vùng thứ 1 SAVE TRAN Dong_1_2 DBMS02 – Slides 51 INSERT INTO #TestTran VALUES(1, 'aaa') INSERT INTO #TestTran VALUES(2, 'bbb‘) --Vùng thứ 2 SAVE TRAN Dong_3 INSERT INTO #TestTran VALUES(3, 'ccc') ROLLBACK TRAN Dong_3 COMMIT TRAN Dong_1_2 Kiểm tra lỗi bên trong giao tác
Một số lỗi thường gặp sau khi thực hiện 1 câu lệnh trong giao tác:
Không có quyền truy cập trên 1 ñối tượng (table, stored procedure,) DBMS02 – Slides 52
Vi phạm ràng buộc toàn vẹn (primary key, foreign key, check, rule, các ràng buộc ñược kiểm tra bằng trigger,).
Deadlock.
Kiểm tra lỗi bên trong giao tác
SQL Server trả giá trị lỗi về trong biến toàn cục @@ERROR.
@@ERROR= 0: không xảy ra lỗi
@@ERROR 0: xảy ra lỗi với mã lỗi là DBMS02 – Slides 53 @@ERROR Kiểm tra lỗi bên trong giao tác
Giao tác không thể tự ñộng ROLLBACK khi gặp những lỗi phát sinh trong quá trình thực hiện 1 câu lệnh thành phần trong giao tác. Vì vậy cần kiểm tra giá trị DBMS02 – Slides 54 của biến @@ERROR sau mỗi câu lệnh thành phần trong giao tác và cần xử lý những lỗi (nếu có): yêu cầu giao tác ROLLBACK một cách tường minh bằng lệnh ROLLBACK TRANSACTION. Ví dụ về kiểm tra lỗi -- Vi du: insert vao bang NhanVien insert into NhanVien values() if (@@ERROR 0 ) DBMS02 – Slides 55 begin rollback tran return end Tóm lại
Giao tác (Transaction) là một loạt các thao tác cần thực hiện dưới dạng một ñơn vị duy nhất.
Truy xuất ñồng thời  vấn ñề DBMS02 – Slides 56
Mất dữ liệu ñã cập nhật
Không thể ñọc lại
Dữ liệu không nhất quán
Cài transaction trên SQL Server: begin transaction, commit, rollback, save transaction Bài tập DBMS02 – Slides 57 Lập trình với T_SQL
Khai báo biến: DECLARE @Tên_Biến Kiểu_Dữ_Liệu
Ví dụ: DECLARE @Tuoi int DBMS02 – Slides 58 DECLARE @MSSV varchar(5) DECLARE @numCount int
Tên biến: Bắt ñầu bởi @
Kiểu dữ liệu của biến: Lấy kiểu dữ liệu hệ thống, trừ kiểu text, ntext, image Gán giá trị cho biến
Cách 1: SET @Tên_Biến = Giá_Trị
Ví dụ: DECLARE @HoTen nvarchar(20) DBMS02 – Slides 59 SET @HoTen = N‘Nguyễn Hằng Nga’ Gán giá trị cho biến
Cách 2: SELECT @Tên_Biến = Giá_Trị
Ví dụ: DECLARE @HoTen nvarchar(20) DBMS02 – Slides 60 SELECT @HoTen = N‘Nguyễn Hằng Nga’ Gán giá trị cho biến
Cách 3: SELECT @Tên_Biến = Tên_cột FROM Tên_Bảng
Ví dụ: Tìm lương lớn nhất của tất cả nhân DBMS02 – Slides 61 viên: DECLARE @MaxSalary decimal(18,2) SELECT @MaxSalary = MAX(Luong) FROM NhanVien SQL Server Object – Local Variables
Các biến ñược sử dụng trong cấu truy vấn như là các tham số.
Cho lược ñồ CSDL và dữ liệu tương ứng như sau: HocSinh DiemThi DBMS02 – Slides 62 MaHS TenHS NgaySinh DiaChi MaHS HocKy NamHoc Diem 01 Bảo 10/10/1977 123 01 01 2001 10 02 Hải 11/11/1981 456 03 01 2002 8 SQL Server Object – Local Variables Ví dụ: Liệt kê danh sách học sinh có ngày sinh vào ngày ‘10/10/1977’ DECLARE @NgaySinh datetime DBMS02 – Slides 63 SET @NgaySinh = ’10/10/1977’ SELECT * FROM HocSinh WHERE NgaySinh = @NgaySinh SQL Server Object – Local Variables Ví dụ: Liệt kê danh sách các học sinh có ñịa chỉ là ‘123’ và ñiểm thi lớn hơn 7 DECLARE @DiaChi nvarchar(50), @Diem Decimal DBMS02 – Slides 64 SELECT@DiaChi=’123’, @Diem = 7 SELECT * FROM HocSinh JOIN DiemThi ON HocSinh.MaHS = DiemThi.MaHS WHERE DiaChi = @DiaChi AND DiemThi > @Diem Cấu trúc ñiều khiển
Cấu trúc ñiều kiện: Nếu (biểu thức ñiều kiện) thì Lệnh/Khối lệnh IF (biểu thức ñiều kiện) DBMS02 – Slides 65 BEGIN Lệnh/Khối lệnh S hoặc SQL Statement END Lệnh/Khối lệnh P hoặc SQL Statement Cấu trúc ñiều kiện
Tìm Max 2 số DECLARE @a, @b, @Max int SET @Max = @a DBMS02 – Slides 66 IF (@a < @b) BEGIN SET @Max = @b END Print @Max Cấu trúc ñiều kiện
Nếu (biểu thức ñiều kiện) thì Lệnh/Khối lệnh S1
Ngược lại Lệnh/Khối liệnh S2 DBMS02 – Slides 67 IF (biểu thức ñiều kiện) BEGIN Lệnh/Khối lệnh S1 END ELSE BEGIN Lệnh/Khối lệnh S END Lệnh/Khối lệnh P Cấu trúc ñiều kiện
Tìm Max 2 số DECLARE @a, @b, @Max int IF (@a < @b) BEGIN SELECT @Max = @b DBMS02 – Slides 68 END ELSE BEGIN SELECT @Max = @a END Print @Max Cấu trúc CASE
Cho phép kiểm tra ñiều kiện và xuất thông tin theo từng trường hợp
Cú pháp 1 DBMS02 – Slides 69 CASE / WHEN THEN WHEN THEN [ELSE ] END Cấu trúc CASE
Cú pháp 2 CASE WHEN THEN WHEN THEN DBMS02 – Slides 70 [ELSE ] END Ví dụ Case Ví dụ 1: SELECT TENCLB1, ‘Kết quả’ = CASE WHEN (SOBANTHANG – SOBANTHUA > 0) THEN ‘Thắng’ DBMS02 – Slides 71 WHEN (SOBANTHANG – SOBANTHUA = 0) THEN ‘Hòa’ WHEN (SOBANTHANG – SOBANTHUA < 0) THEN ‘Thua’ END, TENCLB2 FROM vKETQUA Ví dụ Case (tt) CHITIETBAN(sohd, masach, slban, dgban) Ví dụ 2: SELECT masach,sum(slban)as tongslban, (CASE WHEN sum(slban)>10 THEN N‘Bán chạy' DBMS02 – Slides 72 ELSE N‘Bán chậm' END) as thongtin FROM CHITIETBAN GROUP BY masach Ví dụ Case (tt) SACH (masach, tuasach, sotrang, matheloai, slton) Ví dụ 3: Tăng số trang của những sách thuộc thể loại Tin học thêm 10, câp số trang của những sách thuộc thể loại Toán học UPDATE sach SET sotrang=sotrang+ DBMS02 – Slides 73 CASE WHEN matheloai=1 THEN 10 WHEN matheloai=4 THEN -10 ELSE 0 END Cấu trúc lặp Viết chương trình tính tổng s = 1 + 2 + .. + n DBMS02 – Slides 74
Cấu trúc lặp
BREAK: Thoát khỏi vòng lặp WHILE
CONTINUE: Thực hiện lần lặp mới DBMS02 – Slides 75