Các thủ tục được lưu trữ trong MySQL là các đối tượng chứa một hoặc nhiều câu lệnh SQL để sử dụng lại. Chúng thường chứa logic kinh doanh. Robert Sheldon trình bày cách tạo và gọi các thủ tục được lưu trữ Show
Bài viết này là một phần trong loạt bài tiếp theo của Robert Sheldon về Học MySQL. Để xem tất cả 11 mục trong sê-ri, hãy nhấp vào đây Giống như hầu hết các hệ thống quản lý cơ sở dữ liệu quan hệ, MySQL hỗ trợ việc sử dụng các thủ tục được lưu trữ có thể được gọi theo yêu cầu bởi các ứng dụng dựa trên dữ liệu. Mỗi thủ tục được lưu trữ là một đối tượng cơ sở dữ liệu được đặt tên chứa một thường trình được tạo thành từ một hoặc nhiều câu lệnh SQL. Khi một ứng dụng gọi thủ tục lưu sẵn, MySQL sẽ thực thi các câu lệnh đó và trả về kết quả cho ứng dụng. Một thói quen của thủ tục có thể bao gồm nhiều loại câu lệnh, bao gồm ngôn ngữ định nghĩa dữ liệu (DDL) và ngôn ngữ thao tác dữ liệu (DML). Các thủ tục được lưu trữ của MySQL cũng hỗ trợ việc sử dụng các tham số đầu vào và đầu ra, làm cho chúng trở thành một công cụ rất linh hoạt để đóng gói logic câu lệnh Các thủ tục được lưu trữ cho phép sử dụng lại mã SQL thường xuyên khi cần, giúp đơn giản hóa việc phát triển ứng dụng và giảm các lỗi câu lệnh. Các nhà phát triển không phải viết các truy vấn phức tạp cho từng yêu cầu của ứng dụng và các nhóm QA không cần dành nhiều thời gian để xác minh các truy vấn khi kiểm tra ứng dụng Khả năng sử dụng lại mã cũng làm giảm lưu lượng mạng vì thủ tục được lưu trữ có thể được gọi bằng một câu lệnh Trong bài viết này, tôi trình bày cách tạo và cập nhật thủ tục lưu sẵn, cũng như gọi chúng bằng câu lệnh Chuẩn bị môi trường MySQL của bạnCác ví dụ trong bài viết này dựa trên cơ sở dữ liệu 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 THẢI CƠ SỞ DỮ LIỆU NẾU TỒN TẠI du lịch; TẠO CƠ SỞ DỮ LIỆU du lịch; SỬ DỤNG du lịch; TẠO BẢNG nhà sản xuất ( manufacturer_id INT CHƯA KÝ KHÔNG NULL AUTO_INCREMENT, nhà sản xuất VARCHAR(50) NOT NULL, create_date DẤU THỜI GIAN NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update DẤU THỜI GIAN NOT NULL MẶC ĐỊNH CURRENT_TIMESTAMP BẬT CẬP NHẬT CURRENT_TIMESTAMP, CHÍNH CHÍNH KEY (manufacturer_id) ) ENGINE=InnoDB AUTO_INCREMENT=1001; TẠO BẢNG máy bay ( plane_id INT KHÔNG ĐƯỢC KÝ KHÔNG NULL AUTO_INCREMENT, máy bay VARCHAR(50) NOT NULL, manufacturer_id INT CHƯA KÝ KHÔNG NULL, engine_type VARCHAR(50) NOT NULL, engine_count TINYINT NOT NULL, max_weight VỪA KHÔNG ĐƯỢC KÝ KHÔNG NULL, sải cánh THẬP PHÂN(5,2) NOT NULL, plane_length DECIMAL(5,2) NOT NULL, parking_area INT ĐƯỢC TẠO LUÔN LUÔN AS ((sải cánh * plane_length)) STORED, icao_code CHAR(4) NOT NULL, create_date DẤU THỜI GIAN NOT NULL DEFAULT CURRENT_TIMESTAMP, last_update DẤU THỜI GIAN NOT NULL MẶC ĐỊNH CURRENT_TIMESTAMP BẬT CẬP NHẬT CURRENT_TIMESTAMP, CHÍNH CHÍNH KEY (plane_id), HÀNG CHẾ fk_manufacturer_id FOREIGN KEY (manufacturer_id) TÀI LIỆU THAM KHẢO nhà sản xuất (manufacturer_id) ) ENGINE=InnoDB AUTO_INCREMENT=101; Bảng 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CHÈN VÀO nhà sản xuất (nhà sản xuất) GIÁ TRỊ ('Airbus'), ('Beechcraft'), ('Piper'); CHÈN VÀO máy bay (máy bay, manufacturer_id, engine_type, engine_count, trọng lượng tối đa, sải cánh, plane_length, icao_code) GIÁ TRỊ ('A380-800', 1001< . 65, 'jet', 4, 1267658, 261.65, 238. 62, 'A388'), ('A319neo Sharklet', 1001, 'jet', 2, 166449, 117.45, 111. 02, 'A319'), ('ACJ320neo (Phiên bản máy bay phản lực dành cho doanh nghiệp)', 1001, 'jet', 2, 174165, 117.45, 123. 27, 'A320'), ('A300-200 (A300-C4-200, F4-200)', . 08 1001, 'jet', 2, 363760, 147.08, 175. 50, 'A30B'), ('Beech 390 Premier I, IA, II (Raytheon Premier I)',< . 50 1002, 'jet', 2, 12500, 44.50, 46. 00, 'PRM1'), ('Beechjet 400 (từ/giống như MU-300-10 Diamond II)' . 50 1002, 'jet', 2, 15780, 43.50, 48. 42, 'BE40'), ('1900D', 1002, 'Turboprop', 2,17120, 57.75, 57. 67, 'B190'), ('PA-24-400 Comanche', 1003, 'piston', 1, 3600, 36.00, 24. 79, 'PA24'), ('PA-46-600TP Malibu Meridian, M600', 1003, 'Turboprop', 1, 6000, 43.17, 29. 60, 'P46T'), ('J-3 Cub', 1003 . 00, 'piston', 1, 1220, 38.00, 22. 42, 'J3'); Như với các câu lệnh Tạo một thủ tục được lưu trữ trong MySQLĐể xây dựng thủ tục lưu trữ trong MySQL, bạn phải sử dụng câu lệnh Khi xây dựng câu lệnh Hầu hết các thủ tục được lưu trữ đều sử dụng câu lệnh phức hợp ngay cả khi chúng chỉ bao gồm một câu lệnh SQL duy nhất. Ví dụ: quy trình trong câu lệnh 1 2 3 4 5 6 7 8 9 10 11 12 13 14 DÒNG KHÁC // TẠO THỦ TỤC get_plane_info() BẮT ĐẦU CHỌN a. manufacturer_id, m. nhà sản xuất, ĐẾM(*) AS plane_count, VÒNG(AVG(a.sải cánh), 2) AS avg_span, VÒNG(AVG(a.plane_length), 2) AS avg_length TỪ máy bay a BÊN TRONG JOIN manufacturers m BẬT a. manufacturer_id = m. nhà sản xuất_id NHÓM BỞI a. nhà sản xuất_id ĐẶT HÀNG BỞI m. nhà sản xuất; END//
DÒNG KHÁC ; Ví dụ tạo một thủ tục tên là Câu lệnh ghép được xác định bởi cú pháp Như bạn có thể thấy, tạo một thủ tục lưu sẵn đơn giản là một quá trình khá đơn giản. Ở mức tối thiểu, bạn phải cung cấp tên và nội dung quy trình. Tuy nhiên, chắc chắn bạn đã chú ý đến việc bao gồm hai câu lệnh Theo mặc định, MySQL sử dụng dấu chấm phẩy (;) làm dấu phân cách câu lệnh. Điều này giúp đảm bảo rằng máy khách gửi toàn bộ câu lệnh đến máy chủ mà không nhầm lẫn nó với các câu lệnh khác. Tuy nhiên, một câu lệnh ghép trong một thủ tục được lưu trữ có thể bao gồm một hoặc nhiều dấu phân cách, ngoài dấu phân cách cuối cùng của định nghĩa và tất cả các dấu phân cách này có thể gây nhầm lẫn khi chuyển câu lệnh Để giải quyết vấn đề này, MySQL hỗ trợ sử dụng câu lệnh Tôi cũng muốn chỉ ra rằng MySQL Workbench cung cấp một công cụ (ở dạng tab) để tạo và chỉnh sửa các thủ tục được lưu trữ. Công cụ này tương tự như công cụ được sử dụng để tạo và chỉnh sửa chế độ xem. Nó cung cấp một sơ khai để xây dựng một tuyên bố Hình 1. Thêm một thủ tục được lưu trữ thông qua GUI Workbench Để khởi chạy tab Thủ tục được lưu trữ, hãy chọn cơ sở dữ liệu đích trong Bộ điều hướng và sau đó nhấp vào nút tạo thủ tục được lưu trữ trên thanh công cụ Workbench. (Nút này bao gồm chú giải công cụ Tạo một thủ tục được lưu trữ mới trong sơ đồ đang hoạt động trong máy chủ được kết nối. ) Khi tab Thủ tục được lưu trữ xuất hiện, bạn có thể bắt đầu xây dựng câu lệnh của mình. Sau khi bạn hoàn thành, nhấp vào Áp dụng. MySQL sau đó sẽ thêm một vài thành phần câu lệnh cần thiết để tạo thủ tục. Xem lại tập lệnh cuối cùng, bấm Áp dụng một lần nữa rồi bấm Kết thúc. Thủ tục được lưu trữ sẽ được thêm vào cơ sở dữ liệu đích Tab Quy trình được lưu trữ có thể hữu ích để tạo và chỉnh sửa quy trình được lưu trữ, vì vậy tôi muốn chắc chắn rằng bạn biết nó có sẵn. Tuy nhiên, tôi thích sử dụng tab truy vấn hơn khi làm việc với thủ tục lưu trữ vì tôi nghĩ rằng nó dễ dàng hơn và tiết kiệm các bước, vì vậy đây là cách tiếp cận mà tôi áp dụng cho các ví dụ trong bài viết này Xác minh một thủ tục được lưu trữ mới được tạoSau khi bạn chạy câu lệnh Hình 2. Xem thủ tục được lưu trữ trong Bộ điều hướng Từ Bộ điều hướng, bạn có thể mở định nghĩa quy trình trong tab Quy trình được lưu trữ bằng cách nhấp vào biểu tượng cờ lê bên cạnh tên quy trình. Hình 3 hiển thị định nghĩa thủ tục khi bạn tạo nó, ngoại trừ một điểm khác biệt. Bây giờ nó bao gồm mệnh đề Hình 3. Xem định nghĩa quy trình trên tab Quy trình được lưu trữ Mệnh đề Khác với mệnh đề Một cách khác để xác minh xem thủ tục được lưu trữ của bạn đã được tạo hay chưa là truy vấn chế độ xem 1 2 CHỌN * TỪ lược đồ thông tin. quy trình WHERE routine_schema = 'du lịch'; Tuyên bố bao gồm một mệnh đề Bạn có thể giới hạn kết quả hơn nữa bằng cách chỉ định tên thủ tục trong mệnh đề 1 2 3 4 CHỌN routine_definition TỪ thông tin_lược đồ. quy trình WHERE routine_schema = 'travel' VÀ routine_name = 'get_plane_info'; Mặc dù câu lệnh chỉ trả về một giá trị duy nhất, nhưng nó vẫn có thể khó đọc, đặc biệt nếu đó là một câu lệnh ghép phức tạp. Để xem toàn bộ câu lệnh, hãy nhấp chuột phải vào giá trị trực tiếp trong kết quả và nhấp vào Mở Giá trị trong Trình xem. Chọn Văn bản nếu nó chưa được chọn. MySQL khởi chạy một cửa sổ riêng hiển thị giá trị, như trong Hình 4 hinh 4. Kiểm tra phần thân quy trình của thủ tục được lưu trữ trong Trình xem Tất nhiên, việc xác minh rằng quy trình được lưu trữ tồn tại không cho bạn biết liệu nó có hoạt động như mong đợi hay không. Vì lý do này, bạn cũng nên thực hiện quy trình và xem nó trả về loại kết quả nào (ngoài việc chạy nó qua một chu kỳ QA phù hợp). Đối với điều này, hãy sử dụng câu lệnh 1 GỌI get_plane_info; Khi bạn gọi thủ tục, MySQL sẽ chạy thủ tục được lưu trữ và trả về kết quả câu lệnh, được hiển thị trong Hình 5 Hình 5. Xem kết quả sau khi gọi thủ tục lưu sẵn Như mong đợi, câu lệnh Thêm một tham số đầu vào vào một thủ tục được lưu trữThủ tục lưu trữ Trong phần này, tôi trình bày cách thêm tham số đầu vào vào định nghĩa thủ tục. (Tôi sẽ đề cập đến các tham số đầu ra trong phần tiếp theo. ) Trước khi tôi đi sâu vào vấn đề đó, bạn nên lưu ý rằng bạn không thể đơn giản thay đổi định nghĩa thủ tục giống như bạn có thể thay đổi định nghĩa bảng hoặc dạng xem. Bạn có thể sửa đổi các đặc điểm của thủ tục, nhưng không có gì khác. Để thực hiện bất kỳ cập nhật quan trọng nào, bạn phải bỏ quy trình rồi tạo lại quy trình đó, kết hợp mọi yếu tố mới Để loại bỏ một thủ tục được lưu trữ, bạn có thể sử dụng câu lệnh 1 THẢI THỦ TỤC NẾU TỒN TẠI get_plane_info; Mệnh đề 1 2 CHỌN * TỪ lược đồ thông tin. quy trình WHERE routine_schema = 'du lịch'; Bây giờ, câu lệnh sẽ trả về một tập kết quả trống, trừ khi bạn đã tạo các hàm hoặc thủ tục được lưu trữ khác Sau khi bạn xóa thủ tục được lưu trữ
Câu lệnh 1 2 3 4 5 6 7 8 9 10 11 12 13 14 DÒNG KHÁC // TẠO THỦ TỤC get_plane_info( IN in_name VARCHAR(50)) COMMENT 'truy xuất thông tin tổng hợp về máy bay' BẮT ĐẦU CHỌN a. manufacturer_id, m. nhà sản xuất, ĐẾM(*) AS plane_count, VÒNG(AVG(a.sải cánh), 2) AS avg_span, VÒNG(AVG(a.plane_length), 2) AS avg_length TỪ máy bay a BÊN TRONG JOIN manufacturers m BẬT a. manufacturer_id = m. nhà sản xuất_id Ở ĐÂU m. nhà sản xuất = in_name; END// DÒNG KHÁC ; Định nghĩa tham số được đặt trong ngoặc đơn và bao gồm từ khóa Câu lệnh Khi gọi thủ tục lưu trữ nhận tham số đầu vào, bạn phải bao gồm giá trị tham số trong dấu ngoặc đơn. Nếu đó là một giá trị ký tự, bạn phải đặt nó trong dấu nháy đơn. Ví dụ: câu lệnh 1 GỌI get_plane_info ('piper'); Khi MySQL chạy quy trình của thủ tục, nó sẽ thay thế giá trị Hình 6. Gọi một thủ tục được lưu trữ với một tham số đầu vào Khi xác định thủ tục được lưu trữ của bạn, bạn có thể bao gồm nhiều tham số Thêm tham số đầu ra vào một thủ tục được lưu trữBây giờ hãy xem cách thêm nhiều tham số Để thêm các tham số, bạn sẽ cần bỏ quy trình một lần nữa và sau đó chạy câu lệnh 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 THẢI THỦ TỤC NẾU TỒN TẠI get_plane_info; DÒNG KHÁC // TẠO THỦ TỤC get_plane_info( IN in_name VARCHAR(50), OUT out_id INT CHƯA KÝ, OUT out_name VARCHAR(50), OUT plane_count SMALLINT KHÔNG ĐƯỢC KÝ, OUT avg_wingspan DECIMAL(5,2), OUT avg_length DECIMAL(5,2)) COMMENT 'truy xuất thông tin tổng hợp về máy bay' BẮT ĐẦU CHỌN a. manufacturer_id, m. nhà sản xuất, ĐẾM(*), VÒNG(AVG(a.sải cánh), 2), VÒNG(AVG(a.plane_length), 2) VÀO out_id, out_name, plane_count, avg_wingspan, avg_length TỪ máy bay a BÊN TRONG JOIN manufacturers m BẬT a. manufacturer_id = m. nhà sản xuất_id Ở ĐÂU m. nhà sản xuất = in_name; END// DÒNG KHÁC ; Đối với mỗi tham số đầu ra, bạn phải chỉ định từ khóa Khi bạn gọi một thủ tục được lưu trữ trả về các tham số đầu ra, bạn có thể nắm bắt các giá trị tham số đó bằng cách chuyển vào một biến do người dùng xác định để mỗi tham số đầu ra giữ giá trị của nó, như được hiển thị trong câu lệnh 1 2 GỌI get_plane_info ('beechcraft', @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length); Câu lệnh Cách chính xác mà bạn sẽ xử lý các tham số đầu ra trong ứng dụng của mình sẽ phụ thuộc vào ngôn ngữ lập trình mà bạn đang sử dụng. Trong thời gian chờ đợi, bạn có thể xác minh rằng các biến của mình chứa các giá trị dự kiến bằng cách chạy câu lệnh 1 SELECT @out_id, @out_name, @plane_count, @avg_wingspan, @avg_length; Hình 7 cho thấy kết quả trả về của câu lệnh Hình 7. Xem các giá trị tham số đầu ra của thói quen cho các mặt phẳng Beechcraft Hình này cho thấy kết quả khi bạn chỉ định Hình 8. Xem các giá trị thông số đầu ra của quy trình cho các máy bay Airbus Cả hai tham số Thay đổi một thủ tục được lưu trữ trong MySQLMySQL hỗ trợ câu lệnh 1 2 3 THAY ĐỔI THỦ TỤC get_plane_info ĐỌC SQL DỮ LIỆU SQL BẢO MẬT INVOKER; Đặc tính Sau khi bạn chạy câu lệnh Hình 9. Xem định nghĩa quy trình trên tab Quy trình được lưu trữ Lưu ý rằng câu lệnh Làm việc với thủ tục lưu sẵn trong MySQLCác thủ tục được lưu trữ có thể mang lại rất nhiều tính linh hoạt, đồng thời giúp hợp lý hóa việc phát triển ứng dụng. Tuy nhiên, một thủ tục chỉ có hiệu quả như thủ tục cơ bản của nó và các câu lệnh SQL mà nó chứa. Trong bài tập này, tôi đã chỉ cho bạn cách tạo một thủ tục lưu trữ có thủ tục chứa một câu lệnh
Robert Sheldon 07 Tháng mười 2022 Robert Sheldon 07 Tháng mười 2022Giới thiệu câu lệnh INSERT của MySQL 3 3 Lukas Vileikis 31 tháng một 2023 Lukas Vileikis 31 tháng một 2023Tối ưu hóa truy vấn trong MySQL. Tối ưu hóa số lần đọc 1 Làm thế nào để gọi một thủ tục trong MySQL?Chúng ta có thể sử dụng câu lệnh CALL để gọi thủ tục lưu sẵn. Câu lệnh này trả về các giá trị cho người gọi nó thông qua các tham số của nó (IN, OUT hoặc INOUT). Cú pháp sau đây được sử dụng để gọi thủ tục được lưu trữ trong MySQL. GỌI tên thủ tục ((các) tham số)
Cái gì được sử dụng để gọi thủ tục cơ sở dữ liệu?CallableStatement của API JDBC được sử dụng để gọi một thủ tục được lưu trữ. Câu lệnh Callable có thể có tham số đầu ra, tham số đầu vào hoặc cả hai.
Thủ tục trong cơ sở dữ liệu MySQL là gì?Một thủ tục là một chương trình con (như chương trình con) bằng ngôn ngữ kịch bản thông thường, được lưu trữ trong cơ sở dữ liệu . Trong trường hợp của MySQL, các thủ tục được viết bằng MySQL và được lưu trữ trong cơ sở dữ liệu/máy chủ MySQL. Một thủ tục MySQL có tên, danh sách tham số và (các) câu lệnh SQL.
Lệnh nào dùng để gọi một thủ tục trong SQL Server?Lệnh EXEC được sử dụng để thực thi một thủ tục được lưu trữ hoặc một chuỗi SQL được truyền cho nó. |