Stored procedure và Trigger trong SQL Server

Trong bài viết này, chúng ta sẽ khám phá về stored procedure và trigger trong SQL Server và cách chúng được sử dụng. Hãy xem chúng có đáng sợ, nguy hiểm và cao siêu như những gì mọi người thường nghĩ hay không.

(Những ví dụ trong bài viết dựa trên cơ sở dữ liệu sakila - https://dev.mysql.com/doc/sakila/en/sakila-installation.html)

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 SQL Server.
• Stored procedure (SP) cho phép lưu trữ các logic ứng dụng class='hover-show-link replace-link-5' ứng dụng span class='hover-show-content'> trên cơ sở dữ liệu. Khi gọi SP lần đầu tiên, SQL Server 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, SQL Server 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.
• SP là một mã SQL đã được chuẩn bị sẵn mà bạn có thể lưu trữ, do đó đoạn 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 sử 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 class='hover-show-link replace-link-5' ứng dụng span class='hover-show-content'> như thêm, cập nhật có thể lưu ở phía cơ sở dữ liệu.
• Cải thiện tốc độ thực thi câu lệnh SQL.
• Bảo trì: Nếu có sự thay đổi trong cơ sở dữ liệu, mã lệnh cần thay đổi có thể xác định trong các stored procedure.
• An ninh cơ sở dữ liệu tốt hơn: Trong các ứng dụng class='hover-show-link replace-link-5' ứng dụng span class='hover-show-content'> 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 (Lack of Portability):
  • Stored procedure khó chuyển từ một hệ quản trị cơ sở dữ liệu (DBMS) sang một DBMS khác. Yêu cầu lập trình và kiểm thử lại đáng kể.
  • Ví dụ: SQL Server sử dụng T-SQL, Oracle sử dụng PL-SQL.
• Tải DB Server:
  • Sử dụng stored procedure nhiều có thể gây quá tải cho SQL Server (SQL Server phải lưu trữ quá nhiều kế hoạch thực thi).
• 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.
  • Lưu ý: Các phiên bản tương lai của SQL Server có thể cung cấp các giao diện cho phép tạo stored procedure sử dụng các ngôn ngữ bên ngoài, ví dụ Java.

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

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.

Sự khác nhau của Thủ tục và Hàm

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_name FROM actor ORDER BY first_name; END;

Tạo một thủ tục tên uspActorList có tham số đầu vào là max_id trả về danh sách first_name, last_name của các actor sắp xếp theo first_name và có id < max_id:

CREATE PROCEDURE actorList(IN max_id INT) AS BEGIN     SELECT first_name, last_name FROM actor WHERE actor_id < max_id ORDER BY first_name; END;

Tạo một function tên film_in_stock trả về số lượng với đầu vào (IN) là p_film_id, p_store_id, đầu ra (OUT) là p_film_count:

CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) READS SQL DATA BEGIN     SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND inventory_in_stock(inventory_id);     SELECT FOUND_ROWS() INTO p_film_count; END;

Các câu lệnh khác:

• DELIMITER $$: Thường được sử dụng khi xác định các hàm, thủ tục và trigger (trong đó bạn phải sử dụng nhiều câu lệnh). Dấu $$ được sử dụng để xác định phần đầu của toàn bộ thủ tục, bên trong các câu lệnh riêng lẻ được kết thúc bởi ;. Bằng cách đó, khi mã được chạy trong máy khách MySQL, máy khách có thể cho biết nơi toàn bộ thủ tục kết thúc và thực thi nó như một đơn vị thay vì thực thi các câu lệnh riêng lẻ bên trong.
• Gán giá trị cho biến: Sử dụng SET hoặc SELECT INTO.
• Gọi thủ tục: Sử dụng CALL.
• Mệnh đề IF THEN.
• Mệnh đề CASE.
• Mệnh đề REPEAT UNTIL.
• Mệnh đề WHILE.
• Mệnh đề LEAVE: LEAVE dùng để thoát khỏi vòng lặp. LEAVE cũng có thể dùng để thoát khỏi BEGIN-END. Tương tự như mệnh đề BREAK trong các ngôn ngữ lập trình khác.
• Xử lý lỗi thông qua Handlers.
• Cách quản lý stored procedure.

Ví dụ

Tạo một stored procedure tên là displayFilmInfo nhận category_id và language_id là các tham số:

• Nếu category_id và language_id được chỉ rõ, trả lại thông tin các film có category_id và language bằng các giá trị truyền vào.
• Nếu 0 được truyền như một tham số cho language_id thì trả lại thông tin các film có category_id là tham số truyền vào.
• Nếu 0 được truyền như một tham số cho category_id thì trả lại các film có language_id là tham số truyền vào.

DELIMITER $$ CREATE PROCEDURE displayFilmInfo(IN p_category_id INT, IN p_language_id INT) BEGIN     IF(p_language_id = 0) THEN         SELECT film.film_id         FROM film         JOIN film_category ON film.film_id = film_category.film_id AND film_category.category_id = p_category_id;     ELSEIF (p_category_id = 0) THEN         SELECT film.film_id         FROM film         WHERE film.language_id = p_language_id;     ELSEIF (p_language_id > 0 AND p_category_id > 0) THEN         SELECT film.film_id         FROM film         JOIN film_category ON film.film_id = film_category.film_id AND film_category.category_id = p_category_id         WHERE film.language_id = p_language_id;     END IF; END $$ DELIMITER ;

Trigger (Hàm)

Trigger là cái 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 class='hover-show-link replace-link-5' ứng dụng span class='hover-show-content'> có thể bao gồm: lưu lại thay đổi hoặc cập nhật dữ liệu của 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 cho cơ sở dữ liệu.

Cú pháp

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

Cú pháp lệnh bên trong tương tự stored procedure. Trong trigger, mã lệnh có thể truy cập các cột của bản ghi hiện tại.

• OLD.columnname trả lại nội dung của bản ghi trước khi bị thay đổi hoặc xóa (UPDATE, DELETE).
• NEW.columnname trả lại nội dung của bản ghi mới hoặc bản ghi thay thế (INSERT, UPDATE).

Ví dụ

Tạo trigger upd_film sau khi cập nhật trên bảng film, nếu title hoặc description thay đổi, cập nhật thông tin đó cùng với film_id vào bảng film_text:

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:

DROP TABLE IF EXISTS payment_log; SHOW CREATE TABLE payment;  CREATE TABLE payment_log (     payment_id SMALLINT(5) DEFAULT NULL,     customer_id SMALLINT(5) DEFAULT NULL,     staff_id TINYINT(3) UNSIGNED DEFAULT NULL,     rental_id INT(11) DEFAULT NULL,     amount DECIMAL(5,2) DEFAULT NULL,     payment_date DATETIME DEFAULT NULL,     changedate DATETIME DEFAULT NULL,     action VARCHAR(50) DEFAULT NULL );  DELIMITER $$ DROP TRIGGER IF EXISTS update_payment; 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 $$ DELIMITER ;

Quản lý trigger

• Trigger được lưu trữ.
• Trigger được lưu dưới dạng tệp văn bản đơn giản trong thư mục cơ sở dữ liệu như sau: /data_folder/database_name/table_name.trg.
• Để hiển thị các 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.

Đọc xong bạn cảm thấy có vui không?