Bài giảng Các hệ cơ sở dữ liệu - 2. Quản lý truy xuất đồng thời

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+ CASE WHEN matheloai=1 THEN 10 WHEN matheloai=4 THEN -10 ELSE 0 END

pdf19 trang | Chia sẻ: vutrong32 | Ngày: 19/10/2018 | Lượt xem: 7 | Lượt tải: 0download
Bạn đang xem nội dung tài liệu Bài giảng Các hệ cơ sở dữ liệu - 2. Quản lý truy xuất đồng thời, để tải tài liệu về máy bạn click vào nút DOWNLOAD ở trê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 Vn ñ 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 vn ñ 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

Các file đính kèm theo tài liệu này:

  • pdfdbms02_4576.pdf
Tài liệu liên quan