Stored Procedure trong MySQL

Tình huống mở đầu

Tạp Hóa Cô Tấm dùng một ứng dụng bán hàng nhỏ. Mỗi lần khách mua một túi gạo, ứng dụng làm bốn việc: kiểm tra tồn kho, trừ kho, ghi đơn, rồi lấy mã đơn vừa tạo. Nếu viết từng việc thành lệnh riêng, gói trong một giao dịch, mỗi lần bán ứng dụng gửi sáu lệnh qua mạng (START TRANSACTION, SELECT, UPDATE, INSERT, SET, COMMIT). Một chiều thứ Bảy đông khách, mạng chập chờn đúng lúc giữa lệnh thứ ba và thứ tư: kho đã trừ nhưng đơn chưa ghi, và cô Tấm không biết túi gạo đó đi đâu.

Ý tưởng quen thuộc: đưa cả chuỗi này vào server thành một thủ tục (stored procedure) có tên, ứng dụng chỉ nói "làm công thức số 3 cho mình". Nhưng thủ tục không tự nhiên an toàn hơn: nó chỉ làm đúng những gì bạn viết.

Hãy đoán trước: một lần bán hàng gọi qua thủ tục thì ứng dụng gửi bao nhiêu lệnh, 6, 2 hay 1? Và nếu kho không đủ hàng, bên gọi thấy gì? Cuối bài bạn sẽ có con số đo thật.

Ôn nhanh bài trước: trigger AFTER INSERT chạy bao nhiêu lần khi một lệnh INSERT ... SELECT ghi 2000 dòng?

Đáp án: 2000 lần, một lần cho mỗi dòng. Trigger chạy ngầm theo sự kiện, còn thủ tục chỉ chạy khi bạn gọi tên nó bằng CALL.

Mục tiêu bài học

Sau bài này bạn có thể:

• Tạo và gọi thủ tục bằng CREATE PROCEDURE và CALL, và giải thích vì sao máy khách mysql cần DELIMITER.
• Phân biệt tham số IN, OUT, INOUT và đoán giá trị của biến bên gọi trước và sau lệnh CALL.
• Viết thủ tục có giao dịch, DECLARE ... HANDLER và SIGNAL để lỗi giữa chừng không để lại dữ liệu dở dang.
• Đo được bằng số lệnh ứng dụng phải gửi: một CALL thay cho sáu lệnh rời.
• Nhận ra ba bẫy: tham số trùng tên cột, START TRANSACTION trong thủ tục tự chốt giao dịch của bên gọi, và handler nuốt mất lỗi khi thiếu RESIGNAL.

Bức tranh lớn

Xuyên suốt bài, thủ tục là một tờ công thức dán ở quầy: nhân viên chỉ cần nói tên công thức và số phần, quầy tự làm đủ các bước theo thứ tự. Dự án xuyên suốt là quy trình bán hàng của Tạp Hóa Cô Tấm trên database shop, với bảng products (kho) và orders (đơn).

Hình dưới trả lời một câu hỏi: khi ứng dụng gọi place_order, thủ tục đi qua những bước nào và rẽ nhánh ở đâu?

Cách đọc hình: đọc từ ô xanh dương xuống. Hai ô tím là hai đoạn việc thật của thủ tục: mở giao dịch, khóa dòng hàng, rồi trừ kho và ghi đơn. Ô cam là câu hỏi duy nhất: đủ hàng không. Nhánh có đủ hàng đi tới ô xanh lá (COMMIT). Nhánh thiếu hàng đi tới hai ô đỏ: lỗi được phát ra, handler hoàn tác, rồi bên gọi nhận lỗi và biến OUT không được gán.

Chỗ ẩn dụ không còn đúng: tờ công thức giấy không tự biết hoàn tác khi nhân viên làm hỏng giữa chừng. Thủ tục cũng vậy, việc hoàn tác phải do bạn viết vào thủ tục.

Khái niệm cốt lõi

Bài này có bốn khái niệm.

1. Tạo, gọi và DELIMITER

