Mysql stored procedure

I. MySQL: Stored Procedure là gì?

Stored Procedure được định nghĩa như một tập các khai báo sql được lưu trữ ngay trong cơ sở dữ liệu (database) và sau đó, được triệu gọi bởi một program, một trigger hay thậm chí là một stored procedure khác.

Stored Procedured cho phép truyền tham số.

Ưu điểm của Stored Proccedure trong Mysql

• Stored procedure làm tăng khả năng thực thi của ứng dụng.

• Sau khi được tạo, stored procedure sẽ được biên dịch (compile) và lưu trữ ngay trong database. Lẽ dĩ nhiên, nó sẽ chạy nhanh hơn là một lệnh sql chưa compile được gửi trực tiếp từ ứng dụng.

• Stored procedure làm giảm lưu lượng thông tin giao tiếp giữa ứng dụng và database server, thay vì gửi những câu lệnh sql chưa complile dài , ứng dụng chỉ việc gửi tên của stored procedure và lấy lại kết quả.

Những nhược điểm của Stored Procudure

• Nếu bạn tạo ra quá nhiều Procedure thì hệ quản trị sẽ sử dụng bộ nhớ để lưu trữ các thủ tục này khá nhiều.
• Ngoài ra nếu bạn thực hiện quá nhiều xử lý trong mỗi thủ tục thì đồng nghĩa với việc CPU sẽ làm việc nặng hơn.
• Stored procedure chỉ chứa đựng các khai báo sql, vì vậy rất khó có thể viết 1 procedure nhằm thực hiện các thao tác xử lý phức tạp như các ngôn ngữ khác làm được ở tần ứng dụng như php,c#...
• Một số hệ quản trị CSDL có những tool hỗ trợ Debug Store nhưng MYSQL thì không có.

Tạo Mysql Stored Procedure đầu tiên

DELIMITER $$
CREATE PROCEDURE procedureName()
BEGIN
   SELECT *  FROM products;
END; $$
DELIMITER

Giải thích:

• DELIMITER $$ dùng để phân cách bộ nhớ lưu trữ thủ tục Cache và mở ra một ô lưu trữ mới.

• CREATE PROCEDURE procedureName() dùng để khai báo tạo một Procedure mới, trong đó procedureName chính là tên thủ tục còn hai từ đầu là từ khóa.

• **BEGIN và END; $$ ** dùng để khai báo bắt đầu của Procedure và kết thúc Procedure

• **DELIMITER ; ** đóng lại ô lưu trữ

Sau đó chạy câu SQL này và nó báo thành công tức là bạn đã tạo mới một thủ tục với tên procedureName

Cách gọi procedure

	CALL procedureName();

** Sửa procedure **

Trong Mysql không cung cấp lệnh sửa Stored nên thông thường chúng ta sẽ chạy lệnh tạo mới.

Lệnh Drop để xóa đi Procedure đó và tạo lại:

DELIMITER $$
DROP PROCEDURE IF EXISTS `procedureName`$$
CREATE PROCEDURE procedureName()
BEGIN
   SELECT *  FROM products;
END; $$
DELIMITER

** Phân quyền cho procedure **

Khi bạn dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vi của User đó.

Ví dụ bạn không có quyền edit mà bạn tạo mới một Procedure Edit thì khi chạy sẽ bị báo lỗi . Chính vì vậy thông thường khi edit bạn phải thêm người định nghĩa nó như sau:

DELIMITER $$
DROP PROCEDURE IF EXISTS `procedureName`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE procedureName()
BEGIN
   SELECT *  FROM products;
END; $$
DELIMITER

DEFINER=root@localhost chính là tên người đã tạo.

III. Biến (variable) trong MYSQL Stored Procedure

DECLARE variable_name datatype(size) DEFAULT default_value

Giải thích:

DECLARE: là từ khóa tạo biến variable_name: là tên biến datatype(size): là kiểu dữ liệu của biến và kích thước của nó DEFAULT default_value: là gán giá trị mặc định cho biến

SET variable_name = 'value';

Ví dụ: Định nghĩa biến age và gán giá trị 20 cho nó.

