Microsoft SQL Server

Sử dụng CURSOR trong MS-SQL Server

1.  Khi nào dùng?

– Khi cần duyệt qua từng dòng dữ liệu của một bảng để thao tác với chúng.

-Chỉ dùng khi không còn cách nào khác để cho ra kết quả tương tự, hoặc trong khả năng của bạn, chưa thể tìm cách giải quyết, chỉ chiếm dưới 5% thôi, tùy trường hợp cụ thể,  còn hầu hết các trường hợp còn lại, đều có thể sử dụng các câu lệnh T-SQL khác để xử lý và nên sử dụng cách khác vì vấn đề tốc độ thực thi của cursor trong hầu hết trường hợp đều chậm hơn vì chúng thường sử dụng nhiều tài nguyên SQL Server.

-Hãy cân nhắc khi câu lệnh của bạn tác động đến bảng có số lượng lớn records, tốt nhất là khi bạn chắc chắn là bạn tác động đến không nhiều dữ liệu hiện tại cũng như sự phát triển số lượng dữ liệu sau này và không ảnh hưởng đáng kể đối với tốc độ của ứng dụng khi thực hiện tác vụ nào đó.

2. Dùng như thế nào?

Nhìn vào ví dụ dưới, các phần in đậm là những đoạn code cần thiết khi ta làm việc với CURSOR:

ALTER PROC up_BangKeToKhaiNhapKhauDuaVaoThanhKhoan(
	@LanThanhLy bigint,      
	@NamThanhLy int,      
	@MaDoanhNghiep varchar(50)) 
AS   
BEGIN 
	--
	DECLARE @BKToKhaiNhapDVTK  TABLE  
	(   
	 SoToKhai NVARCHAR(255),  
	 TenNPL NVARCHAR(1000), 
	 DVT VARCHAR(50), 
	 LuongNK numeric(18,8)      
	)  
	--
	DECLARE @soTK bigint --(1)
	DECLARE cs_DSTK CURSOR FOR (SELECT * FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))--(2)
	OPEN cs_DSTK --(3)
	FETCH NEXT FROM  cs_DSTK INTO @soTK --(4)
	WHILE @@FETCH_STATUS = 0 --(5)
	BEGIN
		INSERT INTO @BKToKhaiNhapDVTK			
		SELECT dbo.ufn_LaySoToKhai(dbo.ufn_LaySoToKhaiVnaccTuV4(@soTK)),				
				dbo.ufn_LayTenHangChoBaoCaoDanhSachToKhaiNhapDuaVaoThanhKhoan(@soTK, MaNPL, TenNPL,DonGiaTT, Luong),
				TenDVT_NPL, 
				Luong
		FROM ufn_LayDanhSachHangCuaToKhaiNhapDuaVaoThanhKhoan(@LanThanhLy, @NamThanhLy, @MaDoanhNghiep, @soTK)
		FETCH NEXT FROM cs_DSTK INTO @soTK --(6)
	END
	CLOSE cs_DSTK --(7)
	DEALLOCATE cs_DSTK--(8)

	SELECT * FROM @BKToKhaiNhapDVTK
END
GO

Bạn chú ý các đánh dấu số ở đoạn code trên.  (hình đoạn code trên để bạn dễ theo dõi khi đọc bài biết)

(1) Khai báo một hoặc nhiều  ‘scalar variable’, bạn dùng các biến này để thao tác với từng dòng dữ liệu được duyệt qua, các thông tin của dòng dữ liệu đang duyệt sẽ được gán vào các biến này.

Nếu cần khai báo nhiều biến, bạn có thể khai báo từng dòng, hoặc gộp luôn như  ví dụ sau:

DECLARE @soTK BIGINT, @tenTK NVARCHAR(255)

-Trường hợp bạn thao tác luôn với cả dòng dữ liệu, ví dụ INSERT tất cả dữ liệu vào một bảng, thì không cần phải khai báo.

(2)Khai báo CURSOR

DECLARE cs_DSTK CURSOR FOR (SELECT * FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))

