Giới Thiệu Function Trong Sql Server

Đôi lúc chúng ta muốn tự tạo một hàm riêng cho hệ thống để dễ mở rộng và tái sử dụng, trong SQL cũng hỗ trợ chúng ta thực hiện công việc này. Bài viết này mình xin chia sẻ một số kiến thức về Function (Hàm) trong MS SQL Server.

Bạn đang xem: Giới thiệu function trong sql server

Function là gì?

Định nghĩa:

Là một đối tượng trong cơ sở dữ liệu (CSDL) sử dụng trong các câu lệnh SQL, được biên dịch sẵn và lưu trong CSDL nhằm mục đích thực hiện xử lý nào đó như tính toán phức tạp và trả về kết quả là giá trị nào đó.

Đặc điểm:Luôn trả về giá trịGồm 2 loại: Function hệ thống và Function do người dùng tự định nghĩaFunction người dùng tự định nghĩa gồm 2 loại:Scalar-valued: Trả về giá trị vô hướng của các kiểu dữ liệu T-SQLTable-valued: Trả về bảng, là kết quả của một hoặc nhiều lệnh

Cách định nghĩa Function

1. Tạo Function trả về giá trị loại Scalar-valued

CREATE FUNCTION Tên function>(<
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS kiểu dữ liệu>BEGIN RETURN Biểu thức giá trị đơn>ENDTrong đó:

Tên function: Tên của hàm chúng ta sẽ tạoTên tham số: Là các tham số Input cho hàm. Khai báo báo gồm tên của tham số (trước tên tham số sử dụng tiền tố
), kiểu dữ liệu của tham số, chúng ta có thể chỉ định giá trị mặc định cho tham số. Có thể chỉ định nhiều tham số đầu vàoRETURNS: từ khóa này chỉ định kiểu dữ liệu hàm sẽ trả về. Kiểu dữ liệu phải được chỉ định kiểu độ dài dữ liệu. Ví dụ: varchar(100)WITH ENCRYPTION: Từ khóa chỉ định code của hàm sẽ được mã hóa trong bảng syscomments.AS: Từ khóa cho biết code của hàm bắt đầu.BEGIN: Đi cùng với END để tạo thành bao khối bao các câu lệnh trong thân hàm.RETURN: Từ khóa này sẽ gửi giá trị tới thủ tục gọi hàm.Một số lưu ý:Tên function phải là duy nhất trong 1 CSDL. Function được tạo/định nghĩa trong CSDL nào thì chỉ sử dụng trong CSDL đó. Khác với Function có sẵn của SQL được truy cập ở bất cứ đâu.Danh sách tham số tối đa 1024 tham số.

2. Tạo Function trả về giá trị loại Table-valued

Function Table-valued có 2 loại:

Hàm giá trị bảng đơn giản: Trả về bảng, là kết quả của một câu lệnh SELECT đơnHàm giá trị bảng đa câu lệnh: Trả về bảng, là kết quả của nhiều câu lệnha) Hàm giá trị bảng đơn giản

CREATE FUNCTION Tên function>(<
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS TABLE RETURN Câu lệnh SQL>ENDLưu ý Hàm giá trị bảng đơn còn được gọi là hàm giá trị bảng nội tuyến. Có thể được dùng trong câu lệnh truy vấn thay thế cho tên bảng hoặc tên View.

b) Hàm giá trị bảng đa câu lệnh

CREATE FUNCTION Tên function>(<
tên biến trả về> TABLE (tên cột 1> kiểu dữ liệu> , ..., tên cột n> kiểu dữ liệu> )BEGIN Câu lệnh SQL>RETURNEND

Thay đổi, xóa, xem nội dung Function

1. Thay đổi Function

Để thay đổi các hàm đã khai báo ta sử dụng câu lệnh ALTER FUNCTION. Cú pháp tương tự như tạo mới Function, chỉ thay từ khóa CREATE bằng từ khóa ALTER

ALTER FUNCTION Tên function>(<
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS kiểu dữ liệu> | TABLEBEGIN RETURN Biểu thức giá trị đơn> | Câu lệnh SQLEND

2. Xóa Function

Để xóa hàm ta dùng câu lệnh DROP FUNCTION.

Xem thêm: Chỉnh Google Chrome Sang Tiếng Việt Cho Chrome Cực Kỳ Đơn Giản

DROP FUNCTION Tên function>

3. Xem nội dung Function

Để xem nội dung function ta sử dụng Store Procedure (Thủ tục) có sẵn của SQL là sp_helptext (Transact-SQL)

EXEC sp_helptext "FunctionName"

Ví dụ cụ thể

Cho bài toán quản lý Vay có thế chấp tài sản đơn giản thể hiện qua sơ đồ mức vật lý như sau:

Ví dụ 1. Tạo function cho biết số lượng khách hàng theo địa chỉ bất kỳ nhận vào từ tham số với điều kiện là khách hàng có tổng số tiền vay từ trước đến nay từ 200 triệu trở lên.

Cách 1: Trả về giá trị vô hướng

CREATE FUNCTION count_customer_with_address (
count = count(*) FROM ( SELECT Vay.MaKH FROM Vay,KhachHang WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi=
countENDGọi thực thi function:

PRINT dbo.count_customer_with_address("Ha Noi")Cách 2: Trả về giá trị table bằng câu lệnh đơn

CREATE FUNCTION count_customer_with_address2 (
address varchar(200))RETURNS TABLEAS RETURN SELECT count(*) AS "Total customers" FROM ( SELECT Vay.MaKH FROM Vay,KhachHang WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi=
address GROUp BY Vay.MaKH HAVING SUM(Vay.SoTienVay)>=200) AS TempGọi thực thi function:

SELECT * FROM dbo.count_customer_with_address2 ("Da Nang")Cách 3: Trả về giá trị table bằng đa câu lệnh

CREATE FUNCTION count_customer_with_address3 (
address varchar(200))RETURNS
new_table TABLE (DiaChi varchar(200), SoLuong int)ASBEGINDECLARE
count int = 0SELECT
count = count(*) FROM ( SELECT Vay.MaKH FROM Vay,KhachHang WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi=
address GROUp BY Vay.MaKH HAVING SUM(Vay.SoTienVay)>=200 ) AS Temp INSERT INTO
new_table VALUES (
address,
count) RETURNENDVí dụ 2. Sửa đổi function đã tạo ở trên bổ sung điều kiện khách hàng có tuổi từ 30 trở lên

ALTER FUNCTION count_customer_with_address (
count = count(*) FROM ( SELECT Vay.MaKH FROM Vay JOIN KhachHang ON Vay.MaKH=KhachHang.MaKH WHERE KhachHang.DiaChi=
address AND YEAR(GETDATE()) - YEAR(NgaySinh) >= 30 GROUp BY Vay.MaKH HAVING SUM(Vay.SoTienVay)>=200 ) AS Temp RETURN
countENDVí dụ 3. Xóa bỏ function đã tạo

DROP FUNCTION count_customer_with_address

Kết luận

Trên đây là những gì mình tổng hợp và tự đưa ra ví dụ minh họa cho từng phần. Hi vọng sẽ giúp ích cho các bạn trong quá trình sử dụng function trong câu lệnh sql.