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

В следующих  трех примерах продемонстрированы вызовы хранимых процедур и передача им параметров с использованием исходного порядка  и имен:

EXEC spr_getOrders '01.01.2010', '01.07.2010'

 

EXEC spr_getOrders

@dateBegin = '01.01.2010',

@dateEnd = '01.07.2010'

 

EXEC spr_getOrders '01.01.2010', @dateEnd = '01.07.2010'

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

Применение выходных параметров

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

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

CREATE PROCEDURE spr_addProduct

  @Description nvarchar(100),

  @InStock int = 0,

  @IdProd int OUT

AS

 

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

 

SET @IdProd = @@IDENTITY

 

RETURN

Пример вызова:

DECLARE @IdProd int

 

EXEC spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

 

SELECT @IdProd as N'@IdProd'

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

Подтверждение успешного или неудачного завершения работы с помощью возвращаемых значений. Использование команды RETURN.

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

Чтобы передать некоторое возвращаемое значение из хранимой процедуры обратно в  вызывающий код, достаточно применить  оператор RETURN:

RETURN [<Целое число>]

Обратите  внимание на то, что возвращаемое значение должно быть обязательно целочисленным.

Возвращаемые  значения предназначены исключительно  для указания на успешное или неудачное  завершение хранимой процедуры и  позволяют даже обозначить степень  или характер успеха или неудачи. Использование возвращаемого значения для возврата фактических данных, таких как идентификационное значение или данные о количестве строк, затронутых хранимой процедурой, рассматривается как недопустимая практика программирования. Возвращаемое значение 0 указывает на успешное выполнение процедуры и установлено по умолчанию. Компания Microsoft зарезервировала значения от -99 до -1 для служебного пользования. Разработчикам для возвращения состояния ошибки пользователю рекомендуется использовать значения -100 и меньше.

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

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

ALTER PROCEDURE [dbo].[spr_addProduct]

  @Description nvarchar(100),

  @InStock int = 0,

  @IdProd int OUT

AS

 

IF EXISTS(SELECT * FROM Product WHERE [Description] = @Description)

  RETURN -100

 

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

 

SET @IdProd = @@IDENTITY

 

RETURN 0

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

EXEC @локальная_переменная = имя_хранимой_процедуры;

DECLARE @return_value int,

@IdProd int

 

EXEC @return_value = spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

 

IF @return_value = 0

  BEGIN

    PRINT 'Товар успешно добавлен'

    SELECT @IdProd as N'@IdProd'

  END

ELSE

  BEGIN

    PRINT 'При добавлении товара произошла ошибка'

    SELECT 'Return Value' = @return_value

  END

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

• Возврат списка всех заказов содержащих заданный товар (по IdProd).
• Определение количества клиентов, не имеющих ни одного заказа. Результат должен возвращаться через выходной параметр.
• Удаление из базы данных информации об определенном клиенте (по IdCust). Если с данных клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции. 
  

Лабораторная  работа №9: Функции

Системные функции

SQL Server содержит богатый набор встроенных системных функций, которые формально подразделяются на следующие группы: статистические, функции настройки, функции работы с курсором, функции даты и времени, математические, функции работы с наборами строк, функции безопасности, строковые, системные статистические, функции обработки текста и изображений и прочие. Полный список системных функций, сгруппированных в отдельные папки по вышеуказанным категориям, можно увидеть в Management Studio в узле «Программирование - Функции - Системные функции» дерева обозревателя объектов. Рассмотрим некоторые из наиболее часто используемых скалярных (возвращающих одно значение) встроенных системных функций.

Информационные функции

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

• User_name(). Возвращает имя текущего клиента, каким он представился базе данных. Когда пользователю открыт доступ к базе данных, его имя может отличаться от регистрационного имени входа на сервер.
• Suser_sname(). Возвращает регистрационное имя пользователя, под которым он вошел на SQL Server. Даже если тот был аутентифицирован как член одной из групп пользователей Windows, функция все равно возвращает имя его учетной записи Windows.
• Host_name(). Возвращает имя рабочей станции пользователя.
• App_name(). Возвращает имя приложения, подключенного к SQL Server.

Пример использования:

SELECT

USER_NAME() AS 'Имя пользователя БД',

SUSER_SNAME() AS 'Имя входа',

HOST_NAME() AS 'Имя рабочей станции',

APP_NAME() AS 'Имя приложения'

Строковые функции

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

