Các thủ tục được lưu trữ trong MySQL nhóm nhiều tác vụ thành một và lưu tác vụ trên máy chủ để sử dụng trong tương lai
Các thủ tục được lưu trữ đơn giản hóa việc quản lý cơ sở dữ liệu và giảm lưu lượng mạng. Ví dụ: đưa ra truy vấn tới máy chủ MySQL xử lý truy vấn và trả về kết quả. Sử dụng thủ tục lưu trữ lưu các truy vấn trên máy chủ để chúng có thể được thực thi sau này
Trong hướng dẫn này, bạn sẽ học cách tạo, liệt kê, thay đổi và loại bỏ thủ tục lưu sẵn
điều kiện tiên quyết
- Đã cài đặt MySQL Server và MySQL Workbench
- Tài khoản người dùng MySQL có quyền root
Các thủ tục được lưu trữ trong MySQL là gì?
Các thủ tục được lưu trữ của MySQL là các câu lệnh SQL được biên dịch sẵn được lưu trữ trong cơ sở dữ liệu. Chúng là các chương trình con chứa tên, danh sách tham số và câu lệnh SQL
Tất cả các hệ thống cơ sở dữ liệu quan hệ đều hỗ trợ các thủ tục được lưu trữ và không yêu cầu bất kỳ gói môi trường thời gian chạy bổ sung nào
Làm cách nào để sử dụng các thủ tục được lưu trữ?
Để gọi các thủ tục được lưu trữ, bạn có thể sử dụng câu lệnh
CALL procedure_name;
8 hoặc các thủ tục được lưu trữ khác. Lần đầu tiên một thủ tục lưu sẵn được gọi, MySQL sẽ tra cứu nó trong danh mục cơ sở dữ liệu, biên dịch mã, đặt nó vào bộ nhớ đệm và thực thi nóCác lần chạy tiếp theo trong cùng một phiên sẽ thực thi các thủ tục đã lưu trữ từ bộ nhớ cache, khiến chúng cực kỳ hữu ích cho các tác vụ lặp đi lặp lại
Các thủ tục được lưu trữ sử dụng các tham số để truyền giá trị và tùy chỉnh kết quả. Các tham số được sử dụng để chỉ định các cột trong bảng mà truy vấn sẽ thực hiện và trả về kết quả
Các thủ tục được lưu trữ cũng có thể bao gồm các câu lệnh luồng điều khiển
CALL procedure_name;
9, CALL procedure_name;
0 và CALL procedure_name;
1 thực hiện mã theo thủ tụcTạo thủ tục lưu trữ
Tạo một thủ tục được lưu trữ theo hai cách
1. Sử dụng Vỏ MySQL
Sử dụng cú pháp sau để tạo thủ tục lưu sẵn trong MySQL
DELIMITER //
CREATE PROCEDURE procedure_name [ IN | OUT | INOUT parameter_name parameter_datatype [length], … ]
BEGIN
SQL statements
END //
DELIMITER ;
Theo mặc định, cú pháp được liên kết với cơ sở dữ liệu đang sử dụng, nhưng bạn cũng có thể sử dụng cú pháp cho cơ sở dữ liệu khác bằng cách chỉ định tên cơ sở dữ liệu theo cách sau.
CALL procedure_name;
0Ở đây, đối số
CALL procedure_name;
1 đầu tiên đặt dấu phân cách mặc định thành CALL procedure_name;
2, trong khi đối số CALL procedure_name;
1 cuối cùng đặt nó trở lại dấu chấm phẩy CALL procedure_name;
4. Để sử dụng nhiều câu lệnh, hãy chỉ định các dấu phân cách khác nhau như CALL procedure_name;
5Tên thủ tục xuất hiện sau đối số
CALL procedure_name;
6. Sau tên thủ tục, sử dụng dấu ngoặc đơn để chỉ định các tham số sẽ sử dụng trong thủ tục, tên của tham số, loại dữ liệu và độ dài dữ liệu. Tách từng tham số bằng dấu phẩyCác chế độ tham số là
7 – Sử dụng để truyền tham số làm đầu vào. Khi nó được xác định, truy vấn sẽ chuyển một đối số cho thủ tục được lưu trữ. Giá trị của tham số luôn được bảo vệCALL procedure_name;
8 – Sử dụng để truyền tham số làm đầu ra. Bạn có thể thay đổi giá trị trong thủ tục được lưu trữ và giá trị mới được chuyển trở lại chương trình gọiCALL procedure_name;
9 – Sự kết hợp của các tham sốCALL procedure_name;
7 vàCALL procedure_name;
8. Chương trình gọi chuyển đối số và thủ tục có thể sửa đổi tham sốCALL procedure_name;
9, chuyển giá trị mới trở lại chương trìnhCALL procedure_name;
Ví dụ
Thực thi thủ tục được lưu trữ bằng cách gọi nó
CALL procedure_name;
Truy vấn trả về kết quả cho thủ tục được lưu trữ
2. Sử dụng bàn làm việc của MySQL
Một cách khác để tạo thủ tục lưu sẵn là sử dụng MySQL Workbench Wizard. Trình hướng dẫn trực quan và đơn giản hóa quy trình vì bạn không phải đặt dấu phân cách hoặc lo lắng về định dạng
Thực hiện theo các bước sau
Bước 1. Bấm chuột phải vào Thủ tục được lưu trữ trong cửa sổ Điều hướng của MySQL Workbench và chọn Tạo thủ tục được lưu trữ… để bắt đầu trình hướng dẫn
Bước 2. Chỉ định tên thủ tục và nhập mã trong khối BEGIN … END
Bước 3. Xem lại mã và nhấp vào Áp dụng
Bước 4. Xác nhận thực hiện bằng cách nhấp vào Áp dụng và tạo quy trình bằng cách nhấp vào Kết thúc
Bước 5. Thực hiện thủ tục để xem nếu nó hoạt động. Tạo một tab SQL mới để thực hiện các truy vấn
Bước 6.
CALL procedure_name;
8 thủ tục trong tab SQL và nhấp vào Thực thiNếu không có lỗi trả về, MySQL thực thi thủ tục lưu sẵn và hiển thị kết quả
Liệt kê thủ tục lưu trữ
Có ba cách để xem danh sách tất cả các thủ tục được lưu trữ
1. Sử dụng Vỏ MySQL
Để có danh sách tất cả các thủ tục được lưu trữ mà bạn có quyền truy cập, bao gồm các đặc điểm của chúng, hãy sử dụng cú pháp sau
CALL procedure_name;
5Câu lệnh
CALL procedure_name;
54 trả về một đầu ra dài. Câu lệnh hiển thị tên và đặc điểm của các thủ tục được lưu trữ mà bạn có quyền truy cập trên máy chủCuộn qua đầu ra để tìm các quy trình hiện có trên máy chủ
Đối số
CALL procedure_name;
55 tìm các thủ tục được lưu trữ có chứa một từ cụ thể trong tên của chúng. Sử dụng CALL procedure_name;
56 để thay thế bất kỳ số lượng ký tự nào, kể cả số khôngVí dụ
Đối số
CALL procedure_name;
57 cho phép bạn chỉ liệt kê các thủ tục được lưu trữ trong một cơ sở dữ liệu cụ thểVí dụ
Trong ví dụ này, câu lệnh chỉ trả về các thủ tục được lưu trữ cho cơ sở dữ liệu 'customer_list'
2. Sử dụng từ điển dữ liệu
Cơ sở dữ liệu information_schema chứa một bảng được gọi là thường trình, có thông tin về các thủ tục và chức năng được lưu trữ liên quan đến tất cả các cơ sở dữ liệu trên máy chủ MySQL hiện tại
Sử dụng cú pháp sau để xem tất cả các thủ tục được lưu trữ cho cơ sở dữ liệu
CALL procedure_name;
03. Sử dụng bàn làm việc của MySQL
Đối với cách tiếp cận GUI để xem các thủ tục được lưu trữ, hãy sử dụng MySQL Workbench. Thực hiện theo các bước sau để xem các thủ tục được lưu trữ
Bước 1. Bấm đúp vào cơ sở dữ liệu bạn muốn sử dụng trong phần Bộ điều hướng
Bước 2. Mở rộng mục thả xuống Thủ tục được lưu trữ
Mục này hiển thị tất cả các thủ tục được lưu trữ cho cơ sở dữ liệu hiện tại
Thay đổi thủ tục lưu trữ
Để thay đổi một thủ tục được lưu trữ có nghĩa là thay đổi các đặc điểm của một thủ tục. Không có câu lệnh nào trong MySQL để sửa đổi các tham số hoặc phần thân của thủ tục được lưu trữ. Để thay đổi tham số hoặc nội dung, bỏ thủ tục đã lưu trữ và tạo một thủ tục mới
Thay đổi một thủ tục được lưu trữ theo hai cách
1. Sử dụng Vỏ MySQL
Thay đổi một đặc điểm thủ tục bằng cách sử dụng câu lệnh
CALL procedure_name;
58. Ví dụ: chúng ta có thể thêm nhận xét vào thủ tục mà chúng ta đã tạo trước đó. Cú pháp làCALL procedure_name;
22. Sử dụng bàn làm việc của MySQL
MySQL Workbench GUI cho phép người dùng thay đổi thủ tục được lưu trữ nơi người dùng có thể thêm tham số hoặc thay đổi mã. MySQL Workbench loại bỏ thủ tục được lưu sẵn hiện có và tạo một thủ tục mới sau khi thực hiện các thay đổi
Thực hiện theo các bước sau
Bước 1. Trong phần Bộ điều hướng, nhấp chuột phải vào thủ tục được lưu trữ mà bạn muốn sửa đổi. Chọn mục Thủ tục thay thế được lưu trữ…
Bước 2. Khi tab mở ra, hãy thực hiện các thay đổi mong muốn đối với quy trình được lưu sẵn hiện có và nhấp vào Áp dụng
Bước 3. Một cửa sổ xem xét SQL Script xuất hiện hiển thị quá trình - loại bỏ thủ tục được lưu sẵn hiện có và tạo một thủ tục mới có chứa các thay đổi
Nhấp vào Áp dụng rồi nhấp vào Kết thúc trong cửa sổ tiếp theo để thực thi tập lệnh
Thủ tục lưu trữ thả
Bỏ [xóa] một thủ tục
1. Sử dụng Vỏ MySQL
Xóa thủ tục đã lưu trữ khỏi máy chủ bằng cách sử dụng câu lệnh
CALL procedure_name;
59Cú pháp cơ bản là
CALL procedure_name;
4Tham số
CALL procedure_name;
00 chỉ loại bỏ thủ tục được lưu trữ nếu nó tồn tại trên máy chủ. Nhập tên thủ tục lưu trữ thay cho cú pháp CALL procedure_name;
01Ví dụ
Vì không có quy trình nào có tên 'kiểm tra' trên máy chủ, đầu ra cho biết 0 hàng bị ảnh hưởng và quy trình đã chỉ định không tồn tại
Bỏ một thủ tục không tồn tại mà không có tham số
CALL procedure_name;
00 sẽ trả về lỗi2. Sử dụng bàn làm việc của MySQL
Để loại bỏ một thủ tục đã lưu trữ với MySQL Workbench, hãy làm theo các bước sau
Bước 1. Mở rộng mục Thủ tục được lưu trữ trong phần Bộ điều hướng. Nhấp chuột phải vào thủ tục được lưu trữ mà bạn muốn xóa và chọn Drop Stored Procedure… trong menu ngữ cảnh
Bước 2. Trong cửa sổ xác nhận, nhấp vào Drop Now để xóa thủ tục đã lưu trữ
Hành động này sẽ xóa vĩnh viễn thủ tục
Ưu điểm và nhược điểm của thủ tục lưu trữ MySQL
Các thủ tục được lưu trữ có một số ưu điểm và nhược điểm khi chúng phù hợp với các nhu cầu cụ thể. Dưới đây là một số ưu điểm và nhược điểm
Ưu điểm của việc sử dụng thủ tục lưu trữ
Ưu điểm của thủ tục lưu sẵn là
Giảm lưu lượng mạng
Các thủ tục được lưu trữ giúp giảm lưu lượng mạng giữa các ứng dụng và Máy chủ MySQL bằng cách giữ tất cả logic lập trình trên máy chủ. Thay vì gửi nhiều kết quả truy vấn trên mạng, các ứng dụng chỉ gửi tên quy trình và đầu vào tham số
Cải thiện an ninh
Quản trị viên cơ sở dữ liệu cấp cho ứng dụng các đặc quyền để chỉ gọi và truy cập các thủ tục được lưu trữ cụ thể mà không cấp cho chúng quyền truy cập trực tiếp vào các bảng. Các thủ tục được lưu trữ giúp ngăn chặn các cuộc tấn công chèn tập lệnh vì các tham số đầu vào được coi là giá trị chứ không phải mã thực thi
Logic kinh doanh tập trung
Các thủ tục được lưu trữ gói gọn logic nghiệp vụ có thể tái sử dụng bởi nhiều ứng dụng. Điều đó giúp giảm trùng lặp cùng một logic trong nhiều ứng dụng khác nhau và làm cho cơ sở dữ liệu nhất quán hơn
Nhược điểm của việc sử dụng các thủ tục được lưu trữ
Nhược điểm của thủ tục lưu sẵn là
Sử dụng tài nguyên
Việc sử dụng nhiều thủ tục được lưu trữ và các hoạt động logic làm cho việc sử dụng bộ nhớ và CPU tăng lên đáng kể cho mọi kết nối
Không có tính di động
Không dễ để chuyển các thủ tục được lưu trữ được viết bằng một ngôn ngữ cụ thể từ cài đặt này sang cài đặt khác. Dựa vào một thủ tục được lưu trữ cũng liên kết người dùng với một cơ sở dữ liệu cụ thể
Khắc phục sự cố và Kiểm tra
MySQL không cung cấp các tiện ích để kiểm tra và gỡ lỗi các thủ tục được lưu trữ, vì vậy có thể khó gỡ lỗi chúng. Phát triển và duy trì các thủ tục được lưu trữ đòi hỏi kiến thức sâu rộng. Đây là một thách thức đối với các nhà phát triển mới và dẫn đến tăng thêm chi phí bảo trì
Sự kết luận
Sau khi đọc bài viết này, bạn biết thủ tục lưu trữ là gì và khi nào nên sử dụng chúng. Bạn cũng biết cách tạo, sửa đổi, xem tất cả các thủ tục được lưu sẵn có sẵn và xóa những thủ tục bạn không cần nữa