Giới thiệu về stored procedure trong MySQL (P.7) Con trỏ trong Stored Procedures

MySQl hỗ trợ con trỏ trong cả stored procedures, hàm và triggers. Con trỏ được dùng để duyệt theo từng hàng bảng kết quả trả về từ câu truy vấn và xử lý các hàng đó. Tất cả những version sau MySQL 5.x đều hỗ trợ con trỏ với các thuộc tính sau:


* Read only: bạn không thể update con trỏ
* Non-scrollable: con trỏ chỉ có thể di chuyển theo 1 hướng và không thể bỏ qua bất cứ 1 hàng nào, trượt tới hay trượt lùi trong bản kết quả.
* Asensitive: bạn nên tránh update 1 table trong khi vẫn đang mở con trỏ trong chính table đó. Nếu không bạn sẽ nhận được những kết quả không mong đợi

MySQL hỗ trợ các phát biểu sau khi làm việc với con trỏ:

Khai báo 1 con trỏ với phát biểu DECLARE

Mã:

 DECLARE cursor_name CURSOR FOR SELECT_statement;

Mở con trỏ bằng phát biểu OPEN. Bạn phải mở con trỏ trước khi duyệt qua bất cứ dòng nào trong bảng kết quả:

Mã:

 OPEN cursor_name;

Nhận dữ liệu ở dòng hiện tại trong bản kết quả và di chuyển con trỏ đến dòng kế tiếp bằng phát biểu FETCH:

Mã:

 FETCH cursor_name INTO variable list;

Cuối cùng, đóng con trỏ để vô hiệu hóa và giải phóng bộ nhớ. Sử dụng phát biểu:

Mã:

CLOSE cursor_name;

Một điểm quan trọng khi làm việc với con trỏ là bạn nên sử dụng điều khiển NOT FOUND để tránh gặp phải lỗi “no data to fetch”. Chúng ta sử dụng 1 ví dụ nhỏ dưới đây để demo về con trỏ:

Mã:

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;

Stored procedure này khá đơn giản và có thể thay bằng truy vấn SQL. Chúng ta chỉ sử dụng cho mục đích demo cách thức làm việc của con trỏ

Ta dùng con trỏ để làm việc với table products. Nếu số lượng trong kho của product nhỏ hơn 100, tiến hành ghi lại log trong 1 bảng tạm và sau đó select tất cả các sản phẩm để in ra trong 1 màn hình.

Nhớ là bạn phải khai báo con trỏ trước tiên và sau đó khai báo điều khiển NOT FOUND, nếu không bạn sẽ gặp phải lỗi.