Hướng dẫn select procedure mysql - chọn thủ tục mysql

I. MySQL: Stored Procedure là gì?

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. đượ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ố. 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.
• Stored procedure 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 procedureName()
BEGIN
   SELECT *  FROM products;
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. 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 procedureName() dùng để khai báo tạo một Procedure mới, trong đó procedureName chính là tên thủ tục còn hai từ đầu là từ khóa. dùng để khai báo tạo một Procedure mới, trong đó procedureName 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 procedureName

Cách gọi procedure

	CALL procedureName();

** 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:Drop để xóa đi Procedure đó và tạo lại:

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

** Phân quyền cho procedure **

Khi bạn dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vi 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 . 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 `procedureName`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE procedureName()
BEGIN
   SELECT *  FROM products;
END; $$
DELIMITER

DEFINER=

	CALL procedureName();

5@

	CALL procedureName();

6 chính là tên người đã tạo.

III. Biến (variable) trong MYSQL Stored Procedure

Khai báo biến

DECLARE variable_name datatype(size) DEFAULT default_value

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.: là từ khóa tạo biến variable_name: là tên biến datatype(size): là kiểu dữ liệu của biến và kích thước của nó DEFAULT default_value: là gán giá trị mặc định cho biến

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 còn hai từ đầu là từ khóa.

SET variable_name = 'value';

**BEGIN và END; $$ ** dùng để khai báo bắt đầu của Procedure và kết thúc Procedure

DECLARE a INT(11) DEFAULT 0

SET a = 10

**DELIMITER ; ** đóng lại ô lưu trữ

DECLARE total INT DEFAULT 0

SELECT COUNT(*) INTO total FROM products

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 procedureName

Cách gọi procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS total $$
CREATE PROCEDURE total()
BEGIN
    DECLARE a INT (11) DEFAULT 0;
    DECLARE b INT (11) DEFAULT 0;
    DECLARE result INT (11) DEFAULT 0;

    SET a = 1;
    SET b = 2;
    SET result = a + b;

    SELECT result;

END; $$
DELIMITER;

** 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.IN, tham số OUT và tham số INOUT

• Lệnh Drop để xóa đi Procedure đó và tạo lại:

• ** Phân quyền cho procedure **

• Khi bạn dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vi của User đó.có thể gán giá trị trước và có thể bị thay đổi nếu trong Procedure có tác động.

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 . 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:

DEFINER=

	CALL procedureName();

5@

	CALL procedureName();

6 chính là tên người đã tạo.

III. Biến (variable) trong MYSQL Stored Procedure

	CALL procedureName();

0

Khai báo biến

DECLARE: là từ khóa tạo biến variable_name: là tên biến datatype(size): là kiểu dữ liệu của biến và kích thước của nó DEFAULT default_value: là gán giá trị mặc định cho biến

• Gán giá trị cho biến

• Ví dụ: Định nghĩa biến age và gán giá trị 20 cho nó.

• Gán giá trị thông qua lệnh SELECT

VD:

	CALL procedureName();

1

Câu lệnh trên nó sẽ thực hiện câu truy vấn SQL đếm tổng số record và sau đó gán vào biến total.

Nếu như bạn định nghĩa một biến giữa BEGIN và END thì đó ta gọi là biến cục bộ của Procedure. Bạn có thể định nghĩa nhiều biến trong một Procedure.

	CALL procedureName();

2

IV Truyền tham số vào Mysql Stored Procedure

	CALL procedureName();

3

Trong MYSQL thì sẽ tồn tại ba loại Tham số đó là tham số IN, tham số OUT và tham số INOUT

	CALL procedureName();

4

IN: Đây là chế độ mặc định (nghĩa là nếu bạn không định nghĩa loại nào thì nó sẽ hiểu là IN).

OUT: Chế độ này nếu như trong Procedure có tác động thay đổi thì nó sẽ thay đổi theo. Nhưng có điều đặc biệt là dù trước khi truyền vào mà bạn gán giá trị cho biến đó thì vẫn sẽ không nhận được vì mặc định nó luôn hiểu giá trị truyền vào là NULL.

INOUT: Đây là sự kết hợp giữa IN và OUT. Nghĩa là có thể gán giá trị trước và có thể bị thay đổi nếu trong Procedure có tác động.

Table Products demo: