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ề các khái niệm thủ tục lưu trữ và trigger trong SQL Server, và cách chúng được sử dụng trong ứng dụng, cùng với việc xem xét xem chúng có đáng sợ, nguy hiểm và tuyệt vời như những gì mọi người thường nghĩ không nhé

Có thể bạn quan tâm

(Các 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)

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

Mục Lục

Thủ tục lưu trữ (Stored Procedure)

Thủ tục lưu trữ là gì?

Thủ tục lưu trữ là một đoạn chương trình (programming script) được lưu trong MySQL server dưới dạng đã được biên dịch và có thể thực thi trực tiếp bởi server. Thủ tục lưu trữ có thể chứa các câu lệnh SQL nhúng (embedded SQL), và cho phép lưu trữ một logic ứng dụng trên cơ sở dữ liệu. Khi thủ tục lưu trữ được gọi lần đầu tiên, MySQL sẽ tạo một lịch thực thi và lưu trữ nó trong bộ nhớ cache. Khi được gọi lần tiếp theo, MySQL sẽ sử dụng lại lịch thực thi đã lưu trữ, đảm bảo hiệu suất cao và đáng tin cậy.
Thủ tục lưu trữ là một đoạn mã SQL chuẩn đã được chuẩn bị sẵn và có thể lưu trữ lại, giúp tiết kiệm thời gian và công sức trong việc viết lại mã nếu câu truy vấn tương tự được sử dụng nhiều lần.
Thủ tục lưu trữ cũng có thể nhận các tham số đầu vào để thực hiện các tác vụ cụ thể dựa trên các giá trị được truyền vào.

Tại sao nên sử dụng Thủ tục lưu trữ?

Giảm sự trùng lặp 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ể được lưu trữ trên máy chủ cơ sở dữ liệu.
Cải thiện hiệu suất thực thi truy vấn SQL: Thủ tục lưu trữ đã được tối ưu hóa và lưu trữ sẵn trên server, giúp tăng tốc độ thực thi so với truy vấn SQL thông thường.
Dễ bảo trì: Khi có thay đổi trong cơ sở dữ liệu, chỉ cần sửa đổi thủ tục lưu trữ liên quan mà không cần phải thay đổi ở nhiều nơi trong ứng dụng.
Tăng cường an ninh cơ sở dữ liệu: Thủ tục lưu trữ có thể kiểm soát truy cập dữ liệu và áp dụng các quy định an ninh tập trung cho ứng dụng.

Nhược điểm của Thủ tục lưu trữ

Khó di chuyển giữa các hệ quản trị cơ sở dữ liệu (Lack of Portability)
Tăng tải cho máy chủ cơ sở dữ liệu: Sử dụng quá nhiều thủ tục lưu trữ có thể gây tải nặng cho máy chủ cơ sở dữ liệu vì cần lưu trữ và thực thi nhiều kế hoạch trong cache.
Hạn chế trong việc lập trình: Lập trình thủ tục lưu trữ không đa dạng như các nền tảng phát triển khác như Java hay PHP.

Thủ tục lưu trữ được sử dụng khi nào?

Thực hiện các phép toán phức tạp, được lặp lại nhiều lần
Dự án đòi hỏi hiệu suất thực thi cao. Thủ tục lưu trữ có thể cung cấp hiệu suất thực thi nhanh hơn so với truy vấn SQL thông thường vì đã được lưu sẵn trên server.
Trong quá trình tạo thủ tục lưu trữ, SQL Server đã tối ưu hóa các câu lệnh, giúp tăng tốc độ thực thi so với truy vấn SQL thông thường.

Không cần sử dụng Thủ tục lưu trữ khi nào?

Thủ tục lưu trữ làm quá trình debug trở nên phức tạp hơn.

Vì vậy, hãy xem xét việc sử dụng thủ tục lưu trữ như là một phương án cuối cùng để tối ưu hóa hiệu suất thực thi chương trình. Trong các dự án nhỏ và trung bình, nên ưu tiên sử dụng thư viện ORM (Object Relation Mapping) như Entity Framework cho C#, TypeORM cho NodeJs.

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

Thủ tục (PROCEDURE) Hàm (FUNCTION)

Cách gọi CALL Sử dụng các câu lệnh 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 Trả về một giá trị duy nhất thông qua RETURN

Các tham số Giá trị (input) và tham chiếu (output) Các tham số (IN, OUT, INOUT)

Chỉ các giá trị tham số vào (input). Không cần các thẻ như IN 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 có tên uspActorList trả về danh sách first_name, last_name của các diễn viên được 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 có tên uspActorList và tham số đầu vào là max_id trả về danh sách first_name, last_name của các diễn viên có id < max_id và được sắp xếp theo first_name

CREATE PROCEDURE uspActorList(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 hàm có tên film_in_stock trả về số lượng đĩa có sẵn với các đầu vào (IN) là p_film_id, p_store_id, và đầu ra (OUT) là p_film_count

CREATE FUNCTION 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 đó ta cần sử dụng nhiều câu lệnh). Dấu $$ được sử dụng để xác định phần đầu của một thủ tục, trong đó mỗi câu lệnh riêng lẻ được kết thúc bởi dấu ;. Như vậy, khi chạy mã trong máy chủ MySQL, máy chủ có thể biết nơi 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.
Lưu ý rằng từ khóa DELIMITER chỉ là một tính năng của dòng lệnh giao tiếp với máy chủ MySQL (và một số máy chủ khác) và không phải là một cú pháp chuẩn trong SQL. Nó sẽ không hoạt động nếu chuyển nó qua một API ngôn ngữ lập trình đến MySQL. Các ứng dụng khách khác như PHPMyAdmin có các phương thức khác để chỉ định dấu phân cách không mặc định.

