Giáo trình Hệ quản trị cơ sở dữ liệu - Chương 7: Con trỏ (Cusor) - Lê Thị Minh Nguyên

8/25/2017 1 Chương 7. Con trỏ (Cussor) GV: Lê Thị Minh Nguyện Email: nguyenltm@huflit.edu.vn Nội dung 1. Tại sao phải dùng con trỏ 2. Khái niệm 3. Phân loại con trỏ 4. Cách duyệt con trỏ 5. Quy trình sử dụng con trỏ 6. Nhận xét 7. Kết hợp con trỏ với store procedure Hệ quản trị Cơ sở dữ liệu 2 1. Tại sao phải dùng con trỏ • 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. Hệ quản trị Cơ sở dữ liệu 3 1. Tại sao phải dùng con trỏ Hệ quản trị Cơ sở dữ liệu 4 8/25/2017 2 2. Khái niệm • Cursor là một cấu trúc dữ liệu ánh xạ đến một tập các dòng dữ liệu kết quả của một câu truy vấn và được duyệt với từng dòng dữ liệu đó. • Vị trí hiện hang của cursor có thể được dùng như điều kiện where ở lệnh Update hoặc Delete. Hệ quản trị Cơ sở dữ liệu 5 Định nghĩa cursor Hệ quản trị Cơ sở dữ liệu 6  Cú pháp định nghĩa biến có kiểu cursor DECLARE Tên_cursorCURSOR [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]] Định nghĩa cursor Hệ quản trị Cơ sở dữ liệu 7 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.  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. Định nghĩa cursor Hệ quản trị Cơ sở dữ liệu 8 Trong đó:  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.  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.  SELECT: dùng để chỉ đến các cột bên trong bảng mà chúng ta cần đọc dữ liệu. 8/25/2017 3 3. Phân loại con trỏ • 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 • 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. • 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. Hệ quản trị Cơ sở dữ liệu 9 4. Cách duyệt con trỏ Hệ quản trị Cơ sở dữ liệu 10 FETCH [Next | Prior | First | Last | Absolute n | Relative n ] FROM Tên_cursor [INTO danh_sách_biến] Dùng lệnh Fetch để duyệt tuần tự cursor như sau: Trong đó:  Next, Prior, First, Last: dùng để đọc dữ liệu kế tiếp, trước, đầu, sau cùng. 4. Cách duyệt con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 11  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.  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. : 4. Cách duyệt con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 12 8/25/2017 4 5. Quy trình sử dụng con trỏ  Định nghĩa biến kiểu cursor.  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. Hệ quản trị Cơ sở dữ liệu 13 5. Quy trình sử dụng con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 14  Ví dụ : Đị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 5. Quy trình sử dụng con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 15  Mở Cursor OPEN Tên_cursor  Ví dụ: Mở các cursor đã định nghĩa ở ví dụ trên. OPEN C_MH 5. Quy trình sử dụng con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 16 -- Đọc dữ và xử lý dữ liệu trong con trỏ FETCH NEXT FROM C_MH WHILE @@FETCH_STATUS = 0 BEGIN -- Đọc tiếp dòng kế FETCH NEXT FROM C_MH END 8/25/2017 5 5. Quy trình sử dụng con trỏ (tt) Hệ quản trị Cơ sở dữ liệu 17 Ví dụ: Đóng con trỏ CLOSE C_MH DEALLOCATE C_MH  Đóng cursor CLOSE Tên_cursor DEALLOCATE Tên_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ớ Ví dụ Cho lược đồ quan hệ như sau:  MAT_HG (MaMH, TenMH, DVT, MaNCC)  PNhap (MaPN, NgayNhap, ThanhTien)  CTPNhap (MaMH, MaPN, SLNhap, DonGia) Hệ quản trị Cơ sở dữ liệu 18 Ví dụ (tt)  Ví dụ 1: Đọc dữ liệu cursor của bảng MAT_HANG chỉ đọc các vật tư là Tivi Hệ quản trị Cơ sở dữ liệu 19 -- 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 Ví dụ (tt) Hệ quản trị Cơ sở dữ liệu 20 -- Đọ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 -- Đóng cursor CLOSE cr_MatHang DEALLOCATE cr_MatHang 8/25/2017 6 Ví dụ (tt) Hệ quản trị Cơ sở dữ liệu 21  Ví dụ 2: 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ột ThanhTien. Ví dụ (tt) Hệ quản trị Cơ sở dữ liệu 22 -- 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 Ví dụ (tt) Hệ quản trị Cơ sở dữ liệu 23 -- Đọc dữ liệu và cập nhật giá trị Fetch next from cr_Pnhap into @Sopn while @@fetch_status = 0 begin select @Tongtt = sum(SLNhap*dongia) from ctpnhap where mapn = @sopn update pnhap set Thanhtien = @TongTT Where mapn=@SOPN -- dịch con trỏ đến dòng kế tiếp fetch next from cr_Pnhap into @Sopn end Dịch chuyển con trỏ cr_Pnhap vào @SoPN Ví dụ (tt) Hệ quản trị Cơ sở dữ liệu 24 -- Đóng cursor CLOSE cr_Pnhap DEALLOCATE cr_Pnhap 8/25/2017 7 6. Nhận xét • 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. • 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. Hệ quản trị Cơ sở dữ liệu 25 7. Kết hợp con trỏ với store procedure •Làm bài tập Hệ quản trị Cơ sở dữ liệu 26 Hệ quản trị Cơ sở dữ liệu 27