Создание баз данных

SELECT CONVERT(nvarchar(25), GETDATE(), 1)

SELECT CONVERT(nvarchar(25), GETDATE(), 100)

Функции для обработки пустых значений

Часто пустое значение нужно преобразовать в  некоторое допустимое, чтобы данные можно было понять или чтобы выражение  имело результат. Пустые значения требуют  специальной обработки при использовании  в выражениях, и язык SQL содержит ряд функций, специально предназначенных  для работы с пустыми значениями. Функции isnull () и coalesce() преобразуют пустые значения в пригодные для использования, а функция nullif() создает пустое значение, если выполняется определенное условие.

Наиболее  часто используемой функцией, предназначенной  для работы с пустыми значениями, является isnull(). Эта функция в качестве аргумента принимает одно выражение или столбец, а также подстановочное значение. Если первый аргумент является допустимым значением (т.е. не пустым), эта функция возвращает его. Однако если первый аргумент представляет собой пустое значение, то возвращается значение второго аргумента. Общий синтаксис функции следующий:

Isnull(исходное_выражение, замещающее_значение).

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

SELECT FName, LName, ISNULL(Phone, 'не указан') AS Phone

FROM Customer

Функция coalesce() принимает список выражений или столбцов и возвращает первое значение, которое окажется не пустым. Ее общий синтаксис следующий:

Coalesce(выражение, выражение, ...)

В следующем  примере продемонстрирована функция  coalesce(), возвращающая первое непустое значение (в данном случае это 3): SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc'))

Иногда пустое значение нужно создать на месте  заменяющего его суррогатного. Если база данных заполнена значениями n/a, - или пустыми строками там, где должны находиться пустые значения, вы можете воспользоваться функцией nullif() и расчистить базу данных.

Функция nullif() принимает два аргумента. Если они равны, то возвращается пустое значение, в противном случае возвращается первый параметр.

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

SELECT NULLIF(LTRIM(RTRIM(FName)),'') AS FName

FROM Customer

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

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

Пользовательские функции обладают следующими преимущества:

• С их помощью можно внедрить в запросы сложную логику.
• Создавая новые функции, можно проектировать сложные выражения.
• Эти функции обладают всеми достоинствами представлений, поскольку могут использоваться в предложении FROM инструкции SELECT и в выражениях и могут быть задействованы в схеме. К тому же пользовательские функции могут принимать параметры, в то время как представления — нет.
• Они обладают достоинствами хранимых процедур, так как могут быть скомпилированы и оптимизированы таким же способом.

Главным аргументом против использования пользовательских функций является вопрос переносимости. Пользовательские функции привязаны к SQL Server, и любую базу данных, использующую множество таких функций, будет сложно или даже невозможно перенести на другую платформу СУБД без существенной переработки. Эта задача усложняется тем, что также должны быть переписаны и все инструкции SELECT, в которые внедрены пользовательские функции. Если в будущем планируется развертывание базы данных на других платформах, то лучше заменить все пользовательские функции представлениями или хранимыми процедурами.

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

• Пользовательские функции могут возвращать значения, относящиеся к большинству типов данных SQL Server. Не допускается использовать в качестве типов возвращаемых значений лишь такие типы, как text, ntext, image, cursor и timestamp.
• Пользовательские функции не должны иметь побочных эффектов. По существу, в пользовательских функциях не допускается выполнение каких-либо действий, выходящих за пределы действия самой функции. Например, в них нельзя модифицировать таблицы, отправлять электронную почту и вносить изменения в значения параметров системы или базы данных.

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

Пользовательские  функции подразделяются на три типа:

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

Скалярные функции

Скалярными  называют те функции, которые возвращают одно значение. Эти функции могут принимать множество параметров, выполнять вычисления, но в результате выдают одно значение. Эти функции могут использоваться в любых выражениях, даже участвующих в ограничениях проверки. Значение возвращается функцией с помощью оператора return — эта команда должна завершать скалярную функцию.

В скалярных  пользовательских функциях не допускаются  операции обновления базы данных, но в  то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких  типов, как text, image и ntext, равно как табличные переменные и курсоры.

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

CREATE FUNCTION имя_функции (входные_параметры) 
RETURNS тип_данных 
AS 
BEGIN 
текст_ функции 
RETURN выражение 
END

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

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

CREATE FUNCTION dbo.Multiply (@A int, @B int = 3)

RETURNS INT

BEGIN

  RETURN @A * @B

END

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

SELECT dbo.Multiply(3,4)

SELECT dbo.Multiply(7, DEFAULT)

Следующий код  создает функцию, возвращающую имя заданного клиента в формате Фамилия И.

CREATE FUNCTION getFICust (@IdCust int)

RETURNS varchar(25)

AS

