Hướng dẫn exec stored procedure mysql

Giới thiệu, hướng dẫn tạo và sử dụng Stored Procedure trong hệ quản trị cơ sở dữ liệu MySQL.

Stored Procedure là gì?

Stored Procedure tương tự như thủ tục hoặc phương thức trong các ngôn ngữ lập trình nhưng giới hạn trong các thao tác truy vấn dữ liệu mà câu lệnh SQL của hệ quản trị cơ sở dữ liệu đó cung cấp.

Cơ chế của Stored Procedure là các câu truy vấn sẽ được biên dịch và lưu trữ sẵn trong bộ nhớ của hệ quản trị cơ sở dữ liệu, các ứng dụng thay vì gửi câu truy vấn vào thời điểm muốn thực thi truy vấn thì gửi tên của Stored Procedure cần sử dụng, như vậy truy vấn sẽ được thực thi ngay thay vì phải thông qua bước biên dịch.

Vì sao cần Stored Procedure?

Tính trừu tượng

Stored Procedure cung cấp một lớp trừu tượng cho các ứng dụng và ngôn ngữ lập trình thao tác với hệ quản trị cơ sở dữ liệu. Việc thực hiện cùng 1 thao tác giống nhau đến cơ sở dữ liệu trên 2 ứng dụng (hoặc nhiều hơn) hay ngôn ngữ lập trình khác nhau không đòi hỏi người lập trình phải viết lại hoàn toàn các thao tác đó.

Ví dụ: có thể tạo 1 Stored Procedure tên total để đếm tổng số dòng trên 1 bảng và gọi đến Stored Procedure từ các ngôn ngữ lập trình khác nhau như PHP, Node.js, C#, …

Lập trình viên không cần quan tâm đến các thao tác này hoạt động như thế nào mà chỉ cần quan tâm đến mục đích của chúng được tạo ra và kết quả mà họ mong muốn nhận về.

Hiệu năng

Stored Procedure được cung cấp bởi hệ quản trị cơ sở dữ liệu, được biên dịch và lưu lại 1 lần duy nhất, được gọi đến và thao tác trực tiếp bởi hệ quản trị cơ sở dữ liệu đó. Vì vậy, hiệu năng đạt được ở mức cao nhất mà hệ quản trị cơ sở dữ liệu có thể thực hiện mà không phụ thuộc vào tính chất của ngôn ngữ lập trình bậc cao nào.

Bảo mật

Nếu hệ quản trị cơ sở dữ liệu được tùy chỉnh để chỉ có thể thực hiện thao tác gọi đến các Stored Procedure có sẵn cho 1 số người dùng cụ thể, sẽ đảm bảo tính an toàn của dữ liệu đối với những người dùng này. Việc phân quyền này đảm bảo người dùng chỉ có quyền hạn thay đổi dữ liệu ở mức cho phép của người quản trị cơ sở dữ liệu mà không thể can thiệp sâu hơn vào dữ liệu đó.

Sử dụng Stored Procedure trong MySQL

Cấu trúc câu lệnh tạo Stored Procedure

CREATE PROCEDURE [procedure_name] ([param1, param2,…])
	BEGIN
		[sql_statements]
   	END

Trong đó:

  • CREATE PROCEDURE là câu lệnh dùng để khai báo Stored Procedure trong MySQL.
  • [procedure_name] là tên của Stored Procedure.
  • [param1, param2, …] là các tham số truyền vào Stored Procedure. Cấu trúc khai báo các tham số:
    • [IN | OUT] param [datatype: INT, VARCHAR, …], với [IN | OUT] chỉ định tham số đầu vào (IN) hay đầu ra (OUT).
    • Ví dụ: (IN param1 INT, OUT param2 VARCHAR(25))
  • BEGIN khai báo phần thân của Stored Procedure, kể từ câu lệnh này trở đi, định nghĩa những thao tác mà Stored Procedure sẽ thực hiện khi được gọi.

Gọi Stored Procedure đã được khai báo:

CALL procedure_name ([param1, param2,…]);

Ví dụ:

CALL count_products (@a);

Môi trường thực nghiệm

  • Windows 10
  • MySQL Community 5
  • MySQL Workbench 6

Tạo dữ liệu mẫu

Tạo database & các bảng

CREATE DATABASE IF NOT EXISTS procedure_example
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci;

USE procedure_example;

CREATE TABLE IF NOT EXISTS san_pham (
    id int(10) NOT NULL AUTO_INCREMENT,
    ten varchar(255) NOT NULL UNIQUE,
    gia int(10) NOT NULL,
    mo_ta mediumtext,
    created_at timestamp DEFAULT NOW() NOT NULL,
    updated_at timestamp DEFAULT NOW() NOT NULL ON UPDATE NOW(),
    PRIMARY KEY (id)
);

describe procedure_example.san_pham;

Mô hình cơ sở dữ liệu thử nghiệm

Hướng dẫn exec stored procedure mysql

Tạo dữ liệu mẫu

USE procedure_example;
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('IPHONE 5S 16GB', 5990000, 'Thiết kế sang trọng, gia công tỉ mỉ, tích hợp cảm biến vân tay cao cấp hơn, camera cho hình ảnh đẹp và sáng hơn.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('iPhone 7 256GB', 21990000, 'iPhone 7 256 GB mang trên mình thiết kế quen thuộc từ thời iPhone 6, máy được trang bị bộ nhớ lưu trữ lớn, khả năng chống nước cao cấp, dàn loa stereo cho âm thanh hay và cấu hình cực mạnh.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Sony Xperia X', 7990000, 'Sony vừa giới thiệu dòng sản phẩm X Serie mới của hãng trong năm 2016 tại triển lãm MWC. Xperia X là chiếc smartphone tầm trung mới với nhiều điểm nhấn đáng chú ý.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Apple Macbook Air MMGF2ZP/A i5 5250U/8GB/128GB', 23990000, 'Với thiết kế gần như hoàn hảo, hiệu năng mượt mà, phiên bản Macbook Air 2015 13 inch sẽ còn tối ưu hơn khi được trang bị card đồ họa tích hợp Intel HD Graphics 6000, thanh RAM 8 GB mạnh mẽ, ổ cứng lưu trữ SSD tốc độ tăng gấp đôi 128 GB và bộ vi xử lý mới từ Intel.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Samsung Galaxy S8 Plus', 20490000, 'Galaxy S8 và S8 Plus hiện đang là chuẩn mực smartphone về thiết kế trong tương lai. Sau nhiều ngày được sử dụng, mình xin chia sẻ những cảm nhận chi tiết nhất về chiếc Galaxy S8 Plus - thiết bị đang có doanh số đặt hàng khủng nhất hiện tại.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Xiaomi Mi MIX (6GB/256GB)', 16990000, 'Mi Mix có thiết kế tối giản với phần viền ở cạnh bên và đỉnh máy đã được làm cực mỏng cho cảm giác như bị loại bỏ hoàn toàn.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Apple Macbook Pro Touch MLH32SA/A i7 6700HQ/16GB/256GB (2016)', 56990000, 'MacBook Pro 15” 2016 là “khủng long xinh đẹp” trong thế giới laptop, đặc biệt là sự xuất hiện của thanh Touch Bar – là một dải cảm ứng cho phép truy cập nhanh công cụ.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Acer Aspire SF514 51 72F8 i7 7500U/8GB/256GB/Win10', 24990000, 'Acer Aspire SF514-51-72F8 i7 7500U là dòng sản phẩm có thế mạnh về thiết kế sang trọng, gọn nhẹ đậm chất “xách tay” dễ di chuyển. Đồng thời cũng được Acer ưu ái gắn cho một cấu hình khỏe để đáp ứng hầu hết các nhu cầu khi sử dụng máy.');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('Tai nghe chụp tai Sony MDR - ZX110AP', 590000, '');
INSERT INTO san_pham (ten, gia, mo_ta) VALUES ('SIM DCOM 4G VIETTEL 7GB', 75000, 'Nạp 70.000đ/tháng để nhận: 7GB data/tháng');
Hướng dẫn exec stored procedure mysql

Tạo Stored Procedure để đếm tổng số sản phẩm trong bảng san_pham

USE procedure_example;

/** COUNT PRODUCTS **/

DROP PROCEDURE IF EXISTS count_products;

delimiter //

CREATE PROCEDURE count_products (OUT param1 INT)
  BEGIN
    SELECT COUNT(*) INTO param1 FROM san_pham;
  END//

delimiter ;

Trong đoạn code trên, sử dụng lệnh delimiter [symbol] để chỉ định kí tự kết thúc cho đoạn lệnh SQL. Mặc định, kí tự chấm phẩy ; được dùng để chỉ thị việc câu lệnh SQL đã chấm dứt.

Trong đoạn lệnh SQL trên, để dùng nhiều câu lệnh SQL khác nhau trong phần thân của Stored Procedure, sử dụng dấu chấm phẩy ; để phân tách các câu lệnh riêng biệt. Do đó, sử dụng kí tự // để báo cho MySQL biết rằng phần khai báo cho Stored Procedure chỉ chấm dứt khi gặp kí tự này, và câu lệnh delimiter được sử dụng để chỉ định điều này.

Kiểm tra kết quả Stored Procedure count_products:

CALL count_products(@a);

SELECT @a;

Kết quả như sau:

Hướng dẫn exec stored procedure mysql

Tạo Stored Procedure tìm kiếm sản phẩm theo id được truyền vào

USE procedure_example;

/** FIND PRODUCT BY ID **/

DROP PROCEDURE IF EXISTS find_product;

delimiter //

CREATE PROCEDURE find_product (IN param1 INT)
  BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_find_product;

    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_find_product AS
      (SELECT * FROM san_pham WHERE id=param1);

    SELECT * FROM tmp_find_product;
  END//

delimiter ;

Kiểm tra kết quả Stored Procedure find_products

CALL find_product(8);

Kết quả như sau:

Hướng dẫn exec stored procedure mysql

Bài chung series

  • Trigger trong MySQL.
  • Stored Procedure trong MySQL.