Лекция № 9

Тема: "Основы программирования СУБД. Функции. Пользовательские функции"

План

1. Основы программирование в разных СУБД

2. Строковые функции в разных СУБД

3. Функции даты и времени в разных СУБД

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

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

1. Основы программирование в разных СУБД

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

Чтобы выполнить такие операции, приложение отсылает на сервер одну или более команд, которые там выполняются. В ответ сервер отправляет клиенту (приложению) результат обработки запроса. Этим результатом может быть как сообщение об успешном завершении выполнения команды, которое занимает всего несколько байт, так и огромный набор данных, включая тысячи строк, занимающих несколько мегабайт. Клиент может обработать полученный результат и на базе этого результата отправить серверу новый запрос.

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

Предложенный подход к обработке данных имеет несколько недостатков. Например, если нужно изменить логику обработки данных, то следует менять исходный код программы, после чего заново компилировать ее и распространять среди пользователей. Кроме того, если та же логика обработки данных используется в нескольких приложениях, то в худшем случае для каждого из этих приложений нужно будет повторять процесс разработки запросов, а в лучшем переносить код с уже работающего приложения. Также следует обратить внимание на сам процесс взаимодействия сервера и клиента. Как видно, алгоритмы обработки данных будут реализованы в виде набора блоков команд, которые отправляются на сервер по очереди. После выполнения блока приложение получает определенный результат, после обработки которого решается, какой следующий блок и с какими параметрами должен быть выполнен.

Также необходимо сказать о безопасности. Для выполнения обработки данных пользователь должен иметь соответствующие права доступа. Предполагается, что эти права будут использованы приложением для доступа к данным. Однако нельзя быть до конца уверенным, что пользователь не сможет обратиться к данным напрямую  и выполнить недопустимые действия. Также нельзя быть уверенным, что команды, отправляемые приложением, осуществляют верные действия. Ошибка разработчика при создании запроса может иногда привести к повреждению данных. Кроме того, нельзя не учитывать, что злоумышленник или тот же разработчик способен изменить код запроса для получения несанкционированного доступа к данным или для их повреждения и даже уничтожения.

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

Для создания таких фрагментов кода, язык SQL поддерживает основы программирования. При этом в разных СУБД язык программирования имеет свои особенности, которые мы и рассмотрим.

Основные элементы программирования в SQL Server

Работа с переменными

 Прежде чем начать использовать переменную, ее необходимо создать - объявить. При объявлении переменной указывается имя и ее тип данных. Для объявления переменной предназначена команда:

declare @имя_пер1 тип, @имя_пер2 тип, ...

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

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

С помощью одной команды DECLARE можно описать несколько переменных, перечислив их через запятую:

declare @a nvarchar (20), @b deciamal (5,2), @c smalldatetime

Если есть несколько переменных одного типа, их все равно описывают отдельно.

declare @d int, @e int

Для присваивания значения переенной можно использовать различные способы. Самый простой из них - применение команды SET. Однако с помощью этой команды можно присвоить значение только одной переменной. Для присвоения значений нескольким переменным необходимо выполнить соответствующее количество команд SET:

set @a = 'Федоров'

set @b = 1.5

set @c = '05.12.1969'

Значения переменным можно присваивать с помощью команды SELECT, позволяет одновременно работать с несколькими переменными:

select @a = 'Федоров', @b = 1.5, @ c = '05.12.1969'

Переменным можно присваивать результаты запросов, если запрос возвращает одно значение. Например, в переменную записать средний оклад всех сотрудников.

declare @sred decimal (5,2)

set @ sred = (select avg(oklad) from sotrudniki)

Если запрос возвращает несколько значений, то их можно присвоить разным переменным в самом запросе. Например, в переменные сохраним количество сотрудников, минимальный и средний оклад сотрудников.

declare @kol int, @minoklad decimal(5,2), @sredoklad decimal (5,2)

select @kol=count(*), @minoklad=min(oklad), @sredoklad=avg(oklad) from sotrudniki

Для вывода значения одной переменной используют команду

print @переменная

Чтобы вывести значения рассчитанных выше переменных можно использовать код:

print @kol

print @minoklad

print @sredoklad

Для вывода значений нескольких переменных можно использоать команду:

select @пер1, @пер2, ..., @перN

Чтобы вывести значения рассчитанных выше переменных в одной строке можно использовать код:

select @kol, @minoklad, @sredoklad

Для задания комментария вначале строки указывают смиволы -- (два минуса).

Работа с временными таблицами

 Переменные используются для хранения отдельных значений. Для временного хранения больших объемов информации в SQL Server предназначены временные таблицы.

Временные таблицы бывают двух типов: глобальные и локальные. Независимо от типа временной таблицы, они всегда создаются в системной базе данных TempDB и автоматически уничтожаются при завершении работы.

Доступ к глобальной временной таблицы (global temporary table) может быть получен с любого соединения, независимо от того, в каком именно из них она была создана. Это предоставляет удобный интерфейс для обмена данными между различными копиями хранимых процедур. И сама хранимая процедура, которая запускается различными пользователями, может обращаться к глобальной временной таблицы для получения информации. Кроме того, глобальные временные таблицы могут быть использованы для обмена данными между различными приложениями.

Глобальная временная таблица видна из любого соединения, поэтому ее имя должно быть уникальным в пределах сервера. При этом не важно, какой пользователь конкретно создал эту временную таблицу и в контексте какой базы данных.

Для того чтобы создать глобальную временную таблицу, достаточно в начале ее имени при создании указать символы ##. Когда сервер встречает эти символы, он обращается к базе данных TempDB для поиска указанной таблицы. Работа с временными таблицами ничем не отличается от работы с обычными таблицами. Глобальная временная таблица существует до тех пор, пока пользователь явно не уничтожит ее с помощью команды DROP TABLE или пока не будет закрыто соединение, в котором она создавалась. Кроме того, глобальные и локальные временные таблицы также уничтожаются при остановке сервера.

Локальные временные таблицы (local temporary table) видимы только из того соединения, в котором они были созданы. При закрытии соединения такая таблица автоматически уничтожается. При следующем открытии соединения она должна быть создана заново. Кроме того, если локальная временная таблица была создана в хранимой процедуре, то после выхода из этой процедуры такая таблица также автоматически уничтожается. В различных соединениях могут создаваться временные локальные таблицы с одинаковыми именами.

Локальные временные таблицы часто используются в работе хранимых процедур. Создание глобальных временных таблиц или стандартных таблиц не позволит полностью контролировать содержание этих таблиц, потому что другая копия хранимой процедуры может удалить, изменить или добавить данные в таблицу. При использовании локальных временных таблиц такая проблема решается автоматически. Даже если в пределах одного соединения хранимая процедура будет вызвана несколько раз, для каждой копии будет создана отдельная таблица. При этом имена этих таблиц могут быть одинаковыми.

Для создания локальной временной таблицы необходимо перед именем таблицы при ее создании указать один символ «#». В дальнейшем для ссылки на созданную таблицу также необходимо в ее имени указывать символ «#».

Допускается создание временной таблицы с помощью конструкции into команды select.

Пример. Пусть на предприятии планируется повысить оклады на 20%. Нужно оценить, сколько людей будет получать более 1500. При этом сами данные в исходной таблице sotrudniki не менять.

Для решения этой задачи сначала отберем данные во временную таблицу, а затем во временной таблице обновим оклады и найдем количество сотрудников.

--копируем все записи в локальную временную таблицу #bufer

select * into #bufer from sotrudniki

--во временной таблице обновляем оклады

update #bufer set oklad = oklad + oklad * 0.2

--выводим нужное количество в колоке kol

select count(*) as kol from #bufer where oklad> 1500

Управляющие конструкции и команды

begin ... end

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

Допускается создание вложенных конструкций begin ... end. SQL Server практически не ограничивает глубину вложенности конструкций. Однако на практике вложенность обычно не превышает 5-7 уровней.

if ... else

С помощью этой конструкции в SQL реализуется разветвления алгоритмов. Типичным примером такого разветвления является выполнение набора команд в зависимости от соблюдения какого-то условия.

Синтаксис конструкции IF ... ELSE следующий:

if условие

  действие

[else

  действие]

Если нужно выполнить блок команд, то необходимо воспользоваться конструкцией begin ... end, как это было описано ранее.

Если нужно проверить несколько условий, то конструкция записывается так:

if условие

  действие

[Else if условие

  действие

[Else действие]]

while ...

С помощью этой команды SQL организует цикл. Сразу отметим, что в SQL Server есть всего один тип цикла - с предусловием, который и реализуется с помощью данной команды.

Синтаксис команды while:

while условие

действие

[Break]

С помощью параметра условие указывается логическое условие. Цикл будет выполняться до тех пор, пока условие возвращает значение true. Предполагается, что в условии участвуют функции или переменные, значения которых изменяются в ходе выполнения цикла. Выход из такого цикла может осуществляться принудительно с помощью команды break, которая должна исполниться в теле цикла (обычно опять же в зависимости от логического условия).

Если в цикле необходимо указать более одной команды, то потребуется использовать конструкцию begin ... end.

case

Данная функция используется как альтернатива оператора if и используется в случае множественного выбора, когда нужно принять решение по выполнению кода на основе множества условий.

Функция имеет вид:

case when условие1 then действие

  when условие2 then действие

  . . .

  when условиеN then действие

  [else действие]

end

use

Все команды выполняются в контексте какой-то базы данных. Такая база данных называется текущей. При обращении к объектам текущей базы данных не требуется указание ее имени. Например, если текущей является база данных pubs, то обращение к таблице authors этой базы данных можно осуществить следующим образом:

select * from authors

Но когда работа ведется с несколькими базами данных, для перехода между ними используют команду:

use имя_БД

Например, пусть текущей является БД Catalog. необходимо отобрать данные из таблицы authors, расположенной в БД pubs.

use pubs

select * from authors

go

Клиент отправляет серверу команды на выполнение так называемыми пакетами (batch). После срабатывания всех команд пакета (или в ходе выполнения пакета) сервер возвращает клиенту результат. После этого клиент может отправить следующий пакет. В качестве пакета понимается набор команд, которые пользователь запускает на выполнение. Однако некоторые команды не могут запускаться в пакетах и ​​должны выполняться отдельно от других команд. К таким команд можно отнести команды create, с помощью которых выполняется создание объектов базы данных, команды васкup и restore, предназначенные, соответственно, для выполнения создания и восстановления резервных копий баз данных, а также некоторые другие команды. Для разделения кода SQL на пакеты нужно после каждого пакета вставлять ключевое слово go.

Например: набор команд:

create database dekan

create table stud . . .

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

create database dekan

go

create table stud . . .

Основные элементы программирования в  в MySQL

Программирование в MySQL имеет несколько отличий от SQL Server.

Имена переменных должны начинаться с символа @, но не требуется их предварительное описание. Тип переменной определяется автоматически при присваивании ей определенного значения.

Каждая команда заканчивается точкой с запятой.

Для задания комментария вначале строки указывают символы "##" или "--пробел". Например:

##комментарий

-- комментарий

Для присваивания значения переменной используют команду:

set @переменная = значение;

Допускается присваивание нескольким переменным значений в одной строке:

set @перем1 = значение, @перем2 = значение,. . . , @перемN = значение;

Если запрос рассчитывает некоторое значение, то результат такого запроса можно записать в переменную с помощью команды:

select функция into @переменная from таблица;

Например, найдем количество студентов:

select count(*) into @kol from students;

Можно одним запросом рассчитать несколько значений:

select функция1, функция2,. . . , ФункцияN into перем1, перем2 ..., перемN from таблица;

Например, найдем количество студентов, минимальный и средний балл студентов:

select count(*), min(srbal), avg(srbal) into @kol, @minbal, @sredbal from students;

В коде допускается использование операторных скобок begin...end.

Оператор условия имеет вид:

if (условие) then

    действие;

elseif

    действие;

else

    действие;

end if;

Оператор цикла имеет вид:

while (условие) do

    действия;

end while;

Оператор case имеет вид:

case (выражение)

    when значение1 then действие;

    when значение2 then действие;

    . . .

    else действие;

end case;

Другая форма записи оператора case:

case

  when условие1 then действие1;

  when условие2 then действие2;

  . . .

  else действие;

end;

 Для подключения нужной БД используют команду

use имя_БД;

Работа с временными таблицами

Временные таблицы в MySQL играют такую же роль, что и в SQL Server. При этом в этой СУБД временные таблицы могут быть только локальными. Имя таблицы не нужно начинать с символа #.

Для создания временной таблицы используют команду вида:

create temporary table имя_временной таблицы

select_запрос_для_создания_временной_таблицы

Пример. Пусть на предприятии планируется повысить оклады на 20%. Нужно оценить, сколько людей будет получать более 1500. При этом сами данные в исходной таблице sotrudniki не менять.

Для решения этой задачи сначала отберем данные во временную таблицу, а затем во временной таблице обновим оклады и найдем количество сотрудников.

#копируем все записи в локальную временную таблицу bufer

create temporary table bufer

select * from sotrudniki;

#во временной таблице обновляем оклады

update bufer set oklad = oklad + oklad * 0.2;

#выводим нужное количество в колонке kol

select count(*) as kol from bufer where oklad> 1500;

2. Строковые функции в разных СУБД

Каждая СУБД имеет большой перечень встроенных функций, которые значительно облегчают написание запросов.

Однако такие функции не являются стандартом языка SQL, поэтому количество и синтаксис таких функций у разных СУБД может значительно отличаться.

Строковые функции выполняют различные операции над данными строковых типов.

Функции SQL Server

В данной СУБД существует большое количество строковых функций. Рассмотрим основные из них:

charindex(s1, s2, [pos]) – позволяет определить номер позиции, с которой в тексте s2 встречается текст s1. Если pos не указан, то поиск выполняется с начала текста. В противном случае с позиции pos. Если вхождений нет, то функция равна 0.

Пример.

charindex('а','Информатика') = 7

charindex('а','Информатика',8) = 11

charindex('е','Информатика') = 0

left(s, n) – выделить n символов в тексте s слева

Пример.

left('Информатика', 4) = Инфо

len(s) – количество символов в тексте s

Пример.

len('Информатика') = 11

lower(s) – делает все буквы строчными (маленькими)

Пример.

lower('Информатика') = информатика

ltrim(s) – удалить из текста s начальные пробелы

Пример.

ltrim(' Информатика') = Информатика

replace(s, s1, s2) - в s заменить кусок s1 на s2

Пример.

replace ('Информатика', 'тика', 'ция') = Информация

reverse(s) – записывает текст в обратном порядке

Пример.

reverse('Информатика') = акитамрофнИ

right(s, n) – выделяет в тексте n последних символов

Пример.

right('Информатика', 4) = тика

rtrim(s) – удаляет из текста конечные пробелы

Пример.

rtrim('Информатика ') = Информатика

str(число) – конвертировать число в текст

stuff(s, pos, n, s1) – в тексте s начиная с позиции pos заменит n символов на текст s1

Пример.

stuff('Информатика', 1, 5, 'Мате') = Математика

substring(s, pos, n) – выделяет из текста n символов, начиная с pos.

Пример.

substring('Информатика', 3, 5) = форма

upper(s) – делает все буквы прописными (большими)

Пример.

upper('Информатика') = ИНФОРМАТИКА

Пример. С помощью запроса в поле fam внести изменения: удалить начальные и конечный пробелы, первую букву сделать прописной, остальные строчными.

Update students