DECLARE a INT(11) DEFAULT 0

SET a = 10

Gán giá trị thông qua lệnh SELECT

DECLARE total INT DEFAULT 0

SELECT COUNT(*) INTO total FROM products

Câu lệnh trên nó sẽ thực hiện câu truy vấn SQL đếm tổng số record và sau đó gán vào biến total.

Nếu như bạn định nghĩa một biến giữa BEGIN và END thì đó ta gọi là biến cục bộ của Procedure. Bạn có thể định nghĩa nhiều biến trong một Procedure.

DELIMITER $$
DROP PROCEDURE IF EXISTS total $$
CREATE PROCEDURE total()
BEGIN
    DECLARE a INT (11) DEFAULT 0;
    DECLARE b INT (11) DEFAULT 0;
    DECLARE result INT (11) DEFAULT 0;

    SET a = 1;
    SET b = 2;
    SET result = a + b;

    SELECT result;

END; $$
DELIMITER;

IV Truyền tham số vào Mysql Stored Procedure

Trong MYSQL thì sẽ tồn tại ba loại Tham số đó là tham số IN, tham số OUT và tham số INOUT

• IN: Đây là chế độ mặc định (nghĩa là nếu bạn không định nghĩa loại nào thì nó sẽ hiểu là IN).

• OUT: Chế độ này nếu như trong Procedure có tác động thay đổi thì nó sẽ thay đổi theo. Nhưng có điều đặc biệt là dù trước khi truyền vào mà bạn gán giá trị cho biến đó thì vẫn sẽ không nhận được vì mặc định nó luôn hiểu giá trị truyền vào là NULL.

• INOUT: Đây là sự kết hợp giữa IN và OUT. Nghĩa là có thể gán giá trị trước và có thể bị thay đổi nếu trong Procedure có tác động.

Table Products demo:

DELIMITER $$
DROP PROCEDURE IF EXISTS getById $$
CREATE PROCEDURE getProductId(
				IN id INT(11),
                IN title VARCHAR(255)
                )
BEGIN
    /*Code*/
END; $$
DELIMITER;

**Tham số loại IN **

DELIMITER $$
DROP PROCEDURE IF EXISTS getProId $$
CREATE PROCEDURE getProId(IN idpro INT(11))
BEGIN
    SELECT * FROM products WHERE id = idpro;
END; $$
DELIMITER;

Kết quả:

Tham số loại OUT trong Mysql Stored Procedure

• Khi truyền tham số dạng OUT mục đích là lấy dữ liệu trong Proedure và sử dụng ở bên ngoài.

• Khi truyền tham số vào dạng OUT phải có chữ @ đằng trước biến

• Hoạt động giống tham chiếu nên biến truyền vào dạng OUT không cần định nghĩa trước, chính vì vậy khởi đầu nó có giá trị NULL

VD:

DELIMITER $$
DROP PROCEDURE IF EXISTS Title $$
CREATE PROCEDURE Title(OUT title VARCHAR(255))
BEGIN
    SET title = 'Title 2';
END; $$
DELIMITER;

Tham số dạng INOUT

INOUT là sự kết hợp giữa IN và OUT, nghĩa là:

DELIMITER $$

DROP PROCEDURE IF EXISTS counter $$

CREATE PROCEDURE counter(INOUT number INT(11))
BEGIN
    SET number = number + 1;
END; $$
DELIMITER;

Sử dụng:

SET @counter = 1;
CALL counter(@counter);
SELECT @counter;

IF if_expression THEN
    commands
   ELSEIF elseif_expression THEN
    commands
   ELSE
    commands
END IF;

Tham khảo IF ELSE Mệnh đề if cho phép bạn tạo luồng xử lý rẻ nhánh, nếu đúng thì thực thi và ngược lại mệnh đề sai thì nó sẽ không thực thi.

Kết Luận: Bạn có thể thấy cách một Stored Procedure hoạt động không khác gì một hàm trong lập trình. Như vậy bạn đã học được cách tạo một Stored Procedure trong MySQL.

Link Tham khảo: mysql

mysqltutorial