Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:

бд / Лекция 9 - Функции

.docx
Скачиваний:
23
Добавлен:
14.05.2015
Размер:
31.77 Кб
Скачать

Функции 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 вида:

  1. Скалярные функции - функции, которые возвращают число или текст, то есть одно или несколько значений;

  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)