[vi] Kiến thức MySQL Stored Procedure là gì?

Syntax tạo procedure
SQL:
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
trong đó
SQL:
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement
Syntax thực thi procedure
SQL:
CALL stored_procedure_name(argument_list);
Các statement trên được dùng để tạo stored routine (a stored procedure hoặc stored function). Có nghĩa là, routine sẽ được nhận biết bời server. Mặc định, stored routine sẽ kết nối tời database mặc định, để kết nối tời db, ta chỉ định rõ db_name.sp_name khi tạo

Ví dụ:
Tạo 1 procedure getAllProducts()
SQL:
DELIMITER //

CREATE PROCEDURE GetAllProducts()
BEGIN
    SELECT *  FROM products;
END //

DELIMITER ;
Delimiter ko phải 1 phần trong syntax của procedure, nó được dùng để đổi delimiter thành dấu // thay vì dấu ; như mặc định. Lý do vì bên phần routine_body (Trong BEGIN...END) chứa nhiều câu lệnh SQL được ngăn cách bằng dấu ; . Vậy nên để SQL biết đâu là dấu kết thúc khai báo procedure, đổi và dùng tạm dấu //

Thực thi procedure
SQL:
CALL GetAllProducts();
Các loại parameter
SQL:
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
Mysql có 4 loại params cho procedure là thường, IN, OUT, OUT trong đó:

  • IN param: là dạng param mặc định (nếu bạn không define thì mặc định là IN). Giá trị của param được protected, nghĩa là giá trị IN param sẽ giữ nguyên dù bên trong procedure có bị thay đổi. Hay procedure chỉ thao tác trên bản copy của IN param
SQL:
delimiter //
create procedure update_price (IN temp_ISBN varchar(10), IN new_price integer)
               -> begin
               -> update book set price=new_price where ISBN=temp_ISBN;
               -> end; //
call update_price(001, 600); //

delimiter ;
select *from book;
+--------+------+------------------------------+--------+--------+-------+-------+
| BookId | ISBN | book_name                    | author | ed_num | price | pages |
+--------+------+------------------------------+--------+--------+-------+-------+
|      1 |    1 | Glimpses of the past         |      1 |      1 |   600 |   396 |  <--- updated this row
|      2 |    2 | Beyond The Horizons of Venus |      1 |      1 |   650 |   396 |
|      3 |    3 | Ultrasonic Aquaculture       |      2 |      1 |   799 |   500 |
|      4 |    4 | Cyrogenic Engines            |      2 |      1 |   499 |   330 |
+--------+------+------------------------------+--------+--------+-------+-------+
4 rows in set (0.0013 sec)
  • OUT param: giá trị của OUT param có thể thay đổi bên trong procedure, và giá trị mới của OUT param sẽ được trả ngược lại tới hàm gọi nó. Procedure không thể truy cập giá trị gốc của OUT param. ta thường truyền vào các biến @ để có thể nhận giá trị trả về từ procedure.
SQL:
delimiter //
create procedure disp_max(OUT highestprice integer)
                 -> begin
                 -> select max(price) into highestprice from book;
                 -> end; //
call disp_max(@M); //
select @M;

+-----+
| @M  |
+-----+
| 799 |
+-----+
1 row in set (0.0005 sec)
  • INOUT param: là sự kết hợp của IN và OUT param. Nó có nghĩa là chương trình gọi sẽ truyền argument vào, procedure nhận và xử lý, thay đổi INOUT param này rồi trả giá trị mới lại cho chương trình gọi.
SQL:
delimiter //
create procedure disp_gender(INOUT mfgender integer, IN emp_gender varchar(6))
                     -> begin
                     -> select COUNT(gender)
                         INTO mfgender FROM author where gender = emp_gender;
                     -> end; //
delimiter ;
call disp_gender(@M, "Male");
select @M;
call disp_gender(@F, "Female");
select @F;

+----+
| @M |
+----+
|  1 |
+----+
1 row in set (0.0004 sec)
+----+
| @F |
+----+
|  1 |
+----+
1 row in set (0.0005 sec)
Ref:
MySQL :: MySQL 8.0 Reference Manual :: 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
Different types of Procedures in MySQL - GeeksforGeeks
MySQL Stored Procedure Parameters
 

Facebook Comments

Top