бд / Лекция 9 - Функции
.docxФункции SQL Server 2008
Фу́нкция — это поименованная последовательность хранимых на сервере команд, выполняемая как одно целое, которая может вызываться из других частей программы столько раз, сколько необходимо.
Отличия функций от хранимых процедур:
- функция всегда возвращает значение в основную программу, а хранимая процедура лишь осуществляет какие-либо преобразования, не возвращая результата.
- функции как и хранимые процедуры могут иметь входные параметры, но выходных параметров у них быть не может
- функции можно применять в выражениях присваивания, хранимые процедуры – нельзя.
Существует 2 класса функций:
- встроенные
- пользовательские.
Встроенные функции
Встроенные функции, имеющиеся в распоряжении пользователей при работе с SQL, можно условно разделить на следующие группы:
-
математические функции ;
-
строковые функции ;
-
функции для работы с датой и временем ;
-
функции конфигурирования;
-
функции системы безопасности;
-
функции управления метаданными;
-
статистические функции.
Математические функции
|
|
ABS |
вычисляет абсолютное значение числа |
ACOS |
вычисляет арккосинус |
ASIN |
вычисляет арксинус |
ATAN |
вычисляет арктангенс |
ATN2 |
вычисляет арктангенс с учетом квадратов |
CEILING |
выполняет округление вверх |
COS |
вычисляет косинус угла |
COT |
возвращает котангенс угла |
DEGREES |
преобразует значение угла из радиан в градусы |
EXP |
возвращает экспоненту |
FLOOR |
выполняет округление вниз |
LOG |
вычисляет натуральный логарифм |
LOG10 |
вычисляет десятичный логарифм |
PI |
возвращает значение "пи" |
POWER |
возводит число в степень |
RADIANS |
преобразует значение угла из градуса в радианы |
RAND |
возвращает случайное число |
ROUND |
выполняет округление с заданной точностью |
SIGN |
определяет знак числа |
SIN |
вычисляет синус угла |
SQUARE |
выполняет возведение числа в квадрат |
SQRT |
извлекает квадратный корень |
TAN |
возвращает тангенс угла |
Для иллюстрации использования математической функции ROUND дополним БД таблицами: Disc – для хранения дисциплин, Ocenka – для хранения оценок по каждой дисциплине.
create table Disc
(
idDisc int identity primary key,
nameDisc varchar(200)
)
create table Ocenka
(
idVed int identity primary key,
idSt int references Student(idSt),
idDisc int references Disc(idDisc),
ocenka smallint
)
insert into Disc values ('Математика')
insert into Disc values ('История')
insert into Disc values ('Информатика')
insert into Ocenka values(3,1,5)
insert into Ocenka values(3,2,4)
insert into Ocenka values(3,3,5)
insert into Ocenka values(4,1,3)
insert into Ocenka values(4,2,4)
insert into Ocenka values(4,3,2)
Проведем расчет среднего балла по всем студентам и округлим значение до 2-х знаков после запятой:
select round(convert(float, sum(ocenka))/convert(float,count(*)),2) from Ocenka
Средний балл для каждого студента:
select
nameSt+' '+sNameSt as ФИО,
round(convert(float, sum(ocenka))/convert(float,count(*)),2) as [Средний балл]
from Ocenka join Student on (Ocenka.idSt=Student.idSt)
group by nameSt+' '+sNameSt
Строковые функции
ASCII |
возвращает код ASCII левого символа строки |
CHAR |
по коду ASCII возвращает символ |
CHARINDEX |
определяет порядковый номер символа, с которого начинается вхождение подстроки в строку |
DIFFERENCE |
возвращает показатель совпадения строк |
LEFT |
возвращает указанное число символов с начала строки |
LEN |
возвращает длину строки |
LOWER |
переводит все символы строки в нижний регистр |
LTRIM |
удаляет пробелы в начале строки |
NCHAR |
возвращает по коду символ Unicode |
PATINDEX |
выполняет поиск подстроки в строке по указанному шаблону |
REPLACE |
заменяет вхождения подстроки на указанное значение |
REVERSE |
возвращает строку, символы которой записаны в обратном порядке |
RIGHT |
возвращает указанное число символов с конца строки |
RTRIM |
удаляет пробелы в конце строки |
SOUNDEX |
возвращает код звучания строки |
SPACE |
возвращает указанное число пробелов |
STR |
выполняет конвертирование значения числового типа в символьный формат |
STUFF |
удаляет указанное число символов, заменяя новой подстрокой |
SUBSTRING |
возвращает для строки подстроку указанной длины с заданного символа |
UPPER |
переводит все символы строки в верхний регистр |
Пример: Использование функции LEFT для получения инициалов студентов
select LEFT(nameSt,1)+'. '+LEFT(lnameSt,1)+'. '+sNameSt as ФИО
from Student
Функции для работы с датой и временем
DATEADD |
добавляет к дате указанное значение дней, месяцев, часов и т.д. |
DATEDIFF |
возвращает разницу между указанными частями двух дат |
DATENAME |
выделяет из даты указанную часть и возвращает ее в символьном формате |
DATEPART |
выделяет из даты указанную часть и возвращает ее в числовом формате |
DAY |
возвращает число из указанной даты |
GETDATE |
возвращает текущее системное время |
ISDATE |
проверяет правильность выражения на соответствие одному из возможных форматов ввода даты |
MONTH |
возвращает значение месяца из указанной даты |
YEAR |
возвращает значение года из указанной даты |
Пример: Выделение из даты рождения дня и месяца в строковом формате.
select LEFT(nameSt,1)+'. '+LEFT(lnameSt,1)+'. '+sNameSt as ФИО,
DATENAME(day,dateB)+' '+DATENAME(month,dateB)
from Student
Пользовательские функции очень похожи на хранимые процедуры. Так же в них можно передавать параметры и они выполняют некоторые действия, однако их главным отличием от хранимых процедур является то, что они выводят (возвращают) какой то результат. Более того, они вызываются только при помощи оператора SELECT, аналогично встроенным функциям. Все пользовательские функции делятся на 2 вида:
-
Скалярные функции - функции, которые возвращают число или текст, то есть одно или несколько значений;
-
Табличные функции - функции, которые выводят результат в виде таблицы.
Для создания новой пользовательской функции используется команда CREATE FUNCTION имеющая следующий синтаксис:
CREATE FUNCTION <Имя функции>
([@<Параметр1> <Тип1>[=<Значение1>],
@<Параметр2> <Тип2>[=<Значение2>], . . .])
RETURNS <Тип>/TABLE
AS
RETURN([<Команды SQL>])
Здесь:
-
Имя функции - имя создаваемой пользовательской функции.
-
Параметр1, Параметр2, . . - параметры передаваемые в функцию.
-
Значение1, Значение2, … - значения параметров по умолчанию.
-
Тип1, Тип2, . . . - типы данных параметров.
После служебного слова RETURNS в скалярных функциях ставится тип данных результата, который возвращает скалярная функция, либо ставится служебное слово TABLE в табличных функциях.
После служебного слова RETURN ставится SQL команда самой функции.
Замечание: После служебного слова RETURN может быть несколько команд, которые располагаются между словами BEGIN и END. В этом случае служебное слово RETURN не ставится.
Замечание: Тип данных параметра должен совпадать с типом данных выражения, в котором он используется.
Замечание: Если используются несколько SQL команд и BEGIN и END, то перед END нужно ставить команду RETURN <результат функции>.
Пример (скалярная пользовательская функция): Функция для вычисления возраста:
create function Age
(@date datetime)
returns varchar(10)
as
begin
declare @a varchar(10)
select @a=DATEDIFF(year, @date, getdate())
return @a
end
Использование созданной функции:
select LEFT(nameSt,1)+'. '+LEFT(lnameSt,1)+'. '+sNameSt as ФИО,
dbo.Age(dateB)+' лет' as Возраст
from Student
Представленная выше пользовательская функция реализована при помощи нескольких команд SQL, но ее можно реализовать при помощи одной строки следующим образом:
create function Age1
(@date datetime)
returns varchar(10)
as
begin
return DATEDIFF(year, @date, getdate())
end
Пример (табличная пользовательская функция): Из таблицы Студенты выводятся поля ИД студента, возраст, который вычисляется как разница дат в годах, между датой рождения и текущей датой (процедура без параметров).
create function AgeStud
()
returns table
as
return( select idSt, DATEDIFF(year, dateB, getdate()) as Age from Student)
Использование функции:
select LEFT(nameSt,1)+'. '+LEFT(lnameSt,1)+'. '+sNameSt as ФИО, Age
from Student join AgeStud() on (student.idSt=AgeStud.idSt)