Пользовательские функции SQL

Определяемая пользователем функция представляет собой подпрограмму, которая принимает параметры, выполняет действия, такие как сложные вычисления, а затем возвращает результат этих действий в виде значения. Возвращаемое значение может быть скалярным значением или таблицей.

Создание пользовательской функции:

CREATE FUNCTION Hello() -- создать функцию
RETURNS varchar(30)
-- объявляем тип возвращаемого значения
AS
BEGIN -- начало тела функции
DECLARE @MyVar varchar(20) = 'Hello World!';
RETURN @MyVar; --возвращаемое значение функции
END; -- конец тела функции
GO
PRINT dbo.Hello();

CREATE FUNCTION Hello() -- создать функцию

RETURNS varchar(30)

-- объявляем тип возвращаемого значения

BEGIN -- начало тела функции

DECLARE @MyVar varchar(20) = 'Hello World!';

RETURN @MyVar; --возвращаемое значение функции

END; -- конец тела функции

PRINT dbo.Hello();

Примеры:

/*Пользовательские функции возвращаемые скалярное значение (не табличное)*/

CREATE FUNCTION Hello() -- создать функцию
RETURNS varchar(30)     -- обьявляем тип возвращаемого значения
AS
BEGIN --начало тела ф-ции
DECLARE @MyVar varchar(20) ='Hello World!';
RETURN @MyVar; --возвращаемое значение ф-ции
END; -- конец тела ф-ции
GO

PRINT dbo.Hello();

DROP TABLE TestTable;
GO

CREATE TABLE TestTable
(
	id int identity not null,
	name varchar(25) not null,
	CDate smalldatetime not null
)
GO

-------------------------------------------------------

DECLARE @MyVar int =1;
DECLARE @MyVcVar varchar(10);

WHILE @MyVar &lt; 20
BEGIN
	SET @MyVcVar = 'Test ' + CAST(@MyVar as varchar);
	-- заносим данные в таблицу
	INSERT TestTable
	( name, CDate )
	VALUES (@MyVcVar, DATEADD(MI, @MyVar, GETDATE()));
	
	SET @MyVar = @MyVar + 1;
END
GO

/* данная выборка будет пустая, так как функция GETDATE() 
 возвращает значение типа datetime (возвращает не только 
 дату но и текущее время до 3-х сотых секунды)*/
SELECT * FROM TestTable
WHERE CDate = GETDATE(); 
GO
---------------------------------------------------------

CREATE FUNCTION DateOnly (@Date datetime) -- создаем функцию вводя ее имя и аргумент
RETURNS date --обявляем возвращаемый тип
AS
BEGIN --тело функции
 RETURN CAST(@Date as date); -- приводим к типу date возвращаемое значение
END
GO

