- •Кафедра «Информационные технологии»
- •Основы языка Transact-SQL
- •7. Переменные
- •При объявлении локальной переменной указывается:
- •Кроме локальных переменных, символ @ также используется при определении имен аргументов хранимых процедур
- •Примеры объявления переменных:
- •Значения переменным можно присваивать с
- •Заданные значения переменных можно вывести на экран с помощью команды SELECT:
- •Второй способ присвоения переменной значения - использование команды SELECT:
- •С помощью команды SELECT
- •Для вывода значений переменных можно
- •Примеры использования команд SELECT, PRINT для вывода значений переменных:
- •Результат обработки - вывод:
- •8. Функции
- •8.1. Встроенные функции
- •Математические функции. Большинство этих функций возвращают результат того же типа, что и исходное
- •Рассмотрим некоторые часто используемые математические функции и примеры их
- •Функция ISNUMERIC проверяет, имеет ли
- •Функция SIGN определяет знак числа. Если
- •Функции FLOOR, CEILING выполняют округление до целых вниз, вверх соответственно.
- •Функция POWER возводит число (первый аргумент) в степень (второй аргумент):
- •Основные математические функции
- •Строковые функции - используются для работы
- •Функции LTRIM, RTRIM удаляют пробелы в начале, конце строки соответственно. При вводе текстовых
- •Функции LOWER, UPPER переводят все символы строки в нижний, верхний регистр соответственно.
- •Функция SUBSTRING возвращает для строки подстроку указанной длины, начиная с заданного символа.
- •Основные строковые функции
- •Функции для работы с датой и временем
- •Примеры функций для работы с датой и временем
- •DAY, MONTH, YEAR - возвращают число, месяц, год (соответственно) из указанной даты.
- •DATEDIFF(datepart, startdate, enddate)
- •Функции даты и времени Transact-SQL
- •Примеры использования функций для работы с датой и временем
- •Простейший способ получить только дату, «обрезав» время, - использовать несколько функций:
- •Функции конфигурирования - возвращают
- •@@LANGUAGE – показывает, какой язык является текущим.
- •@@LOCK_TIMEOUT - возвращает количество миллисекунд, которое сервер будет ожидать выполнения запроса. Для каждого
- •@@MAX_CONNECTIONS - возвращает
- •@@VERSION - возвращает информацию о дате, версии и типе процессора локальной копии SQL
- •Функции системы безопасности
- •IS_SRVROLEMEMBER(''роль'' [, ''учетная запись''])
- •Функции управления метаданными
- •Статистические функции
- •@@IO_BUSY – возвращает количество миллисекунд, которое система SQL Server ожидала выполнения операций ввода-вывода
- •8.2. Пользовательские функции
- •Возможные преимущества пользовательских функций
- •Рассмотрим создание ПФ двух типов:
- •Tабличные функции подчиняются тем же
- •8.2.1. Cкалярные функции
- •CREATE FUNCTION [имя_схемы.] имя_функции
- •Назначение элементов синтаксиса:
- •В параметре [имя_схемы.] не допускается указание имени БД или сервера.
- •@имя_параметра – имя входного параметра (начинается с символа @), должно быть уникальным в
- •[, …n] – указывает на возможность задания многих параметров функции, используя синтаксис,
- •[AS] – после этого необязательного ключевого
- •RETURN скалярное_выражение
- •Пример 1. Рассмотрим создание функции с именем FunctionA и тремя входными параметрами:
- •Переменная @Var3 нужна, чтобы возвращать значение функции.
- •Примеры использования данной
- •Пример 2. Функция Goods для вычисления суммарного количества товара определенного типа. Имя владельца
- •Входной параметр - тип товара . Функция возвращает значение целого типа, полученное из
- •8.2.2. Внедренные табличные функции
- •CREATE FUNCTION [имя_схемы.] имя_функции
- •Основная часть параметров соответствует
- •Возвращаемое функцией значение table
- •Пример 1. Рассмотрим создание и
- •(функция содержит одну инструкцию SELECT после ключевого слова RETURN).
- •Пример 2. Функция, которая возвращает
- •Для примера создана функция без параметров, в
- •Созданная функция может вызываться с помощью оператора SELECT, например:
- •Пример 3. Создание и применение функции табличного типа для определения двух
- •Использовать функцию для получения
- •8.2.3. Табличные функции с множеством инструкций (функции Multi-statement)
- •Общий синтаксис функции Multistatement:
- •Элементы синтаксиса соответствуют приведенным для скалярных функций.
- •Набор данных, возвращаемых функцией,
- •Работа функции завершается при наличии ошибок выполнения или когда в коде
- •Функции Multi-statement сочетают свойства:
- •Пример 1. Функция Multi-statement, которая выводит сведения о студентах (фамилия, имя) по номеру
- •Пример 2. Функция Multi-statement, которая для некоторого сотрудника выводит список всех его
- •Пример данных в таблице emp_mgr.
- •CREATE FUNCTION List(@id_emp CHAR(2))
- •Использована системная функция @@ROWCOUNT, возвращающая количество строк, обработанных
- •Список подчиненных сотрудника ‘a’ создается с помощью оператора
IS_SRVROLEMEMBER(''роль'' [, ''учетная запись''])
С помощью этой функции можно проверить членство
указанной учетной записи в той или иной фиксированной роли сервера. Если пользователь состоит в указанной роли, то возвращается 1, в противном случае – 0.
Если имя роли или учетной записи не распознано, возвращается значение NULL. Если имя учетной записи не указано, то подразумевается текущий пользователь.
42
Функции управления метаданными
- позволяют получить информацию о метаданных объектов.
Например,
COL_LENGTH(''таблица'', ''столбец'')
– функция возвращает информацию о количестве
байт, отведенных в таблице для того или иного столбца.
Статистические функции
С помощью функций этого типа можно получить информацию о состоянии системы и работе сервера. Для получения данной
информации можно также использовать
системную хранимую процедуру sp_monitor.
Примеры таких функций:
@@CPU_BUSY – возвращает количество миллисекунд, которое затратил центральный процессор во время последнего запуска SQL
Server; |
44 |
@@IO_BUSY – возвращает количество миллисекунд, которое система SQL Server ожидала выполнения операций ввода-вывода во время последнего запуска;
@@TIMETICKS – возвращает количество микросекунд в одном такте процессора.
8.2. Пользовательские функции
Наряду с использованием встроенных функций,
пользователь может создавать пользовательские функции (ПФ), содержащие часто используемый код.
При реализации на языке T-SQL сложных и часто используемых алгоритмов, встает вопрос о сохранении разработанного кода для дальнейшего применения.
Эту задачу можно решать с помощью хранимых процедур (ХП), но их нельзя использовать
непосредственно в выражениях, т.к. ХП требуют промежуточного присвоения возвращенного значения
переменной, которая затем и указывается в выражении.
46
Возможные преимущества пользовательских функций
В отличие от ХП, ПФ позволяют вызов разработанных алгоритмов непосредственно в выражениях.
ПФ сходны с ХП, но, в отличие от них, могут применяться в запросах так же, как и системные
встроенные функции.
ПФ, возвращающие таблицы, могут стать
альтернативой просмотрам. Просмотры ограничены одним выражением SELECT, а ПФ могут включать
дополнительные выражения, что позволяет создавать
более сложные и мощные конструкции.
47
Рассмотрим создание ПФ двух типов:
•скалярные функции – возвращают скалярные значения;
•табличные функции – возвращают результат в виде таблицы, в свою очередь, делятся на
–внедренные (функции Inline);
–табличные функции с множеством инструкций (функции Multi-statement).
48
Tабличные функции подчиняются тем же
правилам, что и скалярные функции, но
возвращают результат в виде таблицы. Поэтому они в основном применяются
в предложении FROM инструкции SELECT, и их можно соединять с другими таблицами и представлениями.
Эти функции используют табличные переменные – особый тип переменных, служащий для временного хранения набора строк, - результата работы табличной функции.
49
8.2.1. Cкалярные функции
Cкалярные функции принимают 0 или
несколько входных параметров и возвращают
одно скалярное значение.
По использованию эти функции похожи на
функции, реализуемые в других языках
программирования. Для создания скалярной функции используется инструкция CREATE FUNCTION, имеющая следующий общий синтаксис:
50
CREATE FUNCTION [имя_схемы.] имя_функции
( [ { @имя_параметра
[AS] [имя_схемы.] тип_данных_параметра [ = значение_параметра_по_умолчанию] }
[, …n] ] )
RETURNS тип_возвращаемых_данных [ WITH <function_option> [, …n] ]
[ AS ]
BEGIN тело_функции
RETURN скалярное_выражение END [ ; ]
51
<function_option>::=
{ [ENCRYPTION] | [SCHEMABINDING] | [EXECUTE_AS_Clause] }
52