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;