Trong khuyên bảo này, bạn sẽ tìm hiểu những thứ bạn cần phải biết về function do người tiêu dùng tự khái niệm trong SQL Server, bao hàm scalar-valued function (hàm vô hướng) trả về một giá bán trị 1-1 và table-valued function (hàm bảng) trả về các bản ghi dữ liệu.

Bạn đang xem: Cách dùng function trong sql

Function do người dùng tự có mang trong SQL Server giúp bạn đơn giản dễ dàng hóa công việc lập trình của mình bằng cách đóng gói các logic nghiệp vụ phức tạp để thực hiện lại trong các truy vấn.

Scalar function vào SQL Server

Trong phần này, bạn sẽ tìm hiểu về những scalar function (hàm vô hướng) trong SQL Server cùng cách sử dụng chúng nhằm đóng gói các công thức hoặc súc tích nghiệp vụ và thực hiện lại chúng trong số truy vấn.

Scalar function là gì?

Scalar function (hàm vô hướng) trong SQL hệ thống yêu mong một hoặc các tham số với trả về một cực hiếm đơn.

Scalar function giúp bạn dễ dàng hóa mã của mình. Ví dụ: bạn cũng có thể có một phép tính phức tạp mở ra trong những truy vấn SELECT. Thay vì chưng thêm bí quyết trong những truy vấn, bạn cũng có thể tạo một scalar function đóng góp gói công thức này và thực hiện nó trong mỗi truy vấn.

Tạo scalar function vào SQL Server

Để tạo thành một scalar function, bạn thực hiện câu lệnh CREATE FUNCTION như sau:

CREATE FUNCTION function_name ( parameter_list)RETURNS data_type ASBEGIN statements RETURN valueENDTrong cú pháp này:

Đầu tiên, hướng đẫn tên của function sau các từ khóa CREATE FUNCTION. Tên lược đồ gia dụng là tùy chọn. Trường hợp bạn không chỉ định rõ ràng, SQL vps sẽ sử dụng lược trang bị dbo theo khoác định.Thứ hai, hướng đẫn một danh sách những tham số được bao bọc bởi cặp vết ngoặc đối chọi sau tên function.Thứ ba, chỉ định và hướng dẫn kiểu dữ liệu của giá trị trả về vào câu lệnh RETURNS.Cuối cùng, thêm một câu lệnh RETURN để trả về một giá chỉ trị bên trong phần thân của function.Lưu ý: kiểu dữ liệu trả về trong thân của function bắt buộc giống cùng với kiểu dữ liệu được khai báo sau từ khóa RETURNS.

Ví dụ dưới đây tạo ra một function đo lường và tính toán doanh thu thuần dựa vào số lượng, niêm yết và chiết khấu:

CREATE FUNCTION sales.udfNetSale(
quantity INT,
list_price DEC(10,2),
discount DEC(4,2))RETURNS DEC(10,2)AS BEGIN RETURN
quantity *
list_price * (1 -
discount);END;Sau đó, bạn có thể sử dụng function udfNetSale để giám sát và đo lường doanh thu ròng rã của ngẫu nhiên đơn mua hàng trong bảng order_items tự cơ sở dữ liệu mẫu BikeStores.

Sau khi sinh sản scalar function, chúng ta có thể tìm thấy nó trong Programmability > Functions > Scalar-valued Functions như vào hình sau:

*

Gọi scalar function trong SQL Server

Bạn hotline một scalar function (hàm vô hướng) giống như hàm tích hợp. Ví dụ, câu lệnh sau minh họa giải pháp gọi function udfNetSale:

SELECT sales.udfNetSale(10,100,0.1) net_sale;Đây là tác dụng đầu ra:

*

Ví dụ sau minh họa cách thực hiện function sales.udfNetSale để sở hữu được lệch giá thuần của những đơn mua hàng trong bảng order_items:

SELECT order_id, SUM(sales.udfNetSale(quantity, list_price, discount)) net_amountFROM sales.order_itemsGROUP BY order_idORDER BY net_amount DESC;Hình hình ảnh sau đây đến thấy một trong những phần kết quả đầu ra:

*

Chỉnh sửa scalar function vào SQL Server

