THỦ TỤC TRONG SQL LÀ GÌ

Trong bài viết lần này chúng ta sẽ cùng nhau tìm hiểu hàm, thủ tục và trigger trong SQL Server là cái gì và được sử dụng như thế nào và cùng xem thử 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à cái 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,

SP cho phép lưu trữ các logic ứng dụng trên CSDL. Khi gọi SP 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 sử dựng lại lịch thực thi được lưu rất nhanh với hiệu xuất đáng tin cậy.

Bạn đang xem: Thủ tục trong sql là gì

SP là một mã SQL đã chuẩn bị sẵn mà bạn có thể lưu, 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 nó.

Cũng có thể chuyển các tham số cho một thủ tục được lưu trữ, để thủ tục được lưu trữ có thể hoạt động dựa trên (các) giá trị tham số được truyền vào.

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 SSQL

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 SP

An ninh CSDL tốt hơn: Trong các ứng dụng an ninh cao, với SP 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)SP 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ểSQLServer: T-SQLOracle: PL-SQLTải DB ServerSử dụng SP nhiều có thể gây quá tải MySQL server (SQL Server phải lưu trữ quá nhiều kế hoạc thực thi)Hạn chế ngôn ngữ lập trìnhLập trình SP không phong phú như các nền tảng phát triển khác như Java hay PHPLưu ý: Các phiên bản tương lai của MySQL có thể cung cấp các giao diện cho phép tạo các SP 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ầnProject đò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.

Xem thêm: Giới Hạn Chảy Tiếng Anh Là Gì ? Giới Hạn Chảy Tiếng Anh Là Gì

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

Store Procedured 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, Hàm

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	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	Chỉ có thể gọi các hàm khác

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

CREATE FUNCTION name () RETURNS datatype sqlcode

CREATE PROCEDURE name () 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 uspActorListASBEGIN SELECT first_name, last_price 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 CREATE PROCEDURE actorList(IN max_id INT)ASBEGIN SELECT first_name, last_price FROM actor WHERE actor_id 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 DATABEGIN 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à trình kích hoạt (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.

Xem thêm: Các Quy Định Của Pháp Luật Về Địa Giới Hành Chính Là Gì ? Khái Niệm Về Địa Giới Hành Chính

Lưu ý rằng từ khóa DELIMITER chỉ là một chức năng của command line mysql client (và một số máy khách khác) và không phải là một cấu trúc của SQL thông thường. 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. Một số ứ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.