- •Кафедра «Информационные технологии»
- •Основы языка 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’ создается с помощью оператора
Созданная функция может вызываться с помощью оператора SELECT, например:
SELECT * FROM GetPrice()
или
SELECT * FROM GetPrice() WHERE Количество=1
(в последнем случае вывод результатов работы функции, ограничивается предложением WHERE).
73
Пример 3. Создание и применение функции табличного типа для определения двух
наименований товара с наибольшим остатком.
CREATE FUNCTION user1.itog()
RETURNS TABLE
AS
RETURN (SELECT TOP 2 Товар.Название FROM Товар INNER JOIN Склад
ON Товар.КодТовара=Склад.КодТовара ORDER BY Склад.Остаток DESC)
74
Использовать функцию для получения
двух наименований товара с наибольшим
остатком можно следующим образом:
SELECT Название FROM user1.itog()
75
8.2.3. Табличные функции с множеством инструкций (функции Multi-statement)
Данные функции, как и функции Inline, возвращают значение табличного типа.
Но в отличие от функций Inline, при создании
функции Multi-statement необходимо явно задать структуру возвращаемого значения. Эта структура указывается сразу после ключевого слова TABLE и, таким образом, является частью определения возвращаемого типа данных. При этом используется блок операторов BEGIN76– END.
Общий синтаксис функции Multistatement:
CREATE FUNCTION [имя_схемы.] имя_функции ( [ { @имя_параметра [ AS ] [имя_схемы.]
тип_данных_параметра [ = значение_параметра_по_умолчанию ] } [, …n] ] )
RETURNS @возвращаемая_переменная
TABLE <определение_табличного_типа_ данных> [ WITH <function_option> [, …n] ]
[ AS ]
BEGIN
тело_функции |
|
RETURN |
|
END |
77 |
Элементы синтаксиса соответствуют приведенным для скалярных функций.
Синтаксис конструкции
<определение_табличного_типа_данных> :
<определение_табличного_типа_ данных> ::= ({<определение_столбца> | <ограничение_таблицы>} [, …n] )
где элементы <определение_столбца>, <ограничение_таблицы> полностью соответствуют аналогичным элементам,
используемым при создании таблиц с помощью
команды CREATE TABLE. |
78 |
|
Набор данных, возвращаемых функцией,
должен формироваться с помощью команды
INSERT, выполняемой в теле функции.
В теле функции допускаются различные конструкции T-SQL, контролирующие значения, размещаемые в наборе записей.
При работе с командой INSERT явно указывается имя объекта, в который вставляются строки. Это имя указывается с помощью табличной переменной с именем возвращаемая_переменная.
79
Работа функции завершается при наличии ошибок выполнения или когда в коде
встречается ключевое слово RETURN.
В отличие от скалярных функций при
использовании команды RETURN не нужно
указывать значение, которое должно быть
возвращено. Сервер автоматически возвратит
набор данных табличного типа, имя и структура
которого указана после ключевого слова
RETURNS.
80
Функции Multi-statement сочетают свойства:
•скалярных функций - содержать сложный программный код;
•функций Inline - возвращать табличный результат.
Эти функции: 1) создают табличную переменную и заполняют ее в теле функции; 2) возвращают таблицу- результат, которая может использоваться в инструкциях SELECT.
Преимущество функций Multi-statement - возможность формировать результат с помощью нескольких инструкций SQL (пакета инструкций), а затем напрямую использовать этот набор данных в
инструкциях SELECT.
81
Пример 1. Функция Multi-statement, которая выводит сведения о студентах (фамилия, имя) по номеру группы.
CREATE FUNCTION Students(@Gr NCHAR(6)) RETURNS @report
TABLE(Фамилия NCHAR(20), Имя NCHAR(20))
AS
BEGIN
INSERT INTO @report
SELECT Фамилия, Имя FROM Студенты WHERE (Студенты.Группа=@Gr)
RETURN END
Пример вызова этой функции:
SELECT * FROM Students('ИТ-31')82