Để sửa đổi một scalar function, bạn áp dụng từ khóa ALTER thay vị CREATE như sau:

ALTER FUNCTION function_name ( parameter_list)RETURN data_type ASBEGIN statements RETURN valueENDLưu ý: chúng ta có thể sử dụng câu lệnh CREATE OR ALTER để tạo nên function nếu như nó không tồn tại hoặc để sửa đổi function hiện nay có.CREATE OR ALTER FUNCTION function_name ( parameter_list)RETURN data_type ASBEGIN statements RETURN valueEND

Xóa scalar function vào SQL Server

Để xóa một scalar function (hàm vô hướng) hiện có, bạn sử dụng câu lệnh DROP FUNCTION như sau:

DROP FUNCTION function_name;Ví dụ: để xóa function sales.udfNetSale, bạn áp dụng câu lệnh sau:

DROP FUNCTION sales.udfNetSale;

Những điểm cần nhớ về scalar function vào SQL Server

Sau đấy là một số điểm chính của các scalar function vào SQL Server:

Scalar function rất có thể được áp dụng ở phần lớn mọi nơi trong những câu lệnh T-SQL.Scalar function chấp nhận một hoặc nhiều tham số mà lại chỉ trả về một quý hiếm đơn, cho nên vì vậy chúng phải gồm một câu lệnh RETURN.Scalar function rất có thể sử dụng súc tích như khối IF hoặc vòng lặp WHILE.Scalar function hoàn toàn có thể gọi các function khác.

Trong phần này, bạn đã học cách thực hiện scalar function của SQL Server nhằm đóng gói những công thức phức hợp hoặc lô ghích nghiệp vụ tinh vi và áp dụng lại chúng trong số truy vấn.

Biến thứ hạng bảng trong SQL Server

Trong phần này, các bạn sẽ tìm gọi về đổi thay kiểu bảng (table variable) trong SQL server để lưu trữ các bạn dạng ghi dữ liệu.

Biến kiểu dáng bảng là gì?

Biến vẻ bên ngoài bảng là các loại biến có thể chấp nhận được bạn tàng trữ các phiên bản ghi dữ liệu, tương tự như như những bảng tạm.

Cách khai báo đổi thay kiểu bảng trong SQL Server

Để khai báo một vươn lên là kiểu bảng, bạn thực hiện câu lệnh DECLARE như sau:

DECLARE
table_variable_name TABLE ( column_list);Trong cú pháp này, bạn chỉ định thương hiệu của trở nên kiểu bảng trọng tâm từ khóa DECLARE và TABLE. Tên của những biến vẻ bên ngoài bảng phải ban đầu bằng cam kết tự
.

Theo sau từ bỏ khóa TABLE, các bạn định nghĩa cấu trúc của phát triển thành kiểu bảng tựa như như kết cấu của bảng thông thường bao gồm định nghĩa những cột, dạng hình dữ liệu, kích thước, buộc ràng tùy chọn, v.v.

Phạm vi của các biến loại bảng trong SQL Server

Tương từ như những biến viên bộ, biến kiểu bảng sẽ không còn tồn tại sau khi kết thúc khối lệnh.

Nếu bạn định nghĩa một biến hóa kiểu bảng trong một stored procedure hoặc function, trở nên kiểu bảng sẽ không thể tồn tại sau khi stored procedure hoặc function kết thúc.

Ví dụ về đổi thay kiểu bảng trong SQL Server

Ví dụ, câu lệnh sau khai báo một biến đổi kiểu bảng thương hiệu là
product_table TABLE ( product_name VARCHAR(MAX) NOT NULL, brand_id INT NOT NULL, list_price DEC(11,2) NOT NULL);Chèn tài liệu vào biến kiểu bảng

Sau khi khai báo, phát triển thành kiểu bảng sẽ chưa xuất hiện dữ liệu. Chúng ta cũng có thể chèn các phiên bản ghi vào thay đổi kiểu bảng bằng cách sử dụng câu lệnh INSERT:

INSERT INTO
product_tableSELECT product_name, brand_id, list_priceFROM production.productsWHERE category_id = 1;Truy vấn tài liệu trong biến chuyển kiểu bảng

Tương từ như bảng tạm, bạn có thể truy vấn tài liệu từ biến đổi kiểu bảng bằng phương pháp sử dụng câu lệnh SELECT như sau:

SELECT *FROM
product_table TABLE ( product_name VARCHAR(MAX) NOT NULL, brand_id INT NOT NULL, list_price DEC(11,2) NOT NULL);INSERT INTO
product_tableSELECT product_name, brand_id, list_priceFROM production.productsWHERE category_id = 1;SELECT *FROM
product_table;GOHình ảnh sau đây mang đến thấy một trong những phần kết quả đầu ra:

*

Hạn chế của biến kiểu bảng vào SQL Server

Đầu tiên, các bạn phải định nghĩa cấu trúc của đổi mới kiểu bảng. Không y như bảng thường thì hoặc bảng tạm, bạn không thể đổi khác cấu trúc của những biến thứ hạng bảng sau khoản thời gian chúng được khai báo.

Thứ hai, số liệu thống kê góp trình buổi tối ưu hóa truy tìm vấn chỉ dẫn kế hoạch thực hiện truy vấn tốt. Thật ko may, các biến hình dạng bảng không đựng số liệu thống kê. Bởi đó, bạn nên làm sử dụng những biến thứ hạng bảng nhằm lưu trữ một trong những lượng nhỏ bạn dạng ghi.

Thứ ba, bạn không thể thực hiện biến vẻ bên ngoài bảng làm cho tham số đầu vào hoặc cổng output như các kiểu tài liệu khác. Tuy nhiên, chúng ta có thể trả về trở nên kiểu bảng từ function.

Thứ tư, các bạn không thể tạo ra non-clustered index cho đổi thay kiểu bảng. Mặc dù nhiên, bắt đầu từ SQL hệ thống 2014, đổi thay kiểu bảng được về tối ưu hóa bộ nhớ lưu trữ có sẵn cùng với việc reviews OLTP trong bộ lưu trữ mới chất nhận được bạn thêm non-clustered index như 1 phần của khai báo đổi mới kiểu bảng.

Thứ năm, nếu bạn đang áp dụng biến giao diện bảng với JOIN, bạn cần đặt bí danh mang lại bảng để tiến hành truy vấn. Ví dụ:

SELECT brand_name, product_name, list_priceFROM brands bINNER JOIN
product_table pt ON p.brand_id = pt.brand_id;

Hiệu suất của biến kiểu bảng trong SQL Server

Sử dụng những biến hình trạng bảng vào một stored procedure dẫn tới việc biên dịch lại ít hơn so với áp dụng bảng tạm.

Ngoài ra, một đổi mới kiểu bảng thực hiện ít tài nguyên rộng một bảng nhất thời với ít ngân sách chi tiêu khóa và ghi nhật cam kết hơn.

Tương từ như bảng tạm, các biến mẫu mã bảng triển khai trong cơ sở dữ liệu tempdb, không hẳn trong bộ nhớ.

Xem thêm: Sự Khác Nhau Của Các Hạng Phổ Thông Của Vietnam Airlines, Hạng Phổ Thông

Sử dụng biến chuyển kiểu bảng trong function do người tiêu dùng định nghĩa

Function do người dùng định nghĩa sau mang tên là ufnSplit() trả về biến đổi kiểu bảng.

CREATE OR ALTER FUNCTION udfSplit(
index)); ENDRETURNENDGOCâu lệnh sau hotline function udfSplit():

SELECT * FROM udfSplit("foo,bar,baz",",");Đây là hiệu quả đầu ra:

*

Trong phần này, bạn đã tò mò cách áp dụng biến dạng hình bảng trong SQL hệ thống giúp đem lại một số lợi ích hiệu suất và tính hoạt bát so với những bảng tạm.

Table function trong SQL Server

Trong phần này, các bạn sẽ tìm đọc cách sử dụng table function vào SQL Server bao hàm table function đối kháng câu lệnh với table function đa câu lệnh.

Table function trong SQL vps là gì

Table function (hàm bảng) là function do người dùng tự tư tưởng trả về tài liệu kiểu bảng. Phong cách trả về của table function là một trong những bảng, vì đó chúng ta có thể sử dụng table function như thể như các bạn sẽ sử dụng bảng.

Tạo table function trong SQL Server

