Hệ quản trị CSDL MySQL - Con trỏ trong SProcedure
Con trỏ trong SProcedure
Trong bài học này, chúng ta sẽ tìm hiểu cách sử dụng con trỏ csdl để duyệt trên tập kết quả truy vấn.
MySQL hỗ trợ con trỏ cho thủ tục thường trú, hàm và trigger. Con trỏ được sử dụng để lặp trên một tập các dòng thuộc kết quả truy vấn. Sử dụng con trỏ để xử lý tính toán trên từng dòng. Đối với phiên bản 5.x trở đi, con trỏ trong MySQL có những thuộc tính sau:
- Read only (chỉ đọc): có nghĩa là chúng ta không thể thay đổi giá trị.
- Non-scrollable (không thể quay lại): con trỏ chỉ đi theo một hướng, không thể bỏ qua hay quay lại những dòng đã duyệt qua trong tập kết quả.
- Asensitive: tránh cập nhật bảng dữ liệu khi đang mở con trỏ trên chính bảng dữ liệu đó. Nếu không rất có thể xảy ra những hậu quả ngoài mong đợi.
Quy trình khai báo và sử dụng con trỏ
Bước 1: Khai báo con trỏ với cú pháp sau:
DECLARE cursor_name CURSOR FOR SELECT_statement;
Bước 2: mở con trỏ để sử dụng
OPEN cursor_name;
Bước 3: Lấy ra một dòng để xử lý và chuyển con trỏ sang dòng tiếp theo
FETCH cursor_name INTO variable list;
Bước 4: đóng con trỏ, giải phóng bộ nhớ mà con trỏ chiếm giữ.
CLOSE cursor_name;
Lưu ý: Điểm quan trọng cần lưu ý khi thao tác với con trỏ là nên sử dụng kiểm lỗi NOT FOUND để tránh trường hợp không còn dòng dữ liệu nào cần xử lý.
Dưới đây là một ví dụ về sử dụng con trỏ trong thủ tục thường trú
Ví dụ:
DELIMITER $$
DROP PROCEDURE IF EXISTS CursorProc$$
CREATE PROCEDURE CursorProc()
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
DECLARE prd_code VARCHAR(255);
DECLARE cur_product CURSOR FOR
SELECT productCode FROM products;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_products = 1;
/* for loggging information */
CREATE TABLE infologs (
Id int(11) NOT NULL AUTO_INCREMENT,
Msg varchar(255) NOT NULL,
PRIMARY KEY (Id)
);
OPEN cur_product;
FETCH cur_product INTO prd_code;
REPEAT
SELECT quantityInStock INTO quantity_in_stock
FROM products
WHERE productCode = prd_code;
IF quantity_in_stock < 100 THEN
INSERT INTO infologs(msg)
VALUES (prd_code);
END IF;
FETCH cur_product INTO prd_code;
UNTIL no_more_products = 1
END REPEAT;
CLOSE cur_product;
SELECT * FROM infologs;
DROP TABLE infologs;
END$$
DELIMITER;
Giải thích: ở trên là một ví dụ đơn giản và có thể làm được bằng cách xây dựng các truy vấn SQL. Tuy nhiên đó lại là ví dụ dễ hiểu để chúng ta tìm hiểu cách thức hoạt động của con trỏ.
Chúng ta sử dụng con trỏ trên bảng products và duyệt qua tập kết quả truy vấn trên bảng này. Nếu tổng lượng tồn kho của sản phẩm < 100 thì chúng ta đưa nó vào bảng tạm. Kết thúc quá trình lặp chúng ta sẽ đưa ra được danh sách các sản phẩm có lượng tồn kho dưới 100.