Stored procedure và Trigger trong SQL Server

Trong bài viết này, chúng ta sẽ cùng tìm hiểu về stored procedure (thủ tục) và trigger trong SQL Server. Cùng khám phá xem chúng là gì, được sử dụng như thế nào và liệu chúng có đáng sợ và nguy hiểm như mọi người thường nghĩ không?

Có thể bạn quan tâm

(Những ví dụ trong bài viết dựa trên cơ sở dữ liệu sakila – link)

Bạn đang xem: Stored procedure và Trigger trong SQL Server

Contents

1 Stored procedure (Thủ tục)
2 Sự khác nhau của Thủ tục và Hàm
3 Trigger (Hàm)
4 Quản lý stored procedure và trigger

Stored procedure (Thủ tục)

Stored procedure là gì?

Là đoạn chương trình kịch bản (programming scripts) với các câu lệnh SQL nhúng (embedded SQL) được lưu dưới dạng đã được biên dịch và thi hành thực tiếp bởi MySQL server.
Stored procedure cho phép lưu trữ logic ứng dụng trên CSDL. Khi gọi stored procedure lần đầu tiên, MySQL sẽ tạo một lịch thực thi và lưu trữ nó trong bộ nhớ đệm. Ở những lần gọi hàm tiếp theo, MySQL sử dụng lại lịch thực thi được lưu rất nhanh với hiệu suất đáng tin cậy.
Stored Procedure là một mã SQL đã được chuẩn bị sẵn mà bạn có thể lưu, do đó mã có thể được sử dụng lại nhiều lần.
Vì vậy, nếu bạn có một truy vấn SQL mà bạn viết đi viết lại nhiều lần, hãy lưu nó dưới dạng một thủ tục được lưu trữ, sau đó chỉ cần gọi nó để thực thi.

Vì sao nên dùng Stored Procedure?

Giảm dư thừa mã chương trình: Các đoạn mã tương tự trong các ứng dụng như thêm, cập nhật có thể lưu ở phía CSDL.
Cải thiện tốc độ thực thi câu lệnh SQL.
Bảo trì: Nếu có sự thay đổi trong CSDL, mã lệnh cần thay đổi có thể xác định trong các stored procedure.
An ninh CSDL tốt hơn: Trong các ứng dụng an ninh cao, với stored procedure có thể kiểm soát truy cập dữ liệu và đưa ra các qui định an ninh tập trung.

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

Thiếu tính khả chuyển: Stored procedure khó chuyển từ một DBMS sang một DBMS khác. Yêu cầu lập trình và kiểm thử lại đáng kể.
Tải DB Server: Sử dụng stored procedure nhiều có thể gây quá tải cho MySQL server.
Hạn chế ngôn ngữ lập trình: Lập trình stored procedure không phong phú như các nền tảng phát triển khác như Java hay PHP.

Sử dụng Stored Procedure khi?

Thực hiện những phép toán phức tạp, lặp lại nhiều lần.
Project đòi hỏi nghiêm ngặt về tốc độ thực thi. Stored Procedure cho tốc độ thực thi nhanh hơn so với các câu lệnh SQL thông thường vì stored procedure đã được lưu sẵn tại SQL server, do vậy nó chỉ cần gọi một dòng lệnh để thực thi stored procedure, điều này giúp tăng tốc độ thực thi.
Trong quá trình tạo Stored Procedure, SQL Server đã tối ưu hóa những dòng lệnh này, điều này giúp cho tốc độ thực thi cao hơn câu lệnh SQL thông thường rất nhiều.

Không cần sử dụng Stored Procedure khi?

Stored Procedure làm quá trình debug trở nên khó khăn hơn.

Xem thêm : Công nghệ 11 Bài 11: Bản vẽ xây dựng

Vì vậy hãy nghĩ đến Stored Procedure như là phương án cuối cùng để tối ưu hóa tốc độ thực thi chương trình. Các project có size nhỏ và vừa nên ưu tiên sử dụng các thư viện ORM (Object Relation Mapping), như Entity Framework cho C#, TypeORM cho NodeJs.

Thủ tục (Procedure)	Hàm (Function)
Cách gọi: CALL	Sử dụng các câu lệnh trong SQL như SELECT, UPDATE
Giá trị trả về:	Có thể một hoặc nhiều kết quả SELECT và các tham số out
Các tham số:	Giá trị (input) và tham chiếu (output)
Gọi thủ tục/hàm:	Có thể gọi các thủ tục và hàm khác
Cú pháp tạo thủ tục/hàm:	CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode CREATE PROCEDURE name ([parameterlist]) [options] sqlcode
Ví dụ:	Tạo một thủ tục tên uspActorList trả về danh sách first_name, last_name của các actor sắp xếp theo first_name CREATE PROCEDURE uspActorList AS BEGIN SELECT first_name, last_price FROM actor ORDER BY first_name; END;

Trigger (Hàm)

Trigger là gì?

Trigger là quá trình tự động thực thi các lệnh SQL hoặc stored procedure sau hoặc trước các lệnh INSERT, UPDATE hoặc DELETE.
Các ứng dụng có thể bao gồm: lưu lại thay đổi hoặc cập nhật dữ liệu các bảng khác.
Trigger chạy sau mỗi câu lệnh cập nhật bảng, do đó có thể thêm tải với CSDL.

Cú pháp

CREATE TRIGGER name BEFORE | AFTER INSERT | UPDATE | DELETE ON tablename FOR EACH ROW sql-code

Xem thêm : Hàm setprecision trong c++ | Lấy độ chính xác 2 chữ số thập phân

Cú pháp lệnh bên trong tương tự stored procedure.

Ví dụ

CREATE TRIGGER `upd_film` AFTER UPDATE ON `film`
FOR EACH ROW
BEGIN
   IF (old.title != new.title) or (old.description != new.description) THEN
      UPDATE film_text SET title=new.title, description=new.description, film_id=new.film_id WHERE film_id=old.film_id;
   END IF;
END;

Tạo trigger trên bảng payment, mỗi khi thêm, sửa bảng payment sẽ cập nhật thông tin đó cùng với thông tin là thời gian thêm, sửa vào bảng payment_log (tạo thêm).

CREATE TRIGGER update_payment AFTER UPDATE ON payment
FOR EACH ROW
BEGIN
   INSERT INTO payment_log SET action = 'update', payment_id = OLD.payment_id, customer_id = OLD.customer_id, staff_id = OLD.staff_id, rental_id = OLD.rental_id, amount = OLD.amount, payment_date = OLD.payment_date, changedate = NOW();
END;

Stored Procedure và trigger được lưu trữ
Stored Procedure and trigger được lưu dưới dạng file văn bản trong thư mục của cơ sở dữ liệu như sau: /data_folder/database_name/table_name.trg
Để hiển thị các stored procedure và trigger gắn với một bảng dữ liệu:

SELECT * FROM `Information_Schema`.`Triggers` WHERE Trigger_schema = 'database_name'

Để xóa một trigger:

DROP TRIGGER tablename.triggername

Để thay đổi một trigger:

ALTER TRIGGER, SHOW CREATE TRIGGER, hoặc SHOW TRIGGER STATUS.

Sau khi đọc xong bài viết này, bạn có thấy vui không?

Nguồn: https://ispacedanang.edu.vn
Danh mục: Học tập