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.

Chú ý: phải khai báo con trỏ trước khai báo kiểm lỗi NOT FOUND.