TÌM HIỂU VỀ STORED PROCEDURE TRONG MYSQL

Bài đăng này đã không được cập nhật trong 8 năm

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:

SP dùng để liệt kê thông tin (liệt kê danh sách database ,...)
SP dùng để trình bày thông tin (trình bày thông tin table, ...)
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 :

Resolution: xử lý truy vấn, đọc và kiểm tra lỗi chính tả.
Optimization : tối ưu hóa truy vấn.
Compilation

Một số quy tăc khi tạo SP:

Mỗi thủ tục chỉ nên làm 1 nhiệm vụ
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.
Một thủ tục không thể tạo/xóa rồi lại tham chiếu đến thủ tục đấy.
Các thủ tục có thể tham chiếu đến các bảng tạm thời.
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:

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
Transact-SQL Tạo procedure bằng Editor Query Cú pháp đơn giản:

CREATE PROCEDURE <owner>.<procedure name>
     <Param> <datatype>
AS
     <Body>
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 .

MySql