Các đối tượng được lưu trữ MySQL
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ả 10 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 trữ, 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 sơ khai để xây dựng câu lệnh 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 rồi 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 cho từng 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 minh họa 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
3 3 3 Các đối tượng được lưu trữ trong MySQL là gì?Các đối tượng được lưu trữ bao gồm các loại đối tượng này. . thủ tục lưu trữ. Một đối tượng được tạo bằng CREATE PROCEDURE và được gọi bằng cách sử dụng câu lệnh CALL. . chức năng lưu trữ. Một đối tượng được tạo bằng CREATE FUNCTION và được sử dụng giống như một hàm dựng sẵn. . Kích hoạt. Một đối tượng được tạo bằng CREATE TRIGGER được liên kết với một bảng Bạn có thể lưu trữ đối tượng trong cơ sở dữ liệu MySQL không?Lớp này có thể được sử dụng để lưu trữ và truy xuất các đối tượng trong bản ghi bảng MySQL . Nó tuần tự hóa và hủy tuần tự hóa các đối tượng để lưu trữ và truy xuất nó trong một bảng nhất định. Nó cũng có thể truy vấn, cập nhật và xóa một đối tượng được cung cấp mã định danh được sử dụng để lưu trữ nó trong bảng MySQL.
Làm thế nào các đối tượng được lưu trữ trong cơ sở dữ liệu?Bạn có thể lưu trữ các đối tượng trong bảng quan hệ dưới dạng đối tượng cột hoặc trong bảng đối tượng dưới dạng đối tượng hàng . Những đối tượng có ý nghĩa bên ngoài cơ sở dữ liệu quan hệ mà chúng cư trú, nên được tham chiếu dưới dạng đối tượng hàng trong bảng đối tượng. Mặt khác, chúng nên được lưu trữ dưới dạng đối tượng cột trong bảng quan hệ.
đối tượng trong thủ tục được lưu trữ là gì?Đối tượng StoredProcedure hiển thị các thuộc tính của một thủ tục lưu trữ hệ thống hoặc do người dùng Microsoft SQL Server xác định . |