Hướng dẫn stored procedure trong mysql

Hướng dẫn stored procedure trong mysql

Đã đăng vào thg 5 1, 2016 6:31 SA 4 phút đọc

Stored Procedure là gì?

  • Tương tự như khái niệm function trong các ngôn ngữ lập trình. Stored Procedure được tạo ra nhằm thực hiện các lệnh của mysql theo một nhóm việc cụ thể thay vì thực hiện từng thao tác (insert,update,delete).
  • Trong mysql thì Stored Procedure chỉ được hỗ trợ từ phiên bản 5 trở đi.

Ưu điểm của Stored Procedure

  • Stored Procedure làm tăng hiệu xuất sử lý giữ liệu
  • Làm giảm thời gian giao tiếp giữa ứng dụng với hệ quản trị cơ sở giữ liệu. thay vì gửi từng câu truy vấn thì nay sẽ chỉ gửi một Stored Procedure.
  • module hóa ứng dụng: do Stored Procedure có thể lưu lại và sử dụng lại nhiều lần. điều này sẽ làm cho việc maintenance trở lên dễ dàng hơn.
  • nâng cao tính bảo mật dữ liệu: việc thao tác với database thông qua stored procedure mà không cho thao tác trực tiếp với database sẽ làm giảm các cuộc tấn công thông qua sql injection

Nhược điểm của Stored Procedure

  • 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, điều này không tốt chút nào.

  • Nếu sử dụng thủ tục thì sẽ rất khó phát triển trong ứng dụng, gây khó khăn ở mức logic business.

  • MYSQL thì không có tool hỗ trợ Debug Store.

  • Để phát triển ứng dụng thì bạn phải đòi hỏi có một kỹ năng nhất định mới có thể maintenance được.

Các kiểu Stored Procedure

Chủ yếu có 2 loại:

  • System stored procedures : Thủ tục mà những người sử dụng chỉ có quyền thực hiện, không được phép thay đổi.Các tác vụ quản trị bao gồm : liệt kê, thêm, cập nhập, xóa.
  • Kí hiệu : sp_..., xp_...
  • Để thực thi 1 SP, ta sử dụng lệnh EXEC. VD: EXEC sp_databases (liệt kê danh sách các database có trong SQL server)
  • Được chia thành các nhóm sau:
  1. SP dùng để liệt kê thông tin (liệt kê danh sách database ,...)
  2. SP dùng để trình bày thông tin (trình bày thông tin table, ...)
  3. SP dùng để thêm, xóa, cập nhập thông tin (đổi mật khẩu, ....)
  • User - Defined Stored Procedures: Thủ tục do người dùng tạo và thực hiện.
  • Là các SP do người dùng tạo.
  • Thủ tục khi chạy sẽ theo 3 bước :
  1. Resolution: xử lý truy vấn, đọc và kiểm tra lỗi chính tả.
  2. Optimization : tối ưu hóa truy vấn.
  3. Compilation
  • Một số quy tăc khi tạo SP:
  1. Mỗi thủ tục chỉ nên làm 1 nhiệm vụ
  2. Các lệnh CREATE TABLE và CREATE INDEX có thể được tạo và tham chiếu ngay trong cùng 1 thủ tục.
  3. Một thủ tục không thể tạo/xóa rồi lại tham chiếu đến thủ tục đấy.
  4. Các thủ tục có thể tham chiếu đến các bảng tạm thời.
  5. Có thể tham chiếu đến các đối tượng từ các CSDL khác và server từ xa. ....

Tạo Stored Procedure

Có thể sử dụng 1 trong các cách sau:

  1. Sử dụng SQL Server Management Studio (SSMS) Tạo bằng giao diện SSMS sẽ phát sinh script tạo Store Procedure sẵn, ta chỉ cần thêm nội dung vào trong thân hàm
  2. Transact-SQL Tạo procedure bằng Editor Query Cú pháp đơn giản:
CREATE PROCEDURE .
      
AS
     
Ví dụ:
CREATE PROCEDURE Users_GetUserInfo
    @login nvarchar(30)=null
AS
    SELECT * from users
    WHERE ISNULL(@login,login)=login

Để thực thi precedure bằng script sử dụng lệnh EXECUTE:

EXECUTE procedure_name

hoặc

EXEC procedure_name

Kết luận

Procedure ít được sử dụng trong các website vì nó còn có nhiều nhược điểm. Vì vậy khi phát triển ứng dụng nên xem xét kĩ trước khi sử dụng .

All rights reserved

Hướng dẫn stored procedure trong 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.

Stored Procedured 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.

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

  • **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

Hướng dẫn stored procedure trong mysql

Cách gọi procedure

	CALL procedureName();

Hướng dẫn stored procedure trong mysql

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

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=root@localhost 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:

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
SET variable_name = 'value';

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

DECLARE a INT(11) DEFAULT 0

SET a = 10

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

DECLARE total INT DEFAULT 0

SELECT COUNT(*) INTO total FROM products

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.

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;

Hướng dẫn stored procedure trong mysql

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

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

  • 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ướccó thể bị thay đổi nếu trong Procedure có tác động.

Table Products demo:

Hướng dẫn stored procedure trong mysql

DELIMITER $$
DROP PROCEDURE IF EXISTS getById $$
CREATE PROCEDURE getProductId(
				IN id INT(11),
                IN title VARCHAR(255)
                )
BEGIN
    /*Code*/
END; $$
DELIMITER;

**Tham số loại IN **

DELIMITER $$
DROP PROCEDURE IF EXISTS getProId $$
CREATE PROCEDURE getProId(IN idpro INT(11))
BEGIN
    SELECT * FROM products WHERE id = idpro;
END; $$
DELIMITER;

Kết quả:

Hướng dẫn stored procedure trong mysql

Tham số loại OUT trong Mysql Stored Procedure

  • Khi truyền tham số dạng OUT mục đích là lấy dữ liệu trong Proedure và sử dụng ở bên ngoài.

  • Khi truyền tham số vào dạng OUT phải có chữ @ đằng trước biến

  • Hoạt động giống tham chiếu nên biến truyền vào dạng OUT không cần định nghĩa trước, chính vì vậy khởi đầu nó có giá trị NULL

VD:

DELIMITER $$
DROP PROCEDURE IF EXISTS Title $$
CREATE PROCEDURE Title(OUT title VARCHAR(255))
BEGIN
    SET title = 'Title 2';
END; $$
DELIMITER;

Hướng dẫn stored procedure trong mysql

Tham số dạng INOUT

INOUT là sự kết hợp giữa IN và OUT, nghĩa là:

DELIMITER $$

DROP PROCEDURE IF EXISTS counter $$

CREATE PROCEDURE counter(INOUT number INT(11))
BEGIN
    SET number = number + 1;
END; $$
DELIMITER;

Sử dụng:

SET @counter = 1;
CALL counter(@counter);
SELECT @counter;
V: Câu lệnh if else trong MYSQL
IF if_expression THEN
    commands
   ELSEIF elseif_expression THEN
    commands
   ELSE
    commands
END IF;

Tham khảo IF ELSE Mệnh đề if cho phép bạn tạo luồng xử lý rẻ nhánh, nếu đúng thì thực thi và ngược lại mệnh đề sai thì nó sẽ không thực thi.

Kết Luận: Bạn có thể thấy cách một Stored Procedure hoạt động không khác gì một hàm trong lập trình. Như vậy bạn đã học được cách tạo một Stored Procedure trong MySQL.

Link Tham khảo: mysql

mysqltutorial