Hệ quản trị CSDL MySQL - Tham số trong SProcedure
Tham số trong Store Procedure
Hầu hết các thủ tục thường trú đều yêu cầu tham số. Tương tự như khi chúng ta xây dựng các thủ tục, hàm hay phương thức trong các ngôn ngữ lập trình khác như C, Pascal, C++, java, .v.v.
Mỗi tham số thuộc một trong 3 kiểu IN, OUT hoặc INOUT.
- Kiểu IN: là kiểu mặc định. Kiểu IN quy định giá trị của tham số truyền vào theo kiểu này sẽ không hề thay đổi khi kết thúc quá trình tính toán của thủ tục thường trú. Dù cho trong thân thủ tục thường trú có tồn tại bất kì phép toán nào làm thay đổi giá trị của tham số hay không.
- Kiểu OUT: chỉ ra thủ tục thường trú có thể thay đổi giá trị của tham số loại này.
- Kiểu INOUT: là kết hợp của kiểu IN và OUT, chúng ta có thể truyền tham số vào thủ tục thường trú và nhận lại nó với giá trị mới sau khi đã được thủ tục thường trú tính toán làm thay đổi giá trị.
Cú pháp khai báo tham số như sau:
MODE param_name param_type(param_size)
Trong đó, MODE có thể là IN, OUT hoặc INOUT.
Ví dụ: xây dựng thủ tục thường trú lấy ra danh sách tất cả các văn phòng của công ty tại một quốc gia X.
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT city, phone
FROM offices
WHERE country = countryName;
END //
DELIMITER ;
Gọi thủ tục thường trú trên như sau:
CALL GetOfficeByCountry('USA')
Hay
CALL GetOfficeByCountry(‘France’)
Ví dụ 2: Đếm số lượng hóa đơn theo tình trạng của hóa đơn đó. Ví dụ như cần biết bao nhiêu hóa đơn đã chuyển, đã hủy bỏ hay đang xử lý
DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
IN orderStatus VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(orderNumber)
INTO total
FROM orders
WHERE status = orderStatus;
END$$
DELIMITER ;
Gọi thủ tục thường trú trên như sau
CALL CountOrderByStatus('Shipped',@total);
SELECT @total AS total_shipped;
Hay
CALL CountOrderByStatus('in process',@total);
SELECT @total AS total_in_process;
(Video minh họa)
Ví dụ 3: một ví dụ về chế độ INOUT của tham số
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 ;
Gọi thủ tục
SET @str = 'mysql stored procedure tutorial';
CALL Capitalize(@str);
SELECT @str;