Gán giá trị cho biến

Sử dụng SET hoặc SELECT INTO.

VD:

SET @model_year = 2018;

Xem thêm : OS là gì? Tìm hiểu về hệ điều hành (Operating System) từ A-Z

Gọi thủ tục:

Call film_in_stock(1,1, @film_count);

Select @film_count;

Mệnh đề IF THEN

IF condition THEN commands; [ELSEIF condition THEN commands;] [ELSEcommands;] END IF;

Mệnh đề CASE

CASE expression WHEN value1 THEN commands; [WHEN value2 THEN commands;] [ELSE commands;] END CASE;

Mệnh đề REPEAT UNTIL

[loopname:] REPEAT commands; UNTIL condition END REPEAT [loopname];

Mệnh đề WHILE

[loopname:] WHILE condition DO commands; END WHILE [loopname];

Mệnh đề LEAVE

LEAVE dùng để thoát khỏi vòng lặp
LEAVE cũng có thể được sử 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

Luôn có khả năng gặp lỗi trong quá trình thực thi thủ tục lưu trữ. MySQL cung cấp cơ chế xử lý lỗi thông qua Handler
Một handler nên được định nghĩa sau khi khai báo các biến, con trỏ và điều kiện, nhưng trước các câu lệnh SQL
Cú pháp định nghĩa một Handler

DECLARE type HANDLER FOR condition1, condition2, condition3, … statement;

type: CONTINUE hoặc EXIT
condition(s): Các điều kiện mà handler sẽ được gọi (VD: NOT FOUND, SqlException,..)
statement: Câu lệnh sẽ được thực hiện khi có điều kiện xảy ra

VD: Nếu không tìm thấy (NOT FOUND) customer_id là khách hàng chưa trả đĩa thuê có id được lưu trong kho là p_inventory_id thì trả về NULL

CREATE FUNCTION inventory_held_by_customer(p_inventory_id INT) RETURNS INT READS SQL DATA BEGIN DECLARE v_customer_id INT; DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL; SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END $$

Quản lý thủ tục lưu trữ

Hiển thị tất cả thủ tục lưu trữ hiện có trong tất cả các cơ sở dữ liệu
Hiển thị tất cả các hàm hiện có trong tất cả các cơ sở dữ liệu
Nếu chỉ muốn hiển thị thủ tục lưu trữ của một cơ sở dữ liệu cụ thể, sử dụng câu lệnh:
Tương tự, hiển thị thông tin cụ thể về một thủ tục:

Ví dụ

Tạo một thủ tục lưu trữ có tên displayFilmInfo nhận category_id và language_id làm tham số:

Nếu category_id và language_id được chỉ định, trả về thông tin về các bộ phim có category_id và language_id tương ứng.
Nếu language_id được chỉ định là 0, trả về thông tin về các bộ phim có category_id tương ứng.
Nếu category_id được chỉ định là 0, trả về thông tin về các bộ phim có language_id tương ứng.

DELIMITER $$

DROP PROCEDURE IF EXISTS displayFilmInfo;

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 ;

CALL displayFilmInfo(1,1);

Kết quả

mysql> CALL displayFilmInfo(1,1);

+——+

| film_id |

+——+

| 19 |

| 21 |

| 29 |

| 38 |

| 56 |

| 67 |

…. 64 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Viết một hàm tính tổng số đĩa film được thuê của một cửa hàng trong tháng, năm. Với các tham số là mã cửa hàng, tháng, năm. Sau đó, sử dụng hàm trong câu lệnh SELECT để hiển thị cửa hàng và số lượng film được thuê trong tháng 2/2006.

Thủ tục

DELIMITER $$

DROP FUNCTION IF EXISTS total_movies;

CREATE PROCEDURE total_movies(IN p_store_id INT, IN p_month INT, IN p_year INT) BEGIN SELECT inventory.store_id as store,count(rental.inventory_id) FROM inventory JOIN rental ON inventory.inventory_id = rental.inventory_id AND month(rental.rental_date)=p_month AND year(rental.rental_date)=p_year GROUP BY inventory.store_id HAVING inventory.store_id=p_store_id; END$$

DELIMITER ;

CALL total_movies(1,2,2006);

Kết quả

mysql> CALL total_movies(1,2,2006);

+——+—————————+

| store | count(rental.inventory_id) |

+——+—————————+

| 1 | 92 |

+——+—————————+

1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Tạo một trigger trên bảng payment, mỗi khi thêm hoặc sửa đổi bảng payment, thông tin đó sẽ được ghi lại cùng với thông tin thời gian thêm, sửa vào bảng payment_log.

drop table 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ữ dưới dạng tệp tin văn bản trong thư mục cơ sở dữ liệu: /data_folder/database_name/table_name.trg,
Để hiển thị các trigger liên kết với một bảng dữ liệu
Xóa một trigger
Thay đổi một trigger

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

Nguồn: https://stamboom-boden.com
Danh mục: Là Gì

Thủ tục lưu trữ (Stored Procedure)

Thủ tục lưu trữ là gì?

Tại sao nên sử dụng Thủ tục lưu trữ?

Nhược điểm của Thủ tục lưu trữ

Thủ tục lưu trữ được sử dụng khi nào?

Không cần sử dụng Thủ tục lưu trữ khi nào?

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

Cú pháp tạo Thủ tục/Hàm

Ví dụ

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

Quản lý thủ tục lưu trữ

Ví dụ

Quản lý Trigger

Related Posts