Tìm hiểu về Stored-Procedure & Trigger trong SQL

1. Stored-Procedure

• Là các chương trình trong SQL Server.
• Đặc tính:
  • Cho phép truyền các tham số đầu vào và chấp nhận trả về các giá trị chứa trong các tham số hoặc trả về các trạng thái giá trị để gọi những thủ tục hoặc thực hiện các xử lý theo lô để biết việc thực hiện thành công hay thất bại, nếu thất bại thì có thể đưa ra nguyên nhân thấp bại.
  • Bao gồm cả các lệnh gọi các thủ tục thực thi khác, chứa các lệnh SQL của chương trình để thực hiện các xử lý trong database
• Ta có thể dùng Transact–SQL EXCUTE để thực thi các stored procedure. Stored procedure khác với các hàm xử lý là giá trị trả về của chúng không chứa trong tên và chúng không được sử dụng trực tiếp trong biểu thức.
• So với các chương trình cục bộ, Stored procedure co ưu điểm hơn là:
  • Động: Stored procedure cho phép điều chỉnh chương trình cho phù hợp: Chúng ta có chỉ tạo stored procedure một lần và lưu trữ trong database một lần, trong chương trình chúng ta có thể gọi nó với số lần bất kỳ. Stored procedure có thể được chỉ rõ do một người nào đó tạo ra và sự thay đổi của chúng hoàn toàn độc lập với source code của chương trình.
  • Nhanh hơn: Stored procedure có khả năng phân tích cú pháp và tối ưu hóa trong lần thực thi đầu tiên và một phiên bản dịch của chúng trong đó sẽ được lưu trong bộ nhớ để sử dụng cho lần sau, nghĩa là trong những lần thực hiện sau chúng không cần phải phân tích cú pháp và tối ưu lại, mà chúng sẽ sử dụng kết quả đã được biên dịch trong lần đầu tiên. Do đó stored procedure có khả năng thực thi nhanh hơn là việc xử lý một đoạn lệnh Transact – SQL lớn, lặp.
  • Giảm thiểu bandawidth: Với một sử lý sử dụng Transact-SQL có tới hàng trăm câu lệnh đơn được đồng thời gửi đi dẫn tới tình trạng ngốn bandawidth hoặc có thể là quá tải. Stored procedure cải thiện được vấn đề này bằng cách gửi theo trình tự xử lý. Đồng thời stored procedure còn có thể phân tích cú pháp và tối ưu hóa cậu lệnh trong lần thực thi đầu, giúp cải thiện câu lệnh tốt hơn.
  • Bảo mật: Phân cấp quyền sử dụng cho các user, cấp quyền, giới hạn quyền cho các user thậm chí họ không được phép thực thi trực tiếp những stored procedure này. Khi đó sẽ hạn chế, loại bỏ các vấn đề xâm phạm dữ liệu không được cấp phép.
• Định nghĩa về một stored procedure:
  • Tên của stored procedure
  • Các tham số
  • Thân của stored procedure: bao gồm các lệnh của Transact-SQL dùng để thực thi procedure.
• Cú pháp:
  
  1 2 3 4 5 6 7 8 9 10

  CREATE PROCEDURE  procedure_name     -- The variable parameter in/out     {@parameter data_type  input/output } AS Begin     [Declare variables for processing]     {Transact-SQL statements} End

• Lưu ý: Trong SQL Server, có thể ghi tắt một số từ khóa mà tên có chiều dài hơn 4 ký tự.
  

  Ví dụ: có thể thay thế Create Procedure bằng Create Proc.

• Khai báo biến và gán giá trị cho biến, Ghi chú
  
  1 2 3 4 5 6 7 8 9

  -- Declare variables DECLARE  @parameter_name data_type -- Assigning values to variables SET @parameter_name = value SELECT @parameter_name = value -- Show notice print N'message'

• Notes: Biên dịch và gọi thực thi một stored-procedure
  • Biên dịch : Chọn toàn bộ mã lệnh Tạo stored-procedure => Nhấn F5
  • Gọi thực thi một store-Procedure đã được biên dịch bằng lệnh exec:
    
    1 2 3 4

    EXECUTE procedure_name --Stored-proc nonparametric EXEC procedure_name Para1_value, Para2_value, … --Stored-proc with parameters

• Lệnh cập nhật Procedure
  
  1 2 3 4 5 6 7 8 9

  ALTER PROCEDURE  procedure_name         [ {@parameter data_type } ]     AS     Begin         [Declare variables for processing]         {Transact-SQL statements}     End

• Lệnh xóa Procedure
  
  1 2 3

  DROP PROCEDURE  procedure_name

2. Trigger

• Mỗi khi có sự thay đổi về dữ liệu ở một bảng dữ liệu cụ thể nào đó hoặc các thao tác, xử lý sử dụng các lệnh như: insert, update, delete làm thay đổi Trigger sẽ được gọi. Khá giống với store procedure nhưng đặc biện hơn. Trigger có thể chứ các lệnh truy vấn từ các bảng khác hay những lệnh SQL phức tạp hơn store procedure rất nhiều.
• Ưu điểm, thuận lợi khi sử dụng trigger:
  • Tự động: Trigger sẽ tự động được gọi: Trigger sẽ ngay tức được kích hoạt mỗi khi có sự thay đổi dữ liệu trên bảng dữ liệu.
  • Trigger có thể thực hiện cascade khi việc thi hành có ảnh hưởng đến những bảng liên quan.
  • Trigger có những hiệu lực ít bị hạn chế hơn so với ràng buộc giá trị nghĩa là có thể ràng buộc tham chiếu đến những cột của những bảng dữ liệu khác.
• Khi trigger được kích hoạt bởi 1 lệnh Transact-SQL insert để thêm một bộ mới vào bảng ABC thì bộ mới này được lưu tạm thời vào một bảng tạm có tên là inserted có cùng cấu trúc với bảng ABC. Khi kết thúc trigger này thì bộ dữ liệu mới thật sự lưu xuống CSDL.
• An toàn: Các bảng, các bộ dữ liệu bị xóa sẽ được chuyển tạm vào các bảng tạm deleted tương tự như đối với lệnh delete.
• Cú pháp
  • Lệnh tạo Trigger
    
    1 2 3 4 5 6 7 8 9

    Create Trigger trigger_name on table_name     For    [insert,update,delete]     As     Begin             {Declare variables for processing}             {Transact-SQL statements}     End

  • Lệnh xóa Trigger
    
    1 2 3

    Drop Trigger trigger_Name

Có thể bạn quan tâm:

• Sử dụng trigger trong SQL qua ví dụ cơ bản
• Sql là gì? 6 lý do tại sao bạn nên học SQL
• Tối ưu hoá MySQL sử dụng việc gộp các index

Xem thêm việc làm Software Developers hấp dẫn trên TopDev

TopDev via Viblo