Set fam=upper(ltrim(left(fam, 1))) + lower(rtrim(right(fam, len(ltrim(rtrim(fam)))-1)))

Функции MySQL

char_length(s) – определяет длину текста

Пример.

char_length('Информатика') = 11

concat(s1, s2, …, sn) – объединяет (суммирует) тексты

Пример.

concat('Доброе', ' ', 'утро') = Доброе утро

insert(s, pos, n, s1) – тексте s начиная с позиции pos заменит n символов на текст s1

Пример.

insert('Информатика', 1, 5, 'Мате') = Математика

left(s, n) – выделить n символов в тексте s слева

Пример.

left('Информатика', 4) = Инфо

locate(s1, s2, [pos]) – позволяет определить номер позиции, с которой в тексте s2 встречается текст s1. Если pos не указан, то поиск выполняется с начала текста. В противном случае с позиции pos. Если вхождений нет, то функция равна 0.

Пример.

locate('а','Информатика') = 7

locate('а','Информатика',8) = 11

locate('е','Информатика') = 0

lower(s) – делает все буквы строчными (маленькими)

Пример.

lower('Информатика') = информатика

ltrim(s) - удалить из текста s начальные пробелы

Пример.

ltrim(' Информатика') = Информатика

replace(s, s1, s2) - в s заменить кусок s1 на s2

Пример.

replace ('Информатика', 'тика', 'ция') = Информация

reverse(s) - записывает текст в обратном порядке

Пример.

reverse('Информатика') = акитамрофнИ

right(s, n) - выделяет в тексте n последних символов

Пример.

right('Информатика', 4) = тика

rtrim(s) - – удаляет из текста конечные пробелы

Пример.

rtrim('Информатика ') = Информатика

substring(s, pos, [n]) – выделяет из текста n символов, начиная с pos. Если параметр n не указан, то выделяется текст до конца строки.

Пример.

substring('Информатика', 3, 5) = форма

substring('Информатика', 3) = форматика

trim(s) – удаляет пробелы вначале и конце текста

Пример.

trim(' Информатика ') = Информатика

upper(s) - делает все буквы прописными (большими)

Пример.

upper('Информатика') = ИНФОРМАТИКА

Функции SQLite

instr(s1, s2) - позволяет определить номер позиции, с которой в тексте s1 встречается текст s2. Если вхождений нет, то функция равна 0.

Пример.

instr('Информатика','а') = 7

instr('Информатика','е') = 0

length(s) – длина строки

lower(s) – записывает все символы строчными

ltrim(s) – удаляет начальные пробелы

replace (s, s1, s2) - в s заменить фрагмент s1 на фрагмент s2

rtrim(s) – удаляет все конечные пробелы

substr(s, pos, n) – выделяет из текста n символов, начиная с позиции pos

Пример.

substr('Информатика', 3, 5) = форма

upper(s) - делает все буквы прописными (большими)

Замечание. При работе в SQLite с русским текстом в кодировке UTF8 часто возникают проблемы с использованием строковых функций. Для их решения необходимо настроить параметры утилиты SQLite Expert. Выполните команду меню "Tools - Options". В окне настроек в левой части выберите строку "Data". В правой части внизу выберите второй переключатель "sqlite3.dll (version 3.8.7.1) supports FTS3, FTS4, RTREE, ICU". После этого все строковые функции будут корректно работать с русским текстом в кодировке UTF8.

3. Функции даты и времени в разных СУБД

Данные функции значительно упрощают работу с данными даты или времени при написании запросов.

Функции SQL Server

getdate() – позволяет получить текущую дату и время, установленные на системных часахъ сервера.

datepart (часть, дата) – выделяет заданную часть даты. Часть обозначается с помощью текстовых обозначений: 'year', 'month', 'week', 'day', 'hour', 'minute', 'second'.

Пример.

datepart (year, '18.02.2013') = 2013

datepart (month, '18.02.2013') = 2

datepart (day, '18.02.2013') = 18

day(дата) - выделяет номер дня

Пример.

