Stored Procedure MySQL dùng để làm gì?

Mục tiêu

Luyện tập sử dụng store procedure trong MySql

Mô tả- Store Procedure

Thực hành tạo store procedure trên cơ sở dữ liệu classicmodels.

Chúng ta có bảng customers như sau:

Hướng dẫn

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.

S.P 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 findAllCustomers()

BEGIN

SELECT * FROM customers;

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 findAllCustomers() dùng để khai báo tạo một Procedure mới, trong đó findAllCustomers 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 findAllCustomers.

Cách gọi procedure

call findAllCustomers();

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 `findAllCustomers`//

CREATE PROCEDURE findAllCustomers()

BEGIN

SELECT *  FROM customers where customerNumber = 175;

END; //

DELIMITER

Trên đây CodeGym đã cùng với bạn luyện tập sử dụng store procedure trong MySql . Hãy chụp ảnh màn hình và nộp bài thực hành của bạn trên CodeGymX để cùng nhau luyện tập nhé!

Mysql Stored Procedure giống như hàm trong PHP, có thể gọi là hàm(Procedure) trong MySQL để thực hiện các dòng lệnh với các thao tác như Insert, update…

Đối với hàm thì đương nhiên có tính sử dụng lại(gọi là đệ quy), vấn đề đệ quy này không khuyến khích sử dụng vì MySQL không nên để tính toán quá nhiều.

Với Stored Procedure chúng ta có thể sử dụng các hàm xử lý chuỗi, câu lệnh if else, lệnh switch case và những hàm hỗ trợ khác như replace, count, concat…

Ưu điểm:

• Tăng hiệu suất xử lý của ứng dụng web, vì chúng được lưu trong hệ quản trị cơ sở dữ liệu chỉ cần gọi lại là có thể sử dụng.
• Giúp giảm thời gian giao tiếp giữa các ứng dụng với hệ quản trị MYSQL, bởi thay vì gửi nhiều câu lệnh dài thì ta chỉ cần gọi tới một thủ tục và trong thủ tục này sẽ thực hiện nhiều câu lệnh SQL.
• Dễ sử dụng và ứng dụng dễ nhìn hơn, nghĩa là khi ta đã định nghĩa một Stored thì công việc của chúng ta chỉ quan tâm đến tên thủ tục, các tham số truyền vào.
• Vì nó được thực hiện trong MySQL nên nó còn liên quan đến quyền user trong MySQL, phải cấp quyền cho user mới có thể sử dụng.

Nhược điểm:

• 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.
• Nếu bạn thực hiện quá nhiều xử lý trong một thủ tục thì CPU sẽ làm việc nặng hơn.
• Nếu sử dụng thủ tục thì sẽ rất khóbảo trì và nâng cấp phát triển ứng dụng về sau.

Tạo MYSQL Stored Procedure đầu tiên:

Đầu tiên chúng ta cần có dữ liệu để sử dụng, tiến hành tạo một Database với tên bạn chọn và tạo một bảng shops:

CREATE TABLE IF NOT EXISTS `shops` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` TEXT COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

INSERT INTO `shops` (`id`, `name`, `description`) VALUES ('1', 'shop tiki 1', 'shop tiki 1');
INSERT INTO `shops` (`id`, `name`, `description`) VALUES ('2', 'shop tiki 2', 'shop tiki 2');
INSERT INTO `shops` (`id`, `name`, `description`) VALUES ('3', 'shop tiki 3', 'shop tiki 3');
INSERT INTO `shops` (`id`, `name`, `description`) VALUES ('4', 'shop tiki 4', 'shop tiki 4');

Tạo Stored Procedure: Chúng ta sẽ viết một Procedure với tên là GetShops(), để lấy danh sách tất cả các shop nằm trong bảng Shops. Cú pháp khai báo tạo mới một Procedure như sau:

DELIMITER $$
CREATE PROCEDURE procedureName()
BEGIN
/*Xu ly*/
END; $$
DELIMITER ;

Với:

• Dòng đầu tiên DELIMITER $$. Đây là cú pháp nên bắt buộc bạn phải nhập như vậy
• Dòng 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.
• BEGIN và END; $$ dùng để khai báo bắt đầu của Procedure và kết thúc Procedure
• Cuối cùng là đóng lại ô lưu trữ DELIMITER ;

Lưu ý: chúng ta chạy cú pháp này ở đâu? Có thể sử dụng các IDE để thực hiện một cách dễ dàng như: IDE SqlYog, MySQL Workbench…, hoặc cách đơn giản là dùng Phpmyadmin ở đây mình sẽ sử dụng nó.

Mở phpmyadmin trên máy bạn sau đó chọn cơ sở dữ liệu đã tạo lúc đầu, tiếp theo vào phần SQL chạy cú pháp trên (nhớ đổi procedureName thành GetShops nha), sau đó nhấn Go

Muốn xem mình có tạo thành công chưa, các bạn tiếp tục phần SQL chạy cú pháp sau:

show procedure status;

lệnh này sẽ liệt kê toàn bộ các procedure đang có trong mysql của các bạn hoặc xem bằng cách thoát phpmyadmin rồi vào lại truy cập như trong ảnh sẽ thấy procedure GetShops() mình vừa tạo ngay:

Bây giờ chúng ta sẽ viết câu lệnh Select cho procedure GetShops() này nghĩa là chúng ta phải sửa lại cú pháp tạo ở trên, nhưng trong MySQL không hỗ trợ việc sửa procedure đã có nên chúng ta chỉ có cách xóa đi và tạo mới lại, nếu không xóa mà tạo mới lại nó sẽ báo lỗi vì bị trùng tên. Cú pháp như sau:

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

Cú pháp trên sẽ xóa procedure GetShops() trước sau đó tạo mới lại procedure GetShops() với một câu SQL lấy ra dữ liệu các shop trong bảng shops.

Và một lưu ý nữa là khi dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vu 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 ngay. 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 `GetShops`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetShops`()
BEGIN
SELECT * FROM shops;
END$$
DELIMITER ;

DEFINER=`root`@`localhost` chính là người tạo ra procedure GetShops(): nếu không có dòng này thì sẽ là user mặc định khi đăng nhập vào phpmyadmin.

Vậy là chúng ta đã tạo xong một procedure, vậy sử dụng nó như thế nào? Đơn giản để gọi tới Store nào thì ta chỉ cần dùng cú pháp như sau: CALL storeName();

CALL GetShops();

Trong code PHP thay vì chúng ta viết lệnh Select thì chỉ cần gọi câu lệnh trên là được, kết quả chạy phpmyadmin như sau:

Bài này kết thúc tại đây, bài tiếp theo chúng ta sẽ học cách khai báo tham số truyền vào cho Procedure và cách truyền biến vào khi gọi hàm.

[thongbao]

1. Nếu có thắc mắc gì các bạn để lại comment bên dưới mình sẽ trả lời sớm nhất có thể.
2. Cảm ơn các bạn đã đọc.

[/thongbao]