Bài giảng Cơ sở dữ liệu - Chương 7: Con trỏ (Cussor) - 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