Создание баз данных. Основы Transact SQL. Обработка ошибок. Управление транзакциями. Триггеры
Контрольная работа - Компьютеры, программирование
Другие контрольные работы по предмету Компьютеры, программирование
ра return - эта команда должна завершать скалярную функцию.
В скалярных пользовательских функциях не допускаются операции обновления базы данных, но в то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких типов, как text, image и ntext, равно как табличные переменные и курсоры.
Скалярные функции создаются, изменяются и удаляются с помощью тех же инструкций DDL, что и другие объекты, хотя синтаксис немного отличается, чтобы определить возвращаемое значение:FUNCTION имя_функции (входные_параметры) RETURNS тип_данных AS BEGIN текст_ функции RETURN выражение END
В списке входных параметров должны быть указаны типы данных и, в случае необходимости, значения по умолчанию, аналогично хранимым процедурам (параметр = умолчание). Параметры функции отличаются от параметров хранимых процедур тем, что даже если определены значения по умолчанию, параметры все равно должны присутствовать в вызове функции (т.е. параметры с определенными по умолчанию значениями все равно обязательны). Чтобы запросить значение по умолчанию при вызове функции, ей передается ключевое слово default.
Следующая скалярная функция выполняет простую арифметическую операцию; ее второй параметр имеет значение по умолчанию:
CREATE FUNCTION dbo.Multiply (@A int, @B int = 3)INT@A * @B
END
Скалярные функции могут использоваться в любом месте выражений, где допустимо одно значение. Пользовательские скалярные функции должны всегда вызываться с помощью двухкомпонентного имени (владелец.имя). В следующем примере продемонстрирован вызов ранее созданной функции Multiply:dbo.Multiply(3,4)dbo.Multiply(7, DEFAULT)
Следующий код создает функцию, возвращающую имя заданного клиента в формате Фамилия И.
CREATE FUNCTION getFICust (@IdCust int)varchar(25)@result varchar(25)@result = NULL@result = LName + + SUBSTRING(FName, 1, 1) + .CustomerIdCust = @IdCust@result
Тестирование созданной функции:dbo.getFICust(IdCust) AS CustNameCustomerBY LName, FName
Задание для самостоятельной работы: Создайте скалярные пользовательские функции, возвращающие:
Количество товара на складе по заданному уникальному идентификатору товара;
Суммарную стоимость товаров в заданном заказе.
Внедренные табличные функции
Второй тип пользовательских функций очень похож на представления. Оба обрамляют сохраняемую инструкцию SELECT. Внедренные табличные функции имеют все достоинства представлений, добавляя к ним использование параметров и возможность компиляции. Как и в представлениях, если инструкция SELECT обновляема, то обновляемой является и функция.
Внедренная табличная функция не имеет в своем теле блока BEGIN ... END - вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем:
CREATE FUNCTION имя_функции (параметры) RETURNS Table AS RETURN (инструкция_SELECT)
Следующая внедренная табличная функция является функциональным эквивалентом представления v_Customer созданного в лаб. занятии №6.
CREATE FUNCTION fCustomers ()TABLE
(Customer.IdCust, Customer.FName, Customer.LName, City.CityNameCustomer INNER JOINON Customer.IdCity = City.IdCity
)
Для извлечения данных с помощью функции fCustomers вызовите ее в предложении FROM инструкции SELECT:
SELECT *dbo.fCustomers()BY LName, FName
Одним из преимуществ внедренных табличных функций по сравнению с представлениями является возможность первых включать параметры в предварительно скомпилированные инструкции SELECT. Представления не могут иметь параметров и обычно ограничивают результат с помощью добавления предложения WHERE в инструкцию SELECT, вызывающую представление. В качестве примера рассмотрим функцию возвращающую список клиентов из заданного города.
CREATE FUNCTION [dbo].[fCustomersForCity] (@IdCity int = NULL)TABLE
(IdCust, FName, LNameCustomerIdCity = @IdCity OR @IdCity IS NULL
)
Если функция вызывается с параметром по умолчанию, то возвращается список всех клиентов:
SELECT *dbo.fCustomersForCity(DEFAULT)
Если же в качестве параметра передается уникальный идентификатор города, то скомпилированная инструкция SELECT в функции вернет только клиентов из города с заданным кодом:*dbo.fCustomersForCity(1)
Табличные функции с множеством инструкций
Пользовательские табличные функции с множеством инструкций комбинируют способность скалярных функций содержать сложный программный код со способностью внедренных табличных функций возвращать результирующий набор данных. Этот тип функций создает табличную переменную, а затем заполняет ее в теле функции. Сформированная таблица впоследствии возвращается функцией и может использоваться в инструкциях SELECT.
Главным преимуществом таких функций является то, что результирующий набор данных может формироваться в пакете инструкций, а затем напрямую использоваться в инструкциях SELECT. Это позволяет использовать этот тип функций вместо хранимых процедур.
Синтаксис, используемый для создания табличных функций с множеством инструкций, практически такой же, как и для создания скалярных функций:FUNCTION имя_функиии (входные_параметры) RETURNS @имя_таблицы TABLE (столбцы) AS BEGIN Программный код заполнения табличной переменной RETURN END
Следующая процедура позволяет создать табличную пользовательскую функцию с множеством инструкций, которая возвращает основной результирующий набор данных.
В начале инструкции CREATE FUNCTION создается табличная переменная.
В теле функции с помощью инструкций INSERT заполняют переменную.
После выполнения функции значение табличной переменной передается во внешнюю процедуру как результат функции.
Запишем предыдущую функцию в виде многооператорной функции.
CREATE FUNCTION [dbo].[fCustomersByCity2]
(
@IdCity int = NULL
)
@Result TABLE