Đô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
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ệnhCREATE FUNCTION Tên function>(< Function Table-valued có 2 loại: CREATE FUNCTION Tên function>(< CREATE FUNCTION Tên function>(< ALTER FUNCTION Tên function>(< Xem thêm: Chỉnh Google Chrome Sang Tiếng Việt Cho Chrome Cực Kỳ Đơn Giản DROP FUNCTION EXEC sp_helptext "FunctionName" 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 ( 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 ( 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 ( ALTER FUNCTION count_customer_with_address ( DROP FUNCTION count_customer_with_address
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS kiểu dữ liệu> 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
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS TABLE
tên biến trả về> TABLE (tên cột 1> kiểu dữ liệu> 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
tên tham số> kiểu dữ liệu> <= giá trị mặc định>>, …,<...>>)RETURNS kiểu dữ liệu> | TABLE 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.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)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:
count = count(*) FROM ( SELECT Vay.MaKH FROM Vay,KhachHang WHERE Vay.MaKH=KhachHang.MaKH AND KhachHang.DiaChi=
countENDGọi thực thi function:
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:
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
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ạoKế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.