بررسی مفهوم Table Expression - بخش دوم

در ادامه مباحث مربوط به مفهوم Subquery، قصد داریم View و انواع توابع را مورد بررسی قرار دهیم.

از طریق View، می توانیم به یک جدول از زاویه های مختلفی دسترسی داشته باشیم. View نیز یک Virtual Table یا جدول مجازی می باشد با این ویژگی که در قالب یک آبجکت در دیتابیس ما نیز ذخیره می شود. بنابراین می توانیم روی آن Permission تعریف کنیم و رکورد ها را در بلند مدت ذخیره کنیم.

روش تعریف یک View به این صورت می باشد.

CREATE  VIEW  View_Name
AS 
SELECT_statement;

در Viewها، ORDER BY نمی توانیم داشته باشیم، ستون ها باید دارای نام باشند و نام ستون ها باید منحصر به فرد باشد.

برای مثال یک View ایجاد خواهیم کرد که تعداد سفارش های هر شرکت به همراه شهر آن را نمایش دهد.

CREATE VIEW dbo.Company_List
AS
SELECT
	C.CompanyName,
	C.City,
	(SELECT COUNT(O.OrderID) FROM dbo.Orders AS O 
		WHERE C.CustomerID = O.CustomerID) AS Num
FROM dbo.Customers AS C;
GO

و سپس آن را فراخوانی می کنیم.

SELECT * FROM dbo.Company_List;
GO

و در ادامه فقط مشتریان تهرانی را نمایش می دهیم.

SELECT * FROM dbo.Company_List AS CL
	WHERE CL.City = N'تهران';
GO

تنظیمات Viewها، شامل SCHEMA BINDING و CHECK OPTION می باشد که در مورد آنها صحبت خواهیم کرد.

اگر ما بر روی دیتابیس خود تعداد زیادی View تعریف کرده باشیم و حالا قصد تغییرات در جداول دیتابیس خود را داشته باشیم، Viewهایی که بر اساس ساختار قبلی جداول نوشته شده اند از کار می افتند. SCHEMA BINDING بین Viewهای ما و جداول وابستکی ایجاد می کند و اگر قصد تغییر بر روی جداول وابسته را داشته باشیم، به ما اجازه تغییر را نمی دهد. هنگام استفاده از SCHEMA BINDING، از Select * نمی توانیم استفاده کنیم و نام آبجکت ها باید همراه Schema استفاده شوند.

CREATE  VIEW  View_Name WITH SCHEMABINDING
AS 
SELECT_statement;

از طریق View ما می توانیم بر روی آبجکت خود، عملیات ایجاد، ویرایش و یا حذف رکورد را نیز داشته باشیم. از طریق CHECK OPTION قصد داریم جلوی این کار را بگیریم و آن View را محدود کنیم.

CREATE  VIEW  View_Name
AS 
SELECT_statement
WITH CHECK OPTION;

زمانی که از CHECK OPTION استفاده می کنیم، از DISTINCT ،HAVING ،GROUP BY و TOP نمی توانیم استفاده کنیم.

آخرین نوع از Table Expressionها Table-valued Functions (TVF) می باشد. Function مجموعه ای دستورات می باشد که می تواند دارای ورودی و خروجی باشد و توسط کاربر نیز قابل تعریف هستند که به آنها User-Defined Functions گفته می شود. مهم ترین ویژگی Functionها کپسوله سازی می باشد.

انواع Functionها بر اساس خروجی به دو دسته Scaler-Value و Table-Value تقسیم می شوند. Table-Valueها نیز به دو دسته Inline (تک دستور) و Multi Statement (چند دستور) تقسیم می شود.

روش تعریف یک Scaler-Value Function به این صورت می باشد.

CREATE FUNCTION FUNCTION_Name
	({@Parameter [AS] type[=default]}[,...n])
	RETURNS Type
AS
BEGIN
	Function_Body
	RETURN Expression
END

برای مثال یک Function ایجاد می کنیم که نام و نام خانوادگی را دریافت می کند و یک Abbreviation به ما برمی گرداند.

CREATE FUNCTION dbo.Abbreviation
	(@FirstName NVARCHAR(50),@Family NVARCHAR(100))
	RETURNS NCHAR(3)
AS
BEGIN
	DECLARE @Output NCHAR(3)
	SET @Output = LEFT(@FirstName,1) + '.' + LEFT(@Family,1)
	RETURN @Output
END
GO

و در حالت عادی از آن استفاده می کنیم.

SELECT dbo.Abbreviation('Mohsen', 'Farokhi');
GO

در حالتی که بخواهیم از Function در یک SELECT استفاده کنیم نیز به این صورت می باشد.

SELECT
	FirstName,
	LastName,
	dbo.Abbreviation(FirstName,LastName) AS Abbreviation
FROM dbo.Employees;
GO

روش تعریف یک Inline Table-Value به این صورت می باشد.

CREATE FUNCTION  Function_Name 
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) 
RETURNS TABLE 
[ WITH < function_option > [ [,] ...n ] ] 
[ AS ] 
RETURN [ ( ] select-stmt [ ) ] 

در این کوئری، با استفاده از Inline Table-Value، تمامی سفارش های شرکت‌ شهر ارسال شده از طریق پارامتر را نمایش می دهیم.

CREATE FUNCTION dbo.Func_Customers_Info
	(@City NVARCHAR(50))
RETURNS TABLE
RETURN
	SELECT
		C.CompanyName, C.City ,O.OrderID, O.OrderDate    
	FROM dbo.Customers AS C
	JOIN dbo.Orders AS O
		ON C.CustomerID=O.CustomerID
		AND C.City = @City;
GO

و سپس فراخوانی تابع به این صورت می باشد.

SELECT * FROM dbo.Func_Customers_Info(N'تهران');
GO

روش تعریف یک Multi Statement Table-Value به این صورت می باشد.

CREATE FUNCTION [owner_name.] function_name 
    ({@parameter [AS] type [= default]}[,...n ]) 
	RETURNS @return_variable TABLE < table_type_definition > 
AS
	BEGIN 
	    function_body 
	    RETURN
	END

برای مثال یک جدول را شامل دو ستون از نوع INT و NVARCHAR بر می گردانیم.

CREATE FUNCTION dbo.Multi_Statement_Table_Valued()
	RETURNS @Tbl TABLE (Col1 INT, Col2 NVARCHAR(100))
AS
	BEGIN
		INSERT @Tbl 
		VALUES (1,'HELLO'),(2,'SQL')
	RETURN;
END
GO

و در ادامه از آن استفاده می کنیم.

SELECT * FROM dbo.Multi_Statement_Table_Valued();
GO

توصیه می شود که از این مدل استفاده نشود زیرا Query Optimizer کدهای درون BEGIN و END را در نظر نمی گیرید و پلن بهینه ای برای ما نمی سازد.

پایان