Chi tiết phần này thì khá dài, bạn có thể tham khảo ở microsoft library, hoặc nếu tiếng Anh không tốt thì vào slideshare từ slide 9 đến 16, nội dung từ các trang này khá đầy đủ.

Một số lưu ý:

-Sau từ khóa DECLARE là tên biến cursor, thường bắt đầu bằng ‘cs_’ hoặc ‘cursor’ để phân biệt loại biến.

-Sau tờ khóa FOR là câu lệnh SELECT, bạn chỉ lấy các trường cần thao tác trong bảng dữ liệu được chọn, hoặc SELECT * nếu tất cả các trường đều cần thiết cho việc xử lý.

(3) Mở cursor bạn vừa khai báo.

OPEN [<tencursor>]

(4)Đọc qua dữ liệu từng dòng

FETCH NEXT FROM cs_DSTK INTO @soTK

Lưu ý sau câu lệnh INTO là danh sách các biến mà khi đọc một dòng, dữ liệu các trường sẽ được gán tuần tự vào.

Ví dụ khi bạn khai báo CURSOR như sau:

DECLARE cs_DSTK CURSOR FOR (SELECT SoToKhai, TenToKhai FROM ufn_LayDSToKhaiDuaVaoThanhKhoan(@LanThanhLy))

thì sau khi mở OPEN cursor, có thể lấy dữ liệu từng dòng như sau:

FETCH NEXT FROM cs_DSTK INTO @soTK,@tenTK

(5) Thao tác với dòng dữ liệu đang  được chọn

WHILE @@FETCH_STATUS = 0 
BEGIN
  <code xử lý>
  FETCH NEXT FROM cs_DSTK INTO @soTK  
END

(Bạn có biết) sau @ là khai báo biến tự định nghĩa, còn sau @@ là tên biến hệ thống, nó sẽ hiển thị màu hồng.

Trong phần <code xử lý> bạn có thể thao tác xử lý theo mục đích của bạn, thêm, sửa, xóa lên các bảng có dự liệu liên quan, hoặc xử lý với bảng tạm nào đó,v..v.

Ví dụ như trên, mình đang tạo một báo cáo với dữ liệu được lấy từ nhiều function khác nhau với tham số hàm có @soTK được đọc lần lượt từ các dòng dữ liệu trong cursor. Các dữ liệu này được INSERT vào một biến bảng (table variable) tên @BKToKhaiNhapDVTK, đó là dữ liệu ta cần lấy.

(6) Đọc dòng tiếp theo, tương tự (4)

FETCH NEXT FROM cs_DSTK INTO @soTK

(7) Giải phóng dữ liệu tham chiếu bên trong CURSOR

CLOSE cs_DSTK

(8) Giải phóng CURSOR ra khỏi bộ nhớ.

DEALLOCATE cs_DSTK

Đến đây mới thực sự hủy cursor, bất kỳ tham chiếu đến tên cursor đã hũy đều gây ra lỗi.

3. Tổng kết.

-Bạn chỉ nên sử dụng CURSOR khi không thấy phương pháp nào khả dĩ hơn, cân nhắc kĩ về tốc độ thực thi của ứng dụng khi sử dụng nó.

-Cách sử dụng của CURSOR khá đơn giản

+Khai báo cursor với một SELECT <các trường cần lấy> trong một bảng nào đó

+Đọc qua từng dòng dự liệu , gán tuần tự cho các biến đã khai báo nếu thấy cần thiết

+Thao tác với dữ liệu đọc được từng dòng

+Đọc dòng tiếp theo

+Khi đọc hết thì giải phóng các dòng dữ liệu tham chiếu và giải phóng cursor.

4. Các vấn đề mở rộng.

Bài tiếp theo, mình sẽ bàn về các phương pháp giúp thay thế cursor bằng các T-SQL query .
–================================ HAPPY READING & HAPPY LEARNING ===============================– 
–==========================================****tieunai****===========================================– 

Published by

tuanitpro

Tôi là Lê Thanh Tuấn, và tôi chia sẻ những điều mình cho rằng nó là thú vị, hay giúp ích cho bạn!

Leave a Reply