1. Mô tả
- Lệnh CREATE FUNCTION dùng để tạo hàm (UDF: User-Defined Function) trong CSDL.
- Hàm là một chương trình con (Sub Program).
- Hàm dùng để thực hiện một xử lý tính toán và trả về kết quả. Ví dụ: tính tuổi, tính điểm trung bình, tính tổng nhập, tổng xuất của vật tư…
- Hàm chỉ cho phép loại tham số đầu vào (INPUT).
- Phân loại:
– Hàm đơn trị (Scalar Function).
– Hàm đọc bảng (Inline Table-Valued Function).
– Hàm tạo bảng (Multi-Statement Table-Valued Function).
2. Cú pháp
CREATE FUNCTION Tên_hàm(Khai_báo_tham_số)
RETURNS Kiểu_dữ_liệu_trả_về
AS
BEGIN
Lệnh...
RETURN Giá_trị_trả_về
END
GO CREATE FUNCTION Tên_hàm(Khai_báo_tham_số)
RETURNS TABLE
AS
RETURN
(
Câu_truy_vấn
)
GO CREATE FUNCTION Tên_hàm(Khai_báo_tham_số)
RETURNS Tên_biến TABLE(Khai_báo_cấu_trúc_bảng)
AS
BEGIN
INSERT | UPDATE | DELETE
RETURN
END
GO 3. Ví dụ
--Hàm tính diện tích hình tròn khi biết bán kính
CREATE FUNCTION dbo.Dien_tich_hinh_tron (@Ban_kinh float = 1.0)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN PI() * POWER(@Ban_kinh, 2)
END
GO
--Gọi thực hiện
--Cách 1
PRINT dbo.Dien_tich_hinh_tron(3.5)
GO
--Cách 2
DECLARE @r float =3.5
DECLARE @s float = dbo.Dien_tich_hinh_tron(@r)
PRINT @s
GO --Hàm tính giai thừa một số nguyên N (N >= 0)
CREATE FUNCTION dbo.Giai_thua (@n int = 1)
RETURNS decimal(38, 0)
AS
BEGIN
RETURN
( CASE
WHEN @n < 0 THEN NULL
WHEN @n = 0 THEN 1
WHEN @n > 1 THEN CAST(@n AS float) * dbo.Giai_thua(@n - 1)
WHEN @n = 1 THEN 1 END
)
END
--Gọi thực hiện
PRINT dbo.Giai_thua(3)--Tạo hàm đọc danh sách nhân viên theo mã phòng truyền vào
CREATE FUNCTION Danh_sach_nhan_vien(@depid numeric(3,0))
RETURNS TABLE
AS
RETURN
(
SELECT
FROM EMPLOYEES
WHERE Department_id = @depid OR @depid = 0
)
GO
--Gọi hàm
SELECT FROM Danh_sach_nhan_vien(80) --Tạo hàm
CREATE FUNCTION Thong_ke_nhan_vien()
RETURNS @bang TABLE
(
Maphong numeric(3,0),
Tenphong nvarchar(30),
Sonv tinyint
)
AS
BEGIN
INSERT INTO @bang
SELECT e.Department_id, d.Department_name, COUNT()
FROM EMPLOYEES e JOIN DEPARTMENTS d ON e.Department_id = d.Department_id
GROUP BY e.Department_id, d.Department_name
RETURN
END
--Gọi hàm
SELECT FROM Thong_ke_nhan_vien() Xem thêm: lệnh sửa hàm ALTER FUNCTION, lệnh xóa hàm DROP FUNCTION. (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.
- 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-SQL
- Oracle: PL-SQL
- Tải DB Server
- Sử 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ình
- Lập trình SP 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 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ầ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?
- 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àmThủ 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àmCREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
8
CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
9 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;
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_price
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 $$
Gán giá trị cho biếnSử dụng SET hoặc SELECT INTO. VD:
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 $$
2 Gọi thủ tục:
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 $$
3
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 $$
4 Mệnh đề IF THENIF condition THEN
commands;
[ELSEIF condition THEN
commands;]
[ELSEcommands;]
END IF;
Mệnh đề CASECASE 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ể 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 Store Procedure gặp lỗi trong khi thực thi các lệnh SQL. MySQL cung cấp kỹ thuật xử lý lỗi thông qua Handler
- Một handler cần định nghĩa sau khai báo các biến, con trỏ và điều kiện, nhưng trước các lệnh SQL
- Cú pháp khai báo 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ẽ thi hành khi có điều kiện xảy ra
VD: Nếu không tim 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ì sẽ 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ý store procedure
- Hiển thị tất cả những Stored procedure đang tồn tại trong tất cả các cơ sở dữ liệu
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 $$
5 - Hiển thị tất cả những Function đang tồn tại trong tất cả các cơ sở dữ liệu
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 $$
6 - Nếu chỉ muốn hiển thị ra stored procedure của một cơ sở dữ liệu cụ thể sử dụng câu lệnh:
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 $$
7 - Tương tự với functions:
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 $$ 8 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 $$ 9 IF condition THEN
commands;
[ELSEIF condition THEN
commands;]
[ELSEcommands;]
END IF;
0 - Hiển thị thông tin cụ thể của một procedure
IF condition THEN
commands;
[ELSEIF condition THEN
commands;]
[ELSEcommands;]
END IF;
1
Ví dụ
- Tạo một stored procedure tên là displayFilmInfo nhận category_id và language_id như 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 $$
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 ;
# thực thi procedure
CALL displayFilmInfo(1,1);
Kết quả
CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
0 - Viết hàm tính tổng số đĩa film được thuê của của hàng trong tháng, năm. Với tham số đầu vào là mã của hàng, tháng, năm. Sau đó sử dụng hàm trong lệnh SELECT đưa ra các cửa hàng và tổng số film được thuê tại cửa hàng trong tháng 2/2006.
- Procedure
CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
1 Kết quả
CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
2 CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
3 Kết quả
CREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
4 Trigger (Hàm)
Trigger là cái gì?
- Triggers là quá trình tự động thi hành các lệnhSQL hoặc SP 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ápCREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
5 Cú pháp lệnh bên trong tương tự SP 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ụCREATE PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
6 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 PROCEDURE actorList(IN max_id INT)
AS
BEGIN
SELECT
first_name,
last_price
FROM
actor
WHERE
actor_id < max_id
ORDER BY
first_name;
END;
7 Quản lý trigger
Trigger is stored as plain text file in the database folder as follows: /data_folder/database_name/table_name.trg, |