CREATE PROCEDURE ten(tham_so) BEGIN ... END lưu một khối lệnh vào database, và CALL ten(gia_tri) chạy nó. Thân thủ tục có nhiều lệnh, mỗi lệnh kết thúc bằng dấu chấm phẩy. Máy khách mysql cắt lệnh tại dấu chấm phẩy đầu tiên, nên bạn phải đổi dấu kết thúc tạm thời bằng DELIMITER //. DELIMITER là lệnh của máy khách, không phải lệnh SQL gửi lên server.

2. Ba kiểu tham số

IN (mặc định) truyền giá trị vào. OUT truyền giá trị ra: bên trong thủ tục nó bắt đầu bằng NULL, bất kể bên gọi đưa gì. INOUT làm cả hai: đọc được giá trị bên gọi đưa vào, và giá trị mới chạy ngược ra. Bên gọi truyền biến phiên dạng @ten cho OUT và INOUT. Thủ tục cũng có thể trả tập kết quả bằng một lệnh SELECT thường, đó là một đường ra khác, không liên quan đến tham số OUT.

3. Giao dịch và xử lý lỗi nằm trong thủ tục

Thủ tục không tự là một giao dịch. Muốn "hoặc xong hết, hoặc không gì cả", bạn mở START TRANSACTION, khai báo DECLARE EXIT HANDLER FOR SQLEXCEPTION để ROLLBACK, và dùng RESIGNAL để lỗi vẫn đến tay bên gọi. SIGNAL SQLSTATE '45000' cho phép tự phát lỗi nghiệp vụ, như hết hàng.

4. Tên tham số không được trùng tên cột

Khi tên tham số trùng tên cột, MySQL hiểu tên đó là tham số. Điều kiện WHERE product_id = product_id khi đó so sánh tham số với chính nó, luôn đúng. Quy ước p_ cho tham số và v_ cho biến cục bộ tránh hẳn bẫy này.

Ví dụ đầu tiên

Dựng hai bảng và ba sản phẩm [Đã chạy: MySQL 8.0.44]:

CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(40) NOT NULL,
  stock INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  total      DECIMAL(10, 2) NOT NULL
);

INSERT INTO products VALUES
  (1, 'Rice 5kg', 10, 95000),
  (2, 'Fish sauce', 4, 32000),
  (3, 'Sugar 1kg', 0, 24000);

Thủ tục nhỏ nhất có ích: liệt kê đơn của một sản phẩm. Thêm ba đơn mẫu, tạo thủ tục rồi gọi nó:

INSERT INTO orders (product_id, qty, total)
VALUES (1, 2, 190000), (1, 1, 95000), (2, 1, 32000);

DELIMITER //
CREATE PROCEDURE orders_of(IN p_product_id INT)
BEGIN
  SELECT id, qty, total
  FROM orders
  WHERE product_id = p_product_id
  ORDER BY id;
END//
DELIMITER ;

CALL orders_of(1);

Dự đoán trước khi xem kết quả: sản phẩm 1 có hai đơn trong dữ liệu mẫu. CALL in ra những dòng nào?

Kết quả [Đã chạy: MySQL 8.0.44]:

+----+-----+-----------+
| id | qty | total     |
+----+-----+-----------+
|  1 |   2 | 190000.00 |
|  2 |   1 |  95000.00 |
+----+-----+-----------+

Bây giờ đến thủ tục chính của bài: bán một đơn. Đọc kỹ từng khối, vì mỗi khối có một lý do tồn tại:

DELIMITER //
CREATE PROCEDURE place_order(IN p_product_id INT, IN p_qty INT, OUT p_order_id INT)
BEGIN
  DECLARE v_stock INT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;                              -- báo lại lỗi cho bên gọi
  END;

  START TRANSACTION;
  SELECT stock INTO v_stock
  FROM products WHERE id = p_product_id FOR UPDATE;   -- khóa dòng hàng đang bán

  IF v_stock IS NULL OR v_stock < p_qty THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Not enough stock';
  END IF;

  UPDATE products SET stock = stock - p_qty WHERE id = p_product_id;
  INSERT INTO orders (product_id, qty, total)
  SELECT id, p_qty, price * p_qty FROM products WHERE id = p_product_id;

  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
END//
DELIMITER ;

Gọi nó để bán 2 túi gạo (sản phẩm 1), rồi đọc ba thứ: biến @oid, kho và bảng đơn:

CALL place_order(1, 2, @oid);

SELECT @oid AS order_id;
SELECT id, name, stock FROM products ORDER BY id;
SELECT id, product_id, qty, total FROM orders;

Dự đoán trước khi xem kết quả: @oid bằng mấy, kho gạo còn bao nhiêu, bảng orders có dòng nào?

Kết quả [Đã chạy: MySQL 8.0.44]:

+----------+
| order_id |
+----------+
|        1 |
+----------+
+----+------------+-------+
| id | name       | stock |
+----+------------+-------+
|  1 | Rice 5kg   |     8 |
|  2 | Fish sauce |     4 |
|  3 | Sugar 1kg  |     0 |
+----+------------+-------+
+----+------------+-----+-----------+
| id | product_id | qty | total     |
+----+------------+-----+-----------+
|  1 |          1 |   2 | 190000.00 |
+----+------------+-----+-----------+

Còn khi thiếu hàng? Gán trước @oid = 777 để thấy tham số OUT có bị đụng tới không, rồi bán 99 túi:

SET @oid = 777;
CALL place_order(1, 99, @oid);

SELECT @oid AS order_id;
SELECT id, stock FROM products WHERE id = 1;
SELECT COUNT(*) AS orders_written FROM orders;

Dự đoán trước khi xem kết quả: @oid còn 777 hay trở thành NULL? Kho còn bao nhiêu? Mấy đơn được ghi?

Kết quả [Đã chạy: MySQL 8.0.44]. Dòng lỗi nằm cuối vì shell in lỗi ra luồng riêng. Thứ tự thực hiện vẫn là CALL trước, ba lệnh SELECT sau:

+----------+
| order_id |
+----------+
|      777 |
+----------+
+----+-------+
| id | stock |
+----+-------+
|  1 |    10 |
+----+-------+
+----------------+
| orders_written |
+----------------+
|              0 |
+----------------+
ERROR 1644 (45000): Not enough stock

Lỗi 1644 mang đúng chữ Not enough stock ta đặt trong SIGNAL. @oid vẫn là 777 vì dòng SET p_order_id chưa bao giờ chạy tới. Kho còn 10 và không có đơn nào: ROLLBACK của handler đã hoàn tác.

Còn con số mở đầu: ứng dụng gửi bao nhiêu lệnh cho một lần bán? Bộ đếm Questions của phiên đếm số lệnh do máy khách gửi (lệnh chạy bên trong thủ tục không được đếm). Đo bán 2 túi bằng sáu lệnh rời, đã trừ đi lệnh đo cuối:

SELECT VARIABLE_VALUE INTO @q0
FROM performance_schema.session_status WHERE VARIABLE_NAME = 'Questions';

START TRANSACTION;
SELECT stock INTO @v FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 2 WHERE id = 1;
INSERT INTO orders (product_id, qty, total)
SELECT id, 2, price * 2 FROM products WHERE id = 1;
SET @oid = LAST_INSERT_ID();
COMMIT;

SELECT VARIABLE_VALUE - @q0 - 1 AS statements_sent
FROM performance_schema.session_status WHERE VARIABLE_NAME = 'Questions';

+-----------------+
| statements_sent |
+-----------------+
|               6 |
+-----------------+

Rồi bán bằng một CALL:

SELECT VARIABLE_VALUE INTO @q0
FROM performance_schema.session_status WHERE VARIABLE_NAME = 'Questions';

CALL place_order(1, 2, @oid);

SELECT VARIABLE_VALUE - @q0 - 1 AS statements_sent
FROM performance_schema.session_status WHERE VARIABLE_NAME = 'Questions';

+-----------------+
| statements_sent |
+-----------------+
|               1 |
+-----------------+

Sáu lệnh thành một. Số lần đi về qua mạng giảm theo, và đoạn giữa không còn chỗ nào để mạng chập chờn xen vào làm kho trừ dở.

Xem từng bước

Animation dưới đây chạy lại hai lần gọi place_order: lần đủ hàng rồi lần thiếu hàng. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.

Bảng trace bằng chữ [Đã chạy: MySQL 8.0.44, kho gạo ban đầu 10]:

CALL place_order(1, 2, @oid);
SELECT @oid AS oid_after_success;

CALL place_order(1, 99, @oid);
SELECT @oid AS oid_after_failure;
SELECT stock FROM products WHERE id = 1;
SELECT COUNT(*) AS orders_written FROM orders;

+-------------------+
| oid_after_success |
+-------------------+
|                 1 |
+-------------------+
+-------------------+
| oid_after_failure |
+-------------------+
|                 1 |
+-------------------+
+-------+
| stock |
+-------+
|     8 |
+-------+
+----------------+
| orders_written |
+----------------+
|              1 |
+----------------+
ERROR 1644 (45000): Not enough stock

Bạn vừa làm được: đọc một thủ tục từ trên xuống dưới và nói đúng, với từng lần gọi, giá trị của tham số OUT, kho và bảng đơn.

Concept Check

Dùng thủ tục place_order và dữ liệu của bài.

Câu 1. Trong thân thủ tục, tham số khai báo OUT p_order_id INT có giá trị ban đầu là gì, nếu bên gọi đã gán @oid = 777 trước CALL?

• A. 777, vì tham số OUT luôn nhận bản sao giá trị của biến bên gọi.
• B. NULL, vì OUT bỏ qua giá trị bên gọi đưa vào ngay từ đầu.
• C. 0, vì kiểu INT được MySQL khởi tạo bằng số không khi thiếu giá trị.
• D. Lỗi, vì OUT không được truyền bằng một biến đã có giá trị.

Câu 2. place_order(1, 99, @oid) báo lỗi 1644. Sau đó nên đọc @oid như thế nào?

• A. Tin giá trị trong @oid, vì thủ tục đã tự gán lại khi gặp lỗi.
• B. Coi @oid là NULL, vì lỗi làm mọi biến của phiên bị xóa sạch.
• C. Không tin nó, vì nó có thể còn giá trị cũ của lần gọi trước.
• D. Chỉ tin nó nếu đã khai báo DELIMITER đúng ở lúc tạo thủ tục.

Câu 3. Thủ tục place_order đang chạy giữa chừng thì UPDATE báo lỗi. Chuyện gì xảy ra với lệnh INSERT phía sau nó?

• A. Vẫn chạy, vì lỗi của một lệnh không ảnh hưởng đến lệnh kế tiếp.
• B. Không chạy, vì handler EXIT thoát khỏi thủ tục sau khi hoàn tác.
• C. Chạy nhưng bị đánh dấu lỗi, vì giao dịch đã mở từ trước.
• D. Chạy sau khi MySQL tự thử lại UPDATE thêm một lần nữa.

Đáp án và giải thích:

• Câu 1: B. OUT chỉ chuyển giá trị ra. Bên trong thủ tục nó bắt đầu bằng NULL; bài đã kiểm tra bằng thủ tục out_start. A là hành vi của INOUT. C và D bịa ra quy tắc không có.
• Câu 2: C. Dòng SET p_order_id chưa chạy tới, nên biến giữ nguyên giá trị cũ (777 trong thí nghiệm). A sai vì thủ tục không gán gì khi lỗi. B sai vì lỗi không xóa biến phiên. D không liên quan đến giá trị biến.
• Câu 3: B. EXIT HANDLER chạy khối ROLLBACK; RESIGNAL; rồi thoát khỏi khối BEGIN ... END chứa nó. A sai vì khi có handler EXIT, các lệnh sau không chạy. C và D bịa ra hành vi không có.

Làm cùng mình

Cô Tấm nhập thêm hàng nên cần một thủ tục restock: cộng số lượng vào kho rồi trả về tồn kho mới qua tham số OUT. Bạn sẽ viết hai câu lệnh bên trong thân thủ tục.

Bước 1. Vẽ ra giấy

Đường đi của dữ liệu: p_qty đi vào cột stock, rồi stock mới đi ra p_new_stock. Sản phẩm 3 (đường) đang có 0. Nhập 12 thì @s nên bằng mấy?

Bước 2. Điền chỗ trống

DELIMITER //
CREATE PROCEDURE restock(IN p_product_id INT, IN p_qty INT, OUT p_new_stock INT)
BEGIN
  -- TODO 1: cộng p_qty vào stock của sản phẩm p_product_id trong bảng products
  ...;

  -- TODO 2: đọc stock mới của sản phẩm đó vào p_new_stock bằng SELECT ... INTO
  ...;
END//
DELIMITER ;

Gợi ý cho chỗ trống thứ nhất: UPDATE products SET stock = stock + .... Gợi ý cho chỗ trống thứ hai: SELECT cot INTO tham_so FROM ... WHERE .... Đáp án nằm ở mục Lời giải và đáp án.

Bước 3. Thử với dữ liệu khác

Gọi restock với sản phẩm 1 (đang có 10) và số lượng 5. Dự đoán trước: @s bằng mấy? Sau đó gọi tiếp lần hai cùng một biến @s với số lượng 5 nữa, và đoán xem @s có cộng dồn hay bị ghi đè.

Bước 4. Vẽ lại ra giấy

Ghi một câu: vì sao p_new_stock dùng OUT mà không dùng INOUT.

Thử phá nó đi

Bốn bẫy của thủ tục [Đã chạy: MySQL 8.0.44]:

Bẫy đầu tiên: thiếu DELIMITER.

CREATE PROCEDURE hello()
BEGIN
  SELECT 'hi' AS greeting;
END;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END'

Có hai dòng lỗi: lệnh bị cắt ở dấu chấm phẩy sau SELECT 'hi' AS greeting, nên phần đầu thiếu END, và phần còn lại END; cũng là một lệnh sai.

Bẫy thứ hai: tham số trùng tên cột. Bảng có 3 đơn, thủ tục chỉ định xóa đơn của sản phẩm 2:

INSERT INTO orders (product_id, qty, total)
VALUES (1, 2, 190000), (1, 1, 95000), (2, 1, 32000);

DELIMITER //
CREATE PROCEDURE cancel_orders_bad(IN product_id INT)
BEGIN
  DELETE FROM orders WHERE product_id = product_id;    -- cả hai vế đều là tham số
END//
DELIMITER ;

SELECT COUNT(*) AS before_call FROM orders;
CALL cancel_orders_bad(2);
SELECT COUNT(*) AS after_call FROM orders;

+-------------+
| before_call |
+-------------+
|           3 |
+-------------+
+------------+
| after_call |
+------------+
|          0 |
+------------+

Cả ba đơn biến mất, kể cả hai đơn của sản phẩm 1, vì product_id = product_id là so sánh tham số với chính nó.

Bẫy thứ ba: handler thiếu RESIGNAL. Thủ tục trừ kho rồi tự phát lỗi:

DELIMITER //
CREATE PROCEDURE swallow(IN p_id INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;                              -- thiếu RESIGNAL
  END;
  START TRANSACTION;
  UPDATE products SET stock = stock - 1 WHERE id = p_id;
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Boom';
  COMMIT;
END//
DELIMITER ;

CALL swallow(1);
SELECT 'caller saw no error' AS note;
SELECT stock FROM products WHERE id = 1;

+---------------------+
| note                |
+---------------------+
| caller saw no error |
+---------------------+
+-------+
| stock |
+-------+
|    10 |
+-------+

Không có dòng lỗi nào, bên gọi tưởng mọi việc êm. Kho vẫn 10 vì ROLLBACK chạy, nhưng ứng dụng không có cách nào biết việc bán đã thất bại.

Bẫy thứ tư: bên gọi mở giao dịch, ghi một dòng nhật ký, gọi place_order rồi ROLLBACK để hủy tất cả:

CREATE TABLE audit (id INT PRIMARY KEY AUTO_INCREMENT, note VARCHAR(40));

START TRANSACTION;
INSERT INTO audit (note) VALUES ('before call');
CALL place_order(2, 1, @oid);
ROLLBACK;

SELECT note FROM audit;

+-------------+
| note        |
+-------------+
| before call |
+-------------+

Dòng nhật ký vẫn còn. START TRANSACTION bên trong thủ tục đã chốt giao dịch của bên gọi trước khi mở giao dịch mới.

Hiểu lầm quan trọng nhất của bài là coi thủ tục như một khối nguyên tử tự động. Hình dưới đối chiếu mô hình sai với mô hình đúng.

Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai rằng đưa lệnh vào thủ tục là tự có tính "hoặc xong hết hoặc không gì cả". Chuỗi xanh là thực tế: thủ tục chạy lần lượt từng lệnh, và tính nguyên tử có được nhờ START TRANSACTION cùng handler hoàn tác mà bạn tự viết.

Tự tay làm

Dùng chung các bảng, thủ tục place_order và dữ liệu của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.

Cơ bản

Bài 1. Dự đoán hai lần gọi. Đầu vào: kho mới dựng (sản phẩm 2 có 4 túi nước mắm). Chạy CALL place_order(2, 3, @a); rồi CALL place_order(2, 3, @b);. Dự đoán giá trị @a, @b, kho của sản phẩm 2 và nội dung bảng orders. Tiêu chí đạt: nói đúng lần gọi nào lỗi, @b là gì, kho còn mấy, bảng có mấy đơn. Gợi ý: sau lần gọi đầu, kho còn 1.

Vận dụng

Bài 2. Hủy đơn. Đầu vào: thủ tục place_order đã có. Viết cancel_order(IN p_order_id INT) trả hàng về kho và xóa đơn trong một giao dịch, có handler như place_order, và phát lỗi Order not found nếu mã đơn không tồn tại. Tiêu chí đạt: bán 4 túi gạo rồi hủy đơn thì kho về đúng 10 và bảng orders trống; gọi cancel_order(42) báo lỗi. Gợi ý: SELECT product_id, qty INTO v_product_id, v_qty ... FOR UPDATE.

Thử thách (tùy chọn)

Bài 3. Giỏ hàng cộng dồn. Đầu vào: bảng products. Viết add_line(INOUT p_total DECIMAL(12, 2), IN p_product_id INT, IN p_qty INT) cộng giá của dòng hàng vào tổng. Gọi hai lần với cùng một biến: 2 túi gạo rồi 3 chai nước mắm. Tiêu chí đạt: @t bằng 286000.00. Gợi ý: INOUT đọc được giá trị cũ của @t và ghi giá trị mới ra cùng biến đó.

Tự kiểm tra

Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.

1. (Mục tiêu 1, nhớ lại) DELIMITER là lệnh SQL hay lệnh của máy khách, và vì sao cần nó khi tạo thủ tục?
2. (Mục tiêu 2, dự đoán) SET @n = 10; CALL add_one(@n); với add_one có tham số INOUT cộng 1. @n bằng mấy? Nếu tham số là OUT thì sao?
3. (Mục tiêu 3, giải thích) Vì sao handler cần cả ROLLBACK lẫn RESIGNAL?
4. (Mục tiêu 4, đo) Một lần bán hàng cần 6 lệnh rời hay 1 lệnh CALL, và bộ đếm nào cho bạn biết?
5. (Mục tiêu 5, tìm lỗi) Thủ tục CREATE PROCEDURE bump(IN stock INT) UPDATE products SET stock = stock + 1 WHERE id = 1; định tăng kho của sản phẩm 1 thêm 1. Nó thực sự làm gì?

Điều cần nhớ

• CREATE PROCEDURE lưu khối lệnh vào server, CALL chạy nó; máy khách mysql cần DELIMITER để không cắt thân thủ tục.
• IN đưa vào, OUT bắt đầu bằng NULL và chỉ được gán khi chạy tới dòng gán, INOUT đọc và ghi cùng một biến.
• Thủ tục không tự là giao dịch: tính nguyên tử đến từ START TRANSACTION, handler EXIT có ROLLBACK và RESIGNAL.
• Một CALL thay cho nhiều lệnh rời, nên ứng dụng gửi ít lệnh hơn và không có chỗ hở giữa các bước.
• Đặt tiền tố p_ cho tham số, và nhớ START TRANSACTION trong thủ tục chốt giao dịch đang mở của bên gọi.

Cheat sheet:

Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.

Câu hỏi để dành cho bài sau: thủ tục chạy với quyền của người tạo (DEFINER) chứ không phải người gọi. Nếu ứng dụng chỉ được cấp quyền gọi thủ tục và không được đụng vào bảng, quyền đó nên được cấp thế nào cho đúng?

Đào sâu

Phần này dành cho bạn tò mò.

• Cơ chế bên trong. Thủ tục được lưu trong data dictionary của MySQL, kèm tài khoản tạo (DEFINER) và kiểu bảo mật (SQL SECURITY). Mặc định thủ tục chạy với quyền của DEFINER [Đã chạy: MySQL 8.0.44]:

SELECT routine_name, routine_type, security_type, definer
FROM information_schema.routines
WHERE routine_schema = DATABASE()
ORDER BY routine_name;

+--------------+--------------+---------------+----------------+
| ROUTINE_NAME | ROUTINE_TYPE | SECURITY_TYPE | DEFINER        |
+--------------+--------------+---------------+----------------+
| place_order  | PROCEDURE    | DEFINER       | root@localhost |
+--------------+--------------+---------------+----------------+

Ý nghĩa thực tế: cấp cho một tài khoản chỉ quyền EXECUTE trên thủ tục là đủ để nó chạy các lệnh bên trong, dù tài khoản đó không có quyền trên bảng. Bài sau dùng chính điều này làm một lớp bảo vệ.

• So sánh với lựa chọn khác. Hàm (FUNCTION) trả về một giá trị dùng trong biểu thức, nhưng không được chốt hay mở giao dịch [Đã chạy: MySQL 8.0.44]:

DELIMITER //
CREATE FUNCTION bad_fn() RETURNS INT
BEGIN
  START TRANSACTION;
  RETURN 1;
END//
DELIMITER ;

ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

Vì vậy việc "trừ kho rồi ghi đơn trong một giao dịch" phải nằm trong thủ tục, không viết được thành hàm.

• Hiểu lầm nâng cao. Có người nghĩ đưa logic vào thủ tục thì luôn nhanh hơn. Số lệnh qua mạng giảm thật (6 xuống 1), nhưng thân thủ tục vẫn là các lệnh SQL bình thường cần chỉ mục. Thủ tục khó viết test, khó theo dõi phiên bản bằng git và khó gỡ lỗi hơn mã ứng dụng [Suy luận: đây là đánh đổi thực tế, không phải số đo trong bài].
• Giới hạn của bài. Bài này chưa nói đến DECLARE CONTINUE HANDLER, con trỏ (CURSOR), vòng lặp và SQL động bằng PREPARE. Driver của ngôn ngữ lập trình gọi thủ tục bằng chuỗi CALL ... và không cần DELIMITER [Suy luận: bài không chạy driver nào].
• Đọc thêm. MySQL 8.0 Reference Manual, mục "Stored Programs and Views" và trang CREATE PROCEDURE: https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html và trang SIGNAL: https://dev.mysql.com/doc/refman/8.0/en/signal.html

Bài tiếp theo

Bài sau là Lỗ hổng và bảo mật MySQL: nếu thủ tục nối chuỗi từ đầu vào của khách rồi PREPARE, nó trở thành cửa vào cho SQL injection. Mini-dự án nối tiếp: khóa chặt shop bằng tham số hóa và tài khoản ứng dụng chỉ được cấp đúng quyền cần dùng.

Lời giải và đáp án

Làm cùng mình, Bước 2.

DELIMITER //
CREATE PROCEDURE restock(IN p_product_id INT, IN p_qty INT, OUT p_new_stock INT)
BEGIN
  UPDATE products SET stock = stock + p_qty WHERE id = p_product_id;
  SELECT stock INTO p_new_stock FROM products WHERE id = p_product_id;
END//
DELIMITER ;

CALL restock(3, 12, @s);
SELECT @s AS new_stock;

+-----------+
| new_stock |
+-----------+
|        12 |
+-----------+

Sản phẩm 3 từ 0 cộng 12 thành 12. UPDATE cộng vào cột, rồi SELECT ... INTO p_new_stock đọc lại giá trị sau khi cộng. Với Bước 3: sản phẩm 1 có 10, lần gọi đầu cho @s bằng 15. Lần gọi thứ hai cộng thêm 5 vào kho nên @s được ghi đè thành 20. Tham số OUT không cộng dồn giá trị cũ của biến, nó chỉ nhận tồn kho mới do thủ tục tính ra.

Bài 1.

CALL place_order(2, 3, @a);
CALL place_order(2, 3, @b);

SELECT @a AS a, @b AS b;
SELECT id, stock FROM products WHERE id = 2;
SELECT id, product_id, qty, total FROM orders;

+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
+------+------+
+----+-------+
| id | stock |
+----+-------+
|  2 |     1 |
+----+-------+
+----+------------+-----+----------+
| id | product_id | qty | total    |
+----+------------+-----+----------+
|  1 |          2 |   3 | 96000.00 |
+----+------------+-----+----------+
ERROR 1644 (45000): Not enough stock

Lần gọi đầu thành công (@a là 1, kho 4 giảm còn 1, đơn 1 trị giá 96000.00). Lần gọi thứ hai cần 3 nhưng kho chỉ còn 1 nên lỗi 1644, @b vẫn là NULL vì chưa từng được gán, kho vẫn 1 và vẫn một đơn.

Bài 2.

DELIMITER //
CREATE PROCEDURE cancel_order(IN p_order_id INT)
BEGIN
  DECLARE v_product_id INT;
  DECLARE v_qty INT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  SELECT product_id, qty INTO v_product_id, v_qty
  FROM orders WHERE id = p_order_id FOR UPDATE;

  IF v_product_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order not found';
  END IF;

  UPDATE products SET stock = stock + v_qty WHERE id = v_product_id;
  DELETE FROM orders WHERE id = p_order_id;
  COMMIT;
END//
DELIMITER ;

CALL place_order(1, 4, @oid);
SELECT stock FROM products WHERE id = 1;
CALL cancel_order(@oid);
SELECT stock FROM products WHERE id = 1;
SELECT COUNT(*) AS orders_left FROM orders;

+-------+
| stock |
+-------+
|     6 |
+-------+
+-------+
| stock |
+-------+
|    10 |
+-------+
+-------------+
| orders_left |
+-------------+
|           0 |
+-------------+

Kho về 10 và orders_left bằng 0. Gọi mã đơn không tồn tại:

CALL cancel_order(42);

+-------+
| stock |
+-------+
|     6 |
+-------+
+-------+
| stock |
+-------+
|    10 |
+-------+
+-------------+
| orders_left |
+-------------+
|           0 |
+-------------+
ERROR 1644 (45000): Order not found

Bài 3.

DELIMITER //
CREATE PROCEDURE add_line(INOUT p_total DECIMAL(12, 2), IN p_product_id INT, IN p_qty INT)
BEGIN
  DECLARE v_price DECIMAL(10, 2);
  SELECT price INTO v_price FROM products WHERE id = p_product_id;
  SET p_total = p_total + v_price * p_qty;
END//
DELIMITER ;

SET @t = 0;
CALL add_line(@t, 1, 2);
CALL add_line(@t, 2, 3);
SELECT @t AS cart_total;

+------------+
| cart_total |
+------------+
|  286000.00 |
+------------+

Lần gọi đầu: @t từ 0 cộng 95000 x 2 thành 190000. Lần gọi hai: @t bắt đầu từ 190000 (nhờ INOUT) cộng 32000 x 3 thành 286000.00.

Tự kiểm tra.

1. Là lệnh của máy khách. Máy khách cắt lệnh tại dấu chấm phẩy đầu tiên, nên thân thủ tục nhiều lệnh bị cắt dở nếu không đổi dấu kết thúc.
2. Với INOUT, @n bằng 11. Với OUT, tham số bắt đầu bằng NULL, nên NULL + 1 là NULL và @n trở thành NULL.
3. ROLLBACK bỏ các thay đổi dở dang. RESIGNAL phát lại lỗi để bên gọi biết việc đã thất bại; thiếu nó, handler nuốt lỗi.
4. Sáu lệnh rời so với một lệnh CALL; bộ đếm Questions của phiên cho biết (trừ lệnh đo).
5. Tham số stock trùng tên cột stock, nên vế phải stock + 1 là tham số cộng 1 chứ không phải giá trị cũ của cột. Kho của sản phẩm 1 bị đặt bằng giá trị tham số cộng 1, không phải tăng thêm 1. Đặt tiền tố p_ cho tham số để tránh.