• Substring(строка, начальная_позиция, длина). Возвращает фрагмент строки. Первым параметром является сама строка, вторым — номер символа, с которого вырезается фрагмент, третьим — длина вырезаемого фрагмента. Например, результатом инструкции SELECT SUBSTRING('abcdefg', 3, 2) будет подстрока ‘cd’.
• Stuff(строка, позиция_вставки, число_удаляемых_символов, вставляемая_строка). Противоположная по характеру функции substring(), функция stuff() вставляет одну строку в другую; при этом в позиции вставки может быть удалено заданное количество символов исходной строки. Например, результатом инструкции SELECT STUFF('abcdefg', 3, 2, '123') будет строка 'ab123efg'.
• Replace(строка, строка). Заменяет заданные фрагменты строки другой строкой. Например, функция REPLACE('abacad', 'a', 'e') возвращает строку ‘ebeced’
• Charindex(символ_поиска, строка, начальная_позиция). Возвращает позицию заданного символа в строке. Например, инструкция SELECT CHARINDEX('c', 'abcdefg', 1) вернет результат 3.
• Patindex(%шабпон%, строка). Выполняет поиск по шаблону, который может содержать в строке символы макроподстановки. В следующем примере ищется первое вхождение в строку символа с или d: SELECT PATINDEX('%[cd]%', 'abdedefg'). Результатом данного запроса будет число 3.
• Right(строка, число) и Left(строка, число). Возвращает крайнюю правую или левую часть строки. Например, результатом запроса SELECT LEFT('abcdefg',2) будет ‘ab’
• Len(строка). Возвращает длину строки.
• Rtrim(строка) и Ltrim(строка). Эти функции удаляют соответственно пробелы в начале и в конце строки.
• Upper(строка) и Lower(строка). Преобразует символы строки соответственно в верхний или нижний регистр.

Функции работы с датой и временем

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

Константа	Значение
yy или yyyy	Год
qq или q	Квартал
mm или m	Месяц
wk или ww	Неделя
dw или w	День недели
dy или y	День года
dd или d	День
hh	Час
mi или n	Минута
ss или s	Секунда
ms	Миллисекунда

Например, функция DATEADD принимает в качестве аргументов маркер datepart, величину приращения и исходную дату. Она возвращает результат добавления указанной величины в единицах, указанных в аргументе datepart, к текущей дате. Таким образом, чтобы добавить к текущей дате три дня, вы можете использовать следующий код:

PRINT DATEADD(d, 3, GETDATE())

Ниже приведен полный список доступных функций  даты и времени.

• Dateadd(datepart, величина, дата_начала). Добавляет к дате указанную величину.
• Datediff(datepart, величина, дата_начала). Выводит количество единиц времени, заданных в аргументе datepart, между двумя датами.
• Datename(datepart, дата). Возвращает текстовые имена (например, имя месяца или дня недели), соответствующие заданной дате.
• Datepart(datepart, дата). Извлекает определенный фрагмент из заданной даты.
• Day(дата). Извлекает день из даты.
• Getdate. Возвращает текущее время и дату.
• Getutcdate. Возвращает текущую дату и время, преобразованное в формат универсального синхронизированного времени (UTC).
• Month(дата). Извлекает месяц из даты.
• Year(дата). Извлекает год из даты.

В следующем  примере запрос возвращает список всех заказов сделанных в сентябре месяце с указанием дня недели:

SELECT *, DATENAME(dw, OrdDate) AS 'День недели'

FROM [Order]

WHERE MONTH(OrdDate) = 9

Функции преобразования данных

Для явных  преобразований одно типа данных в  другой в SQL Server используют функции cast() и convert().

• Cast(исходные_данные AS тип_данных). Стандарт ANSI SQL рекомендует явное преобразование одного типа данных в другой. Даже если такое преобразование может быть выполнено неявно сервером, использование функции cast() гарантирует получение нужного типа. Функция cast() программируется несколько отлично от других. Вместо разделения двух своих аргументов запятой используется ключевое слово AS, за которым следует требуемый тип данных, например:

SELECT [Description], 'Остаток на складе: ' + CAST(InStock AS varchar(10))

FROM Product

• Convert(тип_данных, выражение [, стиль]). Эта функция возвращает значение, преобразованное в другой тип данных с произвольным форматированием. Эта функция не предусмотрена стандартом ANSI SQL. Первым ее аргументом является желаемый тип данных, применяемый к выражению. Аргумент стиль предполагает применение к результату некоторого стиля. Стиль обычно применяется при преобразовании из типа даты-времени в символьный и наоборот. Как правило, одно- или двухцифровой стиль предполагает двухцифровой год, а трехцифровой — четырехцифровой год. К примеру, стиль 1 подразумевает следующий формат данных: 01/01/03, в тоже время стиль 3 — 01/01/2003. Пример: