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. Show 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ượngStored 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ăngStored 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ậtNế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 MySQLCấu trúc câu lệnh tạo Stored ProcedureCREATE PROCEDURE [procedure_name] ([param1, param2,…]) BEGIN [sql_statements] END Trong đó:
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
Tạo dữ liệu mẫuTạo database & các bảngCREATE 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ệmTạo dữ liệu mẫuUSE 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'); Tạo Stored Procedure để đếm tổng số sản phẩm trong bảng san_phamUSE 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 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 Kiểm tra kết quả Stored Procedure CALL count_products(@a); SELECT @a; Kết quả như sau: Tạo Stored Procedure tìm kiếm sản phẩm theo id được truyền vàoUSE 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_productsCALL find_product(8); Kết quả như sau: Bài chung series
|