BEGIN

  DECLARE @result varchar(25)

  SET @result = 'NULL'

 

  SELECT @result = LName + ' ' + SUBSTRING(FName, 1, 1) + '.'

  FROM Customer

  WHERE IdCust = @IdCust

 

  RETURN @result

END

Тестирование созданной функции:

SELECT dbo.getFICust(IdCust) AS CustName

FROM Customer

ORDER BY LName, FName

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

• Количество товара на складе по заданному уникальному идентификатору товара;
• Суммарную стоимость товаров в заданном заказе.

Внедренные табличные функции

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

Внедренная  табличная функция не имеет в  своем теле блока BEGIN ... END — вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем:

CREATE FUNCTION имя_функции (параметры) 
RETURNS Table AS 
RETURN (инструкция_SELECT)

Следующая внедренная табличная функция является функциональным эквивалентом представления v_Customer созданного в лаб. занятии №6.

CREATE FUNCTION fCustomers ()

RETURNS TABLE

AS

RETURN

(

  SELECT Customer.IdCust, Customer.FName, Customer.LName, City.CityName

  FROM Customer INNER JOIN

         City ON Customer.IdCity = City.IdCity

)

Для извлечения данных с помощью функции fCustomers вызовите ее в предложении FROM инструкции SELECT:

SELECT *

FROM dbo.fCustomers()

ORDER BY LName, FName

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

CREATE FUNCTION [dbo].[fCustomersForCity] (@IdCity int = NULL)

RETURNS TABLE

AS

RETURN

(

  SELECT IdCust, FName, LName

  FROM Customer

  WHERE IdCity = @IdCity OR @IdCity IS NULL

)

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

SELECT *

FROM dbo.fCustomersForCity(DEFAULT)

Если же в качестве параметра передается уникальный идентификатор города, то скомпилированная инструкция SELECT в функции вернет только клиентов из города с заданным кодом:

SELECT *

FROM dbo.fCustomersForCity(1)

Табличные функции с множеством инструкций

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

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

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

CREATE FUNCTION имя_функиии (входные_параметры) 
RETURNS @имя_таблицы TABLE (столбцы) 
AS 
BEGIN 
  Программный код заполнения табличной переменной 
  RETURN 
END

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

1. В начале инструкции CREATE FUNCTION создается табличная переменная.
2. В теле функции с помощью инструкций INSERT заполняют переменную.
3. После выполнения функции значение табличной переменной передается во внешнюю процедуру как результат функции.

Запишем предыдущую функцию в виде многооператорной функции.

CREATE FUNCTION [dbo].[fCustomersByCity2]

(

  @IdCity int = NULL

)

RETURNS

@Result TABLE

(

  IdCust int,

  FName nvarchar(20),

  LName nvarchar(20)

)

AS

BEGIN

  IF (@IdCity IS NULL)

    INSERT @Result

    SELECT IdCust, FName, LName

    FROM Customer

  ELSE

    INSERT @Result

    SELECT IdCust, FName, LName

    FROM Customer

    WHERE IdCity = @IdCity

 

  RETURN

END

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

• Список всех товаров, которые не были ни разу заказаны
• Список всех заказов за заданный период времени.

 

Лабораторная  работа №10: Обработка ошибок. Управление транзакциями. Триггеры.

Обработка ошибок. Блок TRY…CATCH.

Стандартным способом перехвата и обработки ошибок в Transact-SQL (начиная с версии SQL Server 2005) является использование конструкции TRY...CATCH, который напоминает обработку исключений, применяемую во многих языках программирования (Delphi, C++, C# и т.д.).

Общий синтаксис  конструкции TRY...CATCH следующий:

BEGIN TRY 
     { инструкции T-SQL } 
END TRY 
BEGIN CATCH 
     [ { инструкции T-SQL } ] 
END CATCH 
[ ; ]

Конструкция TRY…CATCH состоит из двух частей: блок TRY и блок CATCH. При обнаружении ошибки в инструкции T-SQL внутри блока TRY управление передается блоку CATCH, где эта ошибка может быть обработана.

Блок TRY начинается с инструкции BEGIN TRY и завершается инструкцией END TRY. Между ними могут быть помещены одна или несколько инструкций T-SQL, при выполнении которых может произойти ошибка.

За блоком TRY сразу же должен следовать блок обработки ошибок CATCH. Блок CATCH начинается с инструкции BEGIN CATCH и завершается инструкцией END CATCH. В Transact-SQL каждый блок TRY ассоциирован только с одним блоком CATCH.

По завершении обработки исключения блоком CATCH управление передается первой инструкции T-SQL, следующей за инструкцией END CATCH. Если инструкция END CATCH является последней инструкцией хранимой процедуры или триггера, управление возвращается коду, вызвавшему эту хранимую процедуру или триггер. Инструкции T-SQL в блоке TRY, следующие за инструкцией, вызвавшей ошибку, не выполняются.