SELECT * FROM TestTable -- данная выборка вернет 19 записей(все за сегодняшний день
WHERE dbo.DateOnly(CDate)= dbo.DateOnly(GETDATE()); --нужно явно указывать схему при вызове пользовательской функции
----------------------------------------
USE AdventureWorks2012
GO

/* Делаем выбоку для просмотра цены каждого горного велосипеда,
в данной выборке используются запросы внутри запросов их назы-
вают вложеными запросами*/

SELECT	Name,
		ListPrice,
		(SELECT AVG(ListPrice) FROM Production.Product WHERE ProductSubcategoryID = 1) as Average, -- используем вложеный запрос, мы помним что функция AVG - возвращаемая среднее значение
		ListPrice - (SELECT AVG(ListPrice) FROM Production.Product WHERE ProductSubcategoryID = 1) as DifferencePrice
FROM Production.Product 
WHERE ProductSubcategoryID = 1; -- делаем выборку для Mountain Bikers
GO

DROP FUNCTION AvgPrice
DROP FUNCTION DfrncPrice
GO

-- вложенный запрос (SELECT AVG(ListPrice)FROM Production.Product) можно оформить в виде функции, 
-- так как он возвращает выборку из одного значения
CREATE FUNCTION AvgPrice()
RETURNS money
WITH SCHEMABINDING    -- Запрещает удаление таблицы.
AS
BEGIN
	RETURN (SELECT AVG(ListPrice)FROM Production.Product WHERE ProductSubcategoryID = 1);
END
GO

--- использование вложенных функций
CREATE FUNCTION DfrncPrice(@Price money)
RETURNS money
AS
BEGIN
	RETURN @Price - dbo.AvgPrice(); -- возможно создавать вложенные функции
END;
GO

-- выполним тот же запрос для просмотра цены каждого горного велосипеда
SELECT  Name,
		ListPrice,
		dbo.AvgPrice() as AvgPrice,
		dbo.DfrncPrice(ListPrice) as DifferencePrice
FROM Production.Product
WHERE ProductSubcategoryID = 1;
GO

/*Пользвательские функции возвращемые таблицу*/

USE AdventureWorks2012;
GO

DROP FUNCTION fnContactList

CREATE FUNCTION fnContactList() -- создаем функцию
RETURNS TABLE -- возвращаемый тип TABLE указывает на то что возвращаться будет таблица
AS
RETURN (SELECT LastName, FirstName, ModifiedDate 
		FROM Person.Person); 
GO

SELECT * FROM dbo.fnContactList(); -- выводим всю информацию из таблицы сформированой при помощи функции fnContactList();
GO

--ЗАДАНИЕ: повторите предыдущий пример с помощью представления

DROP FUNCTION fnContactSearch

CREATE FUNCTION fnContactSearch(@LastName varchar(30)) --передаем один аргумент типа varchar (Фамилия или часть фамилии)
RETURNS TABLE
AS
RETURN (SELECT FirstName , LastName, ModifiedDate 
		FROM Person.Person
		WHERE LastName LIKE @LastName + '%'); -- выборка по фамилии
GO

SELECT * FROM dbo.fnContactSearch('Berry'); -- выводим всю информацию о сторудниках часть фамилии или фамилия которых 'Berry' из таблицы сформированой при помощи fnContactSearch();

SELECT * FROM dbo.fnContactSearch('Ber'); -- выводим всю информацию о сторудниках часть фамилии или фамилия которых 'Ber' из таблицы сформированой при помощи fnContactSearch();

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

/*Пользовательские функции возвращаемые скалярное значение (не табличное)*/

CREATE FUNCTION Hello() -- создать функцию

RETURNS varchar(30) -- обьявляем тип возвращаемого значения

BEGIN --начало тела ф-ции

DECLARE @MyVar varchar(20) ='Hello World!';

RETURN @MyVar; --возвращаемое значение ф-ции

END; -- конец тела ф-ции

PRINT dbo.Hello();

DROP TABLE TestTable;

CREATE TABLE TestTable

(

id int identity not null,

name varchar(25) not null,

CDate smalldatetime not null

)

-------------------------------------------------------

DECLARE @MyVar int =1;

DECLARE @MyVcVar varchar(10);

WHILE @MyVar < 20

BEGIN

SET @MyVcVar = 'Test ' + CAST(@MyVar as varchar);

-- заносим данные в таблицу

INSERT TestTable

( name, CDate )

VALUES (@MyVcVar, DATEADD(MI, @MyVar, GETDATE()));

SET @MyVar = @MyVar + 1;

END

/* данная выборка будет пустая, так как функция GETDATE()

возвращает значение типа datetime (возвращает не только

дату но и текущее время до 3-х сотых секунды)*/

SELECT * FROM TestTable

WHERE CDate = GETDATE();

---------------------------------------------------------

CREATE FUNCTION DateOnly (@Date datetime) -- создаем функцию вводя ее имя и аргумент

RETURNS date --обявляем возвращаемый тип

BEGIN --тело функции

RETURN CAST(@Date as date); -- приводим к типу date возвращаемое значение

END

SELECT * FROM TestTable -- данная выборка вернет 19 записей(все за сегодняшний день

WHERE dbo.DateOnly(CDate)= dbo.DateOnly(GETDATE()); --нужно явно указывать схему при вызове пользовательской функции

----------------------------------------

USE AdventureWorks2012

/* Делаем выбоку для просмотра цены каждого горного велосипеда,

в данной выборке используются запросы внутри запросов их назы-

вают вложеными запросами*/

SELECT Name,

ListPrice,

(SELECT AVG(ListPrice) FROM Production.Product WHERE ProductSubcategoryID = 1) as Average, -- используем вложеный запрос, мы помним что функция AVG - возвращаемая среднее значение

ListPrice - (SELECT AVG(ListPrice) FROM Production.Product WHERE ProductSubcategoryID = 1) as DifferencePrice

FROM Production.Product

WHERE ProductSubcategoryID = 1; -- делаем выборку для Mountain Bikers

DROP FUNCTION AvgPrice

DROP FUNCTION DfrncPrice

-- вложенный запрос (SELECT AVG(ListPrice)FROM Production.Product) можно оформить в виде функции,

-- так как он возвращает выборку из одного значения

CREATE FUNCTION AvgPrice()

RETURNS money

WITH SCHEMABINDING -- Запрещает удаление таблицы.

BEGIN

RETURN (SELECT AVG(ListPrice)FROM Production.Product WHERE ProductSubcategoryID = 1);

END

--- использование вложенных функций

CREATE FUNCTION DfrncPrice(@Price money)

RETURNS money

BEGIN

RETURN @Price - dbo.AvgPrice(); -- возможно создавать вложенные функции

END;

-- выполним тот же запрос для просмотра цены каждого горного велосипеда

SELECT Name,

ListPrice,

dbo.AvgPrice() as AvgPrice,

dbo.DfrncPrice(ListPrice) as DifferencePrice

FROM Production.Product

WHERE ProductSubcategoryID = 1;

/*Пользвательские функции возвращемые таблицу*/

USE AdventureWorks2012;

DROP FUNCTION fnContactList

CREATE FUNCTION fnContactList() -- создаем функцию

RETURNS TABLE -- возвращаемый тип TABLE указывает на то что возвращаться будет таблица

RETURN (SELECT LastName, FirstName, ModifiedDate

FROM Person.Person);

SELECT * FROM dbo.fnContactList(); -- выводим всю информацию из таблицы сформированой при помощи функции fnContactList();

--ЗАДАНИЕ: повторите предыдущий пример с помощью представления

DROP FUNCTION fnContactSearch

CREATE FUNCTION fnContactSearch(@LastName varchar(30)) --передаем один аргумент типа varchar (Фамилия или часть фамилии)

RETURNS TABLE

RETURN (SELECT FirstName , LastName, ModifiedDate

FROM Person.Person

WHERE LastName LIKE @LastName + '%'); -- выборка по фамилии

SELECT * FROM dbo.fnContactSearch('Berry'); -- выводим всю информацию о сторудниках часть фамилии или фамилия которых 'Berry' из таблицы сформированой при помощи fnContactSearch();

SELECT * FROM dbo.fnContactSearch('Ber'); -- выводим всю информацию о сторудниках часть фамилии или фамилия которых 'Ber' из таблицы сформированой при помощи fnContactSearch();

Добавить комментарий Отменить ответ