day('18.02.2013') = 18

hour(время) – выделяет часы из времени

Пример.

hour('14:25:30') = 14

minute(время) – выделяет минуты из времени

Пример.

minute('14:25:30') = 25

month(дата) – выделяет номер месяца

Пример.

month('18.02.2013') = 2

second(время) – выделяет секунды из времени

Пример.

second('14:25:30') = 30

year(дата) – выделяет номер года

Пример.

year('18.02.2013') = 2013

datediff( часть, дата1, дата2) – вычитание частей двух дат и получение разницы в виде числа

Пример.

datediff (day, '18.02.2013', '25.02.2013') = 7

datediff (month, '18.02.2013', '25.02.2013') = 0

dateadd(часть, число, дата) – добавляет в дате число частей

Пример.

dateadd(day, 5, '18.02.2013') = 23.02.2013

dateadd(month, 5, '18.02.2013') = 18.07.2013

dateadd(year, 5, '18.02.2013') = 18.02.2018

Пример. Вывести список студентов, родившихся сегодня

Select fam, imya

From students

Where day(datar)=day(getdate())

      and month(datar)=month(getdate())

Функции MySQL

adddate(дата, количество дней) – суммирует к дате количество дней

Пример.

adddate('2013-02-18', 5) = 2013-02-23

addtime(время1, время2) – суммирует время

Пример.

addtime('12:20:00', '2:10:00') = 14:30:00

curdate() – генерирует текущую дату

curtime() – генерирует текущее время

datediff(дата1, дата2) – вычитает даты (порядок дат неважен). Результат - количество дней

Пример.

datediff('2013-02-23', '2013-02-18') = 5

day(дата) - выделяет номер дня

Пример.

day('2013-02-18') = 18

hour(время) – выделяет часы из времени

Пример.

hour('14:25:30') = 14

minute(время) – выделяет минуты из времени

Пример.

minute('14:25:30') = 25

month(дата) – выделяет номер месяца

Пример.

month('2013-02-18) = 2

second(время) – выделяет секунды из времени

Пример.

second('14:25:30') = 30

timediff (время1, время2) – вычитает время (порядок времен не важен). Результат - значение времени.

Пример.

timediff('14:15:00', '14:05:00') = 00:10:00

year(дата) – выделяет номер года

Пример.

year('2013-02-18') = 2013

Пример. Вывести список студентов, родившихся сегодня

Select fam, imya

From students

Where day(datar)=day(curdate())

      and month(datar)=month(curdate())

Функции SQLite

date('дата', [модификатор1], [модификатор2], …)– генерирует дату

time('дата', [модификатор1], [модификатор2], …)– генерирует время

datetime('дата', [модификатор1], [модификатор2],…)– генерирует сразу и дату и время

strftime('формат', 'дата', [модификатор1], [модификатор2],…)– позволяет выделить часть даты

Параметр 'дата' может содержать текстовую константу 'now' для получения текущей даты и времени.

Параметр 'формат' может содержать следующие специальные символы:

- %d – номер дня

- %m – номер месяца

- %Y – номер года

- %H – часы

- %M – минуты

- %S – секунды

Параметр 'модификатор' может содержать следующие текстовые константы:

± n days

± n hours

± n minutes

± n seconds

± n months

± n years

start of month

start of year

start of day

weekday N

Пример.

date('now') = текущая дата

time('now') = текущее время

strftime('%Y', date('now') = текущий год

date('2013-02-18', '+5 days') = 2013-02-23

date('2013-02-18', '-1 month', '+5 days') = 2013-01-25

Пример. Вывести список студентов, родившихся сегодня

Select fam, imya

From students

Where strftime('%d', datar)=strftime('%d', date('now'))

      and strftime('%m', datar)=strftime('%m', date('now'))

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

Для упрощения разработки программных кодов пользователь может создавать свои собственные функции, которые являются ничем иным, как обычными объектами базы данных. Такие функции являются аналогом подпрограмм, которые можно повторно использовать в других программных кодах БД.

Работа с пользовательскими функциями в SQL Server

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

- в запросах, например SELECT;

Функция, возвражающая одно значение

Для создания функций, возвращающей одно значение, предназначен следующий синтаксис команды:

Create Function имя_функции

([@парам1 тип = [знач. по умолчанию],

@парам2 тип = [знач. по умолчанию], …,

@парамN тип = [знач. по умолчанию])

    Returns тип_результата

    [With параметры] 

Begin

    тело_функции

    Return выражение

End

Рассмотрим назначение и использование параметров команды:

Имя_функции. Данный параметр предназначен для указания имени создаваемой функции.

@парам1 тип = знач.по умолчанию. Посредством этой конструкции определяются входные параметры создаваемой функции. Имена параметров функции должны начинаться с символа @ и быть уникальными в пределах функции. В теле функции параметр может использоваться как обычная переменная. Имя параметра указывается с помощью аргумента @ПАРАМ. Через пробел после имени необходимо задать тип данных, который будет иметь параметр. Для этого предназначен аргумент ТИП. Если для параметра указано значение по умолчанию, то его можно не указывать при вызове функции. Если параметры не нужны, то указывают пустые скобки.

Returns тип_результата. Ключевое слово Returns свидетельствует, что далее следует имя типа данных, значение которого будет возвращать функция. Опять же разрешается использование только скалярных типов данных.

With параметры. Использование данного аргумента необходимо в случаях, когда требуется указать дополнительные опции, с применением которых должна быть создана функция. Параметры могут принимать одно из значений:

• ENCRYPTION. Предписывает серверу выполнить шифрование кода функции, с помощью которой была создана функция. Это позволяет скрыть от пользователей принципы работы функции.
• SCHEMABINDING. Сервер выполняет связывание создаваемой функции со всеми объектами, на которые ссылаются команды функции. Это позволяет избежать модификации этих объектов, которая может привести к нарушению работы функции (например, удаление столбца таблицы, из которого осуществлялась выборка данных).

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

Return выражение. Когда в теле функции встречается команда Return, то сервер завершает выполнение функции. При этом результатом работы функции будет являться значение, полученное в результате вычисления выражения . Этот результат должен иметь тип данных, указанный после ключевого слова Returns. При работе с функцией выход из нее происходит только при выполнении команды Return. В принципе, в теле функции может присутствовать более одной команды Return, каждая из которых обрабатывается в той или иной ситуации.

Пример. Создать функцию для нахождения среднего оклада по предприятию.

Create Function Sred()

    Returns decimal(5,2)

Begin

    Return (Select Avg(oklad) From sotrudniki)

End

имя_функции (список параметров)

имя_функции ()

Print 'Средний оклад равен:'+Str(Sred())

Select fam, imya, otch From sotrudniki Where oklad>Sred()

Create Function SredByPol(@pol Varchar(1))

    Returns decimal(5,2)

Begin

    Return (Select Avg(oklad) From sotrudniki Where pol=@pol)

End

Print 'Разница равна'+Str(Abs(SredByPol('М')-SredByPol('Ж')))

Create Function SredByPol2(@key int)

    Returns decimal(5,2)

Begin

    if @key=1

        Return (Select Avg(oklad) From sotrudniki Where pol='М')

    else if @key=2

        Return (Select Avg(oklad) From sotrudniki Where pol='Ж')

End

Print 'Разница равна'+Str(Abs(SredByPol2(1)-SredByPol2(2)))

Функция, возвражающая набор данных

Для создания функций, возвращающей одно значение, предназначен следующий синтаксис команды:

Create Function имя_функции

([@парам1 тип = [знач. по умолчанию],

@парам2 тип = [знач. по умолчанию], …,

@парамN тип = [знач. по умолчанию])

    Returns Table

    [With параметры] 

As

    Return (запрос_Select)

Параметры функции имеют такое же значение, что и рассмотренные выше. ОБратите внимание, что тип возвращаемого выражения всегда указывается как таблица (Returns Table). В теле функции нет операторных скобок и может указываться только один запрос Select, результат которого вернет функция.

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

Select * From dbo.имя_функции(параметры)

Пример. Создать функцию, которая вернет список всех студентов.

Create Function StudList()

    Returns Table

as

    Return (select * From stud)

Используя функцию, выведем ее данные на экран.

Select * From StudList()

Пример. Изменим предыдущую функцию. Выведем список студентов с фамилией на заданную букву. Заданная буква - это входной параметр функции.

Create Function StudListFam(@fam varchar(15))

    Returns Table

as

    Return (Select * From stud Where fam like @fam+'%')

Используя функцию, выведем на экран студентов с фамилией на букву 'А'

Select * From StudListFam('А')

Изменение  и удаление функции

Для внесения изменения в функцию вместо ключевого слова Create укажите слово Alter и задайте нужные параметры.

Для удаления функции используйте команду

Drop Function имя_функции

Работа с функциями в "Среде SQL Server Management Studio"

Для создания функции откройте в контекстном меню папки Прогрммирование - Функции выберите команду Создать и в меню выберите тип создаваемой функции. Открется окно с шаблоном, в который нужно внести изменения и щелкнуть на кнопке Выполнить. Функция появится в папке, соответсвующей типу данной функции (скалярная или возвращающая таблицу).

Для изменения функции в ее контекстном меню выберите команду Изменить.

Для удаления функции в ее контекстном меню выберите команду Удалить. Если удаление не происходит, то в контестном меню функции выберите команду "Просмотреть зависимости" и определите, каие объекты зависят от удаляемой функции.

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

В СУБД MySQL можно создавать только скалярную (возвращающую одно значение) функцию.  При этом команда создания функции имеет вид:

  парамN тип])

select имя_функции(список параметров)

Пример. Создать функцию для нахождения среднего оклада по предприятию.

Create Function Sred()

Returns decimal(5,2)

Begin

    Return (select avg(oklad) from sotrudniki);

End;

select concat('Средний оклад равен:',' ',Sred());

select fam, imya, otch from sotrudniki Where oklad>Sred();

Create Function SredByPol(p varchar(1))

Returns decimal(5,2)

Begin

    Return (select Avg(ocenka) From ocenki Where pol=p);

End;

select concat('Разница равна',' ',Abs(SredByPol('М')-SredByPol('Ж')));

Create Function SredByPol2 (k integer)

Returns decimal(5,2)

Begin

    if k=1 then

        Return(select Avg(oklad) From sotrudniki Where pol='М');

    elseif k=2 then

        Return (select Avg(oklad) From sotrudniki Where pol='Ж');

    end if;

End;

select concat('Разница равна',' ',Abs(SredByPol2(1)-SredByPol2(2)));

Изменение и удаление функции выполняется аналогично SQL Server.

Работа с функциями в "SQL Manager for MySQL"

Для создания функции в контекстном меню папки Функции выберите команду "Новый объект: Функция". Появится окно, в котором введите имя функции, укажите тип возвращаемого результата, задайте список параметров, введите текст тела функции. После задания всех параметров щелкните на команде Выполнить.

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

Для удаления функции в контекстном меню функции выберите команду "Удалить функцию".

Вопросы для самоконтроля.

1. Как описать переменные в SQL Server? Как присвоить переменным значения и вывести их на экран?

2. Для чего используют временные таблицы в SQL Server? Какие типы временнх таблиц существую? Как задать им имена?

3. Как описать переменные в MySQL? Как присвоить переменным значения и вывести их на экран?

4. Как создать и удалить временную таблицу в MySQL?

5. Приведите 5 строковых функций SQL Server.

6. Приведите 5 строковых функций MySQL.

7. Приведите 5 функций для работы с датой/временем в SQL Server.

8. Приведите 5 функций для работы с датой/временем в MySQL.

9. Приведите команду создания скалярной функции в SQL Server.

10. Приведите команду создания скалярной функции в MySQL.