Câu lệnh tiếp sau đây tạo ra một table function trả về danh sách các sản phẩm bao gồm tên sản phẩm, năm mã sản phẩm và niêm yết cho một năm mã sản phẩm cụ thể:

CREATE FUNCTION udfProductInYear (
model_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year =
model_year;Cú pháp tương tự như cú pháp sinh sản function do người dùng định nghĩa.

Câu lệnh RETURNS TABLE khẳng định function vẫn trả về một bảng. Như bạn có thể thấy, không tồn tại khối lệnh BEGIN...END. Câu lệnh chỉ dễ dàng là trả về truy hỏi vấn dữ liệu từ bảng production.products.

Function udfProductInYear yêu ước một tham số có tên
model_year.

Khi table function được tạo, bạn có thể tìm thấy nó trong Programmability > Functions > Table-valued Functions như trong hình sau:

*

Function trên trả về tập tác dụng của một câu lệnh SELECT, cho nên vì vậy nó còn được gọi là table function 1-1 câu lệnh.

Thực thi table function trong SQL Server

Để thực thi một table function, bạn áp dụng nó vào mệnh đề FROM của câu lệnh SELECT như sau:

SELECT * FROM udfProductInYear(2017);Đây là một trong những phần kết trái đầu ra:

*

Trong lấy một ví dụ này, chúng tôi đã lựa chọn các thành phầm có năm model là 2017.

Bạn cũng rất có thể chỉ định những cột được trả về từ bỏ table function như sau:

SELECT product_name, list_priceFROM udfProductInYear(2018);Đây là cổng đầu ra một phần:

*

Sửa đổi table function trong SQL Server

Để sửa đổi table function, bạn sử dụng từ khóa ALTER thay vì CREATE rồi thay đổi các thành phần bên trong fuction theo nhu yếu của bạn.

Ví dụ: câu lệnh sau sửa thay đổi function udfProductInYear bằng phương pháp thay đổi tham số hiện gồm và thêm một tham số mới:

ALTER FUNCTION udfProductInYear (
end_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year BETWEEN
end_yearFunction udfProductInYear bây chừ trở về các sản phẩm mà năm mã sản phẩm nằm trong khoảng từ năm bước đầu đến năm kết thúc.

Câu lệnh sau call function udfProductInYear để lấy các thành phầm có năm model từ năm 2017 mang lại năm 2018:

SELECT product_name, model_year, list_priceFROM udfProductInYear(2017,2018)ORDER BY product_name;Đây là một phần kết quả đầu ra:

*

Table function nhiều câu lệnh trong SQL Server

Table function nhiều câu lệnh hoặc MSTVF là function có tương đối nhiều câu lệnh và trả về quý hiếm kiểu bảng.

Table function rất hữu ích vì chúng ta cũng có thể thực hiện nhiều truy vấn vào function với tổng hợp kết quả vào bảng được trả về.

Để có mang table function, bạn thực hiện biến thứ hạng bảng làm cho giá trị trả về. Phía bên trong function, bạn triển khai một hoặc những truy vấn và chèn tài liệu vào biến hóa kiểu bảng này.

Function udfContacts() sau đây kết hợp nhân viên cấp dưới và quý khách vào một danh sách liên lạc duy nhất:

CREATE FUNCTION udfContacts() RETURNS
contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20) )ASBEGIN INSERT INTO
contacts SELECT first_name, last_name, email, phone, "Customer" FROM sales.customers; RETURN;END;Câu lệnh sau minh họa cách xúc tiến function udfContacts:

SELECT * FROM udfContacts();Đây là một trong những phần kết quả đầu ra:

*

Khi như thế nào nên sử dụng table function vào SQL Server

Chúng tôi thường sử dụng table function như là 1 trong dạng view bao gồm tham số (view động). đối với stored procedure, table function hoạt bát hơn bởi vì vì chúng ta cũng có thể sử dụng chúng ở bất cứ nơi như thế nào bảng được sử dụng.

Trong phần này, chúng ta đã mày mò về table function vào SQL Server bao hàm table function đối chọi câu lệnh cùng table function nhiều câu lệnh.

Xóa function vào SQL Server

Trong phần này, các bạn sẽ tìm hiểu phương pháp xóa function do người dùng tự định nghĩa bằng cách sử dụng câu lệnh DROP FUNCTION vào SQL Server.

Giới thiệu về câu lệnh DROP FUNCTION vào SQL Server

Để xóa function do người tiêu dùng tự có mang được tạo vì chưng câu lệnh CREATE FUNCTION, bạn thực hiện câu lệnh DROP FUNCTION như sau:

DROP FUNCTION < IF EXISTS > < schema_name. > function_name;Trong cú pháp này:

Tùy chọn IF EXISTS chất nhận được bạn xóa function nếu nó tồn tại. Nếu function không tồn tại thì câu lệnh đã không làm gì cả. Nếu bạn cố gắng xóa một function ko tồn trên mà không chỉ có định tùy lựa chọn IF EXISTS, bạn sẽ gặp lỗi.Tùy lựa chọn schema_name hướng đẫn tên của lược đồ nhưng mà function do người dùng tự có mang thuộc về. Thương hiệu lược trang bị là tùy chọn.function_name là tên của function mà bạn muốn xóa.

Ghi chú:

Nếu function mà bạn muốn xóa được tham chiếu trong view hoặc function khác được tạo bằng tùy lựa chọn WITH SCHEMABINDING thì câu lệnh DROP FUNCTION đang thất bại.

Ngoài ra, ví như có những ràng buộc như check hoặc default và các cột được đo lường liên quan mang đến function, câu lệnh DROP FUNCTION cũng trở thành thất bại.

Để xóa các function do người dùng tự định nghĩa, bạn chỉ định danh sách tên function được phân tách bằng vết phẩy sau mệnh đề DROP FUNCTION như sau:

DROP FUNCTION schema_name.function_name1, schema_name.function_name2, ...;

Ví dụ về câu lệnh DROP FUNCTION vào SQL Server

Chúng tôi sẽ sử dụng bảng order_items tự cơ sở dữ liệu mẫu BikeStores nhằm minh họa xóa function vào SQL Server.

Ví dụ xóa function đơn giản trong SQL Server

Ví dụ dưới đây tạo ra một function tính số tiền chiết khấu từ số lượng, niêm yết và xác suất chiết khấu:

CREATE FUNCTION sales.udf_get_discount_amount (
discountENDĐể xóa function sales.udf_get_discount_amount, bạn sử dụng câu lệnh sau:

DROP FUNCTION IF EXISTS sales.udf_get_discount_amount;Ví dụ xóa function bao gồm WITH SCHEMABINDING vào SQL Server

Ví dụ dưới đây tạo lại function sales.udf_get_discount_amount bằng phương pháp sử dụng tùy lựa chọn WITH SCHEMABINDING:

CREATE FUNCTION sales.udf_get_discount_amount (
discountENDVà câu lệnh tiếp sau đây tạo view sales.discounts sử dụng function sales.udf_get_discount_amount như sau:

CREATE VIEW sales.discountsWITH SCHEMABINDINGASSELECT order_id, SUM(sales.udf_get_discount_amount( quantity, list_price, discount )) AS discount_amountFROM sales.order_items iGROUP BY order_id;Bây giờ, trường hợp bạn cố gắng xóa function sales.udf_get_discount_amount, bạn sẽ gặp lỗi:

DROP FUNCTION sales.udf_get_discount_amount;SQL hệ thống trả về lỗi sau:

Cannot DROP FUNCTION "sales.udf_get_discount_amount" because it is being referenced by object "discounts".Nếu bạn muốn xóa function này, trước tiên các bạn phải xóa view sales.discounts như sau:

DROP VIEW sales.discounts;Và tiếp đến thực thi lại câu lệnh xóa function:

DROP FUNCTION sales.udf_get_discount_amount;Trong phần này, chúng ta đã học cách thực hiện câu lệnh DROP FUNCTION trong SQL Server nhằm xóa một hoặc nhiều function do người dùng tự định nghĩa.


nếu kco.vn bổ ích và khiến cho bạn tiết kiệm thời gian

Bạn hoàn toàn có thể vui lòng tắt trình chặn quảng cáo ❤️ để hỗ trợ chúng tôi bảo trì hoạt đụng của trang web.