Giới thiệu về stored procedure trong MySQL (P.4) Parameters trong Stored Procedures

Trong phần này, bạn sẽ học cách viết 1 stored procedures với parameter. Tôi cũng sẽ cung cấp cho bạn 1 vài ví dụ về stored procedure, qua đó giúp bạn hiểu sâu hơn về parameter trong stored procudes.

Hầu hết các stored procedures đều có param. Param làm cho stored procedure trở nên linh động và hữu dụng hơn. Trong MySQL, 1 param thuộc 1 trong 3 mode sau: IN, OUT và INOUT.

IN: bất cứ sự thay đổi giá trị nào lên param này bên trong stored procedure đều không có kết quả. Nghĩa là param vẫn giữ nguyên giá trị của nó sau khi ra khỏi stored procedure. Ví dụ, bạn truyền vào stored procedure GetAll 1 param có tên Id với giá trị là 10. Ngay cả khi trong stored procedure GetAll, bạn có cố tình thay đổi giá trị của param Id thì giá trị của nó sau khi thực thi stored procedure vẫn là 10.

OUT: cho phép stored procedure thay đổi giá trị của param và trả nó về chương trình gọi.

INOUT: kết hợp giữa 2 mode trên. Bạn có thể truyền 1 param dạng này vào stored procedure và nhận lại 1 giá trị mới.

Cú pháp để khai báo param trong stored procedure:

Mã:

MODE param_name param_type(param_size)

MODE có thể là IN, OUT hay INOUT tùy thuộc vào mục đích sử dụng của bạn.
Param_name là tên của param. Tên của param không nên trùng với tên của column hay table và phải tuân theo quy tắt đặt tên cho param. Theo sau tên param là kiểu param và kích cỡ của nó.

Mỗi param được ngăn cách bởi dấu “,” nếu stored procedure có nhiều hơn 1 param.
Trong ví dụ này, stored procedure có nhiệm vụ lấy các thông tin của tất cả các office ở 1 country nào đó.

Mã:

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
   BEGIN
 SELECT city, phone
 FROM offices
 WHERE country = countryName;
   END //
DELIMITER ;

Bạn có thể thấy, countryName được dùng như 1 param kiểu IN. Kiểu dữ liệu của countryName là varchar và kích thước là 255. Trong phần thân của stored procedure, ta nhận được thông tin của tất cả các office trong country được xác định bởi param countryName.

Giả sử bạn muốn nhận được thông tin về các office ở USA, bạn gọi stored procedure như sau:

Mã:

CALL GetOfficeByCountry('USA')

Tương tự, nếu muốn lấy thông tin của tất cả các văn phòng nằm ở France, gán giá trị của countryName=”France”

Mã:

CALL GetOfficeByCountry(‘France’)

Ví dụ thứ 2, ta sẽ viết 1 stored procedure để tính số lượng các order theo theo từng loại order riêng biệt được xác định bằng chính trạng thái của order đó, ví dụ : shipped, resolverd, cancelled, on hold, disputed hay in process

Mã:

 DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
   BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
   END$$
DELIMITER ;

Stored procedure CountOrderByStatus có 2 param:
orderStatus param có kiểu IN,.
Total param có kiểu OUT.
Để lấy số lượng order theo trạng thái shipped, ta sử dụng phát biểu:

Mã:

CALL  CountOrderByStatus('Shipped',@total);
SELECT @total AS  total_shipped;

Lấy số lượng order ở trạng thái “in process”:

Mã:

CALL CountOrderByStatus('in  process',@total);
SELECT @total AS  total_in_process;

Store procedure thứ 3 dùng để minh họa cho trường hợp param là INOUT. Store procedure này có chức năng in hoa (capitalize) các ký tự đầu tiên của 1 từ trong chuỗi, sau đó trả về cho chương trình gọi thông qua chính param vừa được truyền vào:

Mã:

 DELIMITER $$
CREATE PROCEDURE `Capitalize`(INOUT str VARCHAR(1024))
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE myc, pc CHAR(1);
 DECLARE outstr VARCHAR(1000) DEFAULT str;
 WHILE i <= CHAR_LENGTH(str) DO   SET myc = SUBSTRING(str, i, 1);   SET pc = CASE WHEN i = 1 THEN ' '          ELSE SUBSTRING(str, i - 1, 1)     END;   IF pc IN (' ', '&', '''', '_', '?', ';', ':', '!', ',', '-', '/', '(', '.') THEN       SET outstr = INSERT(outstr, i, 1, UPPER(myc));   END IF;   SET i = i + 1;  END WHILE;  SET str = outstr; END$$ DELIMITER ;

Cách gọi capitalize trong stored procedure:

Mã:

SET @str = 'mysql stored procedure tutorial';
CALL Capitalize(@str);
SELECT @str;

Chuỗi trả về thông qua @str: ‘Mysql Stored